User name and password

When connecting to the MySQL server with the command-line client, you should specify the user name and password for the account you want to use:

shell> mysql --user=monty --password=guess db_name

With a shorter option, the command should be:

shell> mysql -u monty -pguess db_name

Note: there must be no space between the - p option and the following password value. Password here is a special case.

The previous command includes the password value in the command line, which can be dangerous.

shell> mysql --user=monty --password db_name

shell> mysql -u monty -p db_name

Then the client program outputs a prompt and waits for you to enter the password.

Tip: when entering the password, the screen does not display ****** like this. It is blank. You can enter it safely.

Add new user account

You can create a MySQL account in two ways:

• Using GRANT statements
• Direct operation of MySQL authorization table

The best way is to use GRANT statements because they are more precise and have fewer errors.

Use the MySQL client program to set up new users

You must connect to the MySQL server as MySQL root user, and the root account must have INSERT permission and RELOAD management permission of MySQL database.

Use GRANT

First, use the MySQL program to connect to the server as MySQL root:

shell> MySQL --user=root MySQL

If you specify a password for the root account, you also need to provide -- password or - p options for the MySQL command and other commands in this section.

shell> mysql -uroot-p MYSQL

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 account created with GRANT statement has the following attributes:

• Two accounts have the same username Monty and password some_pass. Both accounts are super user accounts with full permissions and can do anything. An account ('monty '@' localhost ') is only used when connecting from the local machine. Another account ('monty '@'% ') can be used to connect from other hosts. Please note that both accounts of Monty must be able to connect with Monty from any Host. There is no localhost account. When Monty connects from the local machine, MySQL_ install_ The anonymous user account of localhost created by DB will prevail. As a result, Monty will be treated as an anonymous user. The reason is that the Host column value of the anonymous user account is more specific than the 'Monty' @ '%' account, so it ranks first in the sorting order of the user table.
• An account has a user name admin and no password. This account is only used to connect from this computer. RELOAD and PROCESS administrative permissions are granted. These permissions allow admin users to execute mysqladmin reload, mysqladmin refresh, mysqladmin flush XXX commands, and mysqladmin processlist. Access to the database was not granted. You can add such permissions through GRANT statements.
• An account has a user name dummy and no password. This account is only used to connect from this computer. Permission not granted. With the use permission in the GRANT statement, you can create an account without granting any permission. It can set all global permissions to 'N'. It is assumed that you will GRANT specific permissions to the account in the future.

Using INSERT

In addition to GRANT, you can directly use the INSERT statement to create the same account, and then use FLUSH PRIVILEGES to tell the server to reload the authorization table.

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 an account with INSERT, the reason for using FLUSH PRIVILEGES is to tell the server to reread the authorization table. Otherwise, the changes will not be noticed until the server is restarted. If GRANT is used, FLUSH PRIVILEGES is not required.

The PASSWORD() function is used with INSERT to encrypt the password. The GRANT statement encrypts the password for you, so password () is not required.

The 'Y' value enables account permissions. For admin accounts, you can also use the more readable INSERT extended syntax (using SET).

In the INSERT statement for the dummy account, only the Host, User, and Password columns in the User table are recorded as the specified values. None of the permissions are listed as explicit settings, so MySQL specifies them as the default value 'N'. This is equivalent to GRANT USAGE.

Please note that to set the super user account, you only need to create a user table entry with the permission column set to 'Y'. The user table permission is global, so other authorization tables no longer need entries.

example

The following example creates three accounts that allow them to access a dedicated database. The user name of each account is custom and the password is obscure.

Want to create an account with GRANT

shell> MySQL --user=root MySQL
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';

These three accounts can be used to:

• The first account can access the bankaccount database, but only from the local computer.
• 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 access.

Using insert statements

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 entries in the user table, allowing the user custom to connect from various hosts with a given password, but do not grant global permissions (all permissions are set to the default value 'N'). The last three INSERT statements add entries in the user table to grant bank account, expenses and customer database permissions to customer, but can only be accessed from the appropriate host. Generally, if you modify the authorization table directly, you should tell the server to reload the authorization table with FLUSH PRIVILEGES to make the permission change effective.

Specify domain

If you want a user to access from all machines in a given domain (for example, mydomain.com), you can use the GRANT statement with the '%' wildcard in the host part of the account name:

mysql> GRANT ...
    ->     ON *.*
    ->     TO 'myname'@'%.mydomain.com'
    ->     IDENTIFIED BY 'mypass';

To directly modify the authorization table:

mysql> INSERT INTO user (Host,User,Password,...)
    ->     VALUES('%.mydomain.com','myname',PASSWORD('mypass'),...);
mysql> FLUSH PRIVILEGES;

delete user

To remove an account, use the DROP USER statement

Restricted account resources

One way to limit the use of MySQL server resources is to set max_ user_ The connections system variable is set to a non-zero value. However, this method is strictly limited to the global and does not allow the management of specific accounts. Moreover, it only limits the number of simultaneous connections using a single account, not the operation after the client connects. Many MySQL administrators are interested in both types of control, especially Internet service providers.

In MySQL 5.1, you can limit the following server resources for specific accounts:

• Number of queries that the account can issue per hour
• Number of updates an account can send per hour
• The number of times the account can connect to the server per hour

The statements that the client can execute are counted according to the query limit. Only statements that modify the database or table count according to the update limit.

You can also limit the number of connections to the server at the same time for each account.

