DCL¶

This section describes how to grant and revoke authority to database objects.

Grant¶

The GRANT command is used to change user accounts, which structurally looks like this:

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

This command means that we grant the rights to the useruser and the password indicated as the password parameter at the privilege level defined as level.

The user has the following options at his disposal:

• global level - parameter level equal to * or *.* allows to grant 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 to database_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 command below allows you to grant CREATE, SELECT, INSERT, UPDATE, DELETE permissions to all databases for the user sda_user.

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

In turn, the query below grants the SELECT permission to the sda database with the change of the password of the user sda_user to sdapassword with the limitation of 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 indicated user, execute the command:

SHOW GRANTS FOR user_name;

Revoke¶

It is possible to revoke user rights by using the REVOKE command. The query schema looks like below:

REVOKE prawa [columns] ON object FROM user;

REVOKE UPDATE, DELETE ON sda.* FROM sda_user;

The above command removes the UPDATE and DELETE) commands for the user sda_user under thesda database.

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

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

Receiving of all permissions is possible by following the instructions below:

REVOKE ALL ON sda.* FROM sda_user;