第15讲 数据库的安全管理

1、回顾回顾事务事务p事务具有事务具有A(原子性原子性)，C(一致性一致性)，I(隔离性隔离性)，D(持久性持久性)p一条独立的一条独立的SQL语句是一个事务。语句是一个事务。例如：例如：工资表中有工资表中有1000条记录，条记录，“UPDATE 工资表工资表 SET 工资工资=工资工资+200”，执行突然停电，执行突然停电，1000条记录要么全修改，要条记录要么全修改，要么全不修改。么全不修改。pBEGIN TRANCommit TRAN中间的操作构成一个中间的操作构成一个事务。事务。触发器触发器p是一种特殊的存储过程。是一种特殊的存储过程。p有有INSTEAD OF触发器和触发器和AFTER触

2、发器两种。触发器两种。p当进行增删改操作时，先执行当进行增删改操作时，先执行INSTEAD OF触发器，触发器，再进行约束验证，再执行再进行约束验证，再执行AFTER触发器。触发器。数据库原理与应用数据库原理与应用第第15讲讲 数据库的安全管理数据库的安全管理提纲提纲重点重点SQL Server的安全机制的安全机制添加登录用户添加登录用户添加数据库用户添加数据库用户管理角色与角色成员管理角色与角色成员为用户和角色分配权限为用户和角色分配权限难点难点对对SQL Server中固定角色的理解中固定角色的理解语句权限与对象权限的区别以及授权方法语句权限与对象权限的区别以及授权方法1. SQL Ser

3、ver 的安全机制的安全机制11.1 SQL Server 11.1 SQL Server 的安全机制的安全机制1.1 SQL Server 的安全机制的安全机制数据访问安全的两项基本机制数据访问安全的两项基本机制（1）身份验证身份验证确认用户的账号是否有效、口令是否确认用户的账号是否有效、口令是否正确。正确。（2）访问许可访问许可指定用户有哪些操作权限。指定用户有哪些操作权限。概括起来，概括起来，SQL Server的安全体系描述为：的安全体系描述为：(1) 要连接到要连接到SQL Server，必须拥有，必须拥有安全帐号安全帐号.(2) 要操作数据库，必须授权安全帐号要操作数据库，必须授权

4、安全帐号访问数据库访问数据库。(3) 用户在数据库中能进行的操作由其所拥用的权限决定，用户在数据库中能进行的操作由其所拥用的权限决定，权限可以从其所属的权限可以从其所属的角色继承角色继承，也可以，也可以单独被授予单独被授予。(4) 操作权限包括两大类：操作权限包括两大类：语句权限语句权限和和数据库对象权限数据库对象权限。例：用户例：用户Tom要要Sales数据库上对数据库上对Employee表进行更新操作，所需经过表进行更新操作，所需经过的安全关。的安全关。可否登录可否登录SQL Server？是是Sales数据库数据库的用户么的用户么?有对有对Employee表更新权限么？表更新权限么？权限

5、可以单独分配给用户，权限可以单独分配给用户，也可分配给用户所属的角也可分配给用户所属的角色色合法的合法的Windows用户可以登录。用户可以登录。合法的合法的SQL Server用户，也可用户，也可登录。登录。接受接受TOM的操作指令的操作指令1.2 SQL Server的两种身份验证模式的两种身份验证模式两种模式：两种模式：(1)Windows身份验证模式身份验证模式。适用于能够提供有效身份验。适用于能够提供有效身份验证的证的Windows操作系统。操作系统。(2)混合身份验证模式混合身份验证模式。被授权的。被授权的Windows用户和合法的用户和合法的SQL Server账号账号都都可以登

6、录。可以登录。两种模式的适用场合两种模式的适用场合(1)Windows 账号验证。当应用程序与账号验证。当应用程序与SQL Server在同一在同一台台Windows服务器上或服务器上或同一个工作组同一个工作组/域时域时，适合采用，适合采用Windows身份验证。身份验证。(2) SQL Server账号验证。当应用程序运行在非账号验证。当应用程序运行在非Windows操作系统上，或者通过远程连接访问操作系统上，或者通过远程连接访问SQL Server。1.3 设置设置SQL Server身份验证模式身份验证模式通过管理平台来设置验证模式。通过管理平台来设置验证模式。操作提示：操作提示：在管理

