Tag Archives: Grant Permission or privileges to user

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.