(mysql.info) adding-users
Info Catalog
(mysql.info) user-names
(mysql.info) user-account-management
(mysql.info) removing-users
5.9.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 create-user, and grant.
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 default-privileges.
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
connection-access.)
* 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;
Info Catalog
(mysql.info) user-names
(mysql.info) user-account-management
(mysql.info) removing-users
automatically generated byinfo2html