You can create MySQL accounts in two ways:
The preferred method is to use
GRANT
statements, because they are
more concise and less error-prone than manipulating the grant
tables directly. GRANT
is described
in Section 12.4.1.2, “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 accounts.
These examples assume that privileges have been set up according
to the defaults described in Section 2.10.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 also need to supply a --password
or
-p
option, both for this mysql
command and 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 statements have the following properties:
Two of the accounts have a user name of
monty
and a password of
some_pass
. Both accounts are superuser
accounts with full privileges to do anything. The
'monty'@'localhost'
account can be used
only when connecting from the local host. The
'monty'@'%'
account uses the
'%'
wildcard for the host part, so it can
be used to connect from any host.
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.5.4, “Access Control, Stage 1: Connection Verification”.)
The 'admin'@'localhost'
account has no
password. This account can be used only by
admin
to connect 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.
The 'dummy'@'localhost'
account has no
password. This account can be used only to connect 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.
To check the privileges for an account, use
SHOW GRANTS
:
mysql> SHOW GRANTS FOR 'admin'@'localhost';
+-----------------------------------------------------+
| Grants for admin@localhost |
+-----------------------------------------------------+
| GRANT RELOAD, PROCESS ON *.* TO 'admin'@'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;
When you create accounts with
INSERT
, it is necessary to use
FLUSH PRIVILEGES
to tell the server to reload 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. (Versions prior
to 3.22.11 have fewer privilege columns, and versions from 4.0.2
on have more.) The INSERT
statement
for the admin
account employs the more readable
extended INSERT
syntax using
SET
that is available starting with MySQL
3.22.11 is used.
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.
To set up a superuser account, it is necessary only to create a
user
table entry with the privilege columns set
to 'Y'
. The 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 user name 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'@'host47.example.com'
->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
host47.example.com
.
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('host47.example.com','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('host47.example.com','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.
To create a user who has access from all machines in a given
domain (for example, mydomain.com
), you can use
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;
User Comments
Add your own comment.