《数据库原理及应用教程-MySQL8.0》课件 第11章 数据库的安全管理

数据库原理与应用教程-MySQL8.0第11章

数据库的安全管理第11章

数据库的安全管理安全性对于任何数据库管理系统来说都是至关重要的，数据库的安全性是指保护数据库以防止因不合法用户的访问而造成数据的泄密或破坏。MySQL提供有效的数据访问安全机制，用检查口令等手段来检查用户身份，保证只有合法的用户才能进入数据库系统。当用户对数据库执行操作时，系统自动检查用户是否有权限进行这些操作。加密是指通过使用加密算法和密钥对数据进行模糊处理的过程，是当数据库被破解或是备份被窃取后的最后一道防线。通过加密，使得未被授权的人在没有密钥或解密算法的情况下所窃取的数据变得毫无意义。1.1MySQL权限介绍MySQL的权限：简单的理解就是mysql允许用户进行权限范围内的操作，不可以越界。比如只允许用户A执行select操作，那么A就不能执行update操作。只允许用户B从某台机器上连接MySQL，那么B就不能从除那台机器以外的其它机器连接MySQL。MySQL的两个阶段验证，第一个阶段：服务器首先会检查是否允许用户连接。第二个阶段：如果用户连接成功，MySQL会检查用户发出的每个操作请求，验证是否有足够的权限来实施。11.1.1MySQL的权限表MySQL8.0中存在6个控制权限的表，分别为user、db、tables_priv、columns_priv、procs_priv和proxies_priv表。权限表的验证过程为：首先，从user表中的Host、User、Password这3个字段中判断连接的IP、用户名、密码是否存在，存在则通过验证。通过身份认证后，接着进行权限分配，如果是直接对表中数据的操作，则按照user、db、tables_priv、columns_priv的顺序进行验证。11.1.1MySQL的权限表先检查全局权限表user，如果user中对应的权限为Y(YES)，则此用户对所有数据库的权限都为Y，将不再检查db、tables_priv、columns_priv；如果为N(NO)，则在db表中检查此用户对应的具体数据库，并得到db中为Y的权限；如果db中为N，则检查tables_priv中此数据库对应的具体表，取得表中为相应的权限；11.1.1MySQL的权限表如果表中没有相应权限，则检查columns_priv中此数据库对应的具体表的列，取得列中相应的权限。如果用户要对存储过程或函数进行某种操作，则检查procs_priv表验证该用户是否具有操作权限。当然，如果对user或db表验证权限为Y，也无需再检查procs_priv表。proxies_priv表（模拟角色）用于实现类似用户组的管理。11.1.2查看MySQL的用户权限要想查看MySQL的用户权限，我们可以先查看MySQL中有哪些用户，然后可以查看其中某个用户都有哪些权限。1.查看MySQL的用户selectuser,hostfrommysql.user;2.查看root用户的权限（1）查询user表USEmysql;SELECT*FROMuserWHEREuser='root'ANDhost='localhost';

11.1.2查看MySQL的用户权限（2）查询表db表例：SELECT*FROMdbWHEREuser='root'ANDhost='localhost';

tables_priv：SELECT*FROMtables_privWHEREuser='root'ANDhost='localhost';

columns_priv：SELECT*FROMcolumns_privWHEREuser='root'ANDhost='localhost';

procs_priv：SELECT*FROMprocs_privWHEREuser='root'ANDhost='localhost';

3.查看root@localhost用户的权限showgrants

