Grant/Revoke and check privileges in MySQL

Grant/Revoke Permission of user in MySQL

 
Display all grant information for a user

SHOW GRANTS [ FOR username ]

SHOW GRANTS FOR 'sunny'@'%';

SHOW GRANTS FOR 'root'@'localhost';

Display all privileges for current user

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

Check privileges type for user

select * from information_schema.user_privileges;

Additional views for check grants

--User-Specific Grants
SELECT * FROM mysql.user;
--Database-Specific Grants
SELECT * FROM mysql.db;
--Table-Specific Grants
SELECT * FROM mysql.tables_priv;
--Column-Specific Grants
SELECT * FROM mysql.columns_priv;

Grant the privileges to user
Note:
[Table name] = “*” = means all users
[HOSTNAME] = “%” = means all hosts otherwise specify IP address or hostname


Syntax:
GRANT ALL ON [database name].[table name] TO [user name]@[host name];
GRANT privileges ON object TO user;

Example:
--Grant DML operation privileges to Contact table
GRANT SELECT, INSERT, UPDATE, DELETE ON contacts TO 'sunny'@'localhost';

--Grant all privileges on contacts table
GRANT ALL ON contacts TO 'sunny'@'localhost';

-- Grant all privileges to DB all objects
GRANT ALL ON database_name.* TO sunny@localhost;

Revoke the privileges from user

Syntax:
REVOKE ALL ON [database name].[table name] FROM [user name]@[host name];

REVOKE privileges ON object FROM user;

Example:
-- Revoke delete update privileges on contact table from sunny user.
REVOKE DELETE, UPDATE ON contacts FROM 'sunny'@'localhost';

-- Revoke all privileges on contact table from sunny user.
REVOKE ALL ON contacts FROM 'sunny'@'localhost';

--Revoke select from contacts table from all users.
REVOKE SELECT ON contacts FROM '*'@'%';

Grant and Revoke Execute permission on Procedure/Function

Syntax:
GRANT EXECUTE ON [ PROCEDURE | FUNCTION ] object TO user;
Example:
GRANT EXECUTE ON FUNCTION calsalary TO 'sunny'@'localhost';

Syntax:
REVOKE EXECUTE ON [ PROCEDURE | FUNCTION ] object FROM user;
Example:
REVOKE EXECUTE ON FUNCTION calsalary FROM 'sunny'@'localhost';

Advertisements

Leave a Reply

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

WordPress.com Logo

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