Structured Query Language/Data Control Language

Data Control Language is used to modify the user rights for the database. It describes two statements: GRANT and REVOKE.

GRANT statement

The exhaustive syntax of the GRANT statement is as follows:

GRANT
{
 ALL PRIVILEGES ON[ TABLE] <table or view name>
|
 {SELECT|DELETE|{INSERT|UPDATE}[ (<column name>[, <column name>]*)]}
 [, {SELECT|DELETE|{INSERT|UPDATE}[ (<column name>[, <column name>]*)]}]* ON[ TABLE] <table or view name>
|
 USAGE ON 
 {DOMAIN <domain name>|COLLATION <collation name>|CHARACTER SET <charset name>|TRANSLATION <translation name>}
|
 REFERENCES <column name>[, <column name>]* ON <table name>
}
[,
 {
  ALL PRIVILEGES ON[ TABLE] <table or view name>
 |
  {SELECT|DELETE|{INSERT|UPDATE}[ (<column name>[, <column name>]*)]}
  [ ,{SELECT|DELETE|{INSERT|UPDATE}[ (<column name>[, <column name>]*)]}]* ON[ TABLE] <table or view name>
 |
  USAGE ON 
  {DOMAIN <domain name>|COLLATION <collation name>|CHARACTER SET <charset name>|TRANSLATION <translation name>}
 |
  REFERENCES <column name>[, <column name>]* ON <table name>
 }
]* TO {PUBLIC|<user name>}[, {PUBLIC|<user name>}]*[ WITH GRANT OPTION];

The GRANT statement is used to give a privilege to someone. Any SQL operations are done using a user name. The user name are created by the database management system.

DBA

Developer 1

Developer 2

Developer 3

User 1

User 2

The privileges apply on the tables (i.e. employee, office, etc...), the views, their columns, the domain, the collation, the charset and the translation.

DBA	Developer 1	Developer 2	Developer 3	User 1	User 2
employee	employee	employee	employee	employee	employee
office	office	office	office	office	office
...	...	...	...	...	...

The privileges can allow to process SELECT ("s"), INSERT ("i"), UPDATE ("u") and DELETE ("d") statements (not CREATE, ALTER or DROP statements). Let's say that only the DataBase Administrator has the privileges.

DBA	Developer 1	Developer 2	Developer 3	User 1	User 2
employee s i u d	employee	employee	employee	employee	employee
office s i u d	office	office	office	office	office
...	...	...	...	...	...

For each privilege ("s", "i", "u" and "d"), there is also a meta-privilege ("S", "I", "U" and "D") : a user can send a privilege to another user. Let's say that only the DataBase Administrator has the meta-privileges.

DBA	Developer 1	Developer 2	Developer 3	User 1	User 2
employee S I U D s i u d	employee	employee	employee	employee	employee
office S I U D s i u d	office	office	office	office	office
...	...	...	...	...	...

The DBA wants to allow DEVELOPER_1 to select columns on the table employee:

DBA	GRANT SELECT ON employee TO DEVELOPER_1;

The rights for DEVELOPER_1 have changed:

DBA	Developer 1	Developer 2	Developer 3	User 1	User 2
employee S I U D s i u d	employee s	employee	employee	employee	employee
office S I U D s i u d	office	office	office	office	office
...	...	...	...	...	...

SELECT indicates that we want to sent the SELECT privilege. The keyword ON followed by employee indicates that the privilege applies on the table employee. The keyword TO followed by DEVELOPER_1 indicates that the privilege has been sent to DEVELOPER_1.

The DBA wants to allow DEVELOPER_2 and DEVELOPER_3 to insert, update and delete rows on the table office:

DBA	GRANT INSERT, UPDATE, DELETE ON office TO DEVELOPER_2 DEVELOPER_3;

The rights for DEVELOPER_2 and DEVELOPER_3 have changed:

DBA	Developer 1	Developer 2	Developer 3	User 1	User 2
employee S I U D s i u d	employee s	employee	employee	employee	employee
office S I U D s i u d	office	office i u d	office i u d	office	office
...	...	...	...	...	...

Whereas you can send several privileges on a table to several users at once, you can't send privileges on several tables at once. If you want to send all the privileges (SELECT, INSERT, UPDATE and DELETE), you can replace the list of privileges by the keywords ALL PRIVILEGES.

Now, the DBA wants to allow USER_1 to insert on the table employee and allow him to send this privilege to other users:

DBA	GRANT INSERT ON employee TO USER_1 WITH GRANT OPTION;