7、平台的对象资源管理器中，右键单击在管理平台的对象资源管理器中，右键单击服务器，选择服务器，选择“属性属性”，打开，打开 “服务器属性服务器属性”窗口，窗口，选择选择“安全性安全性”。2. 登录账号管理登录账号管理通过身份验证并不代表能够访问数据库中的通过身份验证并不代表能够访问数据库中的数据，用户只有获取访问数据库的权限，才数据，用户只有获取访问数据库的权限，才能操作数据库对象，能操作数据库对象，如如：表、视图、存储过：表、视图、存储过程等。程等。2.1 登录用户的作用和类型登录用户的作用和类型作用作用用户凭借登录名和口令连接到用户凭借登录名和口令连接到SQL Server实例。实例。类型：类

8、型：(1)Windows用户。给一个已经存在的用户。给一个已经存在的Windows用户用户/组组授权连接授权连接SQL Server。【例例】anwzf/weizy是一个是一个用户用户，A401/Users是一个是一个用用户组户组。(2)SQL Server登录用户。登录用户。【例例】sa是是SQL Server的一个内置用户，的一个内置用户，sa是是systemadmin的缩写，即的缩写，即SQL Server系统管理员。系统管理员。11.2 11.2 登录账号管理登录账号管理2.2 登录用户管理的任务和方法登录用户管理的任务和方法登录账号管理的任务包括登录账号管理的任务包括授权登录，添加授

9、权登录，添加SQL Server用户。用户。方法方法方法一：使用管理平台方法一：使用管理平台操作提示：启动操作提示：启动SQL Server管理平台，在对象资源管理管理平台，在对象资源管理器中分别展开器中分别展开“服务器服务器”“安全性安全性”“登录名登录名”。 方法二：使用系统存储过程。方法二：使用系统存储过程。2.3 通过计算机管理建新通过计算机管理建新用户用户(1) 创建新创建新用户用户weizy(2) 新建登录名：新建登录名：anwzfweizy(3) 登录。登录。（1）（2-2）（2-1）（3）2.3 通过计算机管理建新通过计算机管理建新用户组用户组(1) 创建新创建新用户组用户组W

10、L，其中有用户其中有用户weizy_lxl(2) 新建登录名：新建登录名：anwzfweizy_lxl(3) 登录。登录。（1-1）（2-1）（3）（1-2）（2-2）2.3 管理登录用户的存储过程管理登录用户的存储过程1(了解了解)(1) sp_grantlogin授权授权Windows用户用户或组连接到或组连接到SQL Server。【例例】授予授予Windows用户用户ANWZFweizy连接到连接到SQL Server的权限。的权限。 EXEC sp_grantlogin ANWZFweizy【例例】授予本地组授予本地组WL中的所有用户连接中的所有用户连接SQL Server的的权限权

11、限(说明：说明：ANWZF关键字代替域名和计算机名关键字代替域名和计算机名)EXEC sp_grantlogin ANWZFwL2.3 管理登录用户的存储过程管理登录用户的存储过程-2(了解了解)(2) sp_addlongin添加添加SQL SERVER登录用户。登录用户。注：注：若要登录成功，还要映射到某数据库，重启后台服务。若要登录成功，还要映射到某数据库，重启后台服务。【例例】添加新登录名为添加新登录名为“ZG002”，密码为，密码为“002”，默认，默认数据库为数据库为“Sales”。EXEC sp_addlogin ZG002,002,Sales【例例】添加没有密码和默认数据库的登

12、录，登录名为添加没有密码和默认数据库的登录，登录名为“ZG003”。EXEC sp_addlogin ZG003注：注：有些系统会提示有些系统会提示密码有效性验证失败。该密码不够密码有效性验证失败。该密码不够复杂，不符合复杂，不符合Windows 策略要求。策略要求。说明：在没有指定密码和默认数据库时，登录用户使说明：在没有指定密码和默认数据库时，登录用户使用默认密码用默认密码NULL和默认数据库和默认数据库master。11.2.1 11.2.1 创建登录账户创建登录账户(3) sp_helplogins用于查看登录用户信息。用于查看登录用户信息。【例例】查看查看所有登录用户所有登录用户信息

