Managing MySQL Users

This post outlines basic tasks for MySQL user management from the command line.

Show all users

To show all users in a mysql database, use the following query:

select user, host from mysql.user;

This will output a table that looks like the following:

+------------------+------------------------+
| user             | host                   |
+------------------+------------------------+
| bart             | %.%.sd.cox.net         |
| root             | 127.0.0.1              |
| root             | ::1                    |
| debian-sys-maint | localhost              |
| web_home_dev     | localhost              |
| leadmaintainer   | localhost              |
+------------------+------------------------+
6 rows in set (0.00 sec)

Grant privileges to a user

To grant all privileges on a schema to a user, use the following mysql query:

grant all privileges on db_name.table to 'user'@'host';

Fill in the following details in the above query:

  • db_name: The name of the database
  • table: The name of the database table to grant access to (usually, it’s a good idea to use the * wildcard to grant access to all tables in the database)
  • user: The name of the user to grant the privileges to; the user does not have to exist in the mysql users table before granting it privileges
  • host: The host to grant access from
    • Use localhost for users that will only access the database from the same machine
    • Use % as a wildcard in this field