forroot@localhost;11.2用户管理当用户使用MySQL时，需要经过两个安全性阶段，身份验证阶段和权限认证阶段。身份验证阶段，用户在MySQL上获得对任何数据库的访问权限之前，必须登录到MySQL服务器上，并且被认为是合法的。用户验证通过后，登录到MySQL服务器上，需要检测用户是否有访问服务器中数据的权限，为此需要为用户授予数据访问权限，权限认证可以控制用户对数据库进行的操作。11.2.1创建登录用户账号创建登录服务器用户账号的方法：在可视化管理工具中菜单界面方式和利用SQL语句。1.菜单界面方式2.SQL语句方式CREATEUSER{login_user[IDENTIFIEDBY[PASSWORD]‘password’]}[,…n];【例11-1】利用SQL语句创建一个MySQL用户账号“user2”，密码为“12#45*”。CREATEUSER'user2'@'localhost'IDENTIFIEDBY'12#45*';11.2.1创建登录用户账号创建的用户账号的密码也可以不指定明文，而是直接指定密文。可以通过不同的加密算法获得不同的密文，MySQL的加密函数为MD5()或SHA()等函数。【例11-2】SQL语句创建一个MySQL用户账号“user3”，密码为“12#45*”，不指定明文。（1）使用MD5()函数获取“12#45*”的密文：SELECTMD5('12#45*');（2）使用密文密码创建用户user3。CREATEUSER'user3'@'localhost'IDENTIFIEDBY'1087e335d938bfc66511810f8a060ea8';11.2.2查看用户账号可视化管理工具的菜单界面或SQL语句查看。1．菜单界面方式2．SQL语句方式selectuser,hostfrommysql.user;11.2.3删除用户账号对于长期闲置的服务器用户账号，最好及时进行清理，以免万一用于非法操作而造成数据库的破坏。1．菜单界面方式2．SQL语句方式方法一：DROPUSER语句删除用户。【例11-3】删除用户user1和user2。DROPUSERuser1@localhost,user2@localhost;方法二：DELETE语句删除mysql.user表内用户。【例11-3】删除用户user1。DELETEFROMmysql.userWHEREhost='localhost'ANDuser='user1';11.3权限管理权限用于控制对数据库对象的访问，以及指定用户对数据库可以执行的操作。用户在登录到MySQL服务器之后，其被赋予的权限决定了该用户能够对哪些数据库对象执行哪种操作以及能够访问、修改哪些数据。11.3.1权限的类别用户可以设置服务器和数据库的权限。服务器权限允许数据库管理员执行管理任务数据库权限用于控制对数据库对象的访问和语句执行11.3.2权限操作可以通过可视化管理工具的菜单界面对用户的权限进行设置，也可以使用SQL语句中的GRANT（授予）、REVOKE（撤销）语句完成。1．菜单界面方式设置权限2．使用SQL语句设置权限（1）授予用户服务器权限GRANT{ALL[PRIVILEGES]|privilege_name[,…n]}ON*.*TOlogin_name[,…n][WITHGRANTOPTION];【例11-4】为用户user2@localhost授予CREATEVIEW的服务器级别权限。GRANTCREATEVIEWON*.*TOuser2@localhost;11.3.2权限操作（2）授予数据库级别的权限SQL语句中授予数据库级别权限的语法：GRANT{ALL[PRIVILEGES]|privilege_name[,…n]}ONdatabase_name.*TOlogin_name[,…n][WITHGRANTOPTION];【例11-5】为用户user3@localhost授予teaching数据库内创建表和查询所有表的权限。GRANTCREATE,SELECTONteaching.*TOuser3@localhost;11.3.2权限操作（3）授予表级别的权限SQL语句中授予表级别权限的语法格式：GRANT{ALL[PRIVILEGES]|privilege_name[,…n]}ONdatabase_name.table_nameTOlogin_name[,…n][WITHGRANTOPTION];【例11-6】为用户user4@localhost授予student表的SELECT、INSERT和UPDATE权限。GRANTSELECT,INSERT,UPDATEONteaching.studentTOuser4@localhost;11.3.2权限操作（4）授予列级别的权限SQL语句中授予列级别权限的语法格式：GRANT{ALL[PRIVILEGES]|privilege_name(column_name[,…n])[,…n]}ONdatabase_name.table_nameTOlogin_name[,…n][WITHGRANTOPTION];【例11-7】为用户user2@localhost授予teaching数据库sc表的score列的SELECT和UPDATE权限。GRANTSELECT(score),UPDATE(score)ONteaching.scTOuser2@localhost;11.3.2权限操作【例11-8】为用户user1@localhost授予teaching数据库course表的SELECT权限和classhour、credit列的UPDATE权限，并且授予GRANTOPTION权限。GRANTSELECT,UPDATE(classhour,credit)ONteaching.courseTOuser1@localhostWITHGRANTOPTION;【例11-9】用户user1@localhost连接MySQL服务器后，为用户user2@localhost授予teaching数据库course表的SELECT权限。GRANTSELECTONteaching.courseTOuser2@localhost;11.3.2权限操作（5）授予存储过程级别的权限GRANT{ALL[PRIVILEGES]|privilege_name[,…n]}ON{FUNCTION|PROCEDURE}database_name.{function­_name|procedure­_name}TOlogin_name[,…n][WITHGRANTOPTION];【例11-10】为用户user1@localhost授予teaching数据库GetStudent存储过程的EXECUTE权限。GRANTEXECUTEONPROCEDUREteaching.GetStudentTOuser1@localhost;11.3.2权限操作（1）撤销某些用户的所有权限REVOKEALL[PRIVILEGES],GRANTOPTIONFROMlogin_name[,…n];（2）撤销某些用户某个级别的指定权限REVOKE{ALL[PRIVILEGES]|privilege_name[(column_name[,…n])][,…n]}[,GRANTOPTION]ON{*.*|database_name.*|database_name.table_name|{FUNCTION|PROCEDURE}database_name.{function­_name|procedure­_name}}

