Last modified on 11 October 2013, at 18:15

Structured Query Language/Data Control Language

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

GRANT statementEdit

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.

Nuvola apps personal.svg
DBA
Nuvola apps personal unisex.svg
Developer 1
Nuvola apps personal unisex.svg
Developer 2
Nuvola apps personal unisex.svg
Developer 3
User.svg
User 1
User.svg
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.

Nuvola apps personal.svg
DBA
Nuvola apps personal unisex.svg
Developer 1
Nuvola apps personal unisex.svg
Developer 2
Nuvola apps personal unisex.svg
Developer 3
User.svg
User 1
User.svg
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.

Nuvola apps personal.svg
DBA
Nuvola apps personal unisex.svg
Developer 1
Nuvola apps personal unisex.svg
Developer 2
Nuvola apps personal unisex.svg
Developer 3
User.svg
User 1
User.svg
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.

Nuvola apps personal.svg
DBA
Nuvola apps personal unisex.svg
Developer 1
Nuvola apps personal unisex.svg
Developer 2
Nuvola apps personal unisex.svg
Developer 3
User.svg
User 1
User.svg
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:

Nuvola apps personal.svg
DBA
GRANT SELECT ON employee TO DEVELOPER_1;

The rights for DEVELOPER_1 have changed:

Nuvola apps personal.svg
DBA
Nuvola apps personal unisex.svg
Developer 1
Nuvola apps personal unisex.svg
Developer 2
Nuvola apps personal unisex.svg
Developer 3
User.svg
User 1
User.svg
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:

Nuvola apps personal.svg
DBA
GRANT INSERT, UPDATE, DELETE ON office TO DEVELOPER_2 DEVELOPER_3;

The rights for DEVELOPER_2 and DEVELOPER_3 have changed:

Nuvola apps personal.svg
DBA
Nuvola apps personal unisex.svg
Developer 1
Nuvola apps personal unisex.svg
Developer 2
Nuvola apps personal unisex.svg
Developer 3
User.svg
User 1
User.svg
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:

Nuvola apps personal.svg
DBA
GRANT INSERT ON employee TO USER_1 WITH GRANT OPTION;

The rights for USER_1 have changed:

Nuvola apps personal.svg
DBA
Nuvola apps personal unisex.svg
Developer 1
Nuvola apps personal unisex.svg
Developer 2
Nuvola apps personal unisex.svg
Developer 3
User.svg
User 1
User.svg
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.svg
User 1
GRANT SELECT ON TABLE employee TO PUBLIC;

The rights of all the users have changed:

Nuvola apps personal.svg
DBA
Nuvola apps personal unisex.svg
Developer 1
Nuvola apps personal unisex.svg
Developer 2
Nuvola apps personal unisex.svg
Developer 3
User.svg
User 1
User.svg
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:

Nuvola apps personal unisex.svg
Developer 3
GRANT INSERT ON TABLE office TO USER_2;
Nuvola apps personal.svg
DBA
Nuvola apps personal unisex.svg
Developer 1
Nuvola apps personal unisex.svg
Developer 2
Nuvola apps personal unisex.svg
Developer 3
User.svg
User 1
User.svg
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 columnsEdit

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 statementEdit

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:

Nuvola apps personal.svg
DBA
REVOKE INSERT, DELETE ON TABLE office FROM DEVELOPER_2, DEVELOPER_3;
Nuvola apps personal.svg
DBA
Nuvola apps personal unisex.svg
Developer 1
Nuvola apps personal unisex.svg
Developer 2
Nuvola apps personal unisex.svg
Developer 3
User.svg
User 1
User.svg
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:

Nuvola apps personal.svg
DBA
REVOKE GRANT OPTION FOR SELECT ON TABLE employee FROM USER_1;
Nuvola apps personal.svg
DBA
Nuvola apps personal unisex.svg
Developer 1
Nuvola apps personal unisex.svg
Developer 2
Nuvola apps personal unisex.svg
Developer 3
User.svg
User 1
User.svg
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.