1、第9章 系统安全管理,(1) 对用户登录进行身份认证(Authentication)。当用户登录到数据库系统时，系统对该用户的账号和口令进行认证,(2) 对用户进行的操作进行权限控制。当用户登录到数据库后，只能对数据库中的数据在允许的权限内进行操作。,个用户如果要对某一数据库进行操作，必须满足以下三个条件：（1）登录SQL Server服务器时必须通过身份验证； （2）必须是该数据库的用户，或者是某一数据库角色的成员； （3）必须有执行该操作的权限。,9.1 SQL Server 2000的身份认证模式,SQL Server2000有两种身份认证模式：Windows NT认证模式和SQL Se
2、rver认证模式,9.1 SQL Server 2000的身份认证模式,1Windows NT认证模式,(1) 必须将NT网络账号加入到SQL Server中，才能采用NT网络账号登录SQL Server。(2) 如果使用NT网络账号登录到另一个网络的SQL Server，必须在NT网络中设置彼此的托管权限。,2SQL Server认证模式,在SQL Server认证模式下，SQL Server服务器要对登录的用户进行身份验证。对于Windows 9x系列的操作系统只能使用SQL Server认证模式，而当SQL Server 在Windows NT或Windows2000/2003上运行时，
3、系统管理员设定登录认证模式的类型可为Windows NT认证模式和混合模式。,9.2.1 Windows NT认证模式登录账号的建立与取消,1. 通过企业管理器建立Windows NT认证模式的登录账号 对于Windows NT或Windows 2000/2003操作系统，安装本地SQL Server 2000的过程中，允许选择认证模式。,9.2.1 Windows NT认证模式登录账号的建立与取消,第步 创建Windows 2003的用户。以管理员身份，登录到Windows 2003，选择“开始”“所有程序”“管理工具”“计算机管理”，进入如图9.2所示的界面，选择“本地用户和组”。选择“用
4、户”图标右击，出现一快捷菜单，选择菜单项“新用户”，进入如图9.3所示的界面，输入用户名、密码，选择“创建”按钮，然后选择“关闭”按钮。,9.2.1 Windows NT认证模式登录账号的建立与取消,第2步 将NT网络账号加入到SQL Server中：以管理员身份登录到SQL Server，进入企业管理器，选择如图9.4中的“登录”图标右击，出现快捷菜单，选择“新建登录”，出现如图9.5所示的界面，点击“常规”选项卡的“浏览”按钮，可选择用户名或用户组添加到SQL Server登录用户列表中。,9.2.1 Windows NT认证模式登录账号的建立与取消,2通过SQL命令建立Windows N
5、T认证模式的登录账号,语法格式： sp_grantlogin loginame = login 参数含义： loginame =：原样输入的常量字符串。 login：指要添加的 Windows NT或Windows2000 用户或组的名称。,【例9.1】将 Windows NT 域Nanjing中的chengfang用户加入到 SQL Server中。EXEC sp_grantlogin Nanjingchengfang 或EXEC sp_grantlogin Nanjingchengfang,9.2.1 Windows NT认证模式登录账号的建立与取消,3. Windows NT认证模式登录
6、账号的取消通过执行系统存储过程sp_revokelogin可取消Windows NT 用户或组登录SQL Server的账号。语法格式： sp_revokelogin loginame = login 参数含义： loginame =:常量字符串； login：Windows NT 用户或组的名称，为 域用户 形式 返回值：0（成功）或 1（失败）。,【例9.2】取消 Windows NT 用户 Nanjingchengfang登录SQL Server的账号。 EXEC sp_revokelogin Nanjingchengfang 或 EXEC sp_revokelogin Nanjingc
7、hengfang,9.2.2 混合认证模式下SQL Server登录账号的建立与删除,第1步 在企业管理器中，选择要登录的SQL Server服务器图标右击，出现一快捷菜单，选择菜单项“属性”，出现如图9.6的SQL Server服务器属性配置窗口。,9.2.2 混合认证模式下SQL Server登录账号的建立与删除,第2步 选择“安全性”选项卡，如图所示，选择身份验证为“SQL Server与Windows”，选择“确定”按钮。,9.2.2 混合认证模式下SQL Server登录账号的建立与删除,设置混合认证方式后，如何创建SQL Server的登录账号呢？可在企业管理器中创建，利用系统存储
8、过程创建。,9.2.2 混合认证模式下SQL Server登录账号的建立与删除,1通过企业管理器创建SQL Server登录账号 步骤如下： 第1步 在企业管理器中选择“登录”图标右击，出现如图9.4的界面，选择“新建登录”菜单项，进入如图9.8的界面； 第2步 输入账号名、密码，选择“SQL Server身份验证”方式，选择“确定”按钮。,2. 利用系统存储过程创建SQL Server登录账号 语法格式： sp_addlogin loginame = login , passwd = password , defdb = database , deflanguage = language ,
9、 sid = sid , encryptopt = encryption_option ,9.2.2 混合认证模式下SQL Server登录账号的建立与删除,【例9.4】创建登录 ID 为wang，密码为dongdong，并指定默认数据库为XSCJ。为用户 cheng 创建一个 SQL Server 登录名，密码为chocolate，默认数据库为pubs，默认语言为us_english，SID为：0x0123456789ABCDEF。 EXEC sp_addlogin wang, dongdong, XSCJ EXEC sp_addlogin cheng, chocolate, pubs, u
10、s_english, 0x0123456789ABCDEF 3. SQL Server登录账号的删除 利用sp_droplogin系统存储过程可删除SQL Server登录账号。 语法格式： sp_droplogin loginame = login 参数含义： login：将被删除的登录账号名。 返回值：0（成功）或 1（失败）。,9.3.1 固定服务器角色,SQL Server提供了以下固定服务器角色： sysadmin：系统管理员，可对SQL Server服务器进行所有的管理工作，为最高管理角色。 securityadmin：安全管理员，可以管理登录和 CREATE DATABASE 权
11、限，还可以读取错误日志和更改密码。 serveradmin：服务器管理员，具有对服务器进行设置及关闭服务器的权限。 setupadmin：设置管理员，添加和删除链接服务器，并执行某些系统存储过程（如 sp_serveroption）。 processadmin：进程管理员，可以管理磁盘文件。 dbcreator：数据库创建者，可以创建、更改和删除数据库。 bulkadmin：可执行BULK INSERT语句，但是这些成员对要插入数据的表必须有INSERT权限。 BULK INSERT语句的功能是以用户指定的格式复制一个数据文件至数据库表或视图。,9.3.1 固定服务器角色,通过企业管理器添加服
12、务器角色成员 第1步 以系统管理员身份登录到SQL Server服务器，在登录图标对应的列表项中，选择登录账号“DLGC-YPOOLOLRW4LIU”的项目双击； 第2步 选择“服务器角色” 选项卡，如图所示，选项卡中列出了SQL Server所有的固定服务器角色，将“System administrators”服务器角色前的复选框选中。,9.3.1 固定服务器角色,2. 利用系统存储过程添加固定服务器角色成员 语法格式： sp_addsrvrolemember loginame = login， rolename = role 参数含义： login：添加到固定服务器角色role的登录账号名
13、，login 可以是SQL Server登录或 Windows NT 用户账号，对于 Windows NT 登录账号，如果还没有授予 SQL Server 访问权限，将自动对其授予访问权限。固定服务器角色名role必须为sysadmin，securityadmin，serveradmin，setupadmin，processadmin，diskadmin，dbcreator，bulkadmin之一。 返回值：0（成功）或 1（失败）。,9.3.1 固定服务器角色,3. 利用系统存储过程删除固定服务器角色成员 利用sp_dropsrvrolemember系统存储过程可从固定服务器角色中删除SQL
14、 Server 登录账号或Windows NT用户或组。 语法格式： sp_dropsrvrolemember loginame = login , rolename = role 参数含义： login：将要从固定服务器角色删除的登录账号名。 role：服务器角色名，默认值为NULL，role 必须是有效的固定服务器角色名。 返回值：0（成功）或 1（失败） 【例9.7】从 sysadmin 固定服务器角色中删除登录 zhang。 EXEC sp_dropsrvrolemember zhang, sysadmin也可在企业管理器中删除固定服务器角色成员，请读者试一试。,9.3.2 固定数据库
15、角色,1. 固定数据库角色,（1）db_owner：数据库所有者，可执行数据库的所有管理操作,（2）db_accessadmin：数据库访问权限管理者，具有添加、删除数 据库使用者、数据库角色和组的权限。,（3）db_securityadmin：数据库安全管理员，管理数据库中的权限，如设置数据库表的增、删、修改和查询等存取权限。 （4）db_ddladmin：数据库DDL管理员，增加、修改或删除数据库中对象。 （5）db_backupoperator：数据库备份操作员，执行数据库备份的权限。 （6）db_datareader：数据库数据读取者。 （7）db_datawriter：数据库数据写入
16、者，对表进行增、删修改的权限。 （8）db_denydatareader：数据库拒绝数据读取者，不能读取数据库中任何表的内容 （9）db_denydatawriter：数据库拒绝数据写入者，不能对任何表进行增、删修改操作。 （10）public：是一个特殊的数据库角色，每个数据库用户都是public 角色的成员 。,9.3.2 固定数据库角色,2. 数据库用户的操作权限,（1）在当前数据库中创建数据库对象及进行数据库备份的权限，主要有：创建表、视图、存储过程、规则、缺省值对象、函数的权限及备份数据库、日志文件的权限。（2）用户对数据库表的操作权限及执行存储过程的权限，主要有：SELECT：对表
17、或视图执行 SELECT 语句的权限；INSERT：对表或视图执行 INSERT 语句的权限；UPDATE：对表或视图执行 UPDATE 语句的权限；DELETE：对表或视图只 DELETE 语句的权限；REFERENCES：用户对表的主键和唯一索引字段生成外码引用的权限；EXECUTE：执行存储过程的权限。(3) 用户对数据库中指定表字段的操作权限，主要有：SELECT：对表字段进行查询操作的权限；UPDATE：对表字段进行更新操作的权限。,9.3.3 用户自定义数据库角色,1通过企业管理器创建数据库角色,第步 创建数据库角色：以系统管理员身份登录SQL Server，并进入企业管理器，选中
18、目录树XSCJ数据库结点的“角色”图标右击，出现如图所示,9.3.3 用户自定义数据库角色,快捷菜单，选择“新建数据库角色”，进入如图的界面，输入角色名，选择确定按钮。,第2步 创建数据库用户并加入数据库角色。,9.3.3 用户自定义数据库角色,第3步 给数据库角色赋予创建数据库对象的权限：在企业管理器目录树中，选择XSCJ数据库结点右击，出现一快捷菜单，选择菜单项“属性”，进入如图所示的界面，选择“权限”选项卡，根据需要，选中允许数据库角色或数据库用户执行的权限。,9.3.3 用户自定义数据库角色,第4步 给数据库角色赋予表操作权限：在企业管理器的目录树中，选择XSCJ数据库结点下角色图标的
19、项目“ROLE1”双击，出现如图所示的界面，选择“权限”按钮，进入如图所示的界面，根据允许的操作可设置相应的权限。,9.3.3 用户自定义数据库角色,图数据库表操作权限设置窗口 图 表的列操作权限设置窗口,9.3.3 用户自定义数据库角色,2通过SQL命令创建数据库角色1）定义数据库角色语法格式： sp_addrole rolename = role , ownername = owner 参数含义： role：新的数据库角色名，role 必须是有效标识符，并且不能已经存在于当前数据库中。 owner：新角色的所有者，默认值为 dbo。owner 必须是当前数据库中的某个用户或角色。当指定Wi
20、ndows NT 用户时，应指定该 Windows NT 用户在数据库中可被识别的名称（用 sp_grantdbaccess 添加）。 返回值：0（成功）或 1（失败）。,说明： (1) 角色名可以包括字母、符号及数字。但是不能含有反斜线 ()。 (2) 不能在用户定义的事务内使用 sp_addrole。 (3) 只有 sysadmin 固定服务器角色及 db_securityadmin 和 db_owner 固定数据库角色的成员才能执行 sp_addrole。,9.3.3 用户自定义数据库角色,【例9.8】如下示例在当前数据库中创建名为ROLE1 的新角色。 USE XSCJ EXEC sp
21、_addrole ROLE1,2）将一个登录账号添加为某个数据库的用户 利用系统存储过程sp_grantdbaccess可将一个登录账号添加为某个数据库的用户。 语法格式： sp_grantdbaccess loginame = login ,name_in_db = name_in_db 参数含义： login：SQL Server的登录账号，Windows NT 组和用户必须用 Windows NT 域名限定，格式为“域用户”，例如 Nanjingzhang。 name_in_db ：数据库用户名，如果没有指定，则使用登录账号作为数据库用户名。 返回值：0（成功）或 1（失败）。,9.3.
22、3 用户自定义数据库角色,说明： (1) 数据库用户名可含有字母、符号和数字。但不能包含反斜线，不能为 NULL，也不能为空字符串 ()。 (2) sp_grantdbaccess 仅可以在当前数据库中添加用户（账户），若要从数据库中删除账户，使用 sp_revokedbaccess。 (3) 如果当前数据库中没有 guest账户，而且 login 为 guest，则可将guest添加为当前数据库的账户。 (4) sa 登录不能添加到数据库中。 (5) 不能从用户定义的事务中执行 sp_grantdbaccess。 (6) 只有 sysadmin 固定服务器角色、db_accessadmin
23、和 db_owner 固定数据库角色的成员才能执行sp_grantdbaccess； (7) 存储过程sp_adduser的功能与sp_grantdbaccess的功能相同。,9.3.3 用户自定义数据库角色,【例9.9】将Windows NT 登录账号 “DLGC-YPOOLOLRW4liu”添加为当前数据库的账户，并取名为 Dongdong。将SQL Server的登录账号“WANG” 添加为当前数据库的账户，并取名为WANG。 USE XSCJ EXEC sp_grantdbaccess DLGC-YPOOLOLRW4liu, Dongdong GO EXEC sp_grantdbacc
24、ess WANG GO,3）给数据库角色添加成员 语法格式： sp_addrolemember rolename = role , membername = security_account 参数含义： role：当前数据库中角色名。 security_account：添加到角色的数据库用户账号，可以是所有有效的SQL Server用户、当前数据库角色。 返回值：0（成功）或 1（失败）,9.3.3 用户自定义数据库角色,【例9.10】将 Windows NT 用户 DLGC-YPOOLOLRW4liu 添加为XSCJ数据库的用户，用户名为dong，然后再将 dong 添加到XSCJ数据库的
25、ROLE1角色中。 USE XSCJ GO EXEC sp_grantdbaccess DLGC-YPOOLOLRW4liu, dong /*dong为数据库用户名*/ GO EXEC sp_addrolemember ROLE1, dong,【例9.11】将SQL Server登录账号“WANG”添加到当前数据库，其用户名为“WANG”，然后再将“WANG”添加到XSCJ数据库的ROLE1角色中。 USE XSCJ EXEC sp_grantdbaccess WANG, WANG EXEC sp_addrolemember ROLE1,WANG GO,9.3.3 用户自定义数据库角色,4）数
26、据库用户、角色操作权限的授予、拒绝和收回,授予执行T-SQL语句的权限。 语法格式： GRANT ALL | statement ,.n TO security_account ,.n 授予对数据库对象操作的权限。 语法格式： GRANT ALL PRIVILEGES | permission ,.n ( column ,.n ) ON table | view | ON table | view ( column ,.n ) | ON stored_procedure | extended_procedure | ON user_defined_function TO security_ac
27、count ,.n WITH GRANT OPTION AS group | role ,9.3.3 用户自定义数据库角色,【例9.12】给用户zhang 和 wang 以及 Windows NT 组 Nanjingliu 授予执行T-SQL语句的权限。 GRANT CREATE DATABASE, CREATE TABLE TO zhang, wang, Nanjingliu /*如果用户账号含有空格、反斜杠（），则要用引号或中括号将安全账号括起来*/ GO 【例9.13】 首先在当前数据库XSCJ中给 public 角色授予 SELECT 权限。然后，将特定的权限授予用户 liu、zhan
28、g 和 dong，使用户有对 XS 表的所有操作权限。 USE XSCJ GO GRANT SELECT ON XS TO public GO GRANT INSERT, UPDATE, DELETE ON XS TO liu,zhang,dong GO 【例9.14】将 CREATE TABLE 权限授予 Accounting 角色的所有成员。 GRANT CREATE TABLE TO Accounting,9.3.3 用户自定义数据库角色,【例9.15】 在数据库XSCJ中，用户 zhang 拥有对表 XS的所有操作权限，zhang 将表 XS的 SELECT 权限授予 Accounti
29、ng 角色（指定 WITH GRANT OPTION 子句）。用户 li 是 Accounting 的成员，要将表 XS上的 SELECT 权限授予用户 huang，huang 不是 Accounting 的成员。 /*用户zhang*/ GRANT SELECT ON XS TO Accounting WITH GRANT OPTION /*用户 li*/ GRANT SELECT ON XS TO huang AS Accounting /*由于 li是Accounting 角色的成员，因此必须用 AS 子句对huang授予权限。*/【例9.16】 在当前数据库XSCJ中给public角色
30、赋予对表XS中学号、姓名字段的SELECT权限。 USE XSCJ GRANT SELECT (学号,姓名) ON XS TO public GO,9.3.3 用户自定义数据库角色,语法格式： DENY ALL | statement ,.n TO security_account ,.n 拒绝对象权限。 语法格式： DENY ALL PRIVILEGES | permission ,.n ( column ,.n ) ON table | view | ON table | view ( column ,.n ) | ON stored_procedure | extended_proced
31、ure | ON user_defined_function TO security_account ,.n CASCADE ,9.3.3 用户自定义数据库角色,【例9.17】对多个用户不允许使用 CREATE DATABASE 和 CREATE TABLE 语句。 DENY CREATE DATABASE, CREATE TABLE TO zhang, wang, Nanjingliu GO 【例9.18】 首先给 public 角色授予 对于表XS的SELECT 权限，然后，拒绝用户 zhang, wang, Nanjingliu 的特定权限，这样，这些用户就没有对XS表的操作权限了。 U
32、SE XSCJ GO GRANT SELECT ON XS TO public GO DENY SELECT, INSERT, UPDATE, DELETE ON XS TO zhang, wang, Nanjingliu GO 【例9.19】对所有 Accouting 角色成员拒绝 CREATE TABLE 权限。 DENY CREATE TABLE TO Accounting GO,9.3.3 用户自定义数据库角色,语法格式： REVOKE ALL | statement ,.n FROM security_account ,.n 取消以前授予或拒绝的对象权限。 语法格式： REVOKE
33、GRANT OPTION FOR ALL PRIVILEGES | permission ,.n ( column ,.n ) ON table | view | ON table | view ( column ,.n ) | ON stored_procedure | extended_procedure | ON user_defined_function TO | FROM security_account ,.n CASCADE AS group | role ,【例9.20】取消已授予用户 zhang和 Nanjingliu 的 CREATE TABLE 权限。 REVOKE CR
34、EATE TABLE FROM zhang, Nanjingliu GO,9.3.3 用户自定义数据库角色,【例9.21】取消授予多个用户的多个语句权限。 REVOKE CREATE TABLE, CREATE DEFAULT FROM wang, liu GO 【例9.22】取消以前对zhang 授予或拒绝的权限。 REVOKE SELECT ON XS FROM zhang 【例9.23】在数据库XSCJ中，用户 zhang 拥有表 XS的操作权限，zhang 将表 XS的 SELECT 权限授予ROLE 角色。用户 li是 ROLE 的成员，他要将已授予用户huang的在表XS上的SEL
35、ECT权限取消，huang 不是 ROLE 的成员。 /* User zhang */ GRANT SELECT ON XS TO ROLE WITH GRANT OPTION /* User li*/ GRANT SELECT ON XS TO huang AS ROLE /* User li*/ REVOKE SELECT ON XS TO huang AS ROLE /*用户li是角色ROLE的成员，通过用户li取消权限时，必须用AS指定其所属的角色*/,9.3.3 用户自定义数据库角色,3数据库用户、角色的删除1) 删除数据库用户 在企业管理器中删除数据库用户很简单，直接在目录树的某一
36、数据库结点下，选中须删除的用户项目，按“Del”键即可。 语法格式： sp_revokedbaccess name_in_db = name 参数含义： name_in_db =：常量字符串； name：指要删除的数据库帐户名。 返回值：0（成功）或 1（失败）,说明： (1) 在用户定义事务内部不能执行 sp_revokedbaccess。 (2) 只有sysadmin固定服务器角色成员及 db_accessadmin 和 db_owner 固定数据库角色成员才能执行sp_revokedbaccess。,9.3.3 用户自定义数据库角色,【例9.24】从当前数据库中删除用户帐户DLGC-YP
37、OOL0LRW4liu。 USE XSCJ EXEC sp_revokedbaccess DLGC-YPOOL0LRW4liu GO 2）删除数据库角色 要删除用户自定义的数据库角色，首先应删除该角色的所有成员，下面介绍删除数据库角色成员及删除数据库角色的系统存储过程。,语法格式： sp_droprolemember rolename = role , membername = security_account 参数含义： role：当前数据库的一个角色名。 security_account：指将要从role所指角色中删除的用户账号，security_account 可以是当前数据库用户或另一
38、个角色。 返回值：0（成功）或 1（失败）,9.3.3 用户自定义数据库角色,【例9.25】从角色ROLE中删除用户 zhang。 USE XSCJ EXEC sp_droprolemember ROLE, zhang GO利用系统存储过程删除数据库角色 语法格式： sp_droprole rolename = role 参数含义： rolename =：常量字符串； role：当前数据库中用户自定义的角色名。 返回值：0（成功）或 1（失败）,说明： (1) 在删除角色之前，首先必须从该角色删除其所有的成员。 (2) 不能删除固定角色及 public 角色。 (3) 如果角色拥有对象，那么就不能将其删除。 (4) 不能在用户定义的事务内执行 sp_droprole。 (5) 只有 sysadmin 固定服务器角色、db_owner 和 db_securityadmin 固定数据库角色成员才能执行 sp_droprole。,习 题,1. SQL Server采用哪些措施实现数据库的安全管理？ 2. 如何创建Windows身份认证模式的登录账号？ 3. 如何创建SQL Server身份认证模式的登录账号？ 4.服务器角色分为哪几类，每类有哪些权限？ 5. 固定数据库角色分为哪几类？每类有哪些操作权限？ 6. 如何给一个数据库角色、用户赋予操作权限？,
