MySQL 用户管理 说明

MySQL 用户管理 说明

分类： MySQL 2011-12-02 15:00 462人阅读 评论(0) 收藏 举报

 

在前一篇看了MySQL的权限系统，具体参考：

MySQL 访问权限 系统 说明

http://blog.csdn.net/tianlesoftware/article/details/7032429

 

这篇看一下MySQL的用户管理这块。

 

一.User Names and Passwords

MySQL storesaccounts in the user table of the mysql database. An account is defined interms of a user name and the client host or hosts from which the user canconnect to the server. The account may also have a password. For informationabout account representation in the user table。MySQL 5.5 supports authentication plugins, so it is possible that anaccount authenticates using some external authentication method.

--MySQL 将账户信息存储在mysql 数据库的user 表里。 账户有username 和 host 组成， 这个host 是连接server 的client的host。 在MySQL5.5 开始支持plugins 验证，这样账户验证就可以使用外部的认证方式。

 

There areseveral distinctions between the way user names and passwords are used by MySQLand the way they are used by your operating system:

--根据操作系统的不同，user name 和password 在使用也有一些不同：

（1）User names,as used by MySQL for authentication purposes, have nothing to do with usernames (login names) as used by Windows or Unix. On Unix, most MySQL clients bydefault try to log in using the current Unix user name as the MySQL user name,but that is for convenience only. The default can be overridden easily, becauseclient programs permit any user name to be specified with a -u or --user option.Because this means that anyone can attempt to connect to the server using anyuser name, you cannot make a database secure in any way unless all MySQLaccounts have passwords. Anyone who specifies a user name for an account thathas no password is able to connect successfully to the server.

--在Unix 下，为了方便，大多数MySQL clients 默认使用unix当前用户作为MySQL的登陆用户。 可以在登陆时使用-u 参数指定其他用户。

 

（2） MySQL user names can be up to 16characters long. Operating system user names, because they are completelyunrelated to MySQL user names, may be of a different maximum length. Forexample, Unix user names typically are limited to eight characters.

       --MySQL 的用户名最长为16 个字符。 但是对于操作系统的用户名，可能不一样，比如unix 的user name 一般最长只有8个字符。

 

Warning：

The limit onMySQL user name length is hard-coded in the MySQL servers and clients, and tryingto circumvent it by modifying the definitions of the tables in the mysql databasedoes not work.

--注意这里的MySQL 用户名长度是在编码里控制的，不能通过修改表的定义来绕过这个问题。

 

You should neveralter any of the tables in the mysql database in any manner whatsoever exceptby means of the procedure that is described in Section 4.4.7, “mysql_upgrade —Check Tables for MySQL Upgrade”. Attempting to redefine MySQL's system tablesin any other fashion results in undefined (and unsupported!) behavior.

--修改表的定义是不推荐的，除非升级时可以进行。

 

（3） The serveruses MySQL passwords stored in the user table to authenticate clientconnections using MySQL native authentication (against passwords stored in the mysql.usertable). These passwords have nothing to do with passwords for logging in toyour operating system. There is no necessary connection between the “external”password you use to log in to a Windows or Unix machine and the password youuse to access the MySQL server on that machine.

--server 使用mysql.user 表里的密码来进行登陆验证。

If the serverauthenticates a client using a plugin, the authentication method that theplugin implements may or may not use the password in the user table. In thiscase, it is possible that an external password is also used to authenticate tothe MySQL server.

--如果server 验证使用了plugin，就可以使用plugin来实现验证，也可以使用external password。

 

（4） MySQLencrypts passwords stored in the user table using its own algorithm. Thisencryption is the same as that implemented by the PASSWORD() SQL function butdiffers from that used during the Unix login process. Unix password encryptionis the same as that implemented by the ENCRYPT() SQL function.

--MySQL 使用password() 来实现加密，而Unix 的密码加密使用的是encrypt()函数。

 

From version 4.1on, MySQL employs a stronger authentication method that has better passwordprotection during the connection process than in earlier versions. It is secureeven if TCP/IP packets are sniffed or the mysql database is captured. (Inearlier versions, even though passwords are stored in encrypted form in the usertable, knowledge of the encrypted password value could be used to connect tothe MySQL server.)

 

