Mysql第9章用户和数据安全性课件.ppt

1、9.1 添加和删除用户添加和删除用户9.2 授予权限和回收权限授予权限和回收权限9.3 界面方式操作用户与权限界面方式操作用户与权限9.4 表维护语句表维护语句9.1.4 修改密码修改密码9.1.2 删除用户删除用户9.1.3 修改用户名修改用户名9.1.1 添加用户添加用户9.1.2 删除用户删除用户9.1.1 添加用户添加用户9.1.3 修改用户名修改用户名9.1.2 删除用户删除用户9.1.1 添加用户添加用户可用CREATE USER语法添加一个或多个用户，并设置相应的密码。语法格式：CREATE USER user IDENTIFIED BY PASSWORDpassword,use

2、r DENTIFIED BY PASSWORDpassword.说明：user的格式为：user_name host nameuser_name为用户名，host_name为主机名，password为该用户的密码。IDENTIFIED BY子句，为账户给定一个密码。特别是要在纯文本中指定密码，需忽略PASSWORD关键词。如果不想以明文发送密码，而且知道PASSWORD()函数返回给密码的混编值，则可以指定该混编值，但要加关键字PASSWORD。CREATE USER用于创建新的MySQL账户。CREATE USER会在系统本身的mysql数据库的user表中添加一个新记录。要使用CREATE

3、 USER，必须拥有mysql数据库的全局CREATE USER权限或INSERT权限。如果账户已经存在，则出现错误。【例9.1】添加两个新的用户，king的密码为queen，palo的密码为530415。CREATE USER kinglocalhost IDENTIFIED BY queen,palolocalhost IDENTIFIED BY 530415;说明：llocalhost指定了用户创建的使用MySQL的连接所来自的主机。如果一个用户名和主机名中包含特殊符号如“_”，或通配符如“%”，则需要用单引号将其括起。“%”表示一组主机。l如果两个用户具有相同的用户名但主机不同，MyS

4、QL将其视为不同的用户，允许为这两个用户分配不同的权限集合。l如果没有输入密码，那么MySQL允许相关的用户不使用密码登录。但是从安全的角度并不推荐这种做法。l刚刚创建的用户还没有很多权限。它们可以登录到刚刚创建的用户还没有很多权限。它们可以登录到MySQL，但是它们不能使用但是它们不能使用USE语句来让用户已经创建的任何数据库成为当语句来让用户已经创建的任何数据库成为当前数据库，因此，它们无法访问那些数据库的表，只允许进行不需前数据库，因此，它们无法访问那些数据库的表，只允许进行不需要权限的操作，例如，用一条要权限的操作，例如，用一条SHOW语句查询所有存储引擎和字语句查询所有存储引擎和字符

5、集的列表符集的列表。语法格式：DROP USER user,user_name.DROP USER语句用于删除一个或多个MySQL账户，并取消其权限。要使用DROP USER，必须拥有mysql数据库的全局CREATE USER权限或DELETE权限。【例9.2】删除用户TOM。DROP USER TOMlocalhost;如果删除的用户已经创建了表、索引或其他的数据库对象，它们将继续保留，因为MySQL并没有记录是谁创建了这些对象。修改一个已经存在的SQL用户的名字。语法格式：RENAME USER old_user TO new_user,old_user TO new_user.说明：o

6、ld_user为已经存在的SQL用户。new_user为新的SQL用户。RENAME USER语句用于对原有MySQL账户进行重命名。使用该语句，必须拥有全局CREATE USER权限或mysql数据库UPDATE权限。如果旧账户不存在或者新账户已存在，则会出现错误。【例9.3】将用户king1和king2的名字分别修改为ken1和ken2。RENAME USERking1localhost TO ken1localhost,king2localhost TO ken2localhost;修改某个用户的登录密码，语法格式：SET PASSWORD FOR user=PASSWORD(newpa

7、ssword)说明：如果不加FOR user，表示修改当前用户的密码。加了FOR user则是修改当前主机上的特定用户的密码，user为用户名。user的值必须以user_namehost_name的格式给定。SET PASSWORD FOR kinglocalhost=PASSWORD(queen1);9.2.1 9.2.1 两级访问控制两级访问控制 9.2.2 9.2.2 授予权限授予权限 9.2.3 9.2.3 权限的转移和限制权限的转移和限制 9.2.4 9.2.4 回收权限回收权限 通常情况下，建立一个数据库以后，并不是所有的人都可以看到数据库里的所有数据（无论是修改还是删除）。为了

