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
[edit | edit source]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 |
... | ... | ... | ... | ... | ... |
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 |
... | ... | ... | ... | ... | ... |
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 |
... | ... | ... | ... | ... | ... |
The DBA wants to allow DEVELOPER_1 to select columns on the table employee
:
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 |
... | ... | ... | ... | ... | ... |
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
:
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 |
... | ... | ... | ... | ... | ... |
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:
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 |
... | ... | ... | ... | ... | ... |
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
:
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 |
... | ... | ... | ... | ... | ... |
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
:
GRANT INSERT ON TABLE office TO USER_2;
|
DBA |
Developer 1 |
Developer 2 |
Developer 3 |
User 1 |
User 2 |
... | ... | ... | ... | ... | ... |
The operation has been refused because DEVELOPER_3 hasn't enough privileges.
Sending privileges on columns
[edit | edit source]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
[edit | edit source]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:
REVOKE INSERT, DELETE ON TABLE office FROM DEVELOPER_2, DEVELOPER_3;
|
DBA |
Developer 1 |
Developer 2 |
Developer 3 |
User 1 |
User 2 |
... | ... | ... | ... | ... | ... |
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:
REVOKE GRANT OPTION FOR SELECT ON TABLE employee FROM USER_1;
|
DBA |
Developer 1 |
Developer 2 |
Developer 3 |
User 1 |
User 2 |
... | ... | ... | ... | ... | ... |
Now you can administrate a database.