FROMlogin_name[,…n];11.3.2权限操作【例11-11】把用户user2的所有权限全部撤销。REVOKEALL,GRANTOPTIONFROMuser2@localhost;【例11-12】撤销用户user3在teaching中创建表的权限。REVOKECREATEONteaching.*FROMuser3@localhost;11.3.2权限操作【例11-13】把用户user1修改course表中classhour和credit的权限撤销。REVOKEUPDATE(classhour,credit),GRANTOPTIONONteaching.courseFROMuser1@localhost;【例11-14】把用户user1对teaching数据库中GetStudent存储过程的EXECUTE权限撤销。REVOKEEXECUTEONPROCEDUREteaching.GetStudentFROMuser1@localhost;11.3.2权限操作3.普通用户登录MySQL用户获得权限后，可以登录MySQL，在其权限范围内对数据库进行操作。（1）菜单界面方式打开Navicat，新建连接，输入用户名“user1”和user1用户的密码。【例11-15】用户user1输入一个权限范围内的操作，比如查询teaching数据库中student表中所有学生的学号和姓名。【例11-16】用户user1输入一个权限范围外的操作，比如查询teaching数据库中sc表。11.3.2权限操作（2）SQL语句方式在WINDOWS的cmd命令行窗口选择C:\ProgramFiles\MySQL\MySQLServer8.0\bin目录，然后输入普通用户登录MySQL的语句，语句格式：mysql–hhost_name–uuser_name–p【例11-17】用户user1通过MySQL命令行客户端登录服务器，然后执行其权限范围内的查询语句。登录命令：mysql–hlocalhost–uuser1–p查询命令：SELECTsno,sname,ssexFROMstudent;11.4角色管理角色是一种MySQL的安全账户，是MySQL内部的管理单元，是管理权限时可以视为单个单元的其他安全账户的集合。若用户被加入到某一个角色中，则具有该角色的全部权限。可以建立一个角色来代表单位中一类工作人员所执行的工作，然后给这个角色授予适当的权限。利用角色，MySQL的管理者可以将某些用户设置为某一个角色，这样只对角色进行权限设置便可以实现对所有用户权限的设置，极大地减少了管理员的工作量。11.4.1创建角色创建角色的语法：CREATEROLErole[,…n];其中，role的格式为role_name@host_name。【例11-18】创建两个角色分别命名为role1和role2。USEmysql;CREATEROLErole1@localhost,role2@localhost;创建的角色和用户一样都存放在user表中，可以使用SELECT语句进行查看。SELECT*FROMuserWHEREhost='localhost'ANDuserIN('role1','role2');11.4.2授予和撤销角色权限授予和撤销角色的权限只需要将GRANT语句中TO后和REVOKE语句中FROM后的用户改为角色即可。1.授予角色权限【例11-19】分别给角色role1和role2授予teaching数据库中创建对象和teaching数据库中所有数据表的操作（读、写）角色。GRANTCREATEONteaching.*TOrole1@localhost;GRANTSELECT,INSERT,UPDATE,DELETEONteaching.*TOrole2@localhost;11.4.2授予和撤销角色权限这两个角色的权限都是数据库级别的，可以在db表中进行查看。比如：SELECT*FROMdbWHEREhost='localhost'ANDuserIN('role1','role2');11.4.2授予和撤销角色权限2.撤销角色权限【例11-20】撤销角色role2删除表中数据的权限。REVOKEDELETEONteaching.*FROMrole2@localhost;查询role2角色的权限：SELECT*FROMdbWHEREhost='localhost'ANDuser='role2';11.4.3授予和撤销用户角色如果想让某用户成为某角色的成员，就需要授予其该角色；如果不再想让某用户成为某角色的成员，可以撤销其相应的角色。1.授予用户角色授予用户角色的语法格式如下：GRANTrole[,…n]TOlogin_name[,…n];【例11-21】为用户user2授予role1角色。GRANTrole1@localhostTOuser2@localhost;如果想查看哪些用户是那些角色的成员，可以查询role_edges表。SELECT*FROMrole_edges;11.4.3授予和撤销用户角色2.撤销用户角色撤销用户角色的语法格式如下：REVOKErole[,…n]FROMlogin_name[,…n];【例11-22】撤销用户user2的role1角色。REVOKErole1@localhostFROMuser2@localhost;11.4.4删除角色如果某些角色不再需要了，可以将其删除，删除语句的语法格式如下：DROPROLErole[,…n];【例11-23】删除role1角色。DROPROLErole1@localhost;注意：如果某个角色还有成员用户，删除该角色后，用户的角色成员身份自动撤销。11.5数据加密加密是一种帮助保护数据的机制，它通过特定的加密（encryption）算法和密钥（secretkey）将数据变为乱码，使原始数据转为不可读形式，只有拥有密钥的访问者才能使用解密（decryption）算法和密钥将数据解密，实现正确的读取。11.5.1数据加密简介1．数据加密分类一般来说，加密可以分为两大类：对称加密（SymmetricCryptography）和非对称加密（AsymmetricCryptography）。11.5.1数据加密简介对于对称加密而言，因为使用数据时不仅仅需要传输数据本身，还要通过某种方式传输密钥，这很有可能使得密钥在传输的过程中被窃取。而对于非对称加密，私钥持有人可以把公钥发送给任何人，但不需要将私钥发送出去，因此安全性大大提高。对称加密算法简单、效率高，非对称加密算法复杂、效率低，因此，一种折中的办法是使用对称密钥来加密数据（数据一般较大），而使用非对称密钥来加密对称密钥（密钥一般较小）。这样既可以利用对称密钥的高性能，也可以利用非对称密钥的可靠性。11.5.1数据加密简介2．MySQL中的加密简介MySQL的加密思路有两种，一是在数据库外部加密后存入数据库，二是在数据库内部对数据进行加密。第二种比第一种更方便使用，因为外部加密每一次的查找都需要先算出加密后的数据再放入MySQL中，返回的也是加密的数据，需要在外部进行解密；而第二种可以直接在MySQL中传原值和加密的密钥，解密也可以在MySQL中完成。另外，外部加密所有的加密操作都需要在程序中完成，数据库中加密的数据仅仅是对某一特定程序有意义，而另外的程序如果没有对应的解密密钥和算法，则数据变得毫无意义。11.5.1数据加密简介

