Grant/Revoke Privileges in MySQL
Table of Contents
___________________________________________________________________________________________________
1. Overview
2. What Are User Privileges?
3. How to List Own User Privileges?
4. How to List Other User Privileges?
5. How To Grant User Privileges at the Global Level?
6. How To Grant User Privileges at the Database Level?
7. How To Grant User Privileges at the Table Level?
8. How To Grant User Privileges at the Column Level?
9. How To Grant User Privileges WITH GRANT OPTION?
10. How to Revoke User Privileges?
___________________________________________________________________________________________________
Global Level : A privilege granted at this level applies to all databases on the server. Privileges granted at the global level stored in "mysql.user" table. Database Level: A privilege granted at this level applies to all tables in the specified database. Privileges granted at the global level stored in "mysql.db" table. Table Level: A privilege granted at this level applies to all columns in the specified table. Privileges granted at the global level stored in "mysql.tables_priv" table. Column Level: A privilege granted at this level applies to only the specified column. Privileges granted at the global level stored in "mysql.columns_priv" table. Routine Level: A privilege granted at this level applies to only the specified stored function procedure. Privileges granted at the global level stored in "mysql.procs_priv" table. Proxy Level: Grant proxy access to users
ALL - All privileges.
CREATE - Allows the user to use CREATE TABLE commands.
ALTER - Allows the user to use ALTER TABLE commands.
DROP - Allows the user to use DROP TABLE commands.
DELETE - Allows the user to use DELETE commands.
INSERT - Allows the user to use INSERT commands.
UPDATE - Allows the user to use UPDATE commands.
SELECT - Allows the user to use SELECT commands.
SHUTDOWN - Allows the user to use "mysqladmin shutdown".
INDEX - Allows the user to create and drop indexes.
CREATE USER - Allows the user to manage user accounts.
CREATE VIEW - Allows the user to user "CREATE VIEW" commands.
CREATE ROUTINE - Allows the user to CREATE PROCEDURE and CREATE FUNCTION
USAGE - No privileges.
3. How to List Own User Privileges?
[root@rac1 ~]# mysql -u raj -pRaj_12345% mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 25 Server version: 8.0.23 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> select current_user(); +----------------+ | current_user() | +----------------+ | raj@% | +----------------+ 1 row in set (0.00 sec) mysql> mysql> SHOW GRANTS FOR CURRENT_USER(); +---------------------------------+ | Grants for raj@% | +---------------------------------+ | GRANT USAGE ON *.* TO `raj`@`%` | +---------------------------------+ 1 row in set (0.00 sec) mysql> ---------------- OR ----- mysql> show grants; +---------------------------------+ | Grants for raj@% | +---------------------------------+ | GRANT USAGE ON *.* TO `raj`@`%` | +---------------------------------+ 1 row in set (0.00 sec) mysql>
4. How to List Other User Privileges?
mysql> select current_user(); +----------------+ | current_user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql> SHOW GRANTS FOR 'raj'@'%'; +---------------------------------+ | Grants for raj@% | +---------------------------------+ | GRANT USAGE ON *.* TO `raj`@`%` | +---------------------------------+ 1 row in set (0.00 sec) mysql>
5. How To Grant User Privileges at the Global Level?
To grant all privileges to a user account over all databases, use the following command: GRANT ALL PRIVILEGES ON *.* TO 'database_user'@'localhost'; mysql> create user raj@localhost identified by 'Rajasekhar_123%'; Query OK, 0 rows affected (0.01 sec) mysql> mysql> SHOW GRANTS FOR 'raj'@'localhost'; +-----------------------------------------+ | Grants for raj@localhost | +-----------------------------------------+ | GRANT USAGE ON *.* TO `raj`@`localhost` | +-----------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> GRANT CREATE ON *.* TO 'raj'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GRANTS FOR 'raj'@'localhost'; +------------------------------------------+ | Grants for raj@localhost | +------------------------------------------+ | GRANT CREATE ON *.* TO `raj`@`localhost` | +------------------------------------------+ 1 row in set (0.00 sec) mysql> [root@rac1 ~]# mysql -u raj -h localhost -pRajasekhar_123% mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 26 Server version: 8.0.23 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select current_user(); +----------------+ | current_user() | +----------------+ | raj@localhost | +----------------+ 1 row in set (0.00 sec) mysql> SHOW GRANTS FOR CURRENT_USER(); +------------------------------------------+ | Grants for raj@localhost | +------------------------------------------+ | GRANT CREATE ON *.* TO `raj`@`localhost` | +------------------------------------------+ 1 row in set (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | dell | | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 6 rows in set (0.00 sec) mysql> use test; Database changed mysql> select database (); +-------------+ | database () | +-------------+ | test | +-------------+ 1 row in set (0.00 sec) mysql> mysql> create table test_raj ( -> trans_id smallint, -> trans varchar(5), -> primary key (trans_id)) -> ENGINE=InnoDB; Query OK, 0 rows affected (0.02 sec) mysql> mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | test_raj | +----------------+ 1 row in set (0.00 sec) mysql> mysql> INSERT INTO test_raj values(1,'SUGI'); ERROR 1142 (42000): INSERT command denied to user 'raj'@'localhost' for table 'test_raj' mysql> mysql> use dell; <---- Login to other database and try to create table Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> mysql> select database (); +-------------+ | database () | +-------------+ | dell | +-------------+ 1 row in set (0.00 sec) mysql> create table test_raj ( -> trans_id smallint, -> trans varchar(5), -> primary key (trans_id)) -> ENGINE=InnoDB; Query OK, 0 rows affected (0.02 sec) mysql> show tables; +----------------+ | Tables_in_dell | +----------------+ | test | | test_raj | +----------------+ 2 rows in set (0.00 sec) mysql> create table test1 ( -> trans_id smallint, -> trans varchar(5), -> primary key (trans_id)) -> ENGINE=InnoDB; Query OK, 0 rows affected (0.02 sec) mysql> show tables; +----------------+ | Tables_in_dell | +----------------+ | test | | test1 | | test_raj | +----------------+ 3 rows in set (0.00 sec) mysql>
6. How To Grant User Privileges at the Database Level?
To grant all privileges to a user account over a specific database, use the following command: GRANT ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost'; mysql> create database orcl; Query OK, 1 row affected (0.00 sec) mysql> create user lori@localhost identified by 'Lori123%'; Query OK, 0 rows affected (0.01 sec) mysql> grant create on orcl.* to lori@localhost; Query OK, 0 rows affected (0.01 sec) mysql> [root@rac1 ~]# mysql -u lori -h localhost -pLori123% mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 27 Server version: 8.0.23 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select current_user (); +-----------------+ | current_user () | +-----------------+ | lori@localhost | +-----------------+ 1 row in set (0.00 sec) mysql> mysql> GRANT ALL ON orcl.* to lori@localhost; Query OK, 0 rows affected (0.01 sec) mysql> [root@rac1 ~]# mysql -u lori -h localhost -pLori123% mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 27 Server version: 8.0.23 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select current_user (); +-----------------+ | current_user () | +-----------------+ | lori@localhost | +-----------------+ 1 row in set (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | orcl | +--------------------+ 2 rows in set (0.00 sec) mysql> use orcl; Database changed mysql> create table test_orcl ( -> trans_id smallint, -> trans varchar(5), -> primary key (trans_id)) -> ENGINE=InnoDB; Query OK, 0 rows affected (0.02 sec) mysql> show tables; +----------------+ | Tables_in_orcl | +----------------+ | test_orcl | +----------------+ 1 row in set (0.00 sec) mysql> SHOW GRANTS FOR CURRENT_USER(); +--------------------------------------------------------+ | Grants for lori@localhost | +--------------------------------------------------------+ | GRANT USAGE ON *.* TO `lori`@`localhost` | | GRANT ALL PRIVILEGES ON `orcl`.* TO `lori`@`localhost` | +--------------------------------------------------------+ 2 rows in set (0.00 sec) mysql>
7. How To Grant User Privileges at the Table Level?
GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.table_name TO database_user@'localhost'; mysql> SHOW GRANTS FOR lori@localhost; +--------------------------------------------------------+ | Grants for lori@localhost | +--------------------------------------------------------+ | GRANT USAGE ON *.* TO `lori`@`localhost` | | GRANT ALL PRIVILEGES ON `orcl`.* TO `lori`@`localhost` | +--------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON orcl.test_orcl TO 'lori'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GRANTS FOR lori@localhost; +----------------------------------------------------------------------------------+ | Grants for lori@localhost | +----------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `lori`@`localhost` | | GRANT ALL PRIVILEGES ON `orcl`.* TO `lori`@`localhost` | | GRANT SELECT, INSERT, UPDATE, DELETE ON `orcl`.`test_orcl` TO `lori`@`localhost` | +----------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql>
8. How To Grant User Privileges at the Column Level?
GRANT SELECT (`trans`), SHOW VIEW ON orcl.`test_orcl` to 'may'@`localhost`; GRANT SELECT (`trans_id`), SHOW VIEW ON orcl.`test_orcl` to 'may'@`localhost`; GRANT INSERT (`trans`), SHOW VIEW ON orcl.`test_orcl` to 'may'@`localhost`; GRANT INSERT (`trans`) ON orcl.`test_orcl` to 'may'@`localhost`; mysql> create user may@localhost identified by 'May1234%'; Query OK, 0 rows affected (0.01 sec) mysql> SELECT CONCAT('GRANT SELECT (`', COLUMN_NAME, '`), SHOW VIEW ON orcl.`', TABLE_NAME, '` to ''may''@`localhost`;') -> FROM INFORMATION_SCHEMA.COLUMNS -> WHERE TABLE_SCHEMA = 'orcl' AND TABLE_NAME = 'test_orcl'; +------------------------------------------------------------------------------------------------------------+ | CONCAT('GRANT SELECT (`', COLUMN_NAME, '`), SHOW VIEW ON orcl.`', TABLE_NAME, '` to ''may''@`localhost`;') | +------------------------------------------------------------------------------------------------------------+ | GRANT SELECT (`trans`), SHOW VIEW ON orcl.`test_orcl` to 'may'@`localhost`; | | GRANT SELECT (`trans_id`), SHOW VIEW ON orcl.`test_orcl` to 'may'@`localhost`; | +------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> GRANT SELECT (`trans`), SHOW VIEW ON orcl.`test_orcl` to 'may'@`localhost`; GRANT SELECT (`trans_id`), SHOW VIEW ON orcl.`test_orcl` to 'may'@`localhost`; mysql> GRANT SELECT (`trans`), SHOW VIEW ON orcl.`test_orcl` to 'may'@`localhost`; Query OK, 0 rows affected (0.00 sec) mysql> GRANT SELECT (`trans_id`), SHOW VIEW ON orcl.`test_orcl` to 'may'@`localhost`; Query OK, 0 rows affected (0.00 sec) mysql> show grants for 'may'@`localhost`; +------------------------------------------------------------------------------------------+ | Grants for may@localhost | +------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `may`@`localhost` | | GRANT SELECT (`trans`, `trans_id`), SHOW VIEW ON `orcl`.`test_orcl` TO `may`@`localhost` | +------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> mysql> select trans from orcl.test_orcl; +-------+ | trans | +-------+ | SUGI | | TEJU | | RAJ | | SOMU | | JANA | +-------+ 5 rows in set (0.00 sec) mysql> mysql> GRANT INSERT (`trans`), SHOW VIEW ON orcl.`test_orcl` to 'may'@`localhost`; Query OK, 0 rows affected (0.01 sec) mysql> mysql> SHOW GRANTS FOR 'may'@`localhost`; +------------------------------------------------------------------------------------------------------------+ | Grants for may@localhost | +------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `may`@`localhost` | | GRANT SELECT (`trans`, `trans_id`), INSERT (`trans`), SHOW VIEW ON `orcl`.`test_orcl` TO `may`@`localhost` | +------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> mysql> GRANT INSERT (`trans`) ON orcl.`test_orcl` to 'may'@`localhost`; Query OK, 0 rows affected (0.01 sec) mysql> mysql> SHOW GRANTS FOR 'may'@`localhost`; +------------------------------------------------------------------------------------------------------------+ | Grants for may@localhost | +------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `may`@`localhost` | | GRANT SELECT (`trans`, `trans_id`), INSERT (`trans`), SHOW VIEW ON `orcl`.`test_orcl` TO `may`@`localhost` | +------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql>
9. How To Grant User Privileges WITH GRANT OPTION?
mysql> use orcl; Database changed mysql> mysql> create table new_test ( -> trans_id smallint, -> trans varchar(5), -> primary key (trans_id)) -> ENGINE=InnoDB; Query OK, 0 rows affected (0.02 sec) mysql> mysql> INSERT INTO new_test values(1,'SUGI'); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO new_test values(2,'TEJU'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO new_test values(3,'RAJ'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO new_test values(4,'SOMU'); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO new_test values(5,'JANA'); Query OK, 1 row affected (0.00 sec) mysql> COMMIT; Query OK, 0 rows affected (0.00 sec) mysql> mysql> create user bose@localhost identified by 'Bose_1234%'; Query OK, 0 rows affected (0.01 sec) mysql> grant select on orcl.* to 'bose'@'localhost' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec) mysql> mysql> SHOW GRANTS FOR 'bose'@'localhost'; +------------------------------------------------------------------+ | Grants for bose@localhost | +------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `bose`@`localhost` | | GRANT SELECT ON `orcl`.* TO `bose`@`localhost` WITH GRANT OPTION | +------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql>
10. How to Revoke User Privileges?
-- Global Level REVOKE ALL ON *.* FROM 'database_user'@'localhost'; REVOKE SELECT,INSERT,UPDATE,DELETE ON *.* FROM 'database_user'@'localhost'; -- Database Level REVOKE ALL PRIVILEGES ON database_name.* FROM 'database_user'@'localhost'; REVOKE SELECT,INSERT,UPDATE,DELETE ON database_name.* FROM 'database_user'@'localhost'; -- Table Level REVOKE ALL PRIVILEGES ON database_name.table_name FROM 'database_user'@'localhost'; REVOKE SELECT,INSERT,UPDATE,DELETE ON database_name.table_name FROM 'database_user'@'localhost'; -- Column Level REVOKE SELECT,INSERT (`trans`) ON orcl.`test_orcl` FROM 'may'@`localhost`; REVOKE INSERT (`trans`) ON orcl.`test_orcl` FROM 'may'@`localhost`; -- Revoke With Grant Option mysql> SHOW GRANTS FOR 'bose'@'localhost'; +------------------------------------------------------------------+ | Grants for bose@localhost | +------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `bose`@`localhost` | | GRANT SELECT ON `orcl`.* TO `bose`@`localhost` WITH GRANT OPTION | +------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> mysql> revoke grant option on orcl.* from bose@localhost; Query OK, 0 rows affected (0.01 sec) mysql> SHOW GRANTS FOR 'bose'@'localhost'; +------------------------------------------------+ | Grants for bose@localhost | +------------------------------------------------+ | GRANT USAGE ON *.* TO `bose`@`localhost` | | GRANT SELECT ON `orcl`.* TO `bose`@`localhost` | +------------------------------------------------+ 2 rows in set (0.00 sec) mysql>
Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/