5.8.2. Adding New User Accounts to MySQL
You can create MySQL accounts in two ways:
By using statements intended for creating accounts, such as
CREATE USER or GRANT
By manipulating the MySQL grant tables directly with
statements such as INSERT,
UPDATE, or DELETE
The preferred method is to use account-creation statements
because they are more concise and less error-prone.
CREATE USER and GRANT are
described in Section 13.5.1.1, “CREATE USER Syntax”, and
Section 13.5.1.3, “GRANT Syntax”.
Another option for creating accounts is to use one of several
available third-party programs that offer capabilities for MySQL
account administration. phpMyAdmin is one
such program.
The following examples show how to use the
mysql client program to set up new users.
These examples assume that privileges are set up according to
the defaults described in Section 2.9.3, “Securing the Initial MySQL Accounts”.
This means that to make changes, you must connect to the MySQL
server as the MySQL root user, and the
root account must have the
INSERT privilege for the
mysql database and the
RELOAD administrative privilege.
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';
The accounts created by these GRANT
statements have the following properties:
Two of the accounts have a username of
monty and a password of
some_pass. Both accounts are superuser
accounts with full privileges to do anything. One account
('monty'@'localhost') can be used only
when connecting from the local host. The other
('monty'@'%') can be used to connect from
any other host. Note that it is necessary to have both
accounts for monty to be able to connect
from anywhere as monty. Without the
localhost account, the anonymous-user
account for localhost that is created by
mysql_install_db would take precedence
when monty connects from the local host.
As a result, monty would be treated as an
anonymous user. The reason for this is that the
anonymous-user account has a more specific
Host column value than the
'monty'@'%' account and thus comes
earlier in the user table sort order.
(user table sorting is discussed in
Section 5.7.5, “Access Control, Stage 1: Connection Verification”.)
One account has a username of admin and
no password. This account can be used only by connecting
from the local host. It is granted the
RELOAD and PROCESS
administrative privileges. These privileges allow the
admin user to execute the
mysqladmin reload, mysqladmin
refresh, and mysqladmin
flush-xxx commands, as
well as mysqladmin processlist . No
privileges are granted for accessing any databases. You
could add such privileges later by issuing additional
GRANT statements.
One account has a username of dummy and
no password. This account can be used only by connecting
from the local host. No privileges are granted. The
USAGE privilege in the
GRANT statement enables you to create an
account without giving it any 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.
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 using FLUSH PRIVILEGES:
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
go unnoticed until you restart the server. With
GRANT, FLUSH PRIVILEGES is
unnecessary.
The reason for using the PASSWORD() function
with INSERT is to encrypt the password. The
GRANT statement encrypts the password for
you, so PASSWORD() is unnecessary.
The 'Y' values enable privileges for the
accounts. Depending on your MySQL version, you may have to use a
different number of 'Y' values in the first
two INSERT statements. For the
admin account, you may also employ the more
readable extended INSERT syntax using
SET.
In the INSERT statement for the
dummy account, only the
Host, User, and
Password columns in the
user table row are assigned values. None of
the privilege columns are set explicitly, so MySQL assigns them
all the default value of 'N'. This is
equivalent to what GRANT USAGE does.
Note that to set up a superuser account, it is necessary only to
create a user table entry with the privilege
columns set to 'Y'. user
table privileges are global, so no entries in any of the other
grant tables are needed.
The next examples create three accounts and give them access to
specific databases. Each of them has a username of
custom and password of
obscure.
To create the accounts with GRANT, use the
following statements:
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';
The three accounts can be used as follows:
The first account can access the
bankaccount database, but only from the
local host.
The second account can access the
expenses database, but only from the host
whitehouse.gov.
The third account can access the customer
database, but only from the host
server.domain.
To set up the custom accounts without
GRANT, use INSERT
statements as follows to modify the grant tables directly:
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;
The first three INSERT statements add
user table entries that allow the user
custom to connect from the various hosts with
the given password, but grant no global privileges (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 when 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 all machines in
a given domain (for example, mydomain.com),
you can issue a GRANT statement that uses the
‘%’ wildcard character in the
host part of the account name:
mysql> GRANT ...
-> ON *.*
-> TO 'myname'@'%.mydomain.com'
-> IDENTIFIED BY 'mypass';
To do the same thing by modifying the grant tables directly, do
this:
mysql> INSERT INTO user (Host,User,Password,...)
-> VALUES('%.mydomain.com','myname',PASSWORD('mypass'),...);
mysql> FLUSH PRIVILEGES;
