MySQL User Account Management

A MySQL account is defined in terms of a username and the client host or hosts from which the user can connect to the server. The account also has a password. There are several distinctions between the way usernames and passwords are used by MySQL and the way they are used by your operating system.

When you install MySQL, the grant tables are populated with an initial set of accounts.

Connect to MySQL Server

When you connect to a MySQL server with a command-line client, you should specify the username and password for the account that you want to use:

shell> mysql --user=monty --password=guess db_name

If you prefer short options, the command looks like this:

shell> mysql -u monty -pguess db_name

There must be no space between the -p option and the following password value. The preceding commands include the password value on the command line, which can be a security risk. To avoid this, specify the --password or -p option without any following password value:

shell> mysql --user=monty --password db_name

shell> mysql -u monty -p db_name

Then the client program will print a prompt and wait for you to enter the password.

Adding New User Accounts to MySQL

First, use the mysql program to connect to the server as the MySQL root user:

shell> mysql --user=root mysql

If you have assigned a password to the root account, you'll also need to supply a --password or -p option for this mysql command and also for those later in this section. After connecting to the server as root, you can add new accounts. The following statements use GRANT to set up four new accounts:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
    ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
    ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';

mysql> GRANT USAGE ON *.* TO 'dummy'@'localhost';

As an alternative to GRANT, you can create the same accounts directly by issuing INSERT statements and then telling the server to reload the grant tables:

shell> mysql --user=root mysql

mysql> INSERT INTO user
    ->     VALUES('localhost','monty',PASSWORD('some_pass'),
    ->     'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

mysql> INSERT INTO user
    ->     VALUES('%','monty',PASSWORD('some_pass'),
    ->     'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

mysql> INSERT INTO user SET Host='localhost',User='admin',
    ->     Reload_priv='Y', Process_priv='Y';

mysql> INSERT INTO user (Host,User,Password)
    ->     VALUES('localhost','dummy','');
mysql> FLUSH PRIVILEGES;

The reason for using FLUSH PRIVILEGES when you create accounts with INSERT is to tell the server to re-read the grant tables. Otherwise, the changes will go unnoticed until you restart the server. With GRANT, FLUSH PRIVILEGES is unnecessary.

Removing User Accounts from MySQL

To remove a MySQL user account, you should use the following procedure, performing the steps in the order shown: 

• Use SHOW GRANTS to determine what privileges the account has. 
• Use REVOKE to revoke the privileges displayed by SHOW GRANTS. This removes records for the account from all the grant tables except the user table and revokes any global privileges listed in the user table. 
• Delete the account by using DROP USER to remove the user table record.

Happy Exploring!