电工与电子技术电子商务电子课件数据库原理及应用教程（基于Linux的MySQL和NoSQL应用）第13章ppt

1、电子课件数据库原理及应用教程（基于Linux的MySQL和NoSQL应用）第13章第13章MySQL权限管理数据库原理及应用教程（基于Linux的MySQL和NoSQL应用）CONTENTS访问控制1权限表2用户管理3账户权限管理4访问控制113.1 访问控制为满足MySQL服务器的安全基础，考虑以下内容：1.多数用户只需要对表进行读和写，但少数用户需要能创建和删除表；2.某些用户需要读表，但可能不需要更新表；3.可能想允许用户添加数据，但不允许他们删除数据；4.某些用户（管理员）可能需要处理用户账号的权限，但多数用户不需要；5.可能想让用户通过存储过程访问数据，但不允许他们直接访问数据；6.

2、可能想根据用户登录的地点限制对某些功能的访问。MySQL的访问控制就是为用户提供且仅提供他们所需的访问权,以此来确保MySQL服务器的安全访问。建议应该严肃对待root登录的使用权限表2MySQL服务器通过MySQL权限来来控制用户对数据库的访问，安装MySQL数据库成功后，会自动安装多个数据库。MySQL权限表存放在名称为MySQL的数据库里。常用到的表有user、db、table_priv、columns_priv、column_priv和procs_priv。13.2.1 user表use表示MySQL中最终的一个权限表,可以使用DESC语句来查看user的基本结构。当用户进行连接时，权

3、限表的存取过程有以下两个阶段：先从user表中的host、user和password这3个字段中判断连接的ip、用户名称和密码是否存在于表中，如果存在，则通过身份验证，否则拒绝连接。用户列权限列安全列资源控制列普通权限：用于对数据库的操作管理权限：对数据库进行管理的操作13.2.1 user表1)用户字段 用户字段 user表中的host、user和password字段都属于用户字段。如果通过身份验证，按照以下权限的顺序得到数据库权限：user、db、table_priv、colums_priv。这几个表的权限依次递减，全局权限覆盖局部权限。【例13-1】查询user表的相关用户字段selec

4、t host, user, authentication_string from MySQL.user;13.2.1 user表2）权限字段user表中包含几十个与权限有关以priv结尾的字段，这些权限字段决定了用户的权限，这些权限不仅包括基本权限、修改和添加权限等，还包含关闭服务器权限、超级权限和加载权限等。不同用户所拥有的权限可能会有所不同。这些字段的值只有y或n，表示有权限和无权限。默认值是n，可用grant语句为用户赋予权限【例13-2】查看localhost主机下的用户的select、insert、update、权限13.2.1 user表3）安全字段安全列只有6个字段，其中两个是s

5、sl相关的：ssl_type和ssl_cipher，2个是x509相关的：x509_issuer和x509_subject，另外2个是授权插件相关的。ssl用于加密；x509标准可用于标识用户；plugin字段标识可以用于验证用户身份的插件，如果该字段为空，服务器使用内建授权验证机制验证用户身份。【例13-3】可用show variables like have_openssl;语句可以查看have_openssl是否具有ssl功能13.2.1 user表4）资源控制列资源控制列的字段用来限制用户使用的资源，包含4个字段，分别为：max_questions：用户每小时允许执行的查询操作次数。m

6、ax_updates：用户每小时允许执行的更新操作次数。max_connections：用户每小时允许执行的连接操作次数。max_user_connections：单个用户可以同时具有的连接次数。这些字段的默认值为0，表示没有限制。一个小时内用户查询或者连接数量超过资源控制限制，用户将被锁定，直到下一个小时，才可以再此执行对应的操作。13.2.2 db表和host表db表中存储了用户对某个数据库的操作权限，决定用户能从哪个主机存取哪个数据库；host表中存储了某个主机对数据库的操作权限，配合db权限表对给定主机上数据库级操作权限做更细致的控制。字段大致可以分为两类：用户列和权限列。1) 用户列