（5） It ispossible to connect to the server regardless of character set settings if theuser name and password contain only ASCII characters.To connect when the username or password contain non-ASCII characters, the client should call the mysql_options()C API function with the MYSQL_SET_CHARSET_NAME option and appropriate characterset name as arguments. This causes authentication to take place using thespecified character set. Otherwise, authentication will fail unless the serverdefault character set is the same as the encoding in the authenticationdefaults.

 

Standard MySQLclient programs support a --default-character-set option that causes mysql_options()to be called as just described. In addition, character set autodetection issupported as described in Section 9.1.4, “Connection Character Sets and Collations”.For programs that use a connector that is not based on the C API, the connectormay provide an equivalent to mysql_options() that can be used instead. Check theconnector documentation.

The precedingnotes do not apply for ucs2, utf16, and utf32, which are not permitted asclient character sets.

 

When you installMySQL, the grant tables are populated with an initial set of accounts. Thereafter,you normally set up, modify, and remove MySQL accounts using statements such asCREATE USER, GRANT, and REVOKE.

 

When you connectto a MySQL server with a command-line client, specify the user name andpassword as necessary for the account that you want to use:

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

 

If you prefer short options, the commandlooks like this:

shell> mysql -u monty –ppassword db_name

 

There must be nospace between the -p option and the following password value.

If you omit the password value followingthe --password or -p option on the command line, the client prompts for one.

You can use anoption file to avoid giving the password on the command line.

 

二. Adding User Accounts

You can create MySQL accounts in two ways:

--可以通过如下两种方法创建MySQL 帐号：

（1） By using statements intended forcreating accounts, such as CREATE USER or GRANT. These statements cause theserver to make appropriate modifications to the grant tables.

（2） By manipulating the MySQL granttables directly with statements such as INSERT, UPDATE, or DELETE.

 

The preferredmethod is to use account-creation statements(create user 和 grant) because they are moreconcise and less error-prone than manipulating the grant tables directly.

--使用account-creation 比直接修改表要简单，错误也相对较少。

 

Another optionfor creating accounts is to use one of several available third-party programsthat offer capabilities for MySQL account administration. phpMyAdmin is onesuch program.

另一种方法是第三方程序来创建account，如phpMyAdmin。

 

The followingexamples show how to use the mysql client program to set up new accounts. Theseexamples assume that privileges have been set up. This means that to makechanges, you must connect to the MySQL server as the MySQL root user, and the rootaccount must have the INSERT privilege for the mysql database and the RELOAD administrativeprivilege.

 

As noted in theexamples where appropriate, some of the statements will fail if the server'sSQL mode has been set to enable certain restrictions.

In particular,strict mode (STRICT_TRANS_TABLES, STRICT_ALL_TABLES) and NO_AUTO_CREATE_USER willprevent the server from accepting some of the statements. Workarounds areindicated for these cases.

 

--创建account示例：

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

shell> mysql --user=root mysql

 

If you haveassigned a password to the root account, you will also need to supply a --passwordor -p option, both for this mysql command and for those later in this section.

After connectingto the server as root, you can add new accounts. The following statements use GRANTto set up four new accounts:

mysql> CREATE USER 'monty'@'localhost'IDENTIFIED BY 'some_pass';

mysql> GRANT ALL PRIVILEGES ON *.* TO'monty'@'localhost'

 

-> WITH GRANT OPTION;

mysql> CREATE USER 'monty'@'%' IDENTIFIEDBY 'some_pass';

mysql> GRANT ALL PRIVILEGES ON *.* TO'monty'@'%'

 

-> WITH GRANT OPTION;

mysql> CREATE USER 'admin'@'localhost';

mysql> GRANT RELOAD,PROCESS ON *.* TO'admin'@'localhost';

mysql> CREATE USER 'dummy'@'localhost';

 

The accounts created by these statementshave the following properties:

--account 创建完之后有如下属性：

（1） Two of theaccounts have a user name of monty and a password of some_pass. Both accountsare superuser accounts with full privileges to do anything. The 'monty'@'localhost'account can be used only when connecting from the local host. The 'monty'@'%' accountuses the '%' wildcard for the host part, so it can be used to connect from anyhost.    

It is necessaryto have both accounts for monty to be able to connect from anywhere as monty. Withoutthe localhost account,the anonymous-user account for localhost that is createdby 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 forthis is that the anonymous-user account has a more specific Host column valuethan the 'monty'@'%' account and thus comes earlier in the user table sortorder.