13、。信息。EXEC sp_helplogins【例例】查看登录用户查看登录用户ZG002的信息。的信息。EXEC sp_helplogins ZG0022.3 管理登录用户的存储过程管理登录用户的存储过程-3(了解了解)11.2 11.2 登录账号管理登录账号管理 (4) sp_password 用于修改登录用户的口令。用于修改登录用户的口令。语法格式为：语法格式为：sp_password old=old_password,new=new_password , loginame=login(5) sp_defaultdb用于修改登录用户的默认数据库。用于修改登录用户的默认数据库。语法格式为：语法

14、格式为：sp_defaultdb logname= login, defdb=databases【例例】 给已建的登录名给已建的登录名ZG002修改密码，修改默认数据库设置为修改密码，修改默认数据库设置为STUDENT 。 EXEC sp_password 002,123,ZG002 EXEC sp_defaultdb ZG002,STUDENT 2.2 管理登录用户的存储过程管理登录用户的存储过程-4(了解了解)11.2 11.2 登录账号管理登录账号管理 2.3 管理登录用户的存储过程管理登录用户的存储过程-5(了解了解)(6) sp_revokelogin 撤销撤销Windows用户用户

15、/组的登录权限。组的登录权限。【例例】收回收回Windows用用/组组ANWZFweizy/ ANWZFwl的登录权。的登录权。EXEC sp_revokelogin ANWZFweizyEXEC sp_revokelogin ANWZFwl(7)sp_droplogin删除删除SQL Server登录用户。登录用户。【例例】 删除删除SQL Server用户用户ZG002。EXEC sp_droplogin ZG002注注：登录名删除，映射于：登录名删除，映射于student数据库的用户名数据库的用户名ZG002依旧存在依旧存在,不过登录不过登录 不成功。不成功。同一登录名同一登录名可以用已

16、有的数据库用户名映射。可以用已有的数据库用户名映射。3. 数据库用户的管理数据库用户的管理3.1 数据库用户的作用数据库用户的作用作用：作用：登录用户连接到登录用户连接到SQL Server之后，如果要在某数据库中之后，如果要在某数据库中进行操作，必须是该数据库的数据库用户。通常，进行操作，必须是该数据库的数据库用户。通常，数数据库用户名和登录名是相同据库用户名和登录名是相同的，但也可以设置成不相的，但也可以设置成不相同。同。【例例】sa是一个登录名，但在数据库中，其用户名可能是一个登录名，但在数据库中，其用户名可能是是dbo.3.2 数据库用户管理的任务和方法数据库用户管理的任务和方法任务：

17、任务：授权登录用户访问数据库、撤销授权授权登录用户访问数据库、撤销授权方法方法方法一：使用管理平台。方法一：使用管理平台。操作提示：在其操作提示：在其“对象资源管理器对象资源管理器”面板中依次展开面板中依次展开“服务器服务器”“数据库数据库”“安全性安全性”节点。右击节点。右击选择其下的选择其下的“用户用户”对象，选择对象，选择“新建用户新建用户”，打，打开开“数据库用户数据库用户-新建新建”窗口。窗口。方法二：方法二： 使用系统存储过程。使用系统存储过程。3.3 数据库用户管理的存储过程数据库用户管理的存储过程(了解了解)(1) sp_grantdbaccess授权登录用户访问数据库。授权登

18、录用户访问数据库。【例例】授权登录名授权登录名ZG002访问当前数据库。访问当前数据库。EXEC sp_grantdbaccess ZG002 (2)sp_revokedbaccess撤销登录用户访问数据库的授权。撤销登录用户访问数据库的授权。【例例】在当前数据库中删除指定的用户。在当前数据库中删除指定的用户。EXEC sp_revokedbaccess ZG002(3) sp_helpuser查看用户信息查看用户信息【例例】 EXEC sp_helpuser11.3 11.3 数据库用户的管理数据库用户的管理4. 角色管理角色管理4.1 角色的作用和类型角色的作用和类型第第1111章章 数据