7、db表的用户列有3个字段：host、db和user。这3个字段分别表示主机名、数据库名和用户名；ost表的用户列有两个字段：host和db。这两个字段分别表示主机名和数据库名。2) 权限列db表和host表的权限列大致相同，表中create_routine_priv和alter_routine_priv这两个字段表明用户是否有创建和修改存储过程的权限。3.1.4 专门的关系运算设有一个学生-课程数据库见表 3-9，它包括以下内容。学生关系 Student(说明：Sno 表示学号，Sname 表示姓名，Ssex 表示性别，Sage 表示年龄，Sdept 表示所在系)课程关系 Course(说明：

8、Cno 表示课程号，Cname 表示课程名)选修关系 Score(说明：Sno 表示学号，Cno 表示课程号，Degree 表示成绩)其关系模式如下。Student(Sno，Sname，Ssex，Sage，Sdept)Course(Cno，Cname)Score(Sno，Cno，Degree) 【例】查询数学系学生的信息。Sdept=数学系 (Student)或5=数学系 (Student)13.2.3 tables_priv表tables_priv表可以对单个表进行权限设置，用来指定表级权限。这里指定的权限适用于一个表的所有列。用户可以用desc语句查看表结构。tables_priv表有8个

9、字段：host、db、user、table_name、grantor、timestamp、table_priv和column_priv。host、db、user和table_name等4个字段分别表示主机名、数据库名、用户名和表名。grantor表示修改该记录的用户。timestamp字段表示修改该记录的时间。table_priv字段表示对表进行操作的权限，这些权限包括select、insert、update、delete、create、drop、grant、references、index和alter。column_priv字段表示对表中的列进行操作的权限，这些权限包括select、inse

10、rt、update和references。13.2.3 tables_priv表【例13-4】用desc tables_priv G命令查看结构如下: 13.2.4 columns_priv表columns_priv表可以对表中的某一列进行权限设置columns_priv表只有7个字段，分别是host、db、user、table_name、column_name、timestamp和column_priv。其中，column_name用来指定对哪些数据列具有操作权限。MySQL中权限的分配是按照user表、db表、tables_priv表和columns_priv表的顺序进行分配的。数据库系统

11、中，先判断user表中的值是否为y，如果user表中的值是y，就不需要检查后面的表了；如果user表中的值为n，则依次检查db表、tables_priv表和columns_priv表。13.2.5 procs_priv表procs_priv表可以对存储过程和存储函数进行权限设置。可以使用desc语句来查看procs_priv表的基本结构。procs_priv表包含8个字段：host、db、user、routine_name、routine_type、grantor、proc_priv和timestamp等。各个字段的说明如下：host、db和user字段分别表示主机名、数据库名和用户名。rou

12、tine_name字段表示存储过程或存储函数的名称。routine_type字段表示存储过程或存储函数的类型。该字段有两个值，分别是function和procedure。function表示是一个存储函数；procedure表示是一个存储过程。grantor字段存储插入或修改该记录的用户。proc_priv字段表示拥有的权限，包括execute、alter routine、grant3种。timestamp字段存储记录更新的时间。用户管理3MySQL用户账号和信息存储在名为MySQL的MySQL数据库中。这个数据库里有一个名为user的数据表，包含了所有用户账号。在需要获得所有用户账号列表时，

13、可使用以下代码实现：use MySQL;select user from user;13.3.1 添加用户1. 使用create use创建用户账号 user:指定创建用户账号，其格式为user_namehost name。password：指令用户账号的口令，在identified by关键字或password关键字之后。给定的口令值可以是由字母和数字组成的明文，也可以是通过password()函数得到的散列值。create user user identified by password password ,user identified by password password 13.3

14、.1 添加用户【例13-5】在MySQL服务器中添加新的用户，其用户名分别为zhangmei主机名为localhost，口令设置为明文123 create user语句的使用说明如下：(1)要使用create user语句，必须拥有MySQL中MySQL数据库的insert权限或全局create user权限。(2)创建一个用户账号后会在系统自身的MySQL数据库的user表添加一条新记录。如果创建的账户已经存在，则语句执行会出现错误。(3)如果两个用户具有相同的用户名和不同的主机名，MySQL会将他们视为不同的用户。(4)如果create user语句的使用中，没有为用户指定口令，那么MyS

