Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

Show users, Privileges and Passwords in MySQL – Querychat

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.

Syntax

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 MySQLFig 1. Syntax of SHOW GRANTS in MySQL.

Sample Result:

Sample Result

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:

Syntax all MySQL users

Fig 2. Syntax all MySQL users

Sample Result:

Let’s say we have a table of users as follows:

table of users

With this information, we can also present unique users using the following query (Fig. 3):

Syntax unique MySQL users

Fig 3. Syntax unique MySQL users

Sample results:

John - Acc2

Bill   - Acc1

Lila - Acc4

Tom - Acc3

In the following example, we present both users and hosts for each connection (Fig. 4):

Syntax MySQL users and host

Fig 4. Syntax MySQL users and host.

Sample Result:

Sample Result
Finally, we can also present MySQL users, passwords and hosts (Fig. 5).

Syntax MySQL users, host and password

Fig 5. Syntax MySQL users, host and password.

Sample Result:

Sample Result
Note

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:

Syntax to show the grants for the current user

Fig 6. Syntax to show the grants for the current user.

To check privileges for a specific user, we use the following syntax:

Syntax to show the granted privileges for a specific user

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).

Syntax for granted user with a defined host

Fig 8. Syntax for granted user with a defined host.

Note:

MySQL takes % as a wildcard to display all host for that user, e.g. ‘user’@’%’, as shown in Fig.9.

Syntax for MySQL and hosts

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.

Syntax for MySQL REVOKE

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.

Syntax for revoking select privilege example.

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.

Syntax for revoking all privileges example

Fig 12.  Syntax for revoking all privileges example.

The output from revoking will be as follows:

output from revoking
Deleting in MySQL

Let us observe an example of how to delete the user “Bill” from localhost on Fig. 13:

Syntax for deleting a user

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).

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc