Grant / Revoke privileges in MySQL Database

Grant / Revoke privileges in MySQL Database

Check the privileges of User in MySQL Database

Syntax:
SHOW GRANTS FOR user@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;

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

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.