DCL Commands

Control DB access is very impairment because application saves all important and sensitive information like customer login details, Card details, and personal information which we should allow accessing only authored DB users.

We can create DB users using create user command

CREATE USER 'dev1'@'%' IDENTIFIED BY 'dev1pass';
CREATE USER 'read_user1'@'localhost' IDENTIFIED BY 'read_user1pass';
CREATE USER 'read_user2'@'127.11.%' IDENTIFIED BY 'read_user2pass';
CREATE USER 'rw_user1'@'localhost' IDENTIFIED BY 'rw_user1pass';
create user dbuser@'127.0.0.1' IDENTIFIED BY  'YOUR_PASSWORD'
create user dbuser@'137.10.0.1' IDENTIFIED BY  'YOUR_PASSWORD'

In MySQL, DB user is unique with DB user name and his access point. if we give access point “%” then he can connect from any server. Even we grant to access selected LAN IP address like ‘127.11.%’. After create user you can check details using the following SQL commands

select user,host from mysql.user;  

After created user then we need to give privileges to that user. You can check all existing privileges using “show privileges;” command.

using GRANT DCL command we can grant privileges to any DB user.

grant all on emp.employee to dbuser@'127.0.0.1';
grant all on emp.* to dbuser@'127.0.0.1';
grant all on *.* to dbuser@'127.0.0.1';
grant select,insert,delete, UPDATE on *.* to dbuser@'127.0.0.1';

We need to give privileges after the GRANT command. If ALL means granting all privileges. And that we need to specify one which objects data database name dot table names. if we give *.* means he can access all objects. if we give any database ex emp.* This means he can access all objects inside the EMP database. After that, we need to specify the user name.

We can roll back privileges using REVOKE privileges. We can revoke selected list of privileges

REVOKE select,insert   on *.* from dbuser@'127.0.0.1';

MySQL from 8.0 version supporting ROLEs. We can create roles and assign a set of privileges to roles and great selected roles to users.

Create ROLES

CREATE ROLE 'app_developer', 'app_read', 'app_write';

Assign Privileges to ROLEs

GRANT ALL ON app_db.* TO 'app_developer';
GRANT SELECT ON app_db.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write'

Use ROLEs in GRANST commands

GRANT 'app_developer' TO 'dev1'@'localhost';
GRANT 'app_read' TO 'read_user1'@'localhost', 'read_user2'@'localhost';
GRANT 'app_read', 'app_write' TO 'rw_user1'@'localhost';