AnQing Teachers College Department of Computer & Information

1. AnQing Teachers College Department of Computer & Information 数据库原理与应用 Principle and Application of Database system 安庆师范学院计算机与信息学院 Principle and Application of Database System

2. 第16章 SQL Server的安全管理 Principle and Application of Database System

3. 数据的安全性管理是数据库管理系统应实现的重要功能之一。SQL Server数据库采用了如下的安全管理机制： • 对用户登录进行身份认证。当用户登录到数据库系统时，系统对该用户的帐号和口令进行认证，包括确认用户账号是否有效以及能否访问数据库系统。 • 对用户进行的操作进行权限控制。当用户登录到数据库后，只能对数据库中的数据在允许的权限内进行操作。 Principle and Application of Database System

4. 一个用户要对某一数据库进行操作，必须满足以下3个条件：一个用户要对某一数据库进行操作，必须满足以下3个条件： • 登录SQL Server服务器时必须通过身份验证 • 必须是该数据库的用户，或者是某一数据库角色的成员 • 必须有执行该操作的权限 Principle and Application of Database System

5. 16.1 SQL Server 2000的身份认证模式 1. Windows NT认证模式 用户登录Windows NT时进行身份认证，登录SQL Server时不再进行身份验证。 对于Windows NT认证模式登录的几点重要说明： (1) 必须将NT网络账号加入到SQL Server中，才能采用NT网络账号登录SQL Server。 (2) 如果使用NT网络账号登录到另一个网络的SQL Server，必须在NT网络中设置彼此的托管权限。 2. SQL Server认证模式 在SQL Server认证模式下，SQL Server服务器要对登录的用户进行身份验证 对于Windows 9x系列的操作系统只能使用SQL Server认证模式，而当SQL Server 在Windows NT或Windows2000上运行时，系统管理员设定登录认证模式的类型可为Windows NT认证模式和混合模式。 Principle and Application of Database System

6. 16.2.1 Windows NT认证模式登录账号的建立与删除 1. Windows NT认证模式登录账号的建立 1) 通过企业管理器建立Windows NT认证模式的登录账号 Windows 2000本地计算机管理界面 Windows 2000本地计算机创建新用户的界面 Principle and Application of Database System

7. 16.2.1 Windows NT认证模式登录账号的建立与删除 在企业管理器中选择“登录”图标右击 SQL Server信任连接设置窗口 Principle and Application of Database System

8. 16.2.1 Windows NT认证模式登录账号的建立与删除 2) 通过调用系统存储过程建立Windows NT认证模式的登录账号 创建Windows NT、Windows 2000的用户或组后，使用系统存储过程sp_grantlogin也可将一个Windows NT或Windows2000的用户或组的登录账号添加到SQL Server中，以便通过Windows 身份验证连接到SQL Server。 语法格式： sp_grantlogin [@loginame =] 'login' 参数含义： @loginame =：原样输入的常量字符串 注意： (1) 不能在用户定义的事务内执行 sp_grantlogin； (2) 仅 sysadmin 或 securityadmin 固定服务器角色的成员可以执行 sp_grantlogin。 Principle and Application of Database System

9. EXEC sp_grantlogin ‘WYATT\wy’ Principle and Application of Database System

10. 16.2.1 Windows NT认证模式登录账号的建立与删除 2. Windows NT认证模式登录账号的删除 1) 通过企业管理器删除Windows NT认证模式的登录账号 步骤如下： (1) 以系统管理员身份进入企业管理器，并展开目录树； (2) 在目录树的“登录” 节点下，选中待删除的“账号”节点， 2) 通过调用系统存储过程删除Windows NT认证模式的登录账号 通过执行系统存储过程sp_revokelogin可删除Windows NT 用户或组登录SQL Server的账号。 Principle and Application of Database System

11. 16.2.2 混合认证模式下SQL Server登录账号的建立与删除 SQL Server服务器属性配置窗口 • (1) 在企业管理器中，选择要登录的SQL Server服务器图标右击，出现一快捷菜单，选择菜单项“属性”，出现如图的SQL Server服务器属性配置窗口。 • (2) 选择“安全性”选项卡，如图所示，选择身份验证方式为“SQL Server与Windows”，选择“确定”按钮。 SQL Server身份认证方式配置窗口 Principle and Application of Database System

12. 16.2.2 混合认证模式下SQL Server登录账号的建立与删除 1. 通过企业管理器创建SQL Server登录账号 SQL Server登录账号新建窗口 (1) 在企业管理器中选择“登录”图标右击，出现快捷菜单，选择“新建登录”菜单项，进入如 下图的界； (2) 输入账号名、密码，选择“SQL Server身份验证”方式，点击“确定”按钮。 Principle and Application of Database System

