4.3.5 Adding New Users to MySQL

You can add users two different ways: by using GRANT statements or by manipulating the MySQL grant tables directly. The preferred method is to use GRANT statements, because they are more concise and less error-prone. See section 4.3.1 GRANT and REVOKE Syntax.

There are also several contributed programs (such as phpMyAdmin) that can be used to create and administrate users.

The following examples show how to use the mysql client to set up new users. These examples assume that privileges are set up according to the defaults described in the previous section. This means that to make changes, you must be on the same machine where mysqld is running, you must connect as the MySQL root user, and the root user must have the INSERT privilege for the mysql database and the RELOAD administrative privilege. Also, if you have changed the root user password, you must specify it for the mysql commands here.

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

shell> mysql --user=root mysql

Then you can add new users by issuing GRANT statements:

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;

These GRANT statements set up three new users:

monty

A full superuser who can connect to the server from anywhere, but who must use a password 'some_pass' to do so. Note that we must issue GRANT statements for both monty@localhost and monty@"%". If we don't add the entry with localhost, the anonymous user entry for localhost that is created by mysql_install_db will take precedence when we connect from the local host, because it has a more specific Host field value and thus comes earlier in the user table sort order.

admin

A user who can connect from localhost without a password and who is granted the RELOAD and PROCESS administrative privileges. This allows the user to execute the mysqladmin reload, mysqladmin refresh, and mysqladmin flush-* commands, as well as mysqladmin processlist . No database-level privileges are granted. (They can be granted later by issuing additional GRANT statements.)

dummy

A user who can connect without a password, but only from the local host. No privileges are granted--the USAGE privilege type allows you to create a user with no privileges. It has the effect of setting all the global privileges to 'N'. It is assumed that you will grant specific privileges to the account later.

You can also add the same user access information 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;

Depending on your MySQL version, you may have to use a different number of 'Y' values above. (Versions prior to Version 3.22.11 have fewer privilege columns, and versions from 4.0.2 on have more.) For the admin user, the more readable extended INSERT syntax using SET that is available starting with Version 3.22.11 is used.

Note that to set up a superuser, you need only create a user table entry with the privilege fields set to 'Y'. No db or host table entries are necessary.

In the last INSERT statement (for the dummy user), only the Host, User, and Password columns in the user table record are assigned values. None of the privilege columns are set explicitly, so MySQL assigns them all the default value of 'N'. This is the same thing that GRANT USAGE does.

The following example adds a user custom who can access the bankaccount database only from the local host, the expenses database only from the host whitehouse.gov, and the customer database only from the host server.domain. He wants to use the password obscure from all three hosts.

To set up this user's privileges using GRANT statements, run these commands:

shell> mysql --user=root mysql
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    ->     ON bankaccount.*
    ->     TO custom@localhost
    ->     IDENTIFIED BY 'obscure';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    ->     ON expenses.*
    ->     TO custom@'whitehouse.gov'
    ->     IDENTIFIED BY 'obscure';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    ->     ON customer.*
    ->     TO custom@'server.domain'
    ->     IDENTIFIED BY 'obscure';

To set up the user's privileges by modifying the grant tables directly, run these commands (note the FLUSH PRIVILEGES at the end):

shell> mysql --user=root mysql
mysql> INSERT INTO user (Host,User,Password)
    -> VALUES('localhost','custom',PASSWORD('obscure'));
mysql> INSERT INTO user (Host,User,Password)
    -> VALUES('whitehouse.gov','custom',PASSWORD('obscure'));
mysql> INSERT INTO user (Host,User,Password)
    -> VALUES('server.domain','custom',PASSWORD('obscure'));
mysql> INSERT INTO db
    -> (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
    ->  Create_priv,Drop_priv)
    -> VALUES
    -> ('localhost','bankaccount','custom','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db
    -> (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
    ->  Create_priv,Drop_priv)
    -> VALUES
    -> ('whitehouse.gov','expenses','custom','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO db
    -> (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,
    ->  Create_priv,Drop_priv)
    -> VALUES('server.domain','customer','custom','Y','Y','Y','Y','Y','Y');
mysql> FLUSH PRIVILEGES;

As in the preceding example that used INSERT statements, you may need to use a different number of 'Y' values, depending on your version of MySQL.

The first three INSERT statements add user table entries that allow user custom to connect from the various hosts with the given password, but grant no permissions to him (all privileges are set to the default value of 'N'). The next three INSERT statements add db table entries that grant privileges to custom for the bankaccount, expenses, and customer databases, but only when accessed from the proper hosts. As usual, after you modify the grant tables directly , you must tell the server to reload them (with FLUSH PRIVILEGES) so that the privilege changes take effect.

If you want to give a specific user access from any machine in a given domain (for example, mydomain.com), you can issue a GRANT statement like the following:

mysql> GRANT ...
    ->     ON *.*
    ->     TO myusername@'%.mydomain.com'
    ->     IDENTIFIED BY 'mypassword';

To do the same thing by modifying the grant tables directly, do this:

mysql> INSERT INTO user VALUES ('%.mydomain.com', 'myusername',
    ->             PASSWORD('mypassword'),...);
mysql> FLUSH PRIVILEGES;

User Comments

Add your own comment.