The rights for USER_1 have changed:

DBA	Developer 1	Developer 2	Developer 3	User 1	User 2
employee S I U D s i u d	employee s	employee	employee	employee S s	employee
office S I U D s i u d	office	office i u d	office i u d	office	office
...	...	...	...	...	...

The keyword WITH GRANT OPTION indicates that we want to send privileges with the meta-privileges to the user. Now, USER_1 can send the SELECT privilege on the table employee to the other users. Let's say that USER_1 wants to allow anyone to process SELECT on the table employee:

User 1

GRANT SELECT ON TABLE employee TO PUBLIC;

The rights of all the users have changed:

DBA	Developer 1	Developer 2	Developer 3	User 1	User 2
employee S I U D s i u d	employee s	employee s	employee s	employee S s	employee s
office S I U D s i u d	office	office i u d	office i u d	office	office
...	...	...	...	...	...

The keyword PUBLIC indicates that we want to send privileges to all the users and the new future ones.

Let's say that DEVELOPER_3 tries to allow USER_2 to insert records into the table office:

Developer 3

GRANT INSERT ON TABLE office TO USER_2;

DBA	Developer 1	Developer 2	Developer 3	User 1	User 2
employee S I U D s i u d	employee s	employee s	employee s	employee S s	employee s
office S I U D s i u d	office	office i u d	office I i u d	office	office
...	...	...	...	...	...

The operation has been refused because DEVELOPER_3 hasn't enough privileges.

Sending privileges on columns

You can send privileges on columns only (only for INSERT and UPDATE):

GRANT INSERT (name, description) ON TABLE office TO USER_2;
GRANT UPDATE (id_office, name) ON TABLE office TO USER_2;

For INSERT, make all the columns that the user can't fill have default values, are automatically generated or are filled by a trigger before the insertion. Otherwise, the privilege is just useless.

REVOKE statement

The exhaustive syntax of the REVOKE statement is as follows:

REVOKE[ GRANT OPTION FOR] 
{
 ALL PRIVILEGES ON[ TABLE] <table or view name>
|
 {SELECT|DELETE|{INSERT|UPDATE}[ (<column name>[, <column name>]*)]}
 [, {SELECT|DELETE|{INSERT|UPDATE}[ (<column name>[, <column name>]*)]}]* ON[ TABLE] <table or view name>
|
 USAGE ON 
 {DOMAIN <domain name>|COLLATION <collation name>|CHARACTER SET <charset name>|TRANSLATION <translation name>}
|
 REFERENCES <column name>[, <column name>]* ON <table name>
}
[,
 {
  {SELECT|DELETE|{INSERT|UPDATE}[ (<column name>[, <column name>]*)]}
  [, {SELECT|DELETE|{INSERT|UPDATE}[ (<column name>[, <column name>]*)]}]* ON[ TABLE] <table or view name>
 |
  USAGE ON 
  {DOMAIN <domain name>|COLLATION <collation name>|CHARACTER SET <charset name>|TRANSLATION <translation name>}
 |
  REFERENCES <column name>[, <column name>]* ON <table name>
 }
]* FROM {PUBLIC|<user name>}[, {PUBLIC|<user name>}]*[ RESTRICT| CASCADE]

The REVOKE statement is used to take back privileges granted to someone. This revocation may be more complicated than you expect. To completely remove a privilege to a user, this privilege must be taken back by all the users that have sent the privilege.

For instance, the DBA wants to remove the INSERT and DELETE privileges on the table employee to DEVELOPER_2 and DEVELOPER_3:

DBA	REVOKE INSERT, DELETE ON TABLE office FROM DEVELOPER_2, DEVELOPER_3;

DBA	Developer 1	Developer 2	Developer 3	User 1	User 2
employee S I U D s i u d	employee s	employee s	employee s	employee S s	employee s
office S I U D s i u d	office	office u	office u	office	office
...	...	...	...	...	...

If you remove a privilege to a user who was also sent the related meta-privilege (for example, SELECT privilege to USER_1), the operation also removes the meta-privilege. To remove only meta-privileges, add the keywords GRANT OPTION FOR:

DBA	REVOKE GRANT OPTION FOR SELECT ON TABLE employee FROM USER_1;

DBA	Developer 1	Developer 2	Developer 3	User 1	User 2
employee S I U D s i u d	employee s	employee s	employee s	employee s	employee s
office S I U D s i u d	office	office u	office u	office	office
...	...	...	...	...	...

Now you can administrate a database.