MySQL 使用者管理 說明

在前一篇看了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的內容，自己參考官方手冊。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15880878/viewspace-719951/，如需轉載，請註明出處，否則將追究法律責任。