SHOW role MySQL

By default, MySQL installs and works under the root user with all privileges. However, not everyone who accesses the database should have all rights over the data for security reasons.

MySQL provides methods to create new user accounts and grant privileges over the database. A simple command helps provide valuable information about what privileges the users currently have.

This tutorial shows how to check the user privileges on a MySQL server.

Prerequisites

• Access to the command line/terminal.
• MySQL installed and configured.
• Access to the MySQL root user account.

How to Show Privileges for a User in MySQL?

To show privileges for a user in MySQL:

1. Open the terminal (CTRL+ALT+T) and log into the MySQL server as root:

mysql -u root -p

Provide the root password when prompted, and press Enter to start the MySQL monitor.

Note: Logging in as root is not necessary. However, the root user has the SELECT permission, which is needed to overview the grants for all other users.

To provide the special SELECT permission to another user, run the following command as a user with SELECT permissions (or root):

GRANT SELECT ON *.* TO ;

2. If you know the exact username and host for which you'd like to check the privileges, skip this step. Otherwise, show all users and hosts:

SELECT user,host FROM mysql.user;

Locate the exact username and host for the next step.

3. Use the following statement to check the privileges for a specific user:

SHOW GRANTS FOR @;

For example, to check the permissions for test_user:

SHOW GRANTS FOR test_user;

Without a hostname, the command checks for the default host '%'.

Alternatively, check the permissions for the currently logged in user with:

SHOW GRANTS;

The output prints a table with all the access privileges. The first grant was auto-generated when the user was created, and the administrator assigned all the following rights later.

Note: For the best MySQL data management, deploy a Bare Metal Cloud server instance to separate your database from other applications and services. BMC servers are an efficient way to handle high volume applications with ease.

Conclusion

After this tutorial, you now know how to check the permissions for a specific user in a database. The command is simple to use and works together with other MySQL commands to monitor privilege access.

To find out what specific GLOBAL privileges a user/role has, you can access the INFORMATION_SCHEMA.USER_PRIVILEGES table:

mysql> select * from information_schema.user_privileges where GRANTEE='\'mysql.infoschema\'@\'localhost\''; 
+--------------------------------+---------------+----------------+--------------+ 
| GRANTEE                        | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE | 
+--------------------------------+---------------+----------------+--------------+ 
| 'mysql.infoschema'@'localhost' | def           | SELECT         | NO           | 
+--------------------------------+---------------+----------------+--------------+ 
1 row in set (0.00 sec)

Privileges for a user/role

For the actual permissions associate with a role/user you will want to look at the underlying system tables associated with the CREATE USER, GRANT and REVOKE commands.You must understand that the CREATE USER, GRANT and REVOKE commands are the recommended method of interacting with these system tables for privileges and access. MySQL DOES NOT recommend direct modification of the underlying system tables. If you choose to modify the underlying system tables anyway – IT IS DONE AT YOUR OWN RISK!

Let's look at an example of how to use the SHOW GRANTS command in MySQL to display grant information for a user.

For example:

SHOW GRANTS FOR 'techonthenet';

This example would display all grant information for the user called 'techonthenet. Each row that is returned by the SHOW GRANTS command is the GRANT statement that can be used to recreate the privileges. This is a great way to capture privileges that you may want to save for later.

In this first example, when you don't specify a host for the username, MySQL assumes '%' as the host. So the example above would be equivalent to the following SHOW GRANTS command.

SHOW GRANTS FOR 'techonthenet'@'%';

Now let's look at an example of how to use the SHOW GRANTS command when we want to specify the host.

For example:

SHOW GRANTS FOR 'techonthenet'@'localhost';

This SHOW GRANTS example would return the grant information for the user called 'techonthenet' on the host called 'localhost'.

How do I view roles in MySQL?

How to show user permissions in MySQL?

What is MySQL user role?

How to show all user grants in MySQL?