Show Privileges in MySQL Database Server via Command Line

This article is created to show how to view privileges for certain user in MySQL Database Server. It is used to recheck whether the privilege has already granted or not. The other reason is to make sure that to check whether the grant privilege command is working and have an impact to the previous state of privilege list.

For a start, check whether MySQL service is working. There is an article which can be used as a reference titled ‘Check MySQL Service Status‘. If the service of MySQL is working, it will allow to be connected remotely or local depends on the location of MySQL Database Server itself.

Before executing grant privilege command, it is important to be connected to MySQL Command Console. The article which can be used as a reference to connect to MySQL Database Server specifically its MySQL Command Console is titled ‘MySQL Database Access from Command Line in Linux‘.

After successfully connected to MySQL Database Server through MySQL Command Console below is the command which can be used to view the list of privileges that a user has :

show grants for 'username@'source';
Description : 
show grants for : It is the query language pattern which is used in MySQL to list privileges defined for certain user passed as an argument
'username' : It is the username used in order to list the privileges defined
'source' : It is the host, server or workstation defined as the source of connection 

Below is the example of command execution in a MySQL Command Console :

mysql> show grants for 'admin'@'%';
+----------------------------------------------------------+
| Grants for admin@%                                       |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin'@'%'                        |
| GRANT ALL PRIVILEGES ON `database_name`.* TO 'admin'@'%' |
+----------------------------------------------------------+
2 rows in set (0.00 sec)

The above example is a command which is used to show the grant which has already been defined in that MySQL Database Server. The user which is trying to be viewed its privilege list is ‘admin’. The other information is the source is ‘%’. The sign ‘%’ is used to represent any host, workstation or server. It means, specify all privilege list which is defined for user ‘admin’ whom used to connect from any devices with any IP Address identifier whether it is local or localhost, other local network interfaces or any external network interfaces.

First record list is :

GRANT USAGE ON *.* TO 'admin'@'%'

It can be translated as the privilege which is owned by ‘admin’ whom used to connect from any devices is a full usage on any databases exist in that MySQL Database Server. It marks with the sign ‘*.*’.

Second record list is :

GRANT ALL PRIVILEGES ON `database_name`.* TO 'admin'@'%'

It can be translated as the privilege which is owned by ‘admin’ whom used to connect from any devices are all privileges but only to a database named ‘database_name’.