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.
The examples below 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
below.
You can add new users by issuing GRANT
statements:
shell> mysql --user=root mysql
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@localhost
IDENTIFIED BY 'something' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@"%"
IDENTIFIED BY 'something' 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 (
'something'
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 thuse 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-related 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. The
global privileges are all set to
'N'
-- the USAGE
privilege
type allows you to set up a user with no privileges. It is assumed that you
will grant database-specific privileges 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('something'),
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y')
mysql> INSERT INTO user VALUES('%','monty',PASSWORD('something'),
'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 3.22.11 had fewer
privilege columns). For the admin
user, the more readable extended
INSERT
syntax that is available starting with 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.
The privilege columns in the user
table were not set explicitly in the
last INSERT
statement (for the dummy
user), so those columns
are assigned the default value of 'N'
. This is the same thing that
GRANT USAGE
does.
The following example adds a user custom
who can connect from hosts
localhost
, server.domain
and whitehouse.gov
. He wants
to access the bankaccount
database only from localhost
,
the expenses
database only from whitehouse.gov
and
the customer
database from all three hosts. He wants
to use the password stupid
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 'stupid';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON expenses.*
TO custom@whitehouse.gov
IDENTIFIED BY 'stupid';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON customer.*
TO custom@'%'
IDENTIFIED BY 'stupid';
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('stupid'));
mysql> INSERT INTO user (Host,User,Password)
VALUES('server.domain','custom',PASSWORD('stupid'));
mysql> INSERT INTO user (Host,User,Password)
VALUES('whitehouse.gov','custom',PASSWORD('stupid'));
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('%','customer','custom','Y','Y','Y','Y','Y','Y');
mysql> FLUSH PRIVILEGES;
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, when the grant tables are
modified directly, the server must be told 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, you can issue a GRANT
statement like the following:
mysql> GRANT ...
ON *.*
TO myusername@"%.mydomainname.com"
IDENTIFIED BY 'mypassword';
To do the same thing by modifying the grant tables directly, do this:
mysql> INSERT INTO user VALUES ('%.mydomainname.com', 'myusername',
PASSWORD('mypassword'),...);
mysql> FLUSH PRIVILEGES;
You can also use xmysqladmin
, mysql_webadmin
and even
xmysql
to insert, change and update values in the grant tables.
You can find these utilities at the MySQL
Contrib directory.