MySQL中创建和管理用户
2014-11-11 16:34:03 
阿炯
在Mysql中，用户管理分为如下的部分：创建、移除、设置密码、移授权限等。一、用户管理
1.1、添加用户
可以用三种方式创建MySQL账户：
使用GRANT语句
直接操作MySQL授权表
使用Create user语句
最好的方法是使用GRANT语句，因为这样更精确，错误少。如果使用'create user‘语句创建后，仍然要使用grant语句来做进一步的授权。执行这项操作必须有mysql数据库的INSERT权限和RELOAD管理权限或全局的'CREATE USER'权限。
当然也可以只创建用户而未授予权限，通过GRANT语句中的USAGE权限，你可以创建账户而不授予任何权限，它将所有全局权限设为'N'。
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
CREATE USER user_specification [, user_specification] ...
user_specification:
user [IDENTIFIED BY [PASSWORD] 'password']
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
If you specify only the user name part of the account name, a host name part of '%' is used.
通过insert语句来执行插入操作：
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 SET Host='localhost',User='admin',
->Reload_priv='Y', Process_priv='Y';
mysql> FLUSH PRIVILEGES;
完成后，要记得刷新重载一下权限系统。
1.2、删除用户
DROP USER user [, user] ...
DROP USER语句用于删除一个或多个MySQL账户。要使用DROP USER，您必须拥有mysql数据库的全局CREATE USER权限或DELETE权限。使用与GRANT或REVOKE相同的格式为每个 账户命名；例如，'jeffrey'@'localhost'。 账户名称的用户和主机部分与用户表记录的User和Host列值相对应。
使用DROP USER，您可以取消一个账户和其权限，操作如下：
DROP USER user;
DROP USER 'jeffrey'@'localhost';
该语句可以删除来自所有授权表的帐户权限记录。
要点：DROP USER不能自动关闭任何打开的用户对话。而且，如果用户有打开的对话，此时取消用户，则命令不会生效，直到用户对话被关闭后才生效。一旦对话被关闭，用户也被取消，此用户再次试图登录时将会失败。这是有意设计的。 security context.
1.3、用户重命名
RENAME USER old_user TO new_user [, old_user TO new_user] ...
用于将已经存在的用户重新命名，当原用户不存在或新用户已经存在时，将会出错。必须要有用户创建权限或对mysql数据库有更新权限。
RENAME USER 'jeffrey'@'localhost' TO 'jeff'@'127.0.0.1';
If you specify only the user name part of the account name, a host name part of '%' is used.
1.4、重设用户的密码
SET PASSWORD [FOR user] =
{PASSWORD('cleartext password') | OLD_PASSWORD('cleartext password') | 'encrypted password'}
The SET PASSWORD statement assigns a password to a MySQL user account:
SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('cleartext password');
如果不指定'for'语句，即为当前用户修改密码：
SET PASSWORD = PASSWORD('cleartext password');
查看当前连接的用户：
SELECT CURRENT_USER();
You must have the UPDATE privilege for the mysql database.
In MySQL 5.1 and later, when the read_only system variable is enabled, SET PASSWORD requires the SUPER privilege, in addition to any other required privileges.
从5.1开始，
SET PASSWORD FOR 'bob'@'%.example.org' = PASSWORD('cleartext password');
注意：在终端中执行的命令，可能会记录到历史文件中(~/.mysql_history)，在手册页4.5.1.3, “mysql Logging”中有关于此的处理方法。
1.5、资源使用限制
限制MySQL服务器资源使用的一个方法是将max_user_connections系统变量设置为非零值。但该方法严格限于全局，不允许管理具体账户。并且它只限制使用单一账户同时连接的数量，而不是客户端连接后的操作。许多MySQL管理员对两种类型的控制均感兴趣，特别是Internet服务提供者。
在MySQL 5.1中,你可以为具体账户限制下面的服务器资源：
账户每小时可以发出的查询数
账户每小时可以发出的更新数
账户每小时可以连接服务器的次数
客户端可以执行的语句根据查询限制来记数。只有修改数据库或表的语句根据更新限制来记数。还可以限制每个账户的同时连接服务器的连接数。做为使用该特性的先决条件，mysql数据库的user表必须包含资源相关的列。资源限制保存在max_questions、max_updates、max_connections和max_user_connections列内。如果user表没有这些列，必须对它进行升级。
二、权限管理
授予的权限可以分为多个层级：
全局层级
全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。GRANT ALL ON *.*和REVOKE ALL ON *.*只授予和撤销全局权限。
数据库层级
数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db和mysql.host表中。GRANT ALL ON db_name.*和REVOKE ALL ON db_name.*只授予和撤销数据库权限。
表层级
表权限适用于一个给定表中的所有列。这些权限存储在mysql.talbes_priv表中。GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤销表权限。
列层级
列权限适用于一个给定表中的单一列。这些权限存储在mysql.columns_priv表中。当使用REVOKE时，您必须指定与被授权列相同的列。
子程序层级
CREATE ROUTINE, ALTER ROUTINE, EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且，除了CREATE ROUTINE外，这些权限可以被授予为子程序层级，并存储在mysql.procs_priv表中。
1、授权
mysql中的权限授予非常灵活，可为不同的用户在库、表、列及过程函数上进行多种授权。通常也是为了创建用户账户。
The GRANT statement grants privileges to MySQL user accounts. GRANT also serves to specify other account characteristics such as use of secure connections and limits on access to server resources. To use GRANT, you must have the GRANT OPTION privilege, and you must have the privileges that you are granting.A database administrator first uses CREATE USER to create an account, then GRANT to define its privileges and characteristics. For example:
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;
具体可参考：http://dev.mysql.com/doc/refman/5.1/en/grant.html
2、移权
与授权类似，
REVOKE
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level FROM user [, user] ...
REVOKE ALL PRIVILEGES, GRANT OPTION
FROM user [, user] ...
The REVOKE statement enables system administrators to revoke privileges from MySQL accounts. Each account name uses the format described in Section 6.2.3, “Specifying Account Names”. For example:
REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';
To revoke all privileges, use the second syntax, which drops all global, database, table, column, and routine privileges for the named user or users:
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
To use this REVOKE syntax, you must have the global CREATE USER privilege or the UPDATE privilege for the mysql database.
移除(撤销)用户和权限，并不意味将用户删除(从mysql.user表中删除)，要删除用户，需要借助于'drop user'语法了。
参考：http://dev.mysql.com/doc/refman/5.1/en/revoke.html
三、关于MySQL密码
MySQL用户密码是如何生成和保存的
如果你已经接触MySQL一段时间了，那么想必你一定知道MySQL把所有用户的用户名和密码的密文存放在mysql.user表中。MySQL在其内部是不存放用户的明文密码的。一般来说密文是通过不可逆加密算法得到的。这样即使敏感信息泄漏，除了暴力破解是无法快速从密文直接得到明文的。
MySQL用的是哪种不可逆算法来加密用户密码的
MySQL实际上是使用了两次SHA1夹杂一次unhex的方式对用户密码进行了加密。具体的算法可以用公式表示：password_str = concat('*', sha1(unhex(sha1(password)))) 我们可以用下面的方法做个简单的验证。
(mysql) > select password('mypassword'),concat('*',sha1(unhex(sha1('mypassword'))));
+-------------------------------------------+---------------------------------------------+
| password('mypassword') | concat('*',sha1(unhex(sha1('mypassword')))) |
+-------------------------------------------+---------------------------------------------+
| *FABE5482D5AADF36D028AC443D117BE1180B9725 | *fabe5482d5aadf36d028ac443d117be1180b9725 |
+-------------------------------------------+---------------------------------------------+
1 row in set (0.01 sec)
MySQL用户密码的不安全性
其实MySQL在5.6版本以前，对于对于安全性的重视度非常低，对于用户密码也不例外。例如，MySQL对于binary log中和用户密码相关的操作是不加密的。如果你向MySQL发送了例如create user,grant user ... identified by这样的携带初始明文密码的指令，那么会在binary log中原原本本的被还原出来。我们通过下面的例子来验证
创建一个用户
(mysql) > create user plain_password identified by 'freeoa_pass';
Query OK, 0 rows affected (0.00 sec)
用mysqlbinlog查看二进制日志
shell> mysqlbinlog binlog.000007
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create user plain_password identified by 'freeoa_pass'
/*!*/;
DELIMITER ;
# End of log file
MySQL5.6中对于用户密码的安全性加强
好在MySQL5.6开始对安全性有了一定的重视，为了杜绝明文密码出现在binlog中的情况，MySQL引入了一系列会以密文方式记录二进制日志的命令：
REATE USER … IDENTIFIED BY …
GRANT … IDENTIFIED BY …
SET PASSWORD …
SLAVE START … PASSWORD = … (as of 5.6.4)
CREATE SERVER … OPTIONS(… PASSWORD …) (as of 5.6.9)
ALTER SERVER … OPTIONS(… PASSWORD …) (as of 5.6.9)
细心你的也许会发现，change master to master_password=''命令不在这个范畴中。这也就意味着MySQL5.6中仍然使用这样的语法来启动replication时有安全风险的。这也就是为什么5.6中使用带有明文密码的change master to时会有warning提示，具体如下：
(none) > change master to master_host='127.0.0.1',master_port =6033,master_user='freeoa',master_password='rsandbox',master_auto_position=102;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
slave1 [localhost] {msandbox} ((none)) > show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure. |
| Note | 1760 | Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)