Grant and Revoke commands in MySQL

Grant and Revoke commands in MySQL

1. Login with mySQL Database.

mysql -u root -p

2. Create user with Grant command if user is not presented.

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

3. Check the permission present on the User:

show grants for 'testuser'@'localhost';

4. Revoke the permission from 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 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.


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 )

Google photo

You are commenting using your Google 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.