用户和数据安全性

1、第9章 用户和数据安全性,9.1 添加和删除用户,9.1.1 添加用户 可以使用CREATE USER语法添加一个或多个用户，并设置相应的密码。 语法格式： CREATE USER user IDENTIFIED BY PASSWORD password , user IDENTIFIED BY PASSWORD password . 其中，user的格式为： user_name host name 说明： user_name为用户名，host_name为主机名，password为该用户的密码。在大多数SQL产品中，用户名和密码只由字母和数字组成。 使用自选的IDENTIFIED BY子句，可

2、以为账户给定一个密码。特别是要在纯文本中指定密码，需忽略PASSWORD关键词。如果不想以明文发送密码，而且知道PASSWORD()函数返回给密码的混编值，则可以指定该混编值，但要加关键字PASSWORD。 CREATE USER用于创建新的MySQL账户。CREATE USER会在系统本身的mysql数据库的user表中添加一个新记录。要使用CREATE USER，必须拥有mysql数据库的全局CREATE USER权限或INSERT权限。如果账户已经存在，则出现错误。 【例9.1】 添加两个新的用户，king的密码为queen，palo的密码为530415。 CREATE USER kin

3、glocalhost IDENTIFIED BY queen, palolocalhost IDENTIFIED BY 530415,9.1.1 添加用户,说明： 在用户名的后面声明了关键字localhost。这个关键字指定了用户创建的使用MySQL的连接所来自的主机。如果一个用户名和主机名中包含特殊符号如“_”，或通配符如“%”，则需要用单引号将其括起。“%”表示一组主机。 如果两个用户具有相同的用户名但主机不同，MySQL将其视为不同的用户，允许为这两个用户分配不同的权限集合。 如果没有输入密码，那么MySQL允许相关的用户不使用密码登录。但是从安全的角度并不推荐这种做法。 刚刚创建的用户

4、还没有很多权限。它们可以登录到MySQL，但是它们不能使用USE语句来让用户已经创建的任何数据库成为当前数据库，因此，它们无法访问那些数据库的表，只允许进行不需要权限的操作，例如，用一条SHOW语句查询所有存储引擎和字符集的列表,9.1.2 删除用户,语法格式： DROP USER user , user_name . DROP USER语句用于删除一个或多个MySQL账户，并取消其权限。要使用DROP USER，必须拥有mysql数据库的全局CREATE USER权限或DELETE权限。 【例9.2】 删除用户TOM。 DROP USER TOMlocalhost; 如果删除的用户已经创建了

5、表、索引或其他的数据库对象，它们将继续保留，因为MySQL并没有记录是谁创建了这些对象,9.1.3 修改用户名,可以使用RENAME USER语句来修改一个已经存在的SQL用户的名字。 语法格式： RENAME USER old_user TO new_user, , old_user TO new_user . 说明： old_user为已经存在的SQL用户。new_user为新的SQL用户。 RENAME USER语句用于对原有MySQL账户进行重命名。要使用RENAME USER，必须拥有全局CREATE USER权限或mysql数据库UPDATE权限。如果旧账户不存在或者新账户已存在，

6、则会出现错误。 【例9.3】 将用户king1和king2的名字分别修改为ken1和ken2。 RENAME USER king1localhost TO ken1localhost, king2localhost TO ken2localhost,9.1.4 修改密码,要修改某个用户的登录密码，可以使用SET PASSWORD语句。 语法格式： SET PASSWORD FOR user= PASSWORD(newpassword) 说明： 如果不加FOR user，表示修改当前用户的密码。加了FOR user则是修改当前主机上的特定用户的密码，user为用户名。user的值必须以user_