15、QL允许该用户可以不使用口令登录系统，然而从安全的角度而言，不推荐这种做法。(5)新创建的用户拥有的权限很少。13.3.1 添加用户2、使用Insert语句新建普通用户插入数据时，至少要插入以下6个字段的值，即 host, user, password, ssl_cipher, x09_issuer, x509_subject。【例13-6】插入xiaohong用户，主机名为localhost，密码是 password(123)执行完后，要使用FLUSH PRIVILEGES;命令来使用户生效,这个命令需要reload权限。13.3.1 添加用户3、使用grant语句来新建普通用户可以使用gr

16、ant语句来创建新的用户，在创建用户时可以为用户授权。grant语句是MySQL中非常重要的一个命令，不仅可以创建用户、授予权限、还可以修改密码。13.3.2 查看用户select * from MySQL.user where host=host_name and user=user_name 【例13-7】 查看本地主机上的所有用户名。select host,user ,authentication_string from MySQL.user; 13.3.4 修改用户账号rename user old_user to new_user ，old_user to new_user (1)o

17、ld_user:系统中已经存在的MySQL用户账号。(2)new_user:新的MySQL用户账号。【例13-8】将前面例子中用户zhangmei的名字修改成wangwu。13.3.5 修改用户口令1)使用MySQLadmin命令来修改密码MySQLadmin u username -p password 【例13-9】修改root密码为 “Cau123456“;输入命令MySQLadmin u root -p password 后，先根据提示输入旧密码，在输入新密码和确认新密码13.3.5 修改用户口令2)使用set语句来修改密码set password for usernamehostna

18、me = password(new_password);【例13-10】修改xiaohong密码为123set password for xiaohonglocalhost = password (123);13.3.5 修改用户口令3)修改MySQL数据库下的user表，需要对MySQL.user表的修改权限，又有root权限最高，一般情况我们可以使用root用户登录后，修改自己或普通用户的密码。update MySQL.userset authentication_string =password(new_password)where user=user_name and host = h

19、ost_name;【例13-11】使用update修改xiaohong的密码为12345613.3.3 删除用户1、用drop user语句来删除普通用户drop user语句来删除用户，必须有drop user权限。drop user user,user【例13-12】删除xiaohong用户，其host值为localhost13.3.3 删除用户2、使用delete语句来删除普通用户【例13-13】删除wangwu用户，主机名为localhostdelete from MySQL. user where user = wangwu and host = localhost;账户权限管理41

20、3.4.1 权限授予新建的MySQL用户必须被授权，可以使用grant语句来实现。grant priv_type(column_list) ，priv_type(column_list) on object_type priv_level to user_specification，user_specification with with_option Grant命令要求至少提供以下信息：（1）要授予的权限；（2）被授予访问权限的数据库或表；（3）用户名。13.4.1 权限授予【例13-14】授予用户lili在数据库studentinfo的表student上拥有对列sno和列sname的sel

21、ect权限。可以使用select语句来查看表student中列sno和列sname的数据，而且目前仅能执行这项操作，如果执行其他的数据库操作，则会出现错误select * from student ; error 1142 (42000) : select command denied to user lililocalhost for table student 13.4.1 权限授予13.4.1 权限授予【例13-15】当前系统中不存在用户liming和用户huang，要求创建这两个用户，并设置对应的系统登录口令，同时授予他们在数据库studentInfo的表student上拥有selec

22、t和update的权限。grant select,update on studentInfo.student to liminglocalhost identified by Liming123,huanglocalhost identified by Huang789; 语句成功执行后，即可分别使用liming和huang的账户登录MySQL服务器，验证这两个用户是否具有了对表student可以执行select和update操作的权限。13.4.1 权限授予【例13-16】授予系统中已存在用户lili可以在数据库studentinfo中执行所有数据库操作的权限。grant all on st

23、udentInfo.* to lililocalhost;13.4.2 权限的转移和限制1）转移权限如果将with子句指定为with grant option，则表示to子句中所指定的所有用户都具有把自己所拥有的权限授予其他用户的权利，而无论那些其他用户是否拥有该权限。【例13-17】授予当前系统中一个不存在的用户zhou在数据库studentInfo的表student上拥有select和update的权限，并允许其可以将自身的这个权限授予其他用户。使用root登录MySQL服务器，并在MySQL的命令行客户端输入下面的sql语句：grant select,update on studentInfo.stud