8、保护数据不被探查或未经授权的篡改，MySQL采用了一种双层的访问控制机制。第一层用来检查各位用户是否有权与MySQL进行通信。第二层用来检查各位用户都有权对哪些数据库、数据表和数据列进行哪些操作。只有在建立连接的检查完成以后，第二阶段的访问控制才开始实施，这种控制细化到每一条数据库命令。例如，要执行一个SELECT命令，MySQL会检查该用户对数据库、数据表和数据列是否具有执行这个命令的权限。要执行一个INSERT命令，那么MYSQL要检查是否允许该用户改变数据库、数据表直到数据列的内容。MYSQL使用存储着privileges的数据表进行管理的。如果一个用户auser，具有对数据库cxdb1

9、的select权限，那么该用户就可以读取cxdb1中的所有数据。如果该用户具有全局的select权限，该用户就可以读取所有存储于MYSQL的数据库。在设置访问权限的时候，MYSQL提供了一个准确、相互衔接的系统，在MYSQL文档中，这个系统被称为访问权限系统(access privilege system)，这个系统里的各个列表被称为访问控制表ACL。以图形用户界面使用管理程序-最简单方便 使用INSERT和UPDATE命令直接改变MYSQL 使用SQL的命令GRANT和REVOKE。使用Perl脚本程序mysql_setpermission.pl。当然，它假设用户安装并运行着Perl。由My

10、SQL管理的访问权限是依靠数据库来实现的，这个数据库的名字叫mysql，它由几个数据表组成，对应着访问权限的各个方面。Zeuse(包括用户名，主机名，密码)必须被输入到user数据表里。Zeuse的所有全局权限都必须设置为N Zeuse(包括用户名，主机名)还必须要在columns_priv数据表中注册。在columns_priv数据表中，还必须设定都允许Zeuse访问哪个数据列（数据库名，数据表名，数据列名）Select权限（仅）必须要激活。当相应等级的权限设置为N时，数据表db,host,table_priv,columns_priv以这个顺序依次实施。即：如果select权限赋予了use

11、r数据表中的一个用户，那么其他的4个数据表就不再用于检查这个用户是否允许执行SELECT命令。所以，要想对访问权限进行精确的区别，就必须把user数据表里的全局权限全部设置为N.新的SQL用户不允许访问属于其他SQL用户的表，也不能立即创建自己的表，它必须被授权。权限有以下几组。（1）列权限：和表中的一个具体列相关。）列权限：和表中的一个具体列相关。例如，使用UPDATE语句更新表XS学号列的值的权限。（2）表权限：和一个具体表中的所有数据相关。）表权限：和一个具体表中的所有数据相关。例如，使用SELECT语句查询表XS的所有数据的权限。（3）数据库权限：和一个具体的数据库中的所有表相关。）数

12、据库权限：和一个具体的数据库中的所有表相关。例如，在已有的XSCJ数据库中创建新表的权限。（4）用户权限：和）用户权限：和MySQL所有的数据库相关。所有的数据库相关。例如，删除已有的数据库或者创建一个新的数据库的权限。使用使用GRANT语句可以给某用户授予权限。语句可以给某用户授予权限。Grant可以把指定的权限分配给特定的用户，如果这可以把指定的权限分配给特定的用户，如果这个用户不存在，则会创建一个用户。个用户不存在，则会创建一个用户。使用使用SHOW GRANTS语句可以查看当前语句可以查看当前账户拥有什么权限。账户拥有什么权限。其中，权限1,权限2,权限n代表select,insert

