DATA CONTROL LANGUAGE

Access Control

SQL features a three level access control model consisting of login, user, and permission levels. The login level allows access to an instance of sqlserver with the assignment of id and password to a user with the CREATE LOGIN statement or to terminate access with the DROP LOGIN statement. Logins can be either Windows logins or SQL id/passwords. The user level allows a given login access to one or more databases with the CREATE USER statement or to terminate access with the DROP USER statement. Finally, individual users are granted some combination of permissions to select, insert, update, or delete rows from tables within the database with the GRANT statement or to remove those permissions with the REVOKE statement.

CREATE LOGIN Statement

The CREATE LOGIN statement adds a specified user to a database. It has the following general format:

CREATE LOGIN id WITH PASSWORD = 'password'

CREATE LOGIN Examples

DROP LOGIN Statement

The DROP LOGIN statement removes a specified user from a database. It has the following general format

DROP id

DROP LOGIN Examples

CREATE USER Statement

The CREATE USER statement adds a specified user to a database. It has the following general format:

CREATE USER username

CREATE USER Examples

DROP USER Statement

The DROP USER statement removes a specified user from a database. It has the following general format

DROP USER username

DROP USER Examples

GRANT Statement

The GRANT Statement grants access privileges for database objects to other users. It has the following general format:

GRANT privilege-list ON [TABLE] object-list TO user-list

privilege-list is either ALL PRIVILEGES or a comma-separated list of properties: SELECT, INSERT, UPDATE, DELETE. object-list is a comma-separated list of table and view names. user-list is either PUBLIC or a comma-separated list of user names.

The GRANT statement grants each privilege in privilege-list for each object (table) in object-list to each user in user-list. In general, the access privileges apply to all columns in the table or view, but it is possible to specify a column list with the UPDATE privilege specifier:

UPDATE [ ( column-1 [, column-2] ... ) ]

If the optional column list is specified, UPDATE privileges are granted for those columns only.

The user-list may specify PUBLIC. This is a general grant, applying to all users (and future users) in the catalog.

Privileges granted are revoked with the REVOKE statement.

The optional specificier WITH GRANT OPTION may follow user-list in the GRANT statement. WITH GRANT OPTION specifies that, in addition to access privileges, the privilege to grant those privileges to other users is granted.

GRANT Examples

REVOKE Statement

The REVOKE Statement revokes access privileges for database objects previously granted to other users. It has the following general format:

REVOKE privilege-list ON [TABLE] object-list FROM user-list

The REVOKE Statement revokes each privilege in privilege-list for each object (table) in object-list from each user in user-list. All privileges must have been previously granted.

The user-list may specify PUBLIC. This must apply to a previous GRANT TO PUBLIC.

REVOKE Examples