数据库原理电子教案：第十章-系统安全管理.doc

第10章 系统安全管理
课题：系统安全管理
目的要求：
掌握SQL Server 2005的身份认证模式
掌握SQL Server 2005登录账号的管理用法
掌握SQL Server 2005用户管理用法
掌握SQL Server 2005角色管理用法
重点难点：
用户与账号管理
课外作业布置：
复习本节课内容，完成本章习题
10.1 SQL Server 2005的身份认证模式
用户登录SQL Server 2005时SQL Server会对用户登录进行确认。SQL Server 2005的身份认证模式有两种：
1．Windows NT认证模式
该模式使用Windows操作系统的安全机制验证用户身份，只要用户能够通过Windows NT/2005用户账号验证就可以连接上SQL Server。
2．SQL Server认证模式
该模式下SQL Server提供给用户一个登录SQL Server用户账号和密码，这将保存在SQL Server的内部，而且该记录与任何Windows账号无关。
属性设置：服务器/属性/安全性

10.2 建立和管理用户账号
10.2.1 Windows NT认证模式登录账号的建立与删除
1．通过SQL语句授予Windows认证模式的登录权限使用系统存储过程sp_grantlogin授予Windows 的用户或组登录SQL Server。
语法：
sp_grantlogin [@loginname=]'login'
参数说明:
① sp_grantlogin：系统存储过程名。
② @loginname=：占位符，可以省略。
③ login：被授予登录权限的Windows的用户和组名称。一般是“域\用户名或组”或“计算机名\用户名称”。
【例10-1】允许本地组Users中的所有用户连接SQL Server。
sp_grantlogin 'BUILTIN\Users'
【例10-2】在Windows 中有一个用户为liu，该计算机名称是“soft408”，那么授予该用户登录SQL Server的语句是：
sp_grantlogin ‘soft408\liu'
2．Windows NT认证模式登录权限的取消
语法:
sp_revokelogin [@loginname=]'login'
参数说明：
sp_revokelogin 是系统存储过程，功能是取消Windows 用户和组登录SQL Server的权限，但是该用户和组还是可以登录Windows的。
【例10-3】取消BULTIN\Users组登录SQL
Server的权限。
sp_revokelogin 'BUILTIN\Users'
【例10-4】取消soft408\liu用户登录SQL Server的权限。
sp_revokelogin 'soft408\liu'
10.2.2 SQL Server认证模式登录账号的建立与删除
1．通过SQL语句创建SQL Server认证模式的登录账号
语法:
sp_addlogin [@loginname=] 'login'
[,[@passwd=]'password']
[,[@defdb=]'database']
[,[@deflanguage=]'language']
[,[@sid=]sid]
参数说明：
① login：登录的名称。这个登录名要符合SQL的标识符命名规则。
② password：登录密码。该密码存在系统表中。
③ database：登录后连接的数据库，默认为master。
④ language：用户登录到SQL Server时系统指派的语言。
⑤ sid：安全标识号。数据类型为varbinary(16)，默认值为 NULL。如果 sid 为 NULL，则系统为新登录生成 SID。尽管使用 varbinary 数据类型，非 NULL 的值也必须正好为 16 个字节长度，且不能事先存在。SID 很有用，例如，如果要编写 SQL Server 登录脚本，或要将 SQL Server 登录从一台服务器移动到另一台，并且希望登录在服务器间具有相同的 SID 时。
【例10-5】创建一个SQL Server登录账号wen，没有密码和默认数据库。
sp_addlogin 'wen'
【例10-6】创建一个登录账号weng，登录密码是sql，登录后连接到的数据库是Student。
sp_addlogin 'weng', 'sql', 'Student'
2．SQL Server认证模式的登录账号的删除
语法:
sp_droplogin [@loginname=]'login'
10.2.3 通过企业管理器创建和删除登录账号

2．删除登录账号
操作方法是在要删除的登录账号上单击鼠标右键，在弹出菜单中选择“删除”命令即可。
10.3 服务器角色与数据库角色
角色与用户的区别：
角色（role）是具有相同权限的一组用户，数据库角色在整个数据库集群中是全局的（而不是每个库不同）。
要创建一个角色，使用SQL命令CREATE ROLE，格式是：
CREATE ROLE name
10.3.1 固定服务器角色
1．通过SQL语句添加固定服务器角色成员
语法：
sp_addsrvrolemember
[@loginname=]'login',[@rolename=]'role'
参数说明：
① sp_addsrvrolemember：系统存储过程，功能是将
登录账号添加为某个固定服务器成员。
② login：指明要添加的登录账号。
③ role：服务器角色名。
【例10-7】在前面已经创建了两个登录账号“ZCJ\liu”和“liu”，现在分别添加为某个服务器角色成员：
sp_addsrvrolemember 'ZCJ\liu', 'sysadmin'
注意这个语句只能由sysadmin角色的成员才能做。
sp_addsrvrolemember 'liu', 'dbcreator'
2．通过SQL语句删除固定服务器角色成员
语法:sp_dropsrvrolemember [@loginname=] 'login',[@rolename] 'role'
参数说明:
① sp_dropsrvrolemember是系统存储过程，用于从
固定服务器角色中删除登录账号。
② sysadmin固定服务器角色的成员执行sp_dropsrvrolemember，可删除任何固定服务器角色中的成员。
【例10-8】从dbcreator固定服务器角色中删除登录账号liu。
sp_dropsrvrolemember 'liu', 'dbcreator'
2．通过企业管理器添加和删除固定服务器角色成员

10.3.2 固定数据库角色
1．将登录账号添加为某个数据库的用户和从数据库用户中删除登录账号只是能与SQL Server连接，要对具体的某个数据库进行操作，应该首先使该登录账号成为这个数据库的用户。
语法:
sp_grantdbaccess [@loginname=]'login' [,[@name_in_db]='name_in_db']
sp_revokedbaccess [@name_in_db=]'name_in_db'
参数说明:
① login：登录账号名。
② name_in_db：在数据库中的用户名。如果不指定，则使用登录账号作为数据库用户名。
③ sp_grantdbaccess是系统存储过程，仅可以在当前数据库中添加用户。
④ sp_revokedbaccess是系统存储过程，仅可以在当前数据库中删除用户。
⑤ 只有sysadmin固定服务器角色、db_accessadmin和db_owner固定数据库角色的成员才能执行sp_grantdbaccess和sp_revokedbaccess。
【例10-9】向数据库Student中添加用户ZCJ\liu和liu，且分别指定数据库中的用户名为liu1和liu2。
Use Student
Exec sp_grantdbaccess 'ZCJ\liu', 'liu1'
Exec sp_grantdbaccess 'liu' , 'liu2'
Go
【例10-10】将例10-9中所添加的数据库用户liu1和liu2从数据库Student中删除。
Use Student
Exec sp_revokedbaccess 'liu1'
Exec sp_revokedbaccess 'liu2'
Go
2．给固定数据库角色添加成员和删除成员
登录账号成为数据库的用户后是默认属于public角色成员的，但是该角色所拥有的默认权限是非常少的，需要授予用户操作权限。授予的方法大致有三种：
① 使之成为某个固定数据库角色的成员。
② 单独授予某项操作权限，比如授予某个用户对
Student数据库中所有用户表的SELECT操作权限，但
没有UPDATE、INSERT操作权限。
③ 使之成为用户自定义数据库角色的成员。
语法:
sp_addrolemember [@loginname=]'role', [@membername=]'security_account'
sp_droprolemember[@loginname=]'role', [@membername=]'security_account'
参数说明：
① role：要添加或删除的成员所属的角色名称。
② security_account：要添加或删除的成员名称。
③ sp_addrolemember：用于将某数据库用户添加为某个固定数据库角色的成员，也适用于用户自定义的数据库角色。
④ sp_droprolemember：用于将某个固定数据角色的成员删除，也适用于用户自定义的数据库角色。
固定数据库角色的权限

【例10-11】先使登录账号‘ZCJ\liu’成为Student数据库的用户，且用户名为liu1，然后使之成为了db_owner角色的成员。
Use Student
Exec sp_grantdbaccess 'ZCJ\liu', 'liu1'
Exec sp_addrolemember 'db_owner' , 'liu1'
【例10-12】将成员liu1从db_owner角色中删除。
Use Student
Exec sp_droprolemember 'db_owner' , 'liu1'
3．权限管理
在SQL Server中可授予数据库用户的权限分为三种：对象权限、语句权限和隐含权限。
第一种：对象权限。
对象权限指用于决定用户对数据库对象执行操作的权利。

【例10-13】登录账号ZCJ\liu在数据库Student中映射为用户liu1，先将针对数据库表student_Info的SELECT操作权限授予public角色，那么凡是该角色的成员都会获得该权限。然后将针对表student_Info的INSERT、UPDATE和DELETE操作权限授予liu1。
Use Student
Exec sp_grantdbaccess 'ZCJ\liu', 'liu1'
GRANT SELECT ON student_Info TO PUBLIC
GRANT INSERT,UPDATE,DELETE ON student_Info TO liu1
第二种：语句权限。
语句权限决定用户能否操作数据库和创建数据库对象，比如某用户要在数据库中创建表，那么就应向该用户授予CREATE TABLE语句权限。语句权限不同于对象权限，并不针对某个数据库对象，而仅仅针对语句而言。

【例10-16】登录账号ZCJ\liu在数据库Student中的用户名为liu1，该用户被授予在数据库Student中使用CREATE TABLE的权限。
Use Student
Exec sp_grantdbaccess 'ZCJ\liu', 'liu1'
GRANT CREATE TABLE TO liu1
Go
第三种：隐含权限
隐含权限指系统预定义而不需要授予就可以拥
有的权限，包括固定服务器角色、固定数据库
角色和数据库对象所有者所拥有的权限。
10.3.3 用户自定义数据库角色
语法：
sp_addrole [@rolename=]'role' [,[@ownername=]'owner']
sp_droprole [@rolename=]'role'
参数说明：
① sp_addrole是用于在当前数据库创建用户自定义数据库角色的系统存储过程。role指新角色的名称。owner指新角色的所有者。
② sp_droprole是用于在当前数据库删除用户自定义数据库角色的系统存储过程。
③ 当用sp_addrole创建好数据库角色后，还要使用GRANT和DENY给该角色授予或禁止权限，再使用sp_addrolemember添加数据库成员。当然从用户自定义数据库角色中删除某个成员就应该用sp_droprolemember
10.3.4 用户自定义数据库角色
【例10-18】首先创建了两个登录账号wei和zhao，接着使它们成为数据库Student的用户。在数据库Student中创建一个角色，该角色名为DB_role1，它被授予对表student_Info的student_Name和born_Date两列的SELECT操作权限。最后将wei和zhao添加成为角色DB_role1的成员。
Use Student
Exec sp_addlogin 'wei'
Exec sp_addlogin 'zhao'
Exec sp_grantdbaccess 'wei','w'
Exec sp_grantdbaccess 'zhao','zh'
Exec sp_addrole 'DB_role1'
GRANT SELECT(student_Name,born_Date) ON student_Info TO DB_role1
Exec sp_addrolemember 'DB_role1','w'
Exec sp_addrolemember 'DB_role1','zh'
第32教案 实训16：系统安全管理
实训16：系统安全管理
（一）、实训目的
通过实训掌握SQL Server 2005的身份认证模式
通过实训掌握SQL Server 2005登录账号的管理用法
通过实训掌握SQL Server 2005用户管理用法
通过实训掌握SQL Server 2005角色管理用法
（二）、实训内容
例1:允许本地组Users中的所有用户连接SQL Server。
sp_grantlogin 'BUILTIN\Users'
例2:在Windows 中有一个用户为liu，该计算机名称是“soft408”，那么授予该用户登录SQL Server的语句是：
sp_grantlogin ‘soft408\liu'
例3:取消BULTIN\Users组登录SQL
Server的权限。
sp_revokelogin 'BUILTIN\Users'
例4:取消soft408\liu用户登录SQL Server的权限。
sp_revokelogin 'soft408\liu'
例5：创建一个SQL Server登录账号wen，没有密码和默认数据库。
sp_addlogin 'wen'
例6：创建一个登录账号weng，登录密码是sql，登录后连接到的数据库是Student。
sp_addlogin 'weng', 'sql', 'Student'
例7：用企业管理器完成账号的建立与删除
例8：在前面已经创建了两个登录账号“ZCJ\liu”和“liu”，现在分别添加为某个服务器角色成员：
sp_addsrvrolemember 'ZCJ\liu', 'sysadmin'
例9：从dbcreator固定服务器角色中删除登录账号liu。
sp_dropsrvrolemember 'liu', 'dbcreator'
例10：向数据库Student中添加用户ZCJ\liu和liu，且分别指定数据库中的用户名为liu1和liu2。
Use Student
Exec sp_grantdbaccess 'ZCJ\liu', 'liu1'
Exec sp_grantdbaccess 'liu' , 'liu2'
Go
例11:先使登录账号‘ZCJ\liu’成为Student数据库的用户，且用户名为liu1，然后使之成为了db_owner角色的成员。
Use Student
Exec sp_grantdbaccess 'ZCJ\liu', 'liu1'
Exec sp_addrolemember 'db_owner' , 'liu1'
例12:将成员liu1从db_owner角色中删除。
Use Student
Exec sp_droprolemember 'db_owner' , 'liu1'
例13：登录账号ZCJ\liu在数据库Student中映射为用户liu1，先将针对数据库表student_Info的SELECT操作权限授予public角色，那么凡是该角色的成员都会获得该权限。然后将针对表student_Info的INSERT、UPDATE和DELETE操作权限授予liu1。
Use Student
Exec sp_grantdbaccess 'ZCJ\liu', 'liu1'
GRANT SELECT ON student_Info TO PUBLIC
GRANT INSERT,UPDATE,DELETE ON student_Info TO liu1
例14:首先创建了两个登录账号wei和zhao，接着使它们成为数据库Student的用户。在数据库Student中创建一个角色，该角色名为DB_role1，它被授予对表student_Info的student_Name和born_Date两列的SELECT操作权限。最后将wei和zhao添加成为角色DB_role1的成员。
Use Student
Exec sp_addlogin 'wei'
Exec sp_addlogin 'zhao'
Go
Exec sp_grantdbaccess 'wei','w'
Exec sp_grantdbaccess 'zhao','zh'
Go
Exec sp_addrole 'DB_role1'
GRANT SELECT(student_Name,born_Date) ON student_Info
TO DB_role1
Exec sp_addrolemember 'DB_role1','w'
Exec sp_addrolemember 'DB_role1','zh'
Go