13、,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file等14个权限。当权限1,权限2,权限n被all privileges或者all代替，表示赋予用户全部权限。当数据库名称.表名称被*.*代替，表示赋予用户操作服务器上所有数据库所有表的权限。用户地址可以是localhost，也可以是ip地址、机器名字、域名。也可以用%表示从任何地址连接。连接口令不能为空，否则创建失败。grant 权限权限1,权限权限2,权限权限n on 数据库名数据库名.表名表名 to 用户名用户名用户地址用户地

14、址 identified by 口令口令;（1）授予表权限和列权限（2）授予数据库权限（3）授予用户权限 授予表权限时，权限1,权限2,权限n可以是：SELECT：给予用户使用SELECT语句访问特定的表的权力。用户也可以在一个视图公式中包含表。然而，用户必须对视图公式中指定的每个表（或视图）都有SELECT权限。INSERT：给予用户使用INSERT语句向一个特定表中添加行的权力。DELETE：给予用户使用DELETE语句向一个特定表中删除行的权力。UPDATE：给予用户使用UPDATE语句修改特定表中值的权力。授予表权限-续 REFERENCES：给予用户创建一个外键来参照特定的表的权力。

15、CREATE：给予用户使用特定的名字创建一个表的权力。ALTER：给予用户使用ALTER TABLE语句修改表的权力。INDEX：给予用户在表上定义索引的权力。DROP：给予用户删除表的权力。ALL或ALL PRIVILEGES：表示所有权限名。【例9.5】授予用户king在xscj.XS表上的SELECT权限。USE XSCJ;GRANT SELECT ON XS TO kinglocalhost;说明：这里假设是在ROOT用户中输入了这些语句，这样用户king就可以使用SELECT语句来查询XS表，而不管是谁创建的这个表。若在TO子句中给存在的用户指定密码，则新密码将原密码覆盖。如果权限授

16、予了一个不存在的用户，MySQL会自动执行一条CREATE USER语句来创建这个用户，但必须为该用户指定密码。USE XSCJ;GRANT SELECT,UPDATE ON XS TO liulocalhost IDENTIFIED BY LPWD,zhanglocalhost IDENTIFIED BY ZPWD;【例9.6】用户liu和zhang不存在，授予它们在XS表上的SELECT和UPDATE权限。授予列权限时 权限只能取SELECT、INSERT和UPDATE。权限值的后面需要加上列名。【例9.7】授予king在XS表上的学号列和姓名列的UPDATE权限。GRANT UPDATE

17、(姓名姓名,学号学号)ON XS TO kinglocalhost;例如，在一个特定的数据库中创建表和视图的权限。授予数据库权限时，priv_type可以是以下值：SELECT：给予用户使用SELECT语句访问特定数据库中所有表和视图的权力。INSERT：给予用户使用INSERT语句向特定数据库中所有表添加行的权力。DELETE：给予用户使用DELETE语句删除特定数据库中所有表的行的权力。UPDATE：给予用户使用UPDATE语句更新特定数据库中所有表的值的权力。REFERENCES：给予用户创建指向特定的数据库中的表外键的权力。CREATE：给予用户使用CREATE TABLE语句在特定数

18、据库中创建新表的权力。ALTER：给予用户使用ALTER TABLE语句修改特定数据库中所有表的权力。INDEX：给予用户在特定数据库中的所有表上定义和删除索引的权力。DROP：给予用户删除特定数据库中所有表和视图的权力。CREATE TEMPORARY TABLES：给予用户在特定数据库中创建临时表的权力。CREATE VIEW：给予用户在特定数据库中创建新的视图的权力。SHOW VIEW：给予用户查看特定数据库中已有视图的视图定义的权力。CREATE ROUTINE：给予用户为特定的数据库创建存储过程和存储函数等权力。ALTER ROUTINE：给予用户更新和删除数据库中已有的存储过程和存

