Check, Grant and Revoke commands in MySQL

Check the User permission in MySQL

Login with MySQL Database.

mysql -u root -p

Show grant or permission of the user.

--Check the permission of testuser
SHOW GRANTS FOR 'testuser'@'localhost';
--Check the permission of current user
--Check the permission of root user
SHOW GRANTS for 'root'@'localhost'

Create user with grants privileges on MySQL database for all objects.

grant select on database_name.* to 'testuser'@'localhost' identified by 'Password';

Grant the privileges on the Mysql database.

-- 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 permission from the User in MySQL:

-- 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

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


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.