19、库的安全管理数据库的安全管理 角色的作用角色的作用将用户加入角色，用户就具有该角色所拥有的权限。将用户加入角色，用户就具有该角色所拥有的权限。同一数据库的用户可以属于多个角色，从而实现权限的组合。同一数据库的用户可以属于多个角色，从而实现权限的组合。角色可以进行嵌套，从而实现权限的继承。角色可以进行嵌套，从而实现权限的继承。角色的类型角色的类型(1) 固定角色。是固定角色。是SQL Server系统中预设的角色，这些角色不能被系统中预设的角色，这些角色不能被添加、修改和删除。包括固定服务器角色和固定数据库角色。添加、修改和删除。包括固定服务器角色和固定数据库角色。p固定服务器角色。独立于各个数

20、据库，具有预定义的服务器固定服务器角色。独立于各个数据库，具有预定义的服务器操作权限。操作权限。p固定数据库角色。每个数据库中有，具有相同的预定义权限。固定数据库角色。每个数据库中有，具有相同的预定义权限。 (2) 用户定义数据库角色。用于实现固定数据库角色所不能提供的权用户定义数据库角色。用于实现固定数据库角色所不能提供的权限组合。限组合。11.4.1 SQL Server11.4.1 SQL Server角色的类型角色的类型角色名称权 限Sysadmin系统管理员，可以在SQL Server服务器中执行任何操作Serveradmin服务器管理员，具有对服务器设置和关闭的权限。Setupad

21、min设置管理员，添加和删除链接服务器，并执行某些系统存储过程。Securityadmin安全管理员，管理服务器登录标识、更改密码、CREATE DATABASE权限，还可以读取错误日志。processadmin 进程管理员，管理在SQL Server服务器中运行的进程。Dbcreator数据库创建者，可创建、更改和删除数据库。Diskadmin管理系统磁盘文件bulkadmin可执行BULK INSERT语句，但必须有INSERT权限。固定服务器角色11.4.1 SQL Server11.4.1 SQL Server角色的类型角色的类型固定数据库角色角色名称权 限db_owner数据库的所有

22、者，可以执行任何数据库管理工作。db_accessadmin数据库访问权限管理者，可增删用户、组。db_securityadmin管理角色和数据库角色成员、对象所有权、语句执行权限、数据库访问权限。db_ddladmin数据库DDL管理员，创建、删除或修改数据库对象。db_backupoperator执行数据库备份权限。db_datareader能且仅能对数据库中任何表执行SELECT操作。db_datawriter能对任何表执行更新操作，但不能进行查询操作。db_denydatawriter不能对任何表进行增、删、修改操作。db_denydatareader不能读取数据库中任何表的内容。pu

23、blic每个数据库用户都是public角色成员。4.2 角色管理的任务和方法角色管理的任务和方法任务：任务：p对自定义的数据库角色，进行增、删、改、查。对自定义的数据库角色，进行增、删、改、查。p对于角色（含固定角色和自定义角色）的成员，进行对于角色（含固定角色和自定义角色）的成员，进行增、删、查。增、删、查。方法：方法：方法一：使用管理平台。方法一：使用管理平台。操作提示：对于服务器角色成员的管理，操作提示：对于服务器角色成员的管理， 在在 “服务器角服务器角色属性色属性”里管理。对于数据库角色的管理，在某数据里管理。对于数据库角色的管理，在某数据库上的库上的 “数据库角色属性数据库角色属性

24、”里管理。里管理。方法二：使用系统存储过程。方法二：使用系统存储过程。4.3 角色管理的存储过程角色管理的存储过程(了解了解)(1) sp_addrole 用于添加自定义数据库角色。用于添加自定义数据库角色。【例例】在在Sales数据库中创建角色数据库中创建角色“role01”Use SalesGOEXEC sp_addrole role01(2) sp_droprole用于删除自定义数据库角色。用于删除自定义数据库角色。【例例】在在Sales数据库中，删除角色数据库中，删除角色“role01” 。USE SalesGOEXEC SP_droprole role01(3) sp_helpdbf

25、ixedrole查看固定数据库角色信息查看固定数据库角色信息 【例例】 EXEC sp_helpdbfixedrole db_owner(4) sp_helpsrvrole用于查看固定服务器角色信息用于查看固定服务器角色信息【例例】EXEC sp_helpsrvrole sysadmin4.4 角色成员管理的存储过程角色成员管理的存储过程-2(了解了解)(1)sp_addsrvrolemember 用于向固定服务器角色添加成员。用于向固定服务器角色添加成员。【例例】将登录用户将登录用户ZG002添加为固定服务器角色添加为固定服务器角色sysadmin的成员。的成员。EXEC sp_addsrv

26、rolemember ZG002,sysadmin(2) sp_dropsrvrolemember用于删除固定服务器角色的成员。用于删除固定服务器角色的成员。【例例】从固定服务器角色从固定服务器角色sysadmin中删除登录用户中删除登录用户ZG002。EXEC sp_dropsrvrolemember ZG002,sysadmin(3) sp_addrolemember用于向数据库角色添加成员。用于向数据库角色添加成员。【例例】向数据库向数据库Sales添加添加Windows用户用户“anwzfweizy”。USE SalesGOEXEC sp_addrolemember db_ddladm

27、in, anwzfweizyGO(4) sp_droprolemember用于删除当前数据库角色中的成员，用于删除当前数据库角色中的成员，【例例】删除数据库角色删除数据库角色db_owner中的用户中的用户ZG002EXEC sp_droprolemember db_owner, ZG00211.4.2 11.4.2 固定服务器角色管理固定服务器角色管理4.4 角色成员管理的存储过程角色成员管理的存储过程-2(了解了解)(5) sp_helpsrvrolemember用于查看服务器角色的成员信息用于查看服务器角色的成员信息【例例】EXEC sp_helpsrvrole sysadminGOEX

28、EC sp_helpsrvrolemember sysadmin GO5. 权限管理权限管理 5.1 权限的种类权限的种类SQL Server中的权限包括对象权限和语句权限中的权限包括对象权限和语句权限对象权限对象权限对数据库对象执行操作的权力。数据对数据库对象执行操作的权力。数据库对象包括库对象包括表、视图、存储过程表、视图、存储过程等。等。语言权限语言权限执行语句的权限。如创建数据库、表、执行语句的权限。如创建数据库、表、存储过程等。这种语句虽然也包含有操作存储过程等。这种语句虽然也包含有操作(如如CREATE)的对象，但这些对象在执行该语句之前的对象，但这些对象在执行该语句之前并不存在于

29、数据库中，所以将其归为语句权限范并不存在于数据库中，所以将其归为语句权限范畴。畴。11.5 11.5 权限管理权限管理对象权限对象权限 11.5.1 11.5.1 权限的种类权限的种类对 象操 作表SELECT、INSERT、UPDATE、DELETE、REFERANCES视图SELECT、INSERT、UPDATE、DELETE存储过程EXECUTE列SELECT、UPDATE语句权限及其作用 11.5.1 11.5.1 权限的种类权限的种类语 句作 用CREATE DATABASE创建数据库CREATE TABLE在数据库中创建表CREATE VIEW在数据库中创建视图CREATE DEF

30、AULT在数据库中创建默认对象CREATE PROCEDURE在数据库中创建存储过程CREATE RULE在数据库中创建规则CREATE FUNCTION在数据库中创建函数BACKUP DATABASE备份数据库BACKUP LOG备份日志5.2 权限管理的任务和方法权限管理的任务和方法任务任务为用户或角色授权（包括允许和禁止）、撤销权限。为用户或角色授权（包括允许和禁止）、撤销权限。授予权限授予权限(GRANT)用户有权进行某项操作。用户有权进行某项操作。禁止权限禁止权限(DENY)用户无权进行某项操作，即便其所用户无权进行某项操作，即便其所属的角色有权进行该项操作。属的角色有权进行该项操作

31、。撤消权限撤消权限(REVOKE)用于删除用户的权限，但是撤用于删除用户的权限，但是撤消权限是删除曾经授予的权限，并不禁止用户、组或消权限是删除曾经授予的权限，并不禁止用户、组或角色通过别的方式继承权限。角色通过别的方式继承权限。方法方法方法一：使用管理平台。方法一：使用管理平台。方法二：使用方法二：使用T-SQL。5.3 使用管理平台进行权限管理使用管理平台进行权限管理语句权限语句权限（1）在对象资源管理器中）在对象资源管理器中展开展开“数据库服务数据库服务器器”“数据库数据库”。（2）在选择的数据库上单）在选择的数据库上单击鼠标右键，在弹出菜击鼠标右键，在弹出菜单中选择单中选择“属性属性”

32、项，项，打开打开“数据库属性数据库属性”对对话框。话框。（3）在）在“数据库属性数据库属性”对对话框中选择话框中选择“权限权限”选选项卡，进行相应的项卡，进行相应的语句语句权限设置权限设置 11.5 11.5 权限管理权限管理5.3 使用企业管理器管理权限使用企业管理器管理权限11.5.2 11.5.2 授予权限授予权限（1）（2）GRANT授予或具有授予授予或具有授予DENY拒绝拒绝REVOKE5.4 使用使用T-SQL语句管理权限语句管理权限-1(了解了解)(1). 为用户或角色授予对象权限为用户或角色授予对象权限【例例】 在当前数据库中，为在当前数据库中，为goods表授予权限表授予权限

33、GRANT SELECT ON Goods TO public -为角色授权为角色授权GO GRANT INSERT,UPDATE,DELETE ON goods TO ZG002 -为用为用户户 goGRANT INSERT,UPDATE,DELETE ON goods TO ZG002【例例】在当前数据库中，为在当前数据库中，为Employee表的列授予权限表的列授予权限GRANT SELECT (Employee_id,Employee_name) ON Employee TO public 5.4 使用使用T-SQL语句管理权限语句管理权限-2(了解了解)(2)为用户或角色授予语句权限

34、为用户或角色授予语句权限【例例】 USE Sales GO GRANT CREATE TABLE TO ZG0025.4 使用使用T-SQL语句管理权限语句管理权限-3(了解了解)(3) 禁止权限禁止权限【例例】禁止用户禁止用户ZG002使用使用CREATE VIEW语句。语句。 USE SalesGODENY CREATE TABLE TO ZG002【例例】给给pubic角色授予表角色授予表employee上的上的SELETE权限，再拒绝用户权限，再拒绝用户ZG001、ZG002的特定权限，以使这些用户没有对的特定权限，以使这些用户没有对employee表的操作权表的操作权限。限。 USE

35、 SalesGOGRANT SELECT ON employee TO publicGODENY SELECT,INSERT,UPDATE,DELETE ON employee TO ZG002GO此时，虽然此时，虽然ZG001,ZG002属于属于public组，但不能对组，但不能对employee表进行增删改表进行增删改差操作。差操作。11.5.3 11.5.3 禁止与撤消权限禁止与撤消权限5.4 使用使用T-SQL语句管理权限语句管理权限-4(了解了解)(4) 撤消以前授予或拒绝的权限撤消以前授予或拒绝的权限【例例】撤消用户撤消用户ZG002对在对在sales数据库中创建表的权限。数据库中

36、创建表的权限。USE SalesGOREVOKE CREATE TABLE FROM ZG002【例例】 撤消以前撤消以前ZG002授予或拒绝的授予或拒绝的SELECT权限。权限。 Use SalesGOREVOKE SELECT ON employee FROM ZG002此时此时ZG001用户不具有用户不具有employee表上的操作权限，但如表上的操作权限，但如果其所属的角色具有果其所属的角色具有employee表上的操作权限，它可表上的操作权限，它可以继承。以继承。11.5.3 11.5.3 禁止与撤消权限禁止与撤消权限5.4 使用使用T-SQL语句管理权限语句管理权限-5(了解了解)使用使用sp_helprotect可以查询当前数据库中的权限信息。可以查询当前数据库中的权限信息。语法格式为：语法格式为：sp_helprotect name=object_statement, username=security_accoun