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:

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.

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

{| class="wikitable" style="text-align: center; width: 100%;"
 * Nuvola apps personal.svg]] DBA Developer 1 || Nuvola apps personal unisex.svg]] Developer 2 Developer 3 || User.svg]] User 1  User 2
 * - style="border-bottom: hidden"
 * - style="border-bottom: hidden"






 * - style="border-bottom: hidden"
 * - style="border-bottom: hidden"
 * - style="border-bottom: hidden"






 * }
 * }
 * }
 * }

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.

{| class="wikitable" style="text-align: center; width: 100%;"
 * Nuvola apps personal.svg]] DBA Developer 1 || Nuvola apps personal unisex.svg]] Developer 2 Developer 3 || User.svg]] User 1  User 2
 * - style="border-bottom: hidden"
 * - style="border-bottom: hidden"




 * - style="border-bottom: hidden"
 * - style="border-bottom: hidden"
 * - style="border-bottom: hidden"




 * }
 * }
 * }
 * }

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.

{| class="wikitable" style="text-align: center; width: 100%;"
 * Nuvola apps personal.svg]] DBA Developer 1 || Nuvola apps personal unisex.svg]] Developer 2 Developer 3 || User.svg]] User 1  User 2
 * - style="border-bottom: hidden"
 * - style="border-bottom: hidden"




 * - style="border-bottom: hidden"
 * - style="border-bottom: hidden"
 * - style="border-bottom: hidden"




 * }
 * }
 * }
 * }

The DBA wants to allow DEVELOPER_1 to select columns on the table : The rights for DEVELOPER_1 have changed: {| class="wikitable" style="text-align: center; width: 100%;"
 * Nuvola apps personal.svg]] DBA Developer 1 || Nuvola apps personal unisex.svg]] Developer 2 Developer 3 || User.svg]] User 1  User 2
 * - style="border-bottom: hidden"
 * - style="border-bottom: hidden"






 * - style="border-bottom: hidden"
 * - style="border-bottom: hidden"
 * - style="border-bottom: hidden"








 * }
 * }
 * }
 * }

SELECT indicates that we want to sent the SELECT privilege. The keyword ON followed by  indicates that the privilege applies on the table. The keyword TO followed by  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 :

The rights for DEVELOPER_2 and DEVELOPER_3 have changed: {| class="wikitable" style="text-align: center; width: 100%;"
 * Nuvola apps personal.svg]] DBA Developer 1 || Nuvola apps personal unisex.svg]] Developer 2 Developer 3 || User.svg]] User 1  User 2
 * - style="border-bottom: hidden"
 * - style="border-bottom: hidden"






 * - style="border-bottom: hidden"
 * - style="border-bottom: hidden"
 * - style="border-bottom: hidden"




 * }
 * }
 * }
 * }

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  and allow him to send this privilege to other users: The rights for USER_1 have changed: {| class="wikitable" style="text-align: center; width: 100%;"
 * Nuvola apps personal.svg]] DBA Developer 1 || Nuvola apps personal unisex.svg]] Developer 2 Developer 3 || User.svg]] User 1  User 2
 * - style="border-bottom: hidden"
 * - style="border-bottom: hidden"






 * - style="border-bottom: hidden"
 * - style="border-bottom: hidden"
 * - style="border-bottom: hidden"








 * }
 * }
 * }
 * }

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  to the other users. Let's say that USER_1 wants to allow anyone to process SELECT on the table : The rights of all the users have changed: {| class="wikitable" style="text-align: center; width: 100%;"
 * Nuvola apps personal.svg]] DBA Developer 1 || Nuvola apps personal unisex.svg]] Developer 2 Developer 3 || User.svg]] User 1  User 2
 * - style="border-bottom: hidden"
 * - style="border-bottom: hidden"






 * - style="border-bottom: hidden"
 * - style="border-bottom: hidden"
 * - style="border-bottom: hidden"




 * }
 * }
 * }
 * }

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 : {| class="wikitable" style="text-align: center; width: 100%;"
 * Nuvola apps personal.svg]] DBA Developer 1 || Nuvola apps personal unisex.svg]] Developer 2 Developer 3 || User.svg]] User 1  User 2
 * - style="border-bottom: hidden"
 * - style="border-bottom: hidden"






 * - style="border-bottom: hidden"
 * - style="border-bottom: hidden"
 * - style="border-bottom: hidden"






 * }
 * }
 * }
 * }

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  to DEVELOPER_2 and DEVELOPER_3: {| class="wikitable" style="text-align: center; width: 100%;"
 * Nuvola apps personal.svg]] DBA Developer 1 || Nuvola apps personal unisex.svg]] Developer 2 Developer 3 || User.svg]] User 1  User 2
 * - style="border-bottom: hidden"
 * - style="border-bottom: hidden"




 * - style="border-bottom: hidden"
 * - style="border-bottom: hidden"
 * - style="border-bottom: hidden"




 * }
 * }
 * }
 * }

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:

{| class="wikitable" style="text-align: center; width: 100%;"
 * Nuvola apps personal.svg]] DBA Developer 1 || Nuvola apps personal unisex.svg]] Developer 2 Developer 3 || User.svg]] User 1  User 2
 * - style="border-bottom: hidden"
 * - style="border-bottom: hidden"






 * - style="border-bottom: hidden"
 * - style="border-bottom: hidden"
 * - style="border-bottom: hidden"








 * }
 * }
 * }
 * }

Now you can administrate a database.