Grant and Revoke Privileges example in MySQL Database

Check the privileges of User in MySQL Database

Syntax:
SHOW GRANTS FOR user@localhost;

---Check the permission present on the User:
show grants for 'testuser'@'localhost';

--For current user:
SHOW GRANTS;

Grant Privileges to User in MySQL Database

Sytnax: 
GRANT priv_type [(column_list)]
 [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH {GRANT OPTION | resource_option} ...]

Example:
GRANT ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost';

Privileges levels:

1. Global Privileges used to all MySQL databases

GRANT SELECT ON *.* TO hr@localhost;

2. Database privileges apply to all objects in a database

GRANT INSERT ON SALESDB.* TO hr@localhost;

3. Table Privileges used on all columns in a table. 

GRANT DELETE ON salesdb.order TO hr@localhsot;

4. Column privileges is used to single columns in a table.

GRANT SELECT (orderno,orderamount, customerno), 
UPDATE(orderqty,orderamount) ON salesdb.order TO hr@localhost;

5. Stored routine privileges is used for stored procedures and stored functions

GRANT EXECUTE ON PROCEDURE CheckOrder TO hr@localhost;

6. Proxy user privileges user gets all privileges of the proxied user. 

GRANT PROXY ON root TO hr@localhost;

Example of Grant permission in MySQL

--Create user at same time with grants privileges on db of all objects.
grant select on database_name.* to 'testuser'@'localhost' identified by 'Password';

-- Grant all privileges make user as Superuser
grant all on database_name.* to 'testuser'@'localhost;

-- Grant select on specific table
GRANT SELECT ON db_name.sales TO 'testuser'@'localhost';

-- Grant privileges to all the databases
GRANT SELECT, INSERT ON *.* TO 'testuser'@'localhost';

--Grant on specific columns of the table
GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'testuser'@'localhost';

-- Grant procedure or routine priviliges
GRANT CREATE ROUTINE ON DB_NAME.* TO 'testuser'@'localhost';
GRANT EXECUTE ON PROCEDURE DB_NAME.myproc TO 'testuser'@'localhost';

Revoke the privileges from User in MySQL database

Syntax:
REVOKE priv_type [(column_list)] 
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
 FROM user [, user] ...

REVOKE ALL [PRIVILEGES], GRANT OPTION
 FROM user [, user] ...

REVOKE privilegee [,privilege]..
 ON [object_type] privilege_level
FROM user1 [, user2] ..;

REVOKE PROXY ON proxied_user 
FROM proxy_user1[,proxy_user1]...;

Example of Revoke Privileges

--Remove Database privileges
REVOKE INSERT, UPDATE ON salesdb.* FROM hr@localhost;

--Remove Table privileges
REVOKE INSERT, UPDATE ON salesdb.ORDER FROM hr@localhost;

REVOKE ALL, GRANT OPTION FROM hr@localhost;

REVOKE PROXY ON root FROM hr@localhost;

--Global Privilege remove
REVOKE INSERT ON *.* FROM 'hr'@'localhost';


-- Revoke all privileges from the User.
revoke all privileges on database_name.* from 'testuser'@'localhost';

--Revoke only delete privilege
revoke delete on database_name.* from 'testuser'@.'localhost';

--Revoke only alter privilege
revoke alter on database_name.* from ''@'localhost';

--Revoke CREATE permissions for all databases * and all tables *
revoke create on *.* from 'testuser'@'localhost';

--Revoke drop from specific database
REVOKE DROP ON database_name.* FROM 'testuser'@'localhost';

--Revoke all privileges with grant option
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user;

Flush commands is used to reload all the privileges after making all the changes with grant or revoke commands in MySQL. It’s better to run this commands for refresh the privileges.

FLUSH PRIVILEGES;

Note:

GRANT ALL at the global level grants all static global privileges and all currently registered dynamic privileges. A dynamic privilege registered subsequent to the execution of the GRANT statement is not granted retroactively to any account.

REVOKE ALL at the global level revokes all granted static global privileges and all granted dynamic privileges.

The FLUSH PRIVILEGES statement reads the global_grants table for dynamic privilege assignments and registers any unregistered privileges found there.