19、储函数等权力。EXECUTE ROUTINE：给予用户调用特定数据库的存储过程和存储函数的权力。LOCK TABLES：给予用户锁定特定数据库的已有表的权力。ALL或ALL PRIVILEGES：表示以上所有权限名。在GRANT语法格式中，授予数据库权限时ON关键字后面跟“*”和“db_name.*”。“*”表示当前数据库中的所有表；“db_name.*”表示某个数据库中的所有表。GRANT SELECT ON XSCJ.*TO kinglocalhost identified by 123;说明：说明：这个权限适用于所有已有的表，以及此后添加到这个权限适用于所有已有的表，以及此后添加到XSC

20、JXSCJ数据库中的任何表。数据库中的任何表。【例9.9】授予king在XSCJ数据库中所有的数据库权限。USE XSCJ;GRANT ALL ON *TO kinglocalhost;和表权限类似，授予一个数据库权限也不意味着拥有另一个权限。如果用户被授予可以创建新表和视图，但是还不能访问它们。要访问它们，它还需要单独被授予SELECT权限或更多权限。最有效率的权限就是用户权限，对于需要授予数据库权限的所有语句，也可以定义在用户权限上。例如，在用户级别上授予某人CREATE权限，这个用户可以创建一个新的数据库，也可以在所有的数据库（而不是特定的数据库）中创建新表。MySQL授予用户权限时pr

21、iv_type还可以是以下值。CREATE USER：给予用户创建和删除新用户的权力。SHOW DATABASES：给予用户使用该语句查看所有已有的数据库的定义的权利。在GRANT语法格式中，授予用户权限时ON子句中使用“*.*”，表示所有数据库的所有表。【例9.10】授予Peter对所有数据库中的所有表的CREATE、ALTERT和DROP权限。GRANT CREATE,ALTER,DROP ON *.*TO Peterlocalhost IDENTIFIED BY ppwd;GRANT CREATE USER ON *.*TO Peterlocalhost;为了概括权限，表9.1列出了可以

22、在哪些级别授予某条SQL语句权限。-教程p172l GRANT语句的最后可以使用WITH子句。l 如果指定为WITH GRANT OPTION，则表示TO子句中指定的所有用户都有把自己所拥有的权限授予其他用户的权利，而不管其他用户是否拥有该权限。1.首先在ROOT用户下授予David用户SELECT权限：GRANT SELECT ON XSCJ.XS TO Davidlocalhost IDENTIFIED BY 123456 WITH GRANT OPTION;2.以David用户身份登录MySQL，登录方式为：（1）打开DOS窗口，然后进入mysql安装目录下的bin目录，默认安装的路径为

23、：C:Program FilesMySQLMySQL Server 5.1bin。（2）输入：mysql-hlocalhost-uDavid-p123456。登录后界面如图9.1所示。图9.1 用户登录界面登录后，David用户只有查询XSCJ数据库中XS表的权利，它可以把这个权限传递给其他用户，这里假设用户Jim已经创建：GRANT SELECT ON XSCJ.XS TO Jimlocalhost;说明：使用了WITH GRANT OPTION子句后，如果David在该表上还拥有其他权限，他可以将其他权限也授予Jim而不仅限于SELECT。l MAX_QUERIES_PER_HOUR co

24、unt表示每小时可以查询数据库的次数；l MAX_CONNECTIONS_PER_HOUR count表示每小时可以连接数据库的次数；l MAX_UPDATES_PER_HOUR count表示每小时可以修改数据库的次数。l MAX_USER_CONNECTIONS count表示同时连接MySQL的最大用户数。count是一个数值，对于前三个指定，count如果为0则表示不起限制作用。WITH子句也可以对一个用户授予使用限制 GRANT SELECT ON XS TO Jimlocalhost WITH MAX_QUERIES_PER_HOUR 1;除了MAX_QUERIES_PER_HOU

25、R，还可以指定MAX_CONNECTIONS_PER_HOUR、MAX_UPDATES_PER_HOUR和MAX_USER_CONNECTIONS。对于前3个指定，如果值等于0，就没有限制会起作用。要从一个用户回收权限，但不从USER表中删除该用户，可以使用REVOKE语句，这条语句和GRANT语句格式相似，但具有相反的效果。要使用REVOKE，用户必须拥有mysql数据库的全局CREATE USER权限或UPDATE权限。语法格式：REVOKE priv_type(column_list),priv_type(column_list).ON tbl_name|*|*.*|db_name.*F