（2） The 'admin'@'localhost'account has no password. This account can be used only by admin to connect fromthe local host.

It is grantedthe RELOAD and PROCESS administrative privileges. These privileges enable the adminuser to execute the mysqladmin reload, mysqladmin refresh, and mysqladminflush-xxx commands, as well as mysqladmin processlist . No privileges aregranted for accessing any databases. You could add such privileges later byissuing other GRANT statements.

（3） The 'dummy'@'localhost'account has no password. This account can be used only to connect from thelocal host. No privileges are granted. It is assumed that you will grantspecific privileges to the account later.

 

 

The statementsthat create accounts with no password will fail if the NO_AUTO_CREATE_USER SQLmode is enabled. To deal with this, use an IDENTIFIED BY clause that specifiesa nonempty password.

       --如果启动了NO_AUTO_CREATE_USER，那么创建空密码将失败。

 

To check the privileges for an account, useSHOW GRANTS:

mysql> SHOW GRANTS FOR'admin'@'localhost';

+-----------------------------------------------------+

| Grants for admin@localhost |

+-----------------------------------------------------+

| GRANT RELOAD, PROCESS ON *.* TO'admin'@'localhost' |

+-----------------------------------------------------+

 

As analternative to CREATE USER and GRANT, you can create the same accounts directlyby issuing INSERT statements and then telling the server to reload the granttables using FLUSH PRIVILEGES:

--可以直接使用insert 来创建account，但是这种不会自动同步到内存，需要手工的执行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',

-> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y',

-> '','','','',0,0,0,0);

mysql> INSERT INTO user SETHost='localhost',User='admin',

-> Reload_priv='Y', Process_priv='Y';

mysql> INSERT INTO user(Host,User,Password)

-> VALUES('localhost','dummy','');

mysql> FLUSH PRIVILEGES;

 

When you createaccounts with INSERT, it is necessary to use FLUSH PRIVILEGES to tell theserver to reload the grant tables. Otherwise,the changes go unnoticed until yourestart the server. With CREATE USER, FLUSH PRIVILEGES is unnecessary.

The reason forusing the PASSWORD() function with INSERT is to encrypt the password. The CREATEUSER statement encrypts the password for you, so PASSWORD() is unnecessary.

--create user 会自动调用password()函数，而执行insert 必须手工指定函数。

 

The 'Y' valuesenable privileges for the accounts. Depending on your MySQL version, you mayhave 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 INSERTsyntax using SET.

 

In the INSERT statementfor the dummy account, only the Host, User, and Password columns in the user tablerow are assigned values. None of the privilege columns are set explicitly, soMySQL assigns them all the default value of 'N'. This is equivalent to what CREATEUSER does.

 

If strict SQLmode is enabled, all columns that have no default value must have a valuespecified. In this case, INSERT statements must explicitly specify values forthe ssl_cipher, x509_issuer, and x509_subject columns.

 

To set up asuperuser account, it is necessary only to insert a user table row with allprivilege columns set to 'Y'. The user table privileges are global, so noentries in any of the other grant tables are needed.

 

The nextexamples create three accounts and give them access to specific databases. Eachof them has a user name of custom and password of obscure.

--下面的示例创建3个account，并赋给他们指定数据库的权限。

 

To create theaccounts with CREATE USER and GRANT, use the following statements:

shell> mysql --user=root mysql

mysql> CREATE USER 'custom'@'localhost'IDENTIFIED BY 'obscure';

mysql> GRANTSELECT,INSERT,UPDATE,DELETE,CREATE,DROP

-> ON bankaccount.*

-> TO 'custom'@'localhost';

mysql> CREATE USER'custom'@'host47.example.com' IDENTIFIED BY 'obscure';

mysql> GRANTSELECT,INSERT,UPDATE,DELETE,CREATE,DROP

-> ON expenses.*

-> TO 'custom'@'host47.example.com';

mysql> CREATE USER'custom'@'server.domain' IDENTIFIED BY 'obscure';

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP

-> ON customer.*--这里指定了数据库对象名

-> TO 'custom'@'server.domain';

 

The three accounts can be used as follows:

（1） The first account can access the bankaccountdatabase, but only from the local host.

（2） The second account can access the expensesdatabase, but only from the host host47.example.com.

（3） The third account can access the customerdatabase, but only from the host server.domain.

 

To set up the customaccounts without GRANT, use INSERT statements as follows to modify the granttables directly:

当设置账户的权限时，可以直接使用insert 语句修改相关的表：

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 INSERTstatements add user table entries that permit the user custom to connect fromthe various hosts with the given password, but grant no global privileges (allprivileges are set to the default value of 'N'). The next three INSERT statementsadd db table entries that grant privileges to custom for the bankaccount, expenses,and customer databases, but only when accessed from the proper hosts. As usualwhen you modify the grant tables directly, you must tell the server to reloadthem with FLUSH PRIVILEGES so that the privilege changes take effect.

 

To create a userwho 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 granttables directly, do this:

mysql> INSERT INTO user(Host,User,Password,...)

-> VALUES('%.mydomai

 

三. Removing User Accounts

To remove an account, use the DROP USER statement.

 

mysql> create user 'dave'@'%' identifiedby 'dave';

Query OK, 0 rows affected (0.01 sec)

 

mysql> grant  all privileges on *.* to 'dave'@'%';

Query OK, 0 rows affected (0.00 sec)

 

mysql> drop user 'dave'@'%';

Query OK, 0 rows affected (0.00 sec)

 

 

四. Setting Account Resource Limits

One means oflimiting use of MySQL server resources is to set the global max_user_connectionssystem variable to a nonzero value. This limits the number of simultaneousconnections that can be made by any given account, but places no limits on whata client can do once connected. In addition, setting max_user_connections doesnot enable management of individual accounts. Both types of control are ofinterest to many MySQL administrators, particularly those working for InternetService Providers.

-- 设置max_user_connections 为非0值，该值限制同时最大的连接数。

mysql> showvariables like 'max_user_con%';

+----------------------+-------+

|Variable_name        | Value |

+----------------------+-------+

|max_user_connections | 0     |

+----------------------+-------+

1 row in set(0.02 sec)

 

In MySQL 5.5, you can limit use of thefollowing server resources for individual accounts:

--在5.5 版本里，可以设置每个用户的如下资源限制：

 

（1） The number of queries that anaccount can issue per hour

（2） The number of updates that anaccount can issue per hour

（3） The number of times an account canconnect to the server per hour

（4） The number of simultaneousconnections to the server by an account

 

Any statementthat a client can issue counts against the query limit (unless its results areserved from the query cache). Only statements that modify databases or tablescount against the update limit.

An “account” inthis context corresponds to a row in the mysql.user table. That is, aconnection is assessed against the User and Host values in the user table rowthat applies to the connection. For example, an account 'usera'@'%.example.com'corresponds to a row in the user table that has User and Host values of usera and%.example.com, to permit usera to connect from any host in the example.com domain.In this case, the server applies resource limits in this row collectively toall connections by usera from any host in the example.com domain because allsuch connections use the same account.

 

Before MySQL5.0.3, an “account” was assessed against the actual host from which a userconnects. This older method accounting may be selected by starting the serverwith the --old-style-user-limits option. In this case, if usera connectssimultaneously from host1.example.com and host2.example.com, the server appliesthe account resource limits separately to each connection.

 

If usera connectsagain from host1.example.com, the server applies the limits for that connectiontogether with the existing connection from that host.

 

To set resourcelimits for an account, use the GRANT statement. Provide a WITH clause that nameseach resource to be limited. The default value for each limit is zero (nolimit).

--可以使用grant 命令对账户进行资源限制，默认情况没有资源资源限制。

 

For example, tocreate a new account that can access the customer database, but only in alimited fashion, issue these statements:

--示例

mysql> CREATE USER 'francis'@'localhost'IDENTIFIED BY 'frank';

mysql> GRANT ALL ON customer.* TO'francis'@'localhost'

-> WITH MAX_QUERIES_PER_HOUR 20

-> MAX_UPDATES_PER_HOUR 10

-> MAX_CONNECTIONS_PER_HOUR 5

-> MAX_USER_CONNECTIONS 2;

 

The limit typesneed not all be named in the WITH clause, but those named can be present in anyorder. The value for each per-hour limit should be an integer representing acount per hour. For MAX_USER_CONNECTIONS, the limit is an integer representingthe maximum number of simultaneous connections by the account. If this limit isset to zero, the global max_user_connections system variable value determinesthe number of simultaneous connections. If max_user_connections is also zero,there is no limit for the account.

--不是所有的限制参数都用with来指定，限定参数值需要用整数来表示。 如果为0 表示不受限制。

 

To modifyexisting limits for an account, use a GRANT USAGE statement at the global level(ON *.*). The following statement changes the query limit for francis to 100:

--修改一个已经存在账户的权限，使用grant usage 命令，示例：

mysql> GRANT USAGE ON *.* TO'francis'@'localhost'

-> WITH MAX_QUERIES_PER_HOUR 100;

 

The statementmodifies only the limit value specified and leaves the account otherwiseunchanged.

 

To remove alimit, set its value to zero. For example, to remove the limit on how manytimes per hour francis can connect, use this statement:

--移除限制，将参数值设为0即可，示例：

mysql> GRANT USAGE ON *.* TO'francis'@'localhost'

-> WITH MAX_CONNECTIONS_PER_HOUR 0;

 

As mentionedpreviously, the simultaneous-connection limit for an account is determined fromthe MAX_USER_CONNECTIONS limit and the max_user_connections system variable.Suppose that the global max_user_connections value is 10 and three accounts haveresource limits specified with GRANT:

       对于最大同时连接参数：MAX_USER_CONNECTIONS，可以在全局指定后，在每个账户单独指定，如：

 

GRANT ... TO 'user1'@'localhost' WITHMAX_USER_CONNECTIONS 0;

GRANT ... TO 'user2'@'localhost' WITHMAX_USER_CONNECTIONS 5;

GRANT ... TO 'user3'@'localhost' WITHMAX_USER_CONNECTIONS 20;

 

user1 has aconnection limit of 10 (the global max_user_connections value) because it has azero MAX_USER_CONNECTIONS limit). user2 and user3 have connection limits of 5and 20, respectively, because they have nonzero MAX_USER_CONNECTIONS limits.

 

