- MySQL Basics
- MySQL - Home
- MySQL - Introduction
- MySQL - Features
- MySQL - Versions
- MySQL - Variables
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Node.js Syntax
- MySQL - Java Syntax
- MySQL - Python Syntax
- MySQL - Connection
- MySQL - Workbench
- MySQL Databases
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Show Database
- MySQL - Copy Database
- MySQL - Database Export
- MySQL - Database Import
- MySQL - Database Info
- MySQL Users
- MySQL - Create Users
- MySQL - Drop Users
- MySQL - Show Users
- MySQL - Change Password
- MySQL - Grant Privileges
- MySQL - Show Privileges
- MySQL - Revoke Privileges
- MySQL - Lock User Account
- MySQL - Unlock User Account
- MySQL Tables
- MySQL - Create Tables
- MySQL - Show Tables
- MySQL - Alter Tables
- MySQL - Rename Tables
- MySQL - Clone Tables
- MySQL - Truncate Tables
- MySQL - Temporary Tables
- MySQL - Repair Tables
- MySQL - Describe Tables
- MySQL - Add/Delete Columns
- MySQL - Show Columns
- MySQL - Rename Columns
- MySQL - Table Locking
- MySQL - Drop Tables
- MySQL - Derived Tables
- MySQL Queries
- MySQL - Queries
- MySQL - Constraints
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Replace Query
- MySQL - Insert Ignore
- MySQL - Insert on Duplicate Key Update
- MySQL - Insert Into Select
- MySQL Indexes
- MySQL - Indexes
- MySQL - Create Index
- MySQL - Drop Index
- MySQL - Show Indexes
- MySQL - Unique Index
- MySQL - Clustered Index
- MySQL - Non-Clustered Index
- MySQL Operators and Clauses
- MySQL - Where Clause
- MySQL - Limit Clause
- MySQL - Distinct Clause
- MySQL - Order By Clause
- MySQL - Group By Clause
- MySQL - Having Clause
- MySQL - AND Operator
- MySQL - OR Operator
- MySQL - Like Operator
- MySQL - IN Operator
- MySQL - ANY Operator
- MySQL - EXISTS Operator
- MySQL - NOT Operator
- MySQL - NOT EQUAL Operator
- MySQL - IS NULL Operator
- MySQL - IS NOT NULL Operator
- MySQL - Between Operator
- MySQL - UNION Operator
- MySQL - UNION vs UNION ALL
- MySQL - MINUS Operator
- MySQL - INTERSECT Operator
- MySQL - INTERVAL Operator
- MySQL Joins
- MySQL - Using Joins
- MySQL - Inner Join
- MySQL - Left Join
- MySQL - Right Join
- MySQL - Cross Join
- MySQL - Full Join
- MySQL - Self Join
- MySQL - Delete Join
- MySQL - Update Join
- MySQL - Union vs Join
- MySQL Keys
- MySQL - Unique Key
- MySQL - Primary Key
- MySQL - Foreign Key
- MySQL - Composite Key
- MySQL - Alternate Key
- MySQL Triggers
- MySQL - Triggers
- MySQL - Create Trigger
- MySQL - Show Trigger
- MySQL - Drop Trigger
- MySQL - Before Insert Trigger
- MySQL - After Insert Trigger
- MySQL - Before Update Trigger
- MySQL - After Update Trigger
- MySQL - Before Delete Trigger
- MySQL - After Delete Trigger
- MySQL Data Types
- MySQL - Data Types
- MySQL - VARCHAR
- MySQL - BOOLEAN
- MySQL - ENUM
- MySQL - DECIMAL
- MySQL - INT
- MySQL - FLOAT
- MySQL - BIT
- MySQL - TINYINT
- MySQL - BLOB
- MySQL - SET
- MySQL Regular Expressions
- MySQL - Regular Expressions
- MySQL - RLIKE Operator
- MySQL - NOT LIKE Operator
- MySQL - NOT REGEXP Operator
- MySQL - regexp_instr() Function
- MySQL - regexp_like() Function
- MySQL - regexp_replace() Function
- MySQL - regexp_substr() Function
- MySQL Fulltext Search
- MySQL - Fulltext Search
- MySQL - Natural Language Fulltext Search
- MySQL - Boolean Fulltext Search
- MySQL - Query Expansion Fulltext Search
- MySQL - ngram Fulltext Parser
- MySQL Functions & Operators
- MySQL - Date and Time Functions
- MySQL - Arithmetic Operators
- MySQL - Numeric Functions
- MySQL - String Functions
- MySQL - Aggregate Functions
- MySQL Misc Concepts
- MySQL - NULL Values
- MySQL - Transactions
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - SubQuery
- MySQL - Comments
- MySQL - Check Constraints
- MySQL - Storage Engines
- MySQL - Export Table into CSV File
- MySQL - Import CSV File into Database
- MySQL - UUID
- MySQL - Common Table Expressions
- MySQL - On Delete Cascade
- MySQL - Upsert
- MySQL - Horizontal Partitioning
- MySQL - Vertical Partitioning
- MySQL - Cursor
- MySQL - Stored Functions
- MySQL - Signal
- MySQL - Resignal
- MySQL - Character Set
- MySQL - Collation
- MySQL - Wildcards
- MySQL - Alias
- MySQL - ROLLUP
- MySQL - Today Date
- MySQL - Literals
- MySQL - Stored Procedure
- MySQL - Explain
- MySQL - JSON
- MySQL - Standard Deviation
- MySQL - Find Duplicate Records
- MySQL - Delete Duplicate Records
- MySQL - Select Random Records
- MySQL - Show Processlist
- MySQL - Change Column Type
- MySQL - Reset Auto-Increment
- MySQL - Coalesce() Function
MySQL - SHOW GRANTS Statement
MySQL SHOW GRANTS Statement
You can assign various privileges or roles to MySQL accounts. You cannot assign both in one statement. To grant privileges to users using this statement you need to have GRANT OPTION privilege.
The MySQL SHOW GRANTS statement is used to display/ retrieve the privileges and roles assigned to a role or an account. To execute this statement, you need SELECT privilege.
Syntax
Following is the syntax of the MySQL SHOW GRANTS Statement −
SHOW GRANTS [FOR user_or_role [USING role [, role] ...]]
Example
Following example list out all the grants and privileges assigned to the current user −
SHOW GRANTS\G;
Output
Once the query is executed, it will produce the following output −
*************************** 1. row *************************** Grants for root@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES,INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION *************************** 2. row *************************** Grants for root@localhost: GRANT APPLICATION_PASSWORD_ADMIN, AUDIT_ADMIN, BACKUP_ADMIN,BINLOG_ADMIN, BINLOG_ENCRYPTION_ADMIN, CLONE_ADMIN, CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN, GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE, INNODB_REDO_LOG_ENABLE, PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER, REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN, RESOURCE_GROUP_USER,ROLE_ADMIN, SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN ,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER, SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN, XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION *************************** 3. row *************************** Grants for root@localhost: GRANT ALL PRIVILEGES ON `db1`.* TO `root`@`localhost` *************************** 4. row *************************** Grants for root@localhost: GRANT ALL PRIVILEGES ON `test`.* TO `root`@`localhost` *************************** 5. row *************************** Grants for root@localhost: GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION *************************** 6. row *************************** Grants for root@localhost: GRANT `TestRole`@`%`,`TestRole_ReadOnly`@`%` TO `root`@`localhost` 6 rows in set (0.00 sec)
The FOR clause
Using the FOR clause you can retrieve the list of grants or privileges assigned to a particular user.
Example
Assume we have created a user named 'test_user'@'localhost' in MySQL using the CREATE USER statement −
CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'testpassword';
Now, let us create a database and create a table in it.
CREATE DATABASE test_database; USE test_database; Database changed CREATE TABLE MyTable(data VARCHAR(255));
Following query grants SELECT and INSERT privileges on the table created above, to the user 'test_user'@'localhost −
GRANT SELECT, INSERT ON test_database.MyTable TO 'test_user'@'localhost';
You can verify the granted privileges using the SHOW GRANTS statements −
SHOW GRANTS FOR 'test_user'@'localhost';
Output
Following is the output of the above query −
Grants for test_user@localhost |
---|
GRANT USAGE ON *.* TO `test_user`@`localhost` |
GRANT SELECT, INSERT ON `test_database`.`mytable` TO `test_user`@`localhost` |
Listing the privileges assigned to a role
Using the SHOW GRANTS statement, you can also display the Grants assigned for a role
Example
Following query creates a role named TestRole_ReadOnly.
CREATE ROLE 'TestRole_ReadOnly';
Now, let’s grant read only privilege to the created role using the GRANT statement as −
GRANT SELECT, UPDATE, INSERT ON * . * TO 'TestRole_ReadOnly';
Now, let’s grant read only privilege to the created role using the GRANT statement as −
SHOW GRANTS for 'TestRole_ReadOnly';
Output
The above query produces the output shown below −
Grants for TestRole_ReadOnly@% |
---|
GRANT SELECT, INSERT, UPDATE ON *.* TO `TestRole_ReadOnly`@`%` |
Example
Assume we have created a user, a procedure and a table with the name sample in the database as follows −
CREATE USER 'sample_user'@'localhost'; //Creating a procedure DELIMITER // CREATE PROCEDURE sample () BEGIN SELECT 'This is a sample procedure'; END// DELIMITER ; CREATE TABLE sample(data INT);
Following queries grants ALTER ROUTINE, EXECUTE privileges on the above created procedure to the user named 'sample_user'@'localhost'.
GRANT ALTER ROUTINE, EXECUTE ON PROCEDURE test_database.sample TO 'sample_user'@'localhost';
Similarly, following query grants SELECT, INSERT and UPDATE privileges on the table sample to the user 'sample_user'@'localhost −
GRANT SELECT, INSERT, UPDATE ON test.sample TO 'sample_user'@'localhost';
You can verify the list of all privileges granted for the user −
SHOW GRANTS FOR 'sample_user'@'localhost';
Output
After executing the above query, it will generate the following output −
Grants for sample_user@localhost |
---|
GRANT USAGE ON *.* TO `sample_user`@`localhost` |
GRANT SELECT, INSERT, UPDATE ON `test`.`sample` TO `sample_user`@`localhost` |
GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test_database`.`sample` TO `sample_user`@`localhost` |
Following query revokes all the privileges granted to the user 'sample_user'@'localhost −
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'sample_user'@'localhost';
Now, if you verify the grants for the user again you can observe all the above grans are removed from the list −
SHOW GRANTS FOR 'sample_user'@'localhost';
Output
Once the query is executed, it will produce the output shown below −
Grants for sample_user@localhost |
---|
GRANT USAGE ON *.* TO `sample_user`@`localhost` |
The USING clause
If you use the USING clause in the SHOW GRANTS statement, it lists out the privileges granted to role(s) specified in the clause.
Example
Assume we have created two roles and a user using CREATE statements as −
create user demo; CREATE ROLE TestRole1; CREATE ROLE TestRole2;
Now, let’s grant privileges to the created roles using the GRANT statement as −
GRANT SELECT ON * . * TO TestRole1; GRANT INSERT, UPDATE ON * . * TO TestRole2;
Then, you can GRANT the created role to the user as follows −
GRANT TestRole1, TestRole2 TO demo;
Following query lists the privileges granted for the roles created −
SHOW GRANTS FOR demo USING TestRole1, TestRole2;
Output
The above query produces the following output −
Grants for demo@% |
---|
GRANT SELECT, INSERT, UPDATE ON *.* TO `demo`@`%` |
GRANT `TestRole1`@`%`,`TestRole2`@`%` TO `demo`@`%` |