26、ROM user,user.或者：REVOKE ALL PRIVILEGES,GRANT OPTION FROM user,user.说明：第一种格式用来回收某些特定的权限，第二种格式回收所有该用户的权限。REVOKE SELECT ON XS FROM Davidlocalhost;由于David用户对XS表的SELECT权限被回收了，那么包括直接或间接地依赖于它的所有权限也回收了，在这个例子中，Jim也失去了对XS表的SELECT权限。但以上语句执行之后WITH GRANT OPTION还保留，当再次授予David对于同一个表的表权限时，它会立刻把这个权限传递给Jim。授权(grant)撤

27、销授权(revoke)认证-确定用户是否允许连接数据库服务器 授权-确定用户是否拥有足够的权限执行查询请求等。如果认证不成功的话，哪么授权肯定是无法进行的。create user：创建新的用户账户，在创建这个用户的时候不分配任何权限，需要在创建之后通过grant命令来给改用户分配相应的权限。create user identified by 123456；grant select on mydb.*to;drop user：删除一个用户账户drop user guest;rename user：重命名一个用户账号。grant：用于管理访问权限，也就是给用户账号授权。当然它同样可以创建一个新的用

28、户账户。eg：grant select,insert,update,delete on new_db.*to identified by 888;grant 权限 on 数据库.表 to 用户 访问方式 identified by 密码grant select on mydb.*to identified by 123;BTW：如果需要一个空密码或者无密码的账户，必须先用Create User命令，然后通过grant来分配权限。如果如下操作：grant all privileges on mydb.*to；而在数据库user表中没有先创建visitor用户，则会发生1133错误grant只能创

29、有密码的账户。revoke：回收一个账户权限 grant 权限 on 数据库对象 to 用户 grant 普通数据用户，查询、插入、更新、删除 数据库中所有表数据的权利。grant select on testdb.*to grant insert on testdb.*to grant update on testdb.*to grant delete on testdb.*to 或者，用一条 MySQL 命令来替代：grant select,insert,update,delete on testdb.*to grant 创建、修改、删除 MySQL 数据表结构权限。grant creat

30、e on testdb.*to;grant alter on testdb.*to;grant drop on testdb.*to;grant 操作 MySQL 外键权限。grant references on testdb.*to;grant 操作 MySQL 临时表权限。grant create temporary tables on testdb.*to;grant 操作 MySQL 索引权限。grant index on testdb.*to;grant 操作 MySQL 视图、查看视图源代码 权限。grant create view on testdb.*to;grant show

31、 view on testdb.*to;grant 操作 MySQL 存储过程、函数 权限。grant create routine on testdb.*to;-now,can show procedure status grant alter routine on testdb.*to;-now,you can drop a procedure grant execute on testdb.*to;grant all privileges on testdb to 其中，关键字“privileges”可以省略。grant 高级 DBA 管理 MySQL 中所有数据库的权限。grant a

32、ll on*.*to grant 作用在存储过程、函数上：grant execute on procedure testdb.pr_add to grant execute on function testdb.fn_add to 注意：修改完权限以后 一定要刷新服务，或者重启服务，刷新服务用：FLUSH PRIVILEGES如果一个MYSQL用户的全局权限全都被设置为N，该用户还能创建并编辑一个名为test的数据库吗？无论MYSQL数据库在哪种环境下安装，所有连接到mysql的用户都可以创建一个测试数据库。唯一的条件就是数据库的名字前面必须是以test开始。不但所有的mysql用户都可以创建

33、这样的一个数据库，而且每一个用户都可以读取、改变和删除这个数据库中的所欲数据。任何人都可以将这个数据库整个删除。即，test数据库中的数据是没有任何保护的。（1）打开MySQL Administrator，以ROOT用户登录。进入主界面后单击User Administration栏，界面左下方会出现MySQL的用户列表，如图9.2所示。图9.2 用户列表（2）添加用户。单击图9.2中的Add new user按钮，在User Information栏上填写用户名tony和密码，单击“Apply Changes”按钮即可创建新用户。随后，界面左下方的用户列表中就会新添加一个tony用户，如图9.