The serverstores resource limits for an account in the user table row corresponding tothe account. The max_questions, max_updates, and max_connections columns storethe per-hour limits, and the max_user_connections column stores the MAX_USER_CONNECTIONSlimit.

--server 存储resource limits 在user table里。

 

Resource-usecounting takes place when any account has a nonzero limit placed on its use ofany of the resources.

当account 的 资源限制启动后，就会统计资源的使用。

 

As the serverruns, it counts the number of times each account uses resources. If an accountreaches its limit on number of connections within the last hour, furtherconnections for the account are rejected until that hour is up. Similarly, ifthe account reaches its limit on the number of queries or updates, furtherqueries or updates are rejected until the hour is up. In all such cases, anappropriate error message is issued.

--如果使用次数超过资源的限制，就会决绝其他的操作。

 

Resourcecounting is done per account, not per client. For example, if your account hasa query limit of 50, you cannot increase your limit to 100 by making twosimultaneous client connections to the server. Queries issued on bothconnections are counted together.

--resource count 根据配个account 来统计，而不是根据client。

 

The currentper-hour resource-use counts can be reset globally for all accounts, orindividually for a given account:

（1） To reset thecurrent counts to zero for all accounts, issue a FLUSH USER_RESOURCES statement.The counts also can be reset by reloading the grant tables (for example, with aFLUSH PRIVILEGES statement or a mysqladmin reload command).

（2） The countsfor an individual account can be set to zero by re-granting it any of itslimits. To do this, use GRANT USAGE as described earlier and specify a limitvalue equal to the value that the account currently has.

 

Counter resetsdo not affect the MAX_USER_CONNECTIONS limit.

 

All counts beginat zero when the server starts; counts are not carried over through a restart.

--所有的counts 在server 启动是为0. 当serverrestart 之后，counts 重新开始统计。

 

For the MAX_USER_CONNECTIONSlimit, an edge case can occur if the account currently has open the maximum numberof connections permitted to it: A disconnect followed quickly by a connect canresult in an error (ER_TOO_MANY_USER_CONNECTIONS or ER_USER_LIMIT_REACHED) ifthe server has not fully processed the disconnect by the time the connectoccurs. When the server finishes disconnect processing, another connection willonce more be permitted.

 

五.Assigning Account Passwords

Requiredcredentials for clients that connect to the MySQL server can include apassword. This section describes how to assign passwords for MySQL accounts. InMySQL 5.5, it is also possible for clients to authenticate using plugins.

Client 连接server 需要密码或者使用plugins。  

 

To assign apassword when you create a new account with CREATE USER, include an IDENTIFIEDBY clause:

--在创建account时指定密码：

mysql> CREATE USER 'jeffrey'@'localhost'

-> IDENTIFIED BY 'mypass';

 

To assign orchange a password for an existing account, one way is to issue a SET PASSWORD statement:

--修改已经存在的用户的密码：

mysql> SET PASSWORD FOR

-> 'jeffrey'@'localhost' =PASSWORD('mypass'); --注意密码函数

 

MySQL storespasswords in the user table in the mysql database. Only users such as root thathave update access to the mysql database can change the password for otherusers. If you are not connected as an anonymous user, you can change your ownpassword by omitting the FOR clause:

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

 

You can also usea GRANT USAGE statement at the global level (ON *.*) to assign a password to anaccount without affecting the account's current privileges:

--可以使用grant usage 命令给用户设置全局的密码，且不影响用户当前的权限。

mysql> GRANT USAGE ON *.* TO 'jeffrey'@'localhost'

-> IDENTIFIED BY 'mypass';

 

To assign a password from the command line,use the mysqladmin command:

--在命令行使用mysqladmin设置密码：

shell> mysqladmin -u user_name -h host_namepassword "newpwd"

 

The account forwhich this command sets the password is the one with a user table row thatmatches user_name in the User column and the client host from which you connectin the Host column.

 

It is preferableto assign passwords using one of the preceding methods, but it is also possibleto modify the user table directly. In this case, you must also use FLUSHPRIVILEGES to cause the server to reread the grant tables. Otherwise, thechange remains unnoticed by the server until you restart it.

--可以使用grant 命令修改用户的密码，也可以直接修改usertable。如果直接修改，需要flush privileges。

 

（1）    To establish a password for a new account, provide a value for the Passwordcolumn:

--给一个新账户设置密码

mysql> INSERT INTO mysql.user(Host,User,Password)

-> VALUES('localhost','jeffrey',PASSWORD('mypass'));

mysql> FLUSH PRIVILEGES;

 

（2）    To change the password for an existing account, use UPDATE to setthe Password column value:

--修改已经存在用户的密码

mysql> UPDATE mysql.user SET Password =PASSWORD('bagel')

-> WHERE Host = 'localhost' AND User ='francis';

mysql> FLUSH PRIVILEGES;

 

Duringauthentication when a client connects to the server, MySQL treats the passwordin the user table as an encrypted hash value (the value that PASSWORD() wouldreturn for the password). When assigning a password to an account, it isimportant to store an encrypted value, not the plaintext password. Use thefollowing guidelines:

--mysql 使用的是加密后的密码，没有使用明文密码

（1） When you assign a password using CREATEUSER, GRANT with an IDENTIFIED BY clause, or the mysqladmin password command,they encrypt the password for you. Specify the literal plaintext password:

--在创建用户或者使用mysqladmin时，自动使用密码函数

mysql> CREATE USER 'jeffrey'@'localhost'

-> IDENTIFIED BY 'mypass';

 

（2） For CREATE USER or GRANT, you canavoid sending the plaintext password if you know the hash value that PASSWORD()would return for the password. Specify the hash value preceded by the keyword PASSWORD:

       --使用create user 和grant 命令时，如果密码出现明文，可以先使用password()函数加密，然后在使用加密后的密码，如：

mysql> CREATE USER 'jeffrey'@'localhost'

-> IDENTIFIED BY PASSWORD'*90E462C37378CED12064BB3388827D2BA3A9B689';

 

（3） When you assign an account anonempty password using SET PASSWORD, INSERT, or UPDATE, you must use the PASSWORD()function to encrypt the password, otherwise the password is stored asplaintext. Suppose that you assign a password like this:

mysql> SET PASSWORD FOR

-> 'jeffrey'@'localhost' = 'mypass';

 

The result isthat the literal value 'mypass' is stored as the password in the user table,not the encrypted value. When Jeffrey attempts to connect to the server usingthis password, the value is encrypted and compared to the value stored in the usertable.

However, thestored value is the literal string 'mypass', so the comparison fails and theserver rejects the connection with an Access denied error.

       --MySQL连接时也是用加密之后的密码进行匹配，所以密码必须加密，不然登陆就会失败。

 

In MySQL 5.5,enabling the read_only system variable prevents the use of the SET PASSWORD statementby any user not having the SUPER privilege.

 

有关PluggableAuthentication的内容，自己参考官方手册。