Chap28MySQL权限与安全.doc

第28章 MySQL权限与安全MySQL的权限系统主要用来对连接到数据库的用户进行权限的验证，以此来判断此用户是否属于合法的用户，如果是合法用户则赋予相应的数据库权限。数据库的权限和数据库的安全是息息相关的，不当的权限设置可能会导致各种各样的安全隐患，操作系统的某些设置也会对MySQL的安全造成影响。本章对MySQL的权限系统以及相应的安全问题进行了一些探讨，希望能够帮助读者对这些方面有深入的认识。28.1 MySQL权限管理本节从MySQL权限系统的工作原理和账号管理两个方面来进行介绍。读完本节后，希望读者能够在了解权限系统的工作原理基础上，熟练掌握账号的管理和使用方法。28.1.1 权限系统的工作原理MySQL权限系统通过下面两个阶段进行认证：（1）对连接的用户进行身份认证，合法的用户通过认证，不合法的用户拒绝连接；（2）对通过认证的合法用户赋予相应的权限，用户可以在这些权限范围内对数据库做相应的操作。对于身份的认证，MySQL是通过IP地址和用户名联合进行确认的，例如MySQL安装后默认创建的用户rootlocalhost表示用户root只能从本地（localhost）进行连接才可以通过认证，此用户从其他任何主机对数据库进行的连接都将被拒绝。也就是说，同样的一个用户名，如果来自不同的IP地址，则MySQL将其视为不同的用户。MySQL的权限表在数据库启动的时候就载入内存，当用户通过身份认证后，就在内存中进行相应权限的存取，这样，此用户就可以在数据库中做权限范围内的各种操作了。28.1.2 权限表的存取在权限存取的两个过程中，系统会用到“mysql”数据库（安装MySQL时被创建，数据库名称叫“mysql”）中user、host和db这3个最重要的权限表，表定义如表28-1所示。表28-1mysql数据库中的3个权限表定义表名userdbhost用户列HostHostHostUserDbDbPasswordUser权限列Select_privSelect_privSelect_privInsert_privInsert_privInsert_privUpdate_privUpdate_privUpdate_privDelete_privDelete_privDelete_privIndex_privIndex_privIndex_privAlter_privAlter_privAlter_privCreate_privCreate_privCreate_privDrop_privDrop_privDrop_privGrant_privGrant_privGrant_privCreate_view_privCreate_view_privCreate_view_privShow_view_privShow_view_privShow_view_privCreate_routine_privCreate_routine_privAlter_routine_privAlter_routine_privReferences_privReferences_privReferences_privReload_privShutdown_privProcess_privFile_privShow_db_privSuper_privCreate_tmp_table_privCreate_tmp_table_privCreate_tmp_table_privLock_tables_privLock_tables_privLock_tables_privExecute_privRepl_slave_privRepl_client_priv安全列ssl_typessl_cipherx509_issuerx509_subject资源控制列max_questionsmax_updatesmax_connectionsmax_user_connections在这3个表中，最重要的表是user表，其次是db表，host表在大多数情况下并不使用。user中的列主要分为4个部分：用户列、权限列、安全列和资源控制列。其中，通常用得最多的是用户列和权限列，其中权限列有分为普通权限和管理权限。普通权限主要用于数据库的操作，比如select_priv、create_priv等；而管理权限主要用来对数据库进行管理的操作，比如process_priv、super_priv等。当用户进行连接的时候，权限表的存取过程有以下两个阶段。 先从user表中的host、user和password这3个字段中判断连接的IP、用户名和密码是否存在于表中，如果存在，则通过身份验证，否则拒绝连接。 如果通过身份验证，则按照以下权限表的顺序得到数据库权限：userdbtables_privcolumns_priv。在这几个权限表中，权限范围依次递减，全局权限覆盖局部权限。上面的第一阶段好理解，下面以一个例子来详细解释一下第二阶段。（1）创建用户z1localhost，并赋予所有数据库上的所有表的select权限。mysql grant select on *.* to z1localhost;Query OK, 0 rows affected (0.00 sec)mysql select * from user where user=z1 and host=localhost G;* 1. row * Host: localhost User: z1 Password: Select_priv: Y Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N （2）再来看db表：mysql select * from db;Empty set (0.00 sec)可以发现，user表的的select_priv列是“Y”，而db表中并没有记录，也就是说，对所有数据库都具有相同权限的用户记录并不需要记入db表，而仅仅需要将user表中的select_priv改为“Y”即可。换句话说，user表中的每个权限都代表了对所有数据库都有的权限。（3）将z1localhost上的权限改为只对test1数据库上所有表的select权限。mysql revoke select on *.* from z1localhost;Query OK, 0 rows affected (0.00 sec)mysql grant select on test1.* to z1localhost;Query OK, 0 rows affected (0.01 sec)mysql select * from user where user=z1 and host=localhost G;* 1. row * Host: localhost User: z1 Password: Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: Nmysql select * from db G;* 1. row * Host: localhost Db: test1 User: z1 Select_priv: Y Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: NCreate_tmp_table_priv: N Lock_tables_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Execute_priv: N1 row in set (0.00 sec)这个时候发现，user表中的select_priv变为“N”，而db表中则增加了db为test1的一条记录。也就是说，当只授予部分数据库某些权限时，user表中的相应权限列保持“N”，而将具体的数据库权限写入db表。table和column的权限机制和db类似，这里就不再赘述。从上面例子可以看出，当用户通过权限认证，进行权限分配时，将按照userdbtables_privcolumns_priv的顺序进行权限分配，即先检查全局权限表user，如果user中对应权限为“Y”，则此用户对所有数据库的权限都为“Y”，将不再检查db、tables_priv和columns_priv；如果为“N”，则到db表中检查此用户对应的具体数据库，并得到db中为“Y”的权限；如果db中相应权限为“N”，则检查tables_priv中此数据库对应的具体表，取得表中为“Y”的权限；如果tables_priv中相应权限为“N”，则检查columns_priv中此表对应的具体列，取得列中为“Y”的权限。28.1.3 账号管理理解了权限系统的工作原理后，本节开始介绍账号的管理。账号管理也是DBA日常工作中很重要的工作之一，主要包括账号的创建、权限更改和账号的删除。用户连接数据库的第一步都从账号创建开始。1创建账号有两种方法可以用来创建账号：使用GRANT语法创建或者直接操作授权表，但更推荐使用第一种方法，因为操作简单，出错几率更少。下面将详细讲述这两种方式的使用方法。GRANT的常用语法如下：GRANT priv_type (column_list) , priv_type (column_list) . ON object_type tbl_name | * | *.* | db_name.* TO user IDENTIFIED BY PASSWORD password , user IDENTIFIED BY PASSWORD password . WITH GRANT OPTIONobject_type = TABLE | FUNCTION | PROCEDURE来看下面几个例子。例1：创建用户z1，权限为可以在所有数据库上执行所有权限，只能从本地进行连接。mysql grant all privileges on *.* to z1localhost;Query OK, 0 rows affected (0.00 sec)mysql select * from user where user=z1 and host=localhost G;* 1. row * Host: localhost User: z1 Password: Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: N References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: YCreate_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y 可以发现，除了Grant_priv权限外，所有权限在user表里面都是“Y”。例2：在例1基础上，增加对z1的grant权限。mysql grant all privileges on *.* to z1localhost with grant option;Query OK, 0 rows affected (0.00 sec)mysql select * from user where user=z1 and host=localhost G;* 1. row * Host: localhost User: z1 Password: Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: Y 例3：在例2基础上，设置密码为“123”。mysql grant all privileges on *.* to z1localhost identified by 123 with grant option;Query OK, 0 rows affected (0.00 sec)从user表中查看修改的密码：mysql select * from user where user=z1 and host=localhost G;* 1. row * Host: localhost User: z1 Password: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y可以发现，密码变成了一堆加密后的字符串。在MySQL 5.0里面，密码的算法是生成一个以*开始的41位的字符串，而MySQL 4.0之前是16位，因此安全性大大提高。例4：创建新用户z2，可以从任何IP进行连接，权限为对test1数据库里的所有表进行SELECT、UPDATE、INSERT和DELETE操作，初始密码为“123”。mysql grant select,insert,update,delete on test1.* to z2% identified by 123;Query OK, 0 rows affected (0.00 sec)mysql select * from user where user=z2 and host=% G;* 1. row * Host: % User: z2 Password: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 Select_priv: N Insert_priv: N Update_priv: N Delete_priv: Nmysql select * from db where user=z2 and host=% G;* 1. row * Host: % Db: test1 User: z2 Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y如上文所述，user表中的权限都是“N”，db表中增加的记录权限则都是“Y”。一般地，我们只授予用户适当的权限，而一般不会授予过多的权限，本例中的权限适合于大多数应用账号。本例中的IP限制为所有IP都可以连接，因此设置为“*”，mysql数据库中是通过user表的host字段来进行控制，host可以是以下类型的赋值。l Host值可以是主机名或IP号，或“localhost”指出本地主机。 l 可以在Host列值使用通配符字符“%”和“_”。 l Host值“%”匹配任何主机名，空Host值等价于“%”。它们的含义与LIKE操作符的模式匹配操作相同。例如，“%”的Host值与所有主机名匹配，而“%.”匹配域的所有主机。表28-2host和user组合进行连接的例子Host值User值被条目匹配的连接 fred fred，从连接 任何用户，从连接% fred fred，从任何主机连接% 任何用户，从任何主机连接%. fred fred，从在域的任何主机连接x.y.% fred fred，从、、等联接77 fred fred，从有77 IP地址的主机连接144.155.166.% fred fred，从144.155.166 C类子网的任何主机连接可能大家会有这样的疑问，如果权限表中的host既有“”，又有“%”，而此时，连接从主机过来。显然，user表里面这两条记录都符合匹配条件，那系统会选择哪一个呢？如果有多个匹配，服务器必须选择使用哪个条目。按照下述原则来解决：l 服务器在启动时读入user表后进行排序；l 然后当用户试图连接时，以排序的顺序浏览条目；l 服务器使用与客户端和用户名匹配的第一行。当服务器读取表时，它首先以最具体的Host值排序。主机名和IP号是最具体的。“%”意味着“任何主机”并且是最不特定的。有相同Host值的条目首先以最具体的User值排序（空User值意味着“任何用户”并且是最不特定的）。下例是排序前和排序后的结果。排序前： +-+-+-| Host | User | +-+-+-| % | root | | % | jeffrey | | localhost | root | | localhost | | +-+-+-排序后：+-+-+-| Host | User | +-+-+-| localhost | root | .| localhost | | .| % | jeffrey | .| % | root | .+-+-+-很显然，在上面的例子中应该匹配host为“”所对应的权限。注意：mysql数据库的user表中host的值为*或者空，表示所有外部IP都可以连接，但是不包括本地服务器localhost，因此，如果要包括本地服务器，必须单独为localhost赋予权限。例5：授予SUPER、PROCESS、FILE权限给用户z3%。mysql grant super,process,file on *.* to z3%;Query OK, 0 rows affected (0.00 sec)因为这几个权限都属于管理权限，因此不能够指定某个数据库，on后面必须跟“*.*”，下面的语法将提示错误：mysql grant super,process,file on test1.* to z3%;ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES例6：只授予登录权限给z4localhost。mysql grant usage on *.* to z4localhost;Query OK, 0 rows affected (0.00 sec)mysql exitByezzxlocalhost $ mysql -uz4Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 10Server version: 5.0.41-community-log MySQL Community Edition (GPL)Type help; or h for help. Type c to clear the buffer.mysql show databases;+-+| Database |+-+| information_schema | +-+1 row in set (0.00 sec)usage权限只能用于数据库登录，不能执行任何操作。直接操作权限表也可以进行权限的创建，其实GRANT操作的本质就是修改权限表后进行权限的刷新，因此，GRANT比操作权限表更简单，下面继续以上文的例4为例子来说明一下更新权限表的用法。创建新用户z2，可以从任何IP进行连接，权限为对test1数据库里的所有表进行SELECT、UPDATE、INSERT和DELETE，初始密码为123，用grant实现如下：mysql grant select,insert,update,delete on test1.* to z2% identified by 123;Query OK, 0 rows affected (0.00 sec)直接操作权限表如下：zzxlocalhost $ mysql -uz2Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 20Server version: 5.0.41-community-log MySQL Community Edition (GPL)Type help; or h for help. Type c to clear the buffer.mysql show databases;+-+| Database |+-+| information_schema | +-+1 row in set (0.00 sec)mysql exitByezzxlocalhost $ mysql -urootWelcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 21Server version: 5.0.41-community-log MySQL Community Edition (GPL)Type help; or h for help. Type c to clear the buffer.mysql insert into db (host,db,user,select_priv,insert_priv,update_priv,delete_priv) values(%,test1,z2,Y,Y,Y,Y);Query OK, 1 row affected (0.00 sec)mysql flush privileges;Query OK, 0 rows affected (0.00 sec)mysql exitByezzxlocalhost $ mysql -uz2Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 22Server version: 5.0.41-community-log MySQL Community Edition (GPL)Type help; or h for help. Type c to clear the buffer.mysql show databases;+-+| Database |+-+| information_schema | | test1 | +-+2 rows in set (0.01 sec)2查看和更改账号权限创建完账号后，时间长了可能就会忘记分配的权限而需要查看账号权限，也有可能会经过一段时间后需要更改以前的账号权限，下面将介绍查看和更改这两种常用操作的命令。 查看权限。账号创建好后，可以通过如下命令进行查看权限：show grants for userhost;如下例所示：mysql show grants for z1localhost;+-+| Grants for z1localhost |+-+| GRANT USAGE ON *.* TO z1localhost | | GRANT SELECT, INSERT ON test1.* TO z1localhost | +-+2 rows in set (0.00 sec)host可以不写，默认是“%”，如下所示：mysql show grants for z1;ERROR 1141 (42000): There is no such grant defined for user z1 on host %mysql grant select on test1.* to z1%;Query OK, 0 rows affected (0.00 sec)mysql show grants for z1;+-+| Grants for z1% |+-+| GRANT USAGE ON *.* TO z1% | | GRANT SELECT ON test1.* TO z1% | +-+2 rows in set (0.00 sec)对于MySQL 5.0以后的版本，也可以利用新增的information_schema数据库进行权限的查看：mysql use information_schema;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql select * from SCHEMA_PRIVILEGES where grantee=z1localhost;+-+-+-+-+-+| GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | PRIVILEGE_TYPE | IS_GRANTABLE |+-+-+-+-+-+| z1localhost | NULL | test1 | SELECT | NO | | z1localhost | NULL | test1 | INSERT | NO | +-+-+-+-+-+2 rows in set (0.00 sec) 更改权限。可以进行权限的的新增和回收。和账号创建一样，权限变更也有两种方法：使用GRANT（新增）和REVOKE（回收）语句，或者更改权限表。第二种方法和前面一样，直接对user、db、tables_priv和columns_priv中的权限列进行更新即可，这里重点介绍第一种方法。和创建账号语法完全一样，GRANT可以直接用来对账号进行增加。其实GRANT语句在执行的时候，如果权限表中不存在目标账号，则创建账号；如果已经存在，则执行权限的新增。来看下面的一个例子。（1）z2localhost目前只有登录权限。mysql show grants for z2localhost;+-+| Grants for z2localhost |+-+| GRANT USAGE ON *.* TO z2localhost | +-+1 row in set (0.00 sec)（2）赋予z2localhost所有数据库上的所有表的SELECT权限。mysql grant select on *.* to z2localhost;Query OK, 0 rows affected (0.00 sec)mysql show grants for z2localhost;+-+| Grants for z2localhost |+-+| GRANT SELECT ON *.* TO z2localhost | +-+1 row in set (0.00 sec)（3）继续给z2localhost赋予SELECT和INSERT权限，和已有的SELECT权限进行合并。mysql grant select,insert on *.* to z2localhost;Query OK, 0 rows affected (0.00 sec)mysql show grants for z2localhost;+-+| Grants for z2localhost |+-+| GRANT SELECT, INSERT ON *.* TO z2localhost | +-+1 row in set (0.00 sec)REVOKE语句可以回收已经赋予的权限，语法如下：REVOKE priv_type (column_list) , priv_type (column_list) . ON object_type tbl_name | * | *.* | db_name.* FROM user , user .REVOKE ALL PRIVILEGES, GRANT OPTION FROM user , user .对于上面的例子，这里决定要收回z2localhost上的INSERT和SELECT权限：mysql revoke select,insert on *.* from z2localhost;Query OK, 0 rows affected (0.00 sec)mysql show grants for z2localhost;+-+| Grants for z2localhost |+-+| GRANT USAGE ON *.* TO z2localhost | +-+1 row in set (0.00 sec)usage权限不能被回收，也就是说，REVOKE用户并不能删除用户。mysql show grants for z2localhost;+-+| Grants for z2localhost |+-+| GRANT USAGE ON *.* TO z2localhost | +-+1 row in set (0.00 sec)mysql revoke usage on *.* from z2localhost;Query OK, 0 rows affected (0.00 sec)mysql show grants for z2localhost;+-+| Grants for z2localhost |+-+| GRANT USAGE ON *.* TO z2localhost | +-+1 row in set (0.00 sec) 修改密码。方法1：可以用mysqladmin命令在命令行指定密码。shell mysqladmin -u user_name -h host_name password newpwd方法2：执行SET PASSWORD语句。下例中将账号jeffrey%的密码改为biscuit。mysql SET PASSWORD FOR jeffrey% = PASSWORD(biscuit);如果是更改自己的密码，可以省略for语句：mysql SET PASSWORD = PASSWORD(biscuit);方法3：还可以在全局级别使用GRANT USAGE语句(在*.*)来指定某个账户的密码而不影响账户当前的权限。mysql GRANT USAGE ON *.* TO jeffrey% IDEN