13. 16.2.2 混合认证模式下SQL Server登录账号的建立与删除 2. 利用系统存储过程创建SQL Server登录账号 语法格式： sp_addlogin [ @loginame = ] 'login' [ , [ @passwd = ] 'password' ] [ , [ @defdb = ] 'database' ] [ , [ @deflanguage = ] 'language' ] [ , [ @sid = ] sid ] [ , [ @encryptopt = ] 'encryption_option' ] Principle and Application of Database System

14. 16.2.2 混合认证模式下SQL Server登录账号的建立与删除 3. SQL Server登录账号的删除 利用sp_droplogin系统存储过程可删除SQL Server登录账号。 语法格式： sp_droplogin [ @loginame = ] 'login' 参数含义： 'login'：将被删除的登录账号名。 返回值：0（成功）或 1（失败）。 说明 ： (1) 若要删除一个数据库现有用户的登录账号，必须首先 使用 sp_dropuser 删除该用户。 (2) 不能删除系统管理员 (sa) 的登录账号。 (3) 不能在用户定义的事务内执行 sp_droplogin。 (4) 只有sysadmin 和 securityadmin 固定服务器角色的成员才能执行 sp_droplogin。 Principle and Application of Database System

15. 16.3 服务器角色与数据库角色 • 在SQL Server中，通过角色可将用户分为不同的类，对相同类用户（相同角色的成员）进行统一管理，赋予相同的操作权限，SQL Server给用户提供了预定义的服务器角色（固定服务器角色）和数据库角色（固定数据库角色），固定服务器角色和固定数据库角色都是SQL Server内置的，不能进行添加、修改和删除。用户可根据需要，创建自已的数据库角色。 Principle and Application of Database System

16. 16.3.1 固定服务器角色 • 服务器角色独立于各个数据库，如果在SQL Server中创建一个登录账号后，要赋予该登录者具有管理服务器的权限，此时可设置该登录账号为服务器角色的成员。 Principle and Application of Database System

17. SQL Server提共了如下固定服务器角色： • Sysadmin:系统管理员，可对SQL Server服务器进行所有的管理工作，为最高管理角色。 • Securityadmin:安全管理员，可以管理登录和CREATE DATABASE权限，还可以读取错误码日志和更改密码。 • Serveradmin:服务器管理员，具有设置及关闭服务器的权限。 • Setupadmin:设置管理员，添加和删除链接服务器，并执行某些系统存储过程。 • Processadmin:进程管理员，可以管理运行在SQL Server中的进程。 • Diskadmin:可以管理磁盘文件。 • Dbcreator:数据库创建者，可以创建、更改和删除数据库。 • Bulkadmin:可执行bulk insert语句。 Principle and Application of Database System

18. 16.3.1 固定服务器角色 1. 通过企业管理器添加服务器角色成员 (1) 以系统管理员身份登录到SQL Server服务器，在登录图标对应的列表项中，选择登录账号“DLGC-YPOOLOLRW4\wy”的项目双击； (2) 选择“服务器角色” 选项卡，选项卡中列出了SQL Server所有的固定服务器角色，将“System administrators”服务器角色前的复选框选中。 SQL Server服务器角色设置窗口 Principle and Application of Database System

19. 16.3.1 固定服务器角色 2. 利用系统存储过程添加固定服务器角色成员 利用系统存储过程sp_addsrvrolemember可将一登录账号添加到某一固定服务器角色中，使其成为固定服务器角色的成员。 说明： (1) 将登录账号添加到固定服务器角色时，该登录就会得到与此固定服务器角色相关的权限。 (2) 不能更改 sa角色成员资格。 (3) 不能在用户定义的事务内执行 sp_addsrvrolemember 存储过程。 (4)sysadmin 固定服务器的成员可以将登录账号添加到任何固定服务器角色，其他固定服务器角色的成员可以执行sp_addsrvrolemember 将登录账号添加到同一个固定服务器角色。 Principle and Application of Database System

20. 16.3.1 固定服务器角色 3. 利用系统存储过程删除固定服务器角色成员 利用sp_dropsrvrolemember系统存储过程可从固定服务器角色中删除SQL Server 登录账号或Windows NT用户或组。 说明： (1) 不能删除 sa 登录账号。 (2) 不能在用户定义的事务内执行 sp_dropsrvrolemember。 (3) sysadmin固定服务器角色的成员执行sp_dropsrvrolemember，可删除任意固定服务器角色中的登录账号，其他固定服务器角色的成员只可以删除相同固定服务器角色中的其他成员。 Principle and Application of Database System

