Data Control Language in MySQL

Examples of DCL commands include:

GRANT to allow specified users to perform specified tasks.
REVOKE to cancel previously granted or denied permissions.

Granting and Revoking the privileges

Below is the list of some of the Privileges that you can grant to users.
CREATE
SELECT
DROP
DELETE
INSERT
UPDATE
GRANT OPTION

After creating user, you can grant privileges using below syntax.
GRANT SELECT ON deals.* TO ‘adam’@’localhost’;

FLUSH PRIVILEGES;

Note that above privilege will allow adam to only use SQL queries on the tables in deals database.
If user tries to delete, update or insert records in the table, he would get error saying access denied.

To grant all privileges on all database schemas and tables, you can use below syntax.
GRANT ALL PRIVILEGES ON *.* TO ‘adam’@’localhost’;
FLUSH PRIVILEGES;

You can check the privileges of any user by executing below command.
SHOW GRANTS FOR ‘adam’@’localhost’

To view grants of current user, execute below query.
SHOW GRANTS

To Grant File privilege, you need to execute below statements.
GRANT FILE ON . to ‘adam’@’localhost’
FLUSH PRIVILEGES;

You may also like...