MySQL可以对数据库表中特定列进行加密，只保护“用户名”\“密码”\“银行卡号”等敏感数据，既有利于数据的安全性又可以提高数据库的访问速度。（1）单向加密只能对数据进行加密，不能对已经加密的数据再进行解密；采用这种加密方法就保证了被加密的数据只有用户一个人知道，其他人永远无法知道。

MySQL中常用的单向加密函数包括MD5()、SHA()等.（2）双向加密既可以对数据进行加密，也可以对数据进行解密。11.5.1数据加密简介

MySQL中常用的对称加密函数：ENCODE(str,key)

：使用key作为密钥加密字符串str，结果是一个二进制字符串，它以blob类型存储。DECODE(str,key)：使用key作为密钥解密加密字符串str。AES_ENCRYPT(str,key)

：

返回用密钥key对字符串str利用高级加密标准算法加密后的结果，结果是一个二进制字符串，以blob类型存储。AES_DECRYPT(str,key)

：返回用密钥key对字符串str利用高级加密标准算法解密后的结果。11.5.1数据加密简介

MySQL中常用的对称加密函数：DES_ENCRYPT(str[,{key_num|key_str}])

：返回用密钥对字符串str利用3DES（三重加密数据算法）加密后的结果，这种加密方法加密时可以选择使用key_num（数字密钥）或是key_str（字符串密钥）作为加密密钥。DES_DECRYPT(str,key)

：返回用密钥key对字符串str利用三重加密数据算法解密后的结果。

11.5.2数据加密和解密操作

1.单向加密操作在MySQL中，一般使用单向加密函数对密码等不经常在普通查询语句中使用的字段进行加密。对于这样的字段，用户程序一般不需要查看明文信息，不需要解密，不会像双向加密函数一旦加密密钥泄露，其明文就会泄露，所以安全性更高。（1）MD5()函数MD5加密的特点：不可逆；对不同的数据加密的结果是定长的32位字符；对相同的数据加密，得到的结果是一样的；具有抗修改性，即对原数据进行任何改动，哪怕只修改一个字节，所得到的MD5值都有很大区别；弱抗碰撞，已知原数据和其MD5值，想找到一个具有相同MD5值的数据(即伪造数据)是非常困难的；强抗碰撞，想找到两个不同数据，使他们具有相同的MD5值，是非常困难的。11.5.2数据加密和解密操作

11