DCL stands for Data Control Language. In MySQL we have two types of DCL commands – GRANT & REVOKE.
GRANT – This command basically is used to provide database operation’s permission to any user.
Syntax –
GRANT permissions ON database.tables TO ‘user’@’localhost’;
Lets understand from the image below. But to do all of these, we first need to create a new user at localhost in the MySQL database.
For creating a new user in MySQL we need to write,
Syntax – CREATE USER ‘user_name’@’localhost’ IDENTIFIED BY ‘password’;
Now we will provide permission to user@sanjoy into localhost.
Syntax –
GRANT permissions ON database.tables TO ‘user’@’localhost’;
After doing this operation, whenever user sanjoy will want to access table ‘mytable’ of database mydb; then it can only do SELECT(view) operation in MySQL database.
Let’s understand through another example.
It means user@‘linus torvalds’ has all the permissions to all tables of mydb in mysql local database. *(all).
REVOKE
This command is used to revoke/remove permission from any user in MySQL database.
We revoked SELECT(view) permission on ‘mytable’ of database ‘mydb’ from user ‘sanjoy’ belongs to localhost.
Now we are revoking DELETE permission on all tables of database ‘mydb’ from user ‘sanjoy’.
It means now user ‘sanjoy’ can’t delete any records from any table belonging to database ‘mydb’.