# 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 statement

The exhaustive syntax of the GRANT statement is as follows:
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 siud | employee | employee | employee | employee | employee |
| office siud | 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 SIUD siud | employee | employee | employee | employee | employee |
| office SIUD siud | 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 SIUD siud | employee s | employee | employee | employee | employee |
| office SIUD siud | 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 SIUD siud | employee s | employee | employee | employee | employee |
| office SIUD siud | office | office iud | office iud | 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 SIUD siud | employee s | employee | employee | employee S s | employee |
| office SIUD siud | office | office iud | office iud | 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 SIUD siud | employee s | employee s | employee s | employee S s | employee s |
| office SIUD siud | office | office iud | office iud | 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 SIUD siud | employee s | employee s | employee s | employee S s | employee s |
| office SIUD siud | office | office iud | office I iud | 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):
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:
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 SIUD siud | employee s | employee s | employee s | employee S s | employee s |
| office SIUD siud | 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 SIUD siud | employee s | employee s | employee s | employee s | employee s |
| office SIUD siud | office | office u | office u | office | office |
Now you can administrate a database.