34、3所示。删除用户的方法很简单，右击用户名，选择“Delete User”即可删除该用户。图9.3 创建新用户（3）授予用户权限。以tony用户为例，tony用户创建完后并没有授予其任何权限，要授予其权限，步骤为：选择tony，单击Schema Privileges栏，选择要授予权限的数据库，在Available Privileges框中会出现权限列表，选择相应权限，单击“”按钮即可。“”是取消所有权限。最后单击“Apply Changes”按钮完成用户权限授予。图9.4 授予权限 9.5.1 ANALYZE TABLE语句语句在一个定义了索引的列上，该列上不同值的数目被称为该索引列的可压缩性，

35、可以使用SHOW INDEX FROM tb_name语句来显示它。一个索引列的可压缩性不是自动更新的。就是说，用户在某列创建了一个索引，而该列的可压缩性是不会立即计算出来的。这时需要使用ANALYZE TABLE语句来更新它。语法格式：ANALYZE LOCAL|NO_WRITE_TO_BINLOG TABLE tbl_name,tbl_name.在MySQL上执行的所有更新都将写入到一个二进制日志文件中。这里如果直接使用ANALYZE TABLE语句，结果数据也会写入日志文件中。如果指定了NO_ERITE_TO_BINLOG选项，则关闭这个功能（LOCAL是NO_ERITE_TO_BINL

36、OG的同义词），这样ANALYZE TABLE语句也将会更快完成。【例9.15】更新表XS的索引的可压缩性，并随后显示。ANALYZE TABLE XS;SHOW INDEX FROM XS;这条语句用来检查一个或多个表是否有错误，只对MyISAM和InnoDB表起作用。语法格式：CHECK TABLE tbl_name,tbl_name.option.其中，option为：QUICK|FAST|MEDIUM|EXTENDED|CHANGED说明：QUICK：不扫描行，不检查错误的链接，这是最快的方法。FAST：检查表是否已经正确关闭。CHANGED：检查上次检查后被更改的表，以及没有被正确关

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

38、表是否是MyISAM表，都只计算检验和。【例9.17】获得表XS的校验和的值。CHECKSUM TABLE XS;结果为：如果用户不断地使用DELETE、INSERT和UPDATE语句更新一个表，那么表的内部结构就会出现很多碎片和未利用的空间。这时可以使用OPTIMIZE TABLE语句来重新利用未使用的空间，并整理数据文件的碎片。OPTIMIZE TABLE语句只对MyISAM、BDB和InnoDB表起作用。语法格式：OPTIMIZE LOCAL|NO_WRITE_TO_BINLOG TABLE tbl_name,tbl_name.【例9.18】优化XS表。OPTIMIZE TABLE KC

39、;如果一个表或索引已经损坏，可以使用REPAIR TABLE语句尝试修复它。REPAIR TABLE只对MyISAM和ARCHIVE表起作用。REPAIR LOCAL|NO_WRITE_TO_BINLOG TABLE tbl_name,tbl_name.QUICK EXTENDED USE_FRM说明：REPAIR TABLE语句支持以下选项：QUICK：如果指定了该选项，则REPAIR TABLE会尝试只修复索引树。EXTENDED：使用该选项，则MySQL会一行一行地创建索引行，代替使用分类一次创建一个索引。USE_FRM：如果MYI索引文件缺失或标题被破坏，则必须使用此选项。另外，还有两个表维护语句：BACKUP TABLE和RESTORE TABLE语句。使用BACKUP TABLE语句可以对一个或多个MyISAM表备份。语法格式为：BACKUP TABLE tbl_name,tbl_name.TO/path/to/backup/directory使用RESTORE TABLE语句可以获取BACKUP TABLE创建的一个或多个表的备份，将数据读取到数据库中。语法格式为：RESTORE TABLE tbl_name,tbl_name.FROM/path/to/backup/directory但是这两条语句不是很理想，已经不推荐使用了，这里只是大概了解一下。