DCL¶

This section will describe how to grant and revoke authority to database objects.

Grant¶

To change user accounts, the GRANT command is used, which looks structurally as follows:

GRANT right [columns] ON level TO user [IDENTIFIED By password] [WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR how_many | MAX_UPDATES_PER_HOUR how_many | MAX_USER_CONNECTIONS how_many | 
MAX_CONNECTIONS_PER_HOUR]];

This command means that we give user rights to the user and the password indicated as the password parameter at the level of privileges defined as level.

The user has the following options:

• global level - parameter level equal to*or*. *enables the granting of global rights to all objects in a given database
• database level - parameter level equal to database_name. * allows you to grant rights to a specific database
• table level - parameter level equal todatabase_name.table_name allows you to grant rights to a specific database table
• column level - the level parameter can be defined to gain control over specific columns of a specific database.

Permissions¶

Basic operations¶

The following command allows you to grant permissions CREATE, SELECT, INSERT, UPDATE, DELETE to all user databases sda_user.

GRANT CREATE, SELECT, INSERT, UPDATE, DELETE ON * TO sda_user;

The following query grants permission SELECT to database sda along with the user's password variable sda_user on sdapassword along with limiting the number of queries to 50 per hour.

GRANT SELECT ON sda.* TO sda_user IDENTIFIED BY `sdapassword` WITH MAX QUERIES_PER_HOUR 50;

Information about the permissions of the selected user¶

In order to display information about the permissions of the selected user, execute the command:

SHOW GRANTS FOR user_name;

Revoke¶

The user can be revoked using the REVOKE command. The query looks like this:

REVOKE permission [columns] ON object FROM user;

REVOKE UPDATE, DELETE ON sda.* FROM sda_user;

The above command removes permission to execute commands UPDATE and DELETE for user sda_user in database sda.

As part of the REVOKE query, it is possible to revoke permissions for many users at once, e.g.:

REVOKE UPDATE, DELETE ON sda.* FROM sda_user, sda_employee;

Revoking all permissions is possible using the instructions below:

REVOKE ALL ON sda.* FROM sda_user;