mysql 用户管理-权限管理

        学习了用户管理，再学习下权限管理。        

3，权限管理

        权限管理主要是对登录到MySQL的用户进行权限验证。所有用户的权限都存储在MySQL的权限表中，不合理的权限规划会给MySQL服务器带来安全隐患。数据库管理员要对所有用户的权限进行合理规划管理.MySQL权限系统的主要功能是证实连接到一台给定主机的用户，并且赋予该用户在数据库上的SELECT、INSERT、UPDATE和 DELETE 权限。

1，MySQL的各种权限

        账户权限信息被存储在MySQL数据库的 user、db、host、tables_priv、columns_priv和procs_priv表中。在 MySQL启动时，服务器将这些数据库表中权限信息的内容读入内存。

        GRANT和REVOKE语句所涉及的权限的名称如表7所示，还有在授权表中每个权限的表列名称和每个权限有关的操作对象等。

                        表7GRANT和REVOKE语句中可以使用的权限

权限	user表中对应的列	权限的范围
CREATE	Create_priv	数据库、表或索引
DROP	Drop_priv	数据库、表或视图
GRANT OPTION	Grant_priv	数据库、表或存储过程
REFERENCES	References_priv	数据库或表
EVENT	Event_priv	数据库
ALTER	Alter_priv	数据库
DELETE	Delete_priv	表
INDEX	Index_priv	表
INSERT	Insert_priv	表
SELECT	Select_priv	表或列
UPDATE	Update_priv	表或列
CREATE TEMPORARY TABLES	Create_tmp_table_priv	表
LOCK TABLES	Lock_tables_priv	表
TRIGGER	Trigger_priv	表
CREATE VIEW	Create_view_priv	视图
SHOW VIEW	Show_view_priv	视图
ALTER ROUTINE	Alter_routine_priv	存储过程和函数
CREATE ROUTINE	Create_routine_priv	存储过程和函数
EXECUTE	Execute_priv	存储过程和函数
FILE	File_priv	访问服务器上的文件
CREATE TABLESPACE	Create_tablespace_priv	服务器管理
CREATE USER	Create_user_priv	服务器管理
PROCESS	Process_priv	存储过程和函数
RELOAD	Reload_priv	访问服务器上的文件
REPLICATION CLIENT	Repl_client_priv	服务器管理
REPLICATION SLAVE	Repl_slave_priv	服务器管理
SHOW DATABASES	Show_db_priv	服务器管理
SHUTDOWN	Shutdown_priv	服务器管理
SUPER	Super_priv	服务器管理

        （1）CREATE和 DROP权限，可以创建新数据库和表，或删除（移掉)已有数据库和表。如果将MySQL数据库中的DROP权限授予某用户，用户可以删掉 MySQL访问权限保存的数据库。

        （2）SELECT、INSERT、UPDATE和 DELETE 权限允许在一个数据库现有的表上实施操作。

        （3）SELECT权限只有在它们真正从一个表中检索行时才被用到。

        （4）INDEX权限允许创建或删除索引，INDEX适用已有表。如果具有某个表的CREATE权限，可以在CREATE TABLE语句中包括索引定义。

        （5）ALTER权限，可以使用ALTER TABLE来更改表的结构和重新命名表。

        （6）CREATE ROUTINE权限来创建保存的程序（函数和程序），ALTER ROUTINE权限用来更改和删除保存的程序，EXECUTE权限用来执行保存的程序。

        （7）GRANT权限允许授权给其他用户。可用于数据库、表和保存的程序。

        （8）FILE权限给予用户使用LOAD DATA INFILE和SELECT ... INTO OUTFILE语句读或写服务器上的文件,任何被授予FILE权限的用户都能读或写MySQL 服务器上的任何文件。(说明用户可以读任何数据库目录下的文件，因为服务器可以访问这些文件）。FILE 权限允许用户在 MySQL服务器具有写权限的目录下创建新文件，但不能覆盖已有文件。

        其余的权限用于管理性操作，它使用MySQLadmin程序或SQL语句实施。表8显示每个权限允许执行的MySQLadmin命令。

                                表8 不同权限下可以使用的 MySQLadmin命令

权限	权限拥有者允许执行的命令
RELOAD	flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload
SHUTDOWN	shutdown
PROCESS	processlist
SUPER	kill

        （1）reload命令告诉服务器将授权表重新读入内存：flush-privileges是 reload的同义词；refresh命令清空所有表并关闭/打开记录文件；其他flush-xxx 命令执行类似refresh的功能，但是范围更有限，并且在某些情况下可能更好用。例如，如果只是想清空记录文件，flush-logs是比 refresh更好的选择。

        （2）shutdown命令关掉服务器。只能从 MySQLadmin发出命令。

        （3）processlist命令显示在服务器内执行的线程的信息(即其他账户相关的客户端执行的语句）。kill 命令杀死服务器线程。用户总是能显示或杀死自己的线程，但是需要 PROCESS权限来显示或杀死其他用户和SUPER权限启动的线程。

        （4）kill命令能用来终止其他用户或更改服务器的操作方式。总的来说，只授予权限给需要他们的那些用户。

2，授权

        授权就是为某个用户授予权限。合理的授权可以保证数据库的安全。MySQL中可以使用GRANT语句为用户授予权限。

授予的权限可以分为多个层级：

1，全局层级

        全局权限适用于一个给定服务器中的所有数据库。这些权限存储在 MySQL.user 表中。GRANT ALL ON *.*和REVOKE ALL ON*.*只授予和撤销全局权限。

2，数据库层级

        数据库权限适用于一个给定数据库中的所有目标。这些权限存储在 MySQL.db和MySQL.host表中。GRANT ALL ON db_name.和REVOKE ALL ON db_name.*只授予和撤销数据库权限。

3，表层级

        表权限适用于一个给定表中的所有列。这些权限存储在 MySQL.talbes_priv表中。GRANTALL ON db_name.tbl_name和 REVOKE ALL ON db_name.tbl_name 只授予和撤销表权限。

4，列层级

        列权限适用于一个给定表中的单一列。这些权限存储在MySQL.columns_priv表中。当使用REVOKE时,必须指定与被授权列相同的列。

5，子程序层级

        CREATE ROUTINE、ALTER ROUTINE、EXECUTE 和 GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且，除了CREATE ROUTINE外，这些权限可以被授予子程序层级，并存储在MySQL.procs_priv表中。

在 MySQL中，必须是拥有GRANT权限的用户才可以执行GRANT语句。

要使用GRANT或REVOKE，必须拥有GRANT OPTION权限，并且必须用于正在授予或撤销的权限。GRANT的语法如下：

GRANT priv_type [(columns)] [. priv_type [(columns)]] ...
ON [object_type] tablel, table2,., tablen
To user [IDENTIEIED BY [PASSWORD] 'password']
[,user [IDENTIFIED BY [PASSWORD] 'password']] ...[WITH GRANT OPTION]
object_type = TABLE | FUNCTION | PROCEDURE

        其中，priv_type参数表示权限类型; columns 参数表示权限作用于哪些列上，不指定该参数，表示作用于整个表;table1,table2,... .tablen表示授予权限的列所在的表; object_type指定授权作用的对象类型包括TABLE(表)、FUNCTION（函数）和PROCEDURE(存储过程)，当从旧版本的MySQL升级时，要使用object_tpye子句，必须升级授权表;user参数表示用户账户，由用户名和主机名构成，形式是“'username'@"hostname'”;IDENTIFIED BY参数用于设置密码。

WITH关键字后可以跟一个或多个GRANT OPTION。GRANT OPTION的取值有5个，

意义如下：

        （1）GRANT OPTION：被授权的用户可以将这些权限赋予别的用户。

        （2）MAX_QUERIES_PER_HOUR count：设置每个小时可以执行count次查询。

        （3）MAX_UPDATES_PER_HOUR count：设置每小时可以执行count次更新。

        （4）MAX_CONNECTIONS_PER_HOUR count：设置每小时可以建立count个连接。

        （5）MAX_USER_CONNECTIONS count：设置单个用户可以同时建立count个连接。

        【例15】使用GRANT 语句创建一个新的用户 grantUser，密码为“grantpwd”。用户grantUser对所有的数据有查询、插入权限，并授于GRANT权限。GRANT语句及其执行结果如下：

GRANT SELECT, INSERT ON *.* TO 'grantUser'@'localhost'
IDENTIFIED BY 'grantpwd'
WITH GRANT OPTION;

结果显示执行成功，使用SELECT语句查询用户testUser2的权限：

SELECT Host, User, Select_priv, Insert_priv, Grant_priv 
FROM mysql.user where  user = 'grantUser';

        查询结果显示用户test User2被创建成功，并被赋予SELECT、INSERT 和 GRANT权限，其相应字段值均为‘Y’。

        被授予GRANT 权限的用户可以登录MySQL并创建其他用户账户，在这里为名称是grantUser的用户。读者可以使用grantUser登录，并按照【例4】中的过程创建并授权其他账户。

3，收回权限

        收回权限就是取消已经赋予用户的某些权限。收回用户不必要的权限可以在一定程度上保证系统的安全性。MySQL中使用REVOKE 语句取消用户的某些权限。使用REVOKE收回权限之后，用户账户的记录将从db、host、tables_priv和 columns_priv表中删除，但是用户账号记录仍然在.user 表中保存（删除user表中的账户记录，使用DROP USER语句)。

        在将用户账户从user 表删除之前，应该收回相应用户的所有权限，REVOKE语句有两种语法格式，第一种语法是收回所有用户的所有权限，此语法用于取消对于已命名的用户的所有全局层级、数据库层级、表层级和列层级的权限，其语法如下：

REVOKE ALL PRIVILEGES, GRANT OPTION
FROM 'user'@'host" [,'user'@'host' ...]

        REVOKE语句必须和FROM语句一起使用，FROM语句指明需要收回权限的账户。另一种为长格式的REVOKE语句，基本语法如下：

REVOKE priv_type [(columns)] [,priv_type [(eolumns)]]  ...
ON  tablel, table2, .., tablen
FROM 'user'@'host'[,'user'@'host' ...]

        该语法收回指定的权限。其中，priv_type参数表示权限类型; columns参数表示权限作用于哪些列上，如果不指定该参数，表示作用于整个表; table1,table2....tablen表示从哪个表中收回权限;"user@"host'参数表示用户账户，由用户名和主机名构成。

        要使用REVOKE语句，必须拥有MySQL数据库的全局CREATE USER权限或UPDATE权限。

        【例16】使用REVOKE语句取消用户 testUser的更新权限。REVOKE语句及其执行结果如下：

REVOKE UPDATE ON *.* FROM 'testUser'@'localhost';

执行结果显示执行成功，使用SELECT语句查询用户test 的权限：

SELECT Host, User, Select_priv Update_priv, Grant_privFROM MySQL.user where  user = 'testUser';

        查询结果显示用户testUser的 Update_priv字段值为“N”，UPDATE权限已经被收回。

        当从旧版本的MySQL升级时，如果要使用EXECUTE、CREATE VIEW、SHOW VIEW.、CREATE USER、CREATE ROUTINE 和ALTER ROUTINE 权限，必须首先升级授权表。

4，查看权限

        SHOW GRANTS语句可以显示指定用户的权限信息，使用SHOW GRANTS查看账户信息的基本语法格式如下：

SHOW GRANTS FOR 'user'@ 'host';

        其中，user 表示登录用户的名称，host表示登录的主机名称或者IP地址。在使用该语句时，要确保指定的用户名和主机名都要用单引号括起来，并使用‘@’符号，将两个名字分隔开。

        【例17】使用SHOW GRANTS 语句查询用户testUser 的权限信息。SHOW GRANTS语句及其执行结果如下：

SHOW GRANTS FOR 'testUser'@'localhost';

        返回结果的第1行显示了user 表中的账户信息；接下来的行以GRANT SELECT ON关键字开头，表示用户被授予了SELECT权限;*.*表示SELECT权限作用于所有数据库的所有数据表；IDENTIFIED BY PASSWORD关键字后面为用户加密后的密码。

        在这里，只是定义了个别的用户权限，GRANT可以显示更加详细的权限信息，包括全局级的和非全局级的权限，如果表层级或者列层级的权限被授予用户的话，它们也能在结果中显示出来。

        在前面创建用户时，查看新建的账户时使用SELECT语句，也可以通过SELECT 语句查看user表中的各个权限字段以确定用户的权限信息，其基本语法格式如下：

SELECT privileges_list FROM user WHERE user ='username', host= 'hostname';

        其中，privileges_list为想要查看的权限字段，可以为Select_priv、Insert_priv等。根据需要选择要查询的字段。

5，访问控制

正常情况下，并不希望每个用户都可以执行所有的数据库操作。当MySQL 允许一个用户执行各种操作时，它将首先核实该用户向MySQL服务器发送的连接请求，然后确认用户的操作请求是否被允许。MySQL 的访问控制分为两个阶段：连接核实阶段和请求核实阶段。

1，连接核实阶段

        当连接MySQL服务器时，服务器基于用户的身份以及用户是否能通过正确的密码身份验证来接受或拒绝连接。即客户端用户连接请求中会提供用户名称、主机地址名和密码，MySQL使用user表中的3个字段(Host、User 和 Password）执行身份检查，服务器只有在user表记录的Host和User字段匹配客户端主机名和用户名，并且提供正确的密码时才接受连接。如果连接核实没有通过，服务器完全拒绝访问;否则，服务器接受连接，然后进入阶段⒉等待用户请求。

2，请求核实阶段

                建立连接之后，服务器进入访问控制的阶段2。对在此连接上的每个请求，服务器检查用户要执行的操作，然后检查是否有足够的权限来执行它。这正是在授权表中的权限列发挥作用的地方。这些权限可以来自user、db、host、tables_priv或columns_priv表。

确认权限时，MySQL首先检查user表，如果指定的权限没有在user表中被授权;MySQL将检查db表，db表是下一安全层级，其中的权限限定于数据库层级，在该层级的SELECT权限允许用户查看指定数据库的所有表中的数据;如果在该层级没有找到限定的权限,则 MySQL继续检查tables_priv表以及columns_priv表，如果所有权限表都检查完毕，但还是没有找到允许的权限操作，MySQL将返回错误信息，用户请求的操作不能执行，操作失败。请求核实的过程如图1所示。

                                        图1 MySQL请求核实过程

        提示：MySQL通过向下层级的顺序检查权限表（从user表到columns_priv表)，但并不是所有的权限都要执行该过程。例如，一个用户登录到MySQL服务器之后只执行对MySQL的管理操作，此时，只涉及管理权限，因此 MySQL只检查user表。另外，如果请求的权限操作不被允,MySQL也不会继续检查下一层级的表。

6，疑问解答

        疑问1∶已经将一个账户的信息从数据库中完全删除，为什么该用户还能登录数据库?出现这种情况的原因可能有多种，最有可能的是在user 数据表中存在匿名账户。在user表中匿名账户的User 字段值为空字符串，这会允许任何人连接到数据库，检测是否存在匿名登录用户的方法是，输入以下语句：

SELECT * FROM user WHERE User =''';

        如果有记录返回，则说明存在匿名用户，需要删除该记录，以保证数据库的访问安全，删除语句为：

DELETE FROM user WHERE user= '';

        这样一来，该账户肯定不能登录MySQL服务器了。

疑问2∶应该使用哪种方法创建用户?

        前面介绍了创建用户的几种方法：GRANT语句、CREATE USER语句和直接操作user表。

        一般情况，最好使用GRANT或者CREATE USER语句，而不要直接将用户信息插入user表，因为user表中存储了全局级别的权限以及其他的账户信息，如果意外破坏了user表中的记录，则可能会对MySQL服务器造成很大影响。

7，总结

        权限管理，这块平时也是数据库dba管理员去控制。一般都会授予增删改查等权限，如果开发中遇到了权限不同的报错，找数据库dba管理员去授权。

        上一篇：《mysql 用户管理-账户管理》

        下一篇：《范式-规范化理论》