воскресенье, 15 января 2012 г.

Two ways to add or create user accounts in MySQL

By using statements CREATE USER or GRANT. These statements cause the server to make appropriate modifications to the grant tables. And by manipulating the MySQL grant tables directly with statements INSERT, UPDATE, or DELETE.

First, 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 will also need to supply a --password or -p option, both for this mysql command and for those later in this post.

The following statements use GRANT to set up four new accounts:
  1. mysql> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
  2. mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
  3.     ->     WITH GRANT OPTION;
  4. mysql> CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';
  5. mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
  6.     ->     WITH GRANT OPTION;
  7. mysql> CREATE USER 'admin'@'localhost';
  8. mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
  9. mysql> CREATE USER '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 localhost. 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 localhost. 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. 
  • The 'admin'@'localhost' account has no password. This account can be used only by admin to connect from the localhost. It is granted the RELOAD and PROCESS administrative privileges. These privileges enable 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 other GRANT statements.
  • The 'dummy'@'localhost' account has no password. This account can be used only to connect from the localhost. No privileges are granted. It is assumed that you will grant specific privileges to the account later.
The statements that create accounts with no password will fail if the NO_AUTO_CREATE_USER SQL mode is enabled. To deal with this, use an IDENTIFIED BY clause that specifies a nonempty password.

To check the privileges for an account, use SHOW GRANTS:
mysql> SHOW GRANTS FOR 'admin'@'localhost';
  1. +-----------------------------------------------------+
  2. | Grants for admin@localhost                          |
  3. +-----------------------------------------------------+
  4. | GRANT RELOAD, PROCESS ON *.* TO 'admin'@'localhost' |
  5. +-----------------------------------------------------+
As an alternative to CREATE USER and 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:
  1. shell> mysql --user=root mysql
  2. mysql> INSERT INTO user
  3.     ->     VALUES('localhost','monty',PASSWORD('some_pass'),
  4.     ->     'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
  5. mysql> INSERT INTO user
  6.     ->     VALUES('%','monty',PASSWORD('some_pass'),
  7.     ->     'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',
  8.     ->     'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',
  9.     ->     '','','','',0,0,0,0);
  10. mysql> INSERT INTO user SET Host='localhost',User='admin',
  11.     ->     Reload_priv='Y', Process_priv='Y';
  12. mysql> INSERT INTO user (Host,User,Password)
  13.     ->     VALUES('localhost','dummy','');
  14. 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 CREATE USER, FLUSH PRIVILEGES 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. The INSERT statement for the admin account employs 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 CREATE USER does.

The next examples create three accounts and give them access to specific databases.
To create the accounts with CREATE USER and GRANT, use the following statements:
  1. shell> mysql --user=root mysql
  2. mysql> CREATE USER 'custom'@'localhost' IDENTIFIED BY 'obscure';
  3.     ->     ON bankaccount.*
  4.     ->     TO 'custom'@'localhost';
  5. mysql> CREATE USER 'custom'@'host47.example.com' IDENTIFIED BY 'obscure';
  6.     ->     ON expenses.*
  7.     ->     TO 'custom'@'host47.example.com';
  8. mysql> CREATE USER 'custom'@'server.domain' IDENTIFIED BY 'obscure';
  9.     ->     ON customer.*
  10.     ->     TO 'custom'@'server.domain';
The three accounts can be used as follows:
  • The first account can access the bankaccount database, but only from the localhost.
  • 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:
  1. shell> mysql --user=root mysql
  2. mysql> INSERT INTO user (Host,User,Password)
  3.     ->     VALUES('localhost','custom',PASSWORD('obscure'));
  4. mysql> INSERT INTO user (Host,User,Password)
  5.     ->     VALUES('host47.example.com','custom',PASSWORD('obscure'));
  6. mysql> INSERT INTO user (Host,User,Password)
  7.     ->     VALUES('server.domain','custom',PASSWORD('obscure'));
  8. mysql> INSERT INTO db
  9.     ->     (Host,Db,User,Select_priv,Insert_priv,
  10.     ->     Update_priv,Delete_priv,Create_priv,Drop_priv)
  11.     ->     VALUES('localhost','bankaccount','custom',
  12.     ->     'Y','Y','Y','Y','Y','Y');
  13. mysql> INSERT INTO db
  14.     ->     (Host,Db,User,Select_priv,Insert_priv,
  15.     ->     Update_priv,Delete_priv,Create_priv,Drop_priv)
  16.     ->     VALUES('host47.example.com','expenses','custom',
  17.     ->     'Y','Y','Y','Y','Y','Y');
  18. mysql> INSERT INTO db
  19.     ->     (Host,Db,User,Select_priv,Insert_priv,
  20.     ->     Update_priv,Delete_priv,Create_priv,Drop_priv)
  21.     ->     VALUES('server.domain','customer','custom',
  22.     ->     'Y','Y','Y','Y','Y','Y');
  23. mysql> FLUSH PRIVILEGES;
  • The first three INSERT statements add user table entries that permit 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> CREATE USER 'myname'@'%.mydomain.com' IDENTIFIED BY 'mypass';
To do the same thing by modifying the grant tables directly, do this:
  1. mysql> INSERT INTO user (Host,User,Password,...)
  2.     ->     VALUES('%.mydomain.com','myname',PASSWORD('mypass'),...);