21. 16.3.2 固定数据库角色 (1) db_owner：数据库所有者，可执行数据库的所有管理操作。 1. 固定数据库角色 (2) db_accessadmin：数据库访问权限管理者，具有添加、删除数据库使用者、数据库角色和组的权限。 (3) db_securityadmin：数据库安全管理员，可管理数据库中的权限，如设置数据库表的插入、删除、修改和查询等存取权限。 (4) db_ddladmin：数据库DDL管理员，可增加、修改或删除数据库中的对象。 (5) db_backupoperator：数据库备份操作员，具有执行数据库备份的权限。 (6) db_datareader：数据库数据读取者。 (7) db_datawriter：数据库数据写入者，具有对表进行插入、删除和修改的权限。 (16) db_denydatareader：数据库拒绝数据读取者，不能读取数据库中任何表的内容 (9) db_denydatawriter：数据库拒绝数据写入者，不能对任何表进行插入、删除和修改操作。 (10) public：是一个特殊的数据库角色，每个数据库用户都是public 角色的成员，因此，不能将用户、组或角色指派为public角色的成员，也不能删除public角色的成员。 Principle and Application of Database System

22. 16.3.2 固定数据库角色 2. 数据库用户的操作权限 (1) 在当前数据库中创建数据库对象及进行数据库备份的权限，主要有： 创建表、视图、存储过程、规则、缺省值对象、函数的权限及备份数据库、日志文件的权限。 (2) 用户对数据库表的操作权限及执行存储过程的权限，主要有： SELECT：对表或视图执行 SELECT 语句的权限； INSERT：对表或视图执行 INSERT 语句的权限； UPDATE：对表或视图执行 UPDATE 语句的权限； DELETE：对表或视图只 DELETE 语句的权限； EXECUTE：执行存储过程的权限。 (3) 用户对数据库中指定表字段的操作权限，主要有： SELECT：对表字段进行查询操作的权限； UPDATE：对表字段进行更新操作的权限。 Principle and Application of Database System

23. 16.3.3 用户自定义数据库角色 1. 通过企业管理器创建数据库角色 新建数据库角色的属性界面 (1) 创建数据库角色：以系统管理员身份登录SQL Server，并进入企业管理器，选中目录树school数据库结点的“角色”图标右击，出现一快捷菜单，选择“新建数据库角色”，进入如图的界面，输入角色名，选择确定按钮。 Principle and Application of Database System

24. 16.3.3 用户自定义数据库角色 新建数据库用户的属性界面 (2) 创建数据库用户并加入数据库角色。即在某一数据库中为SQL Server服务器的登录账号或Windows NT的登录账号创建一数据库用户账号，将数据库用户加入该数据库中的某一角色，即：使数据库用户成为某一角色的成员。 Principle and Application of Database System

25. 16.3.3 用户自定义数据库角色 设置创建数据库对象权限的窗口 (3) 给数据库角色赋予创建数据库对象的权限：在企业管理器目录树中，选择school数据库结点右击，出现一快捷菜单，选择菜单项“属性”，进入如图所示的界面，选择“权限”选项卡，选中允许数据库角色或数据库用户执行的权限。 (4)给数据库角色赋予表操作权限：在企业管理器的目录树中，选择school数据库结点下角色图标的项目“ROLE”双击，出现如图所示的界面。 数据库角色的属性窗口 Principle and Application of Database System

26. 16.3.3 用户自定义数据库角色 数据库表操作权限设置窗口 表的列操作权限设置窗口 Principle and Application of Database System

27. 16.3.3 用户自定义数据库角色 2. 通过SQL命令创建数据库角色 1) 定义数据库角色 语法格式： sp_addrole [ @rolename = ] 'role'[ , [ @ownername = ] 'owner' ] 说明： (1) 角色名可以包括字母、符号及数字。但是不能含有反斜线 (\)。 (2) 不能在用户定义的事务内使用 sp_addrole。 (3) 只有 sysadmin 固定服务器角色及 db_securityadmin 和 db_owner 固定数据库角色的成员才能执行 sp_addrole。 Principle and Application of Database System