The account in this article is a single record in the user table. Each account is uniquely identified according to the user and Host column values.

As a prerequisite for using this feature, the user table of mysql database must contain resource related columns. Resource limits are saved in max_questions,max_updates,max_connections and max_user_connections column. If the user table does not have these columns, it must be upgraded.

To set resource limits WITH the GRANT statement, make the WITH Clause name each resource to be limited and the limit value according to the hourly count. For example, to create a new account that can access the customer database only in a restricted manner, execute this statement:

mysql> GRANT ALL ON customer.* TO 'francis'@'localhost'
    ->     IDENTIFIED BY 'frank'
    ->     WITH MAX_QUERIES_PER_HOUR 20
    ->          MAX_UPDATES_PER_HOUR 10
    ->          MAX_CONNECTIONS_PER_HOUR 5
    ->          MAX_USER_CONNECTIONS 2;

Restricted types do not need to be named all in the WITH clause, but they can be named in any order. Each hourly limit value should be an integer representing the count per hour. If the GRANT statement does not have a WITH clause, each limit value is set to the default value of zero (that is, there is no limit). For MAX_USER_CONNECTIONS, limited to an integer, indicates the maximum number of connections that the account can connect at one time. If the limit is set to the default value of zero, according to max_ USER_ The connections system variable determines the number of connections that the account can connect at the same time.

To set or change restrictions on existing accounts, use the GRANT USAGE statement at the global level (in *. *). The following statement can change the query limit of francis to 100:

mysql> GRANT USAGE ON *.* TO 'francis'@'localhost'
    ->     WITH MAX_QUERIES_PER_HOUR 100;

This statement does not change the existing permissions of the account, but only modifies the specified limit value.

To remove an existing restriction, set the value to zero. For example, to cancel the limit on the number of connections francis can make per hour, use this statement:

mysql> GRANT USAGE ON *.* TO 'francis'@'localhost'
    ->     WITH MAX_CONNECTIONS_PER_HOUR 0;

When the account uses resources, if there is a non-zero limit, the resource use will be counted.

When the server is running, it counts the number of times each account uses resources. If the number of connections of the account reaches the limit in the last hour, further connections of the account will be rejected. Similarly, if the account reaches the limit of the number of queries or updates, further queries or updates are rejected. In this case, a related error message is given.

Resources are calculated per account, not per client. For example, if the query limit of your account is 50, you cannot connect to the server through two clients at the same time, increasing the limit to 100. The two connected queries are calculated together.

You can reset the current hourly resource usage count globally for all accounts, or reset a given account separately:

• To reset the current count of all accounts to zero, execute FLUSH USER_RESOURCES statement. You can also reset the count by overloading the authorization table (for example, using the FLUSH PRIVILEGES statement or the mysqladmin reload command).
• The account specific restriction can be re granted to any value, which can be set to zero. To achieve this, use GRANT USAGE as described above and specify the limit value as the current limit value of the account.

Counter reset does not affect MAX_USER_CONNECTIONS restrictions.

When the server starts, all counts start from zero.

Set account password

1. Use the mysqladmin command:

shell> mysqladmin -u user_name -h host_name password "newpwd"

This command resets the password to the user in the user table that matches the user column_ The name and Host columns are the records of the client you initiated the connection to.

1. SET PASSWORD statement:

mysql> SET PASSWORD FOR 'jeffrey'@'%' = PASSWORD('biscuit');

Only users such as root who can update the mysql database can change the passwords of other users. If you do not connect as an anonymous user, you can change your password by omitting the FOR clause:

mysql> SET PASSWORD = PASSWORD('biscuit');

1. At the global level, use the GRANT USAGE statement (in *. *) to specify the password of an account without affecting the current permissions of the account:

mysql> GRANT USAGE ON *.* TO 'jeffrey'@'%' IDENTIFIED BY 'biscuit';

In general, it is best to use the above method to specify the password

1. You can also modify the user table directly:

4.1 to create a Password when creating a new account, provide a value in the Password column:

shell> mysql -u root mysql
mysql> INSERT INTO user (Host,User,Password)
	-> VALUES('%','jeffrey',PASSWORD('biscuit'));
mysql> FLUSH PRIVILEGES;

·
4.2 to change the Password of an existing account, use UPDATE to set the Password column value:

shell> mysql -u root mysql
mysql> UPDATE user SET Password = PASSWORD('bagel')
	-> WHERE Host = '%' AND User = 'francis';
 mysql> FLUSH PRIVILEGES;

When you use SET PASSWORD, INSERT or UPDATE to specify the password of the account, you must encrypt it with the PASSWORD() function. (the only exception is that if the password is empty, you don't need to use PASSWORD()). Password () is needed because the user table holds passwords encrypted, not in clear text. If you forget, you may set your password like this:

shell> mysql -u root mysql
mysql> INSERT INTO user (Host,User,Password)
    -> VALUES('%','jeffrey','biscuit');
mysql> FLUSH PRIVILEGES;

The result is that the password 'biscuit' is not encrypted after it is saved to the user table. When jeffrey uses this password to connect to the server, the value is encrypted and compared with the value saved in the user table. However, the saved value is the string 'biscuit', so the comparison will fail and the server rejects the connection:

shell> mysql -u jeffrey -pbiscuit test
Access denied

If you use the GRANT... IDENTIFIED BY statement or the mysqladmin password command to set the password, they will encrypt the password. In this case, you do not need to use the PASSWORD() function.