We will observe in the following article how to get the list of MySQL users, as well as check their passwords and privileges.
Each account includes the user’s name and host. This information is useful because there can be several accounts with privileges assigned for one user. Accounts in MySQL have a direct dependence on hosts for connection sessions.
The list of privileges or roles is achieved with the statement SHOW GRANTS (Fig. 1). It is often used with SELECT privileges. Sometimes we can see that the assigned value is “%” (a special sign that stands for each host) for hosts besides IP address and domains.
Fig 1. Syntax of SHOW GRANTS in MySQL.
Users in MySQL
There are different options to retrieve the list of users before we identify the grants assigned for each user. In this example, we use a SELECT statement.
We can present all users (Fig. 2) as follows:
Fig 2. Syntax all MySQL users
Let’s say we have a table of users as follows:
With this information, we can also present unique users using the following query (Fig. 3):
Fig 3. Syntax unique MySQL users
John - Acc2
Bill - Acc1
Lila - Acc4
Tom - Acc3
In the following example, we present both users and hosts for each connection (Fig. 4):
Fig 4. Syntax MySQL users and host.
Finally, we can also present MySQL users, passwords and hosts (Fig. 5).
Fig 5. Syntax MySQL users, host and password.
that passwords above do not represent the actual passwords needed to log in to these accounts. These are hashed (or encrypted) versions of the password. MySQL does not allow anyone (even the database administrator) to retrieve passwords. In the case of a lost password, a password reset procedure is done.
Users grant in MySQL
We can check the privileges of users with the SHOW GRANTS statement in MySQL.
The SHOW GRANTS statement presents the grant of the current account in frames of active connection session. In addition, we use the SELECT grant statement to check the grants of other accounts.
The GRANT of the current MySQL user (Fig.6) can be shown as follows:
Fig 6. Syntax to show the grants for the current user.
To check privileges for a specific user, we use the following syntax:
Fig 7. Syntax to show the granted privileges for a specific user.
The list of grants for the specified MySQL users can be shown as follows. Fig. 8 shows how we can get a list of grants for a specific user (user_name) on a specific host (host).
Fig 8. Syntax for granted user with a defined host.
MySQL takes % as a wildcard to display all host for that user, e.g. ‘user’@’%’, as shown in Fig.9.
Fig 9. Syntax for MySQL and hosts.
Revoking Privileges in MySQL
We can revoke grants to a user, as shown in Fig. 10. This is particularly helpful if we identify suspicious or unnecessary privileges to an account. Before revoking a privilege, we must be sure that there is no impact to the running application.
Fig 10. Syntax for MySQL REVOKE.
For instance, we can revoke the SELECT PRIVILEGE to “accounts” table with user Bill, as shown in Fig 11. This means that the account Bill logged in to localhost will no longer be able to perform a SELECT statement on the accounts table.
Fig 11. Syntax for revoking select privilege example.
If we want to revoke all privileges to “accounts” table with user Bill, we can use the following statement as shown in Fig 12.
Fig 12. Syntax for revoking all privileges example.
The output from revoking will be as follows:
Deleting in MySQL
Let us observe an example of how to delete the user “Bill” from localhost on Fig. 13:
Fig 13. Syntax for deleting a user
Summary regarding GRANTS
- Password and user account must be secured from inappropriate and/or unknown users;
- MySQL privilege system is an important topic to know. We must avoid granting unnecessary privileges to users;
- Passwords from dictionaries are not secured;
- Investing in a firewall is a very practical decision because it decreases the risk of security issues;
- Transmission of data over the Internet is dangerous and can be intercepted;
- There are utilities which we can use to secure our private data (tcpdump and strings).