How to Show Privileges of a User in MySQL

This article is focusing on the subject which is described in the title, to present on how to show privileges of a user in MySQL Database Server. Maybe, it is quite simple if it is done by GUI which is represented with an application such a MySQL Editor but on the other hand doing it in by executing a certain command from a MySQL Command Console is also not a difficult thing to do.

With a single command executed in MySQL Command Console, it can automatically generates output displaying all of the privileges owned by a user in MySQL Database Server mentioned. Below are the steps taken to achieve the purpose :

1. Make sure and confirm that MySQL Database Service is active. Read the following article to know how to do it which is titled as ‘Check MySQL Service Status’ in this link.

2. Access MySQL Database Service by connecting to it via command line. The command can be executed further after logging in to MySQL command line. The article which can be used as a reference is in this site titled ‘MySQL Database Access from Command Line in Linux’ and it is available in this link.

3. After successfully logging in to MySQL command line, execute the following command to display or to show the available privileges for a certain user in MySQL Database Server. Below is the pattern :

show grants for 'user'@'host;

For an example :

user@hostname:~$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 5.7.12-0ubuntu1.1 (Ubuntu)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
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> show grants for 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `test`.* TO 'root'@'localhost'              |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
3 rows in set (0,00 sec)
mysql>

In the example given above, the user which is trying to look up for its privileges is ‘root’ and the host which the root is connect from is ‘localhost’.