28. 16.3.3 用户自定义数据库角色 2) 将一个登录账号添加为某个数据库的用户 利用系统存储过程sp_grantdbaccess可将一个登录账号添加为某个数据库的用户。 语法格式： sp_grantdbaccess [@loginame =] 'login'[,[@name_in_db =] 'name_in_db'] 说明： (1) 数据库用户名可含有字母、符号和数字。但不能包含反斜线，不能为 NULL，也不能为空字符串 ('')。 (2) sp_grantdbaccess 仅可以在当前数据库中添加用户（账户），若要从数据库中删除账户，使用 sp_revokedbaccess。 (3) 如果当前数据库中没有 guest账户，而且 login 为 guest，则可将guest添加为当前数据库的账户。 (4) sa 登录账号不能添加到数据库中。 (5) 不能从用户定义的事务中执行 sp_grantdbaccess。 (6) 只有 sysadmin 固定服务器角色、db_accessadmin 和 db_owner 固定数据库角色的成员才能执行 sp_grantdbaccess； (7) 存储过程sp_adduser的功能与sp_grantdbaccess的功能相同。 Principle and Application of Database System

29. 16.3.3 用户自定义数据库角色 3) 给数据库角色添加成员 利用系统存储过程sp_addrolemember可将当前数据库的用户或角色添加到当前数据库的某个角色中，使其成为该角色的成员。 语法格式： sp_addrolemember [ @rolename = ] 'role' ,[ @membername = ] security_account' 说明： (1) 当使用 sp_addrolemember 将用户账号添加到角色时，新成员将继承所有应用到角色的权限。 (2) 不能将固定数据库或固定服务器角色，或者 dbo 添加到其他角色。例如，不能将 db_owner 固定数据库角色添加成为用户定义的角色 YourRole 的成员。 (3) 在用户定义的事务中不能使用 sp_addrolemember。 (4) 只有 sysadmin 固定服务器角色和 db_owner 固定数据库角色中的成员可以执行 sp_addrolemember，将用户账号添加到固定数据库角色。 (5) db_securityadmin 固定数据库角色的成员可以将用户添加到任何用户定义的角色。 Principle and Application of Database System

30. 16.3.3 用户自定义数据库角色 4) 数据库用户、角色操作权限的授予、拒绝和收回 (1) 数据库用户、角色操作权限的授予 利用GRANT语句可以给数据库用户或数据库角色赋予执行T-SQL语句的权限及对数据库对象进行操作的权限。 语法格式： GRANT { ALL | statement [ ,...n ] } TO security_account [ ,...n ] 功能：授予执行T-SQL语句的权限。 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_account [ ,...n ] [ WITH GRANT OPTION ] [ AS { group | role } ] Principle and Application of Database System

31. 16.3.3 用户自定义数据库角色 3. 数据库用户、角色的删除 1) 删除数据库用户 在企业管理器中删除数据库用户很简单，直接在目录树的某一数据库结点下，选中须删除的用户项目，按“Del”键即可。 语法格式： sp_revokedbaccess [ @name_in_db = ] 'name' 说明： (1) 在用户定义事务内部不能执行 sp_revokedbaccess。 (2) 只有sysadmin固定服务器角色成员及 db_accessadmin 和 db_owner 固定数据库角色成员才能执行sp_revokedbaccess。 Principle and Application of Database System

32. 16.3.3 用户自定义数据库角色 2) 删除数据库角色 要删除用户自定义的数据库角色，首先应删除该角色的所有成员，下面介绍删除数据库角色成员及删除数据库角色的系统存储过程。 语法格式： sp_droprolemember [ @rolename = ] 'role' ,[ @membername = ] 'security_account' 说明： (1) 不能从用户定义的事务内执行 sp_droprolemember。 (2) 只有 sysadmin 固定服务器角色、db_owner 和 db_securityadmin 固定数据库角色的成员才能执行 sp_droprolemember。 Principle and Application of Database System

33. 在数据库school中，用户zhang拥有对表student的所有操作，zhang将表student的SELECT 权限授予role角色(指定WITH GRANT OPTION），用户li是role的成员，他要将表student上的SELECT权限授予用户huang，huang不是role的成员。 Principle and Application of Database System

34. /*用户zhang*/ • GRANT SELECT ON student TO role WITH GRANT OPTION • /*用户li*/ • GRANT SELECT ON XS TO huang AS role Principle and Application of Database System

35. exec sp_addlogin 'zhang' • exec sp_addlogin 'huang' • exec sp_addlogin 'li' • exec sp_addrole 'role' • exec sp_grantdbaccess 'li' • exec sp_addrolemember 'role','li' • exec sp_adduser 'zhang' • exec sp_adduser 'huang' • grant all on student to zhang with grant option • grant all to zhang Principle and Application of Database System

36. grant select on student to role with grant option • grant select on student to huang as role Principle and Application of Database System