7、namehost_name的格式给定。 【例9.4】 将用户king的密码修改为queen1。 SET PASSWORD FOR kinglocalhost = PASSWORD(queen1,9.2 授予权限和回收权限,9.2.1 授予权限 新的SQL用户不允许访问属于其他SQL用户的表，也不能立即创建自己的表，它必须被授权。可以授予的权限有以下几组。 （1）列权限：和表中的一个具体列相关。例如，使用UPDATE语句更新表XS学号列的值的权限。 （2）表权限：和一个具体表中的所有数据相关。例如，使用SELECT语句查询表XS的所有数据的权限。 （3）数据库权限：和一个具体的数据库中的所有表相

8、关。例如，在已有的XSCJ数据库中创建新表的权限。 （4）用户权限：和MySQL所有的数据库相关。例如，删除已有的数据库或者创建一个新的数据库的权限。 给某用户授予权限可以使用GRANT语句。使用SHOW GRANTS语句可以查看当前账户拥有什么权限。 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

9、BY PASSWORD password . WITH with_option with_option,9.2.1 授予权限,其中，object_type： TABLE | FUNCTION | PROCEDURE with_option ： GRANT OPTION | MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count | MAX_CONNECTIONS_PER_HOUR count | MAX_USER_CONNECTIONS count 说明：priv_type为权限的名称，如SELECT、UPDATE等，给不同的对象授予权限

10、priv_type的值也不相同。TO子句用来设定用户的密码。ON关键字后面给出的是要授予权限的数据库或表名，下面将一一介绍。 （1）授予表权限和列权限 授予表权限时，priv_type可以是以下值： SELECT：给予用户使用SELECT语句访问特定的表的权力。用户也可以在一个视图公式中包含表。然而，用户必须对视图公式中指定的每个表（或视图）都有SELECT权限。 INSERT：给予用户使用INSERT语句向一个特定表中添加行的权力。 DELETE：给予用户使用DELETE语句向一个特定表中删除行的权力。 UPDATE：给予用户使用UPDATE语句修改特定表中值的权力,9.2.1 授予权限,R

11、EFERENCES：给予用户创建一个外键来参照特定的表的权力。 CREATE：给予用户使用特定的名字创建一个表的权力。 ALTER：给予用户使用ALTER TABLE语句修改表的权力。 INDEX：给予用户在表上定义索引的权力。 DROP：给予用户删除表的权力。 ALL或ALL PRIVILEGES：表示所有权限名。 在授予表权限时，ON关键字后面跟tb1_name，tb1_name为表名或视图名。 【例9.5】 授予用户king在XS表上的SELECT权限。 USE XSCJ; GRANT SELECT ON XS TO kinglocalhost; 说明： 这里假设是在ROOT用户中输入了

12、这些语句，这样用户king就可以使用SELECT语句来查询XS表，而不管是谁创建的这个表。 若在TO子句中给存在的用户指定密码，则新密码将原密码覆盖。如果权限授予了一个不存在的用户，MySQL会自动执行一条CREATE USER语句来创建这个用户，但必须为该用户指定密码,9.2.1 授予权限,例9.6】 用户liu和zhang不存在，授予它们在XS表上的SELECT和UPDATE权限。 GRANT SELECT,UPDATE ON XS TO liulocalhost IDENTIFIED BY LPWD, zhanglocalhost IDENTIFIED BY ZPWD; 对于列权限，pr

13、iv_type的值只能取SELECT、INSERT和UPDATE。权限的后面需要加上列名column_list。 【例9.7】 授予king在XS表上的学号列和姓名列的UPDATE权限。 GRANT UPDATE(姓名, 学号) ON XS TO kinglocalhost; （2）授予数据库权限 表权限适用于一个特定的表。MySQL还支持针对整个数据库的权限。例如，在一个特定的数据库中创建表和视图的权限。 授予数据库权限时，priv_type可以是以下值： SELECT：给予用户使用SELECT语句访问特定数据库中所有表和视图的权力。 INSERT：给予用户使用INSERT语句向特定数据库中

14、所有表添加行的权力。 DELETE：给予用户使用DELETE语句删除特定数据库中所有表的行的权力。 UPDATE：给予用户使用UPDATE语句更新特定数据库中所有表的值的权力。 REFERENCES：给予用户创建指向特定的数据库中的表外键的权力,9.2.1 授予权限,CREATE：给予用户使用CREATE TABLE语句在特定数据库中创建新表的权力。 ALTER：给予用户使用ALTER TABLE语句修改特定数据库中所有表的权力。 INDEX：给予用户在特定数据库中的所有表上定义和删除索引的权力。 DROP：给予用户删除特定数据库中所有表和视图的权力。 CREATE TEMPORARY TAB

15、LES：给予用户在特定数据库中创建临时表的权力。 CREATE VIEW：给予用户在特定数据库中创建新的视图的权力。 SHOW VIEW：给予用户查看特定数据库中已有视图的视图定义的权力。 CREATE ROUTINE：给予用户为特定的数据库创建存储过程和存储函数等权力。 ALTER ROUTINE：给予用户更新和删除数据库中已有的存储过程和存储函数等权力。 EXECUTE ROUTINE：给予用户调用特定数据库的存储过程和存储函数的权力。 LOCK TABLES：给予用户锁定特定数据库的已有表的权力。 ALL或ALL PRIVILEGES：表示以上所有权限名。 在GRANT语法格式中，授予数

16、据库权限时ON关键字后面跟“*”和“db_name.*”。“*”表示当前数据库中的所有表；“db_name.*”表示某个数据库中的所有表。 【例9.8】 授予king在XSCJ数据库中的所有表的SELECT权限。 GRANT SELECT ON XSCJ.* TO kinglocalhost,9.2.1 授予权限,说明： 这个权限适用于所有已有的表，以及此后添加到XSCJ数据库中的任何表。 【例9.9】 授予king在XSCJ数据库中所有的数据库权限。 USE XSCJ; GRANT ALL ON * TO kinglocalhost; 和表权限类似，授予一个数据库权限也不意味着拥有另一个权限

17、。如果用户被授予可以创建新表和视图，但是还不能访问它们。要访问它们，它还需要单独被授予SELECT权限或更多权限。 （3）授予用户权限 最有效率的权限就是用户权限，对于需要授予数据库权限的所有语句，也可以定义在用户权限上。例如，在用户级别上授予某人CREATE权限，这个用户可以创建一个新的数据库，也可以在所有的数据库（而不是特定的数据库）中创建新表。 MySQL授予用户权限时priv_type还可以是以下值。 CREATE USER：给予用户创建和删除新用户的权力。 SHOW DATABASES：给予用户使用SHOW DATABASES语句查看所有已有的数据库的定义的权利,9.2.1 授予权限

18、,在GRANT语法格式中，授予用户权限时ON子句中使用“*.*”，表示所有数据库的所 有表。 【例9.10】 授予Peter对所有数据库中的所有表的CREATE、ALTERT和DROP权限。 GRANT CREATE ,ALTER ,DROP ON *.* TO Peterlocalhost IDENTIFIED BY ppwd; 【例9.11】 授予Peter创建新用户的权力。 GRANT CREATE USER ON *.* TO Peterlocalhost; 为了概括权限，表9.1列出了可以在哪些级别授予某条SQL语句权限,9.2.2 权限的转移和限制,GRANT语句的最后可以使用WI

19、TH子句。如果指定为WITH GRANT OPTION，则表示TO子句中指定的所有用户都有把自己所拥有的权限授予其他用户的权利，而不管其他用户是否拥有该权限。 【例9.12】 授予David在XS表上的SELECT权限，并允许其将该权限授予其他用户。 首先在ROOT用户下授予David用户SELECT权限： GRANT SELECT ON XSCJ.XS TO Davidlocalhost IDENTIFIED BY 123456 WITH GRANT OPTION; 接着，以David用户身份登录MySQL，登录方式为： （1）打开DOS窗口，然后进入mysql安装目录下的bin目录，默认安

20、装的路径为：C:Program FilesMySQLMySQL Server 5.1bin。 （2）输入命令：mysql -hlocalhost -uDavid -p123456。其中-h后为主机名，-u后为用户名，-p后为用户密码。登录后界面如图9.1所示,9.2.2 权限的转移和限制,图9.1 用户登录界面,登录后，David用户只有查询XSCJ数据库中XS表的权利，它可以把这个权限传递给其他用户，这里假设用户Jim已经创建： GRANT SELECT ON XSCJ.XS TO Jimlocalhost; 说明：使用了WITH GRANT OPTION子句后，如果David在该表上还拥有

21、其他权限，他可以将其他权限也授予Jim而不仅限于SELECT,9.2.2 权限的转移和限制,WITH子句也可以对一个用户授予使用限制，其中，MAX_QUERIES_PER_HOUR count表示每小时可以查询数据库的次数；MAX_CONNECTIONS_PER_HOUR count表示每小时可以连接数据库的次数；MAX_UPDATES_PER_HOUR count表示每小时可以修改数据库的次数。例如，某人每小时可以查询数据库多少次。MAX_USER_CONNECTIONS count表示同时连接MySQL的最大用户数。count是一个数值，对于前三个指定，count如果为0则表示不起限制作用

22、。 【例9.13】 授予Jim每小时只能处理一条SELECT语句的权限。 GRANT SELECT ON XS TO Jimlocalhost WITH MAX_QUERIES_PER_HOUR 1; 除了MAX_QUERIES_PER_HOUR，还可以指定MAX_CONNECTIONS_PER_HOUR、MAX_UPDATES_PER_HOUR和MAX_USER_CONNECTIONS。对于前3个指定，如果值等于0，就没有限制会起作用,9.2.3 回收权限,要从一个用户回收权限，但不从USER表中删除该用户，可以使用REVOKE语句，这条语句和GRANT语句格式相似，但具有相反的效果。要使用

23、REVOKE，用户必须拥有mysql数据库的全局CREATE USER权限或UPDATE权限。 语法格式： REVOKE priv_type (column_list) , priv_type (column_list) . ON tbl_name | * | *.* | db_name.* FROM user , user . 或者： REVOKE ALL PRIVILEGES, GRANT OPTION FROM user , user . 说明：第一种格式用来回收某些特定的权限，第二种格式回收所有该用户的权限。 【例9.14】 回收用户David在XS表上的SELECT权限。 REVOK

24、E SELECT ON XS FROM Davidlocalhost; 由于David用户对XS表的SELECT权限被回收了，那么包括直接或间接地依赖于它的所有权限也回收了，在这个例子中，Jim也失去了对XS表的SELECT权限。但以上语句执行之后WITH GRANT OPTION还保留，当再次授予David对于同一个表的表权限时，它会立刻把这个权限传递给Jim,9.3 界面方式操作用户与权限,除了命令行方式，可以通过界面方式来操作用户与权限，具体步骤如下。 （1）打开MySQL Administrator，以ROOT用户登录。进入主界面后单击User Administration栏，界面左下

25、方会出现MySQL的用户列表，如图9.2所示,图9.2 用户列表,9.3 界面方式操作用户与权限,2）添加用户。单击图9.2中的Add new user按钮，在User Information栏上填写用户名tony和密码，单击“Apply Changes”按钮即可创建新用户。随后，界面左下方的用户列表中就会新添加一个tony用户，如图9.3所示。删除用户的方法很简单，右击用户名，选择“Delete User”即可删除该用户,图9.3 创建新用户,9.3 界面方式操作用户与权限,3）授予用户权限。以tony用户为例，tony用户创建完后并没有授予其任何权限，要授予其权限，步骤为：选择tony，单

26、击Schema Privileges栏，选择要授予权限的数据库，在Available Privileges框中会出现权限列表，选择相应权限，单击“”按钮即可。“”是取消所有权限。最后单击“Apply Changes”按钮完成用户权限授予,图9.4 授予权限,9.4 表维护语句,9.4.1 ANALYZE TABLE语句 在一个定义了索引的列上，该列上不同值的数目被称为该索引列的可压缩性，可以使用SHOW INDEX FROM tb_name语句来显示它。 一个索引列的可压缩性不是自动更新的。就是说，用户在某列创建了一个索引，而该列的可压缩性是不会立即计算出来的。这时需要使用ANALYZE TA

27、BLE语句来更新它。 语法格式： ANALYZE LOCAL | NO_WRITE_TO_BINLOG TABLE tbl_name , tbl_name . 在MySQL上执行的所有更新都将写入到一个二进制日志文件中。这里如果直接使用ANALYZE TABLE语句，结果数据也会写入日志文件中。如果指定了NO_ERITE_TO_BINLOG选项，则关闭这个功能（LOCAL是NO_ERITE_TO_BINLOG的同义词），这样ANALYZE TABLE语句也将会更快完成。 【例9.15】 更新表XS的索引的可压缩性，并随后显示。 ANALYZE TABLE XS; SHOW INDEX FROM

28、 XS,9.4.2 CHECK TABLE语句,这条语句用来检查一个或多个表是否有错误，只对MyISAM和InnoDB表起作用。 语法格式： CHECK TABLE tbl_name , tbl_name . option . 其中，option为： QUICK | FAST | MEDIUM | EXTENDED | CHANGED 说明： 使用该语句有多个选项。 QUICK：不扫描行，不检查错误的链接，这是最快的方法。 FAST：检查表是否已经正确关闭。 CHANGED：检查上次检查后被更改的表，以及没有被正确关闭的表。 MEDIUM：扫描行，以验证被删除的链接是有效的。也可以计算各行的关

29、键字校验和，并使用计算出的校验和验证这一点。 EXTENDED：对每行的所有关键字进行全面的关键字查找。这可以确保表是100一致的，但是花的时间较长。 【例9.16】 检查XS表是否正确。 CHECK TABLE XS; 结果为,9.4.3 CHECKSUM TABLE语句,对于数据库中的每一个表，都可以使用CHECKSUM TABLE语句获得一个校验和。 语法格式： CHECKSUM TABLE tbl_name , tbl_name . QUICK | EXTENDED 说明：如果表是MyISAM表，如果指定了QUICK，则报告表校验和，否则报告NULL。指定EXTENDED则表示无论表是否是MyISAM表