数据库原理与应用：第16章 SQL Server的安全管理

数据库原理与应用第16章SQLServer的安全管理

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

一个用户要对某一数据库进行操作，必须满足以下3个条件：登录SQLServer服务器时必须通过身份验证必须是该数据库的用户，或者是某一数据库角色的成员必须有执行该操作的权限16.1SQLServer2000的身份认证模式

1.WindowsNT认证模式用户登录WindowsNT时进行身份认证，登录SQLServer时不再进行身份验证。对于WindowsNT认证模式登录的几点重要说明：

(1)必须将NT网络账号加入到SQLServer中，才能采用NT网络账号登录SQLServer。

(2)如果使用NT网络账号登录到另一个网络的SQLServer，必须在NT网络中设置彼此的托管权限。2.SQLServer认证模式在SQLServer认证模式下，SQLServer服务器要对登录的用户进行身份验证对于Windows9x系列的操作系统只能使用SQLServer认证模式，而当SQLServer在WindowsNT或Windows2000上运行时，系统管理员设定登录认证模式的类型可为WindowsNT认证模式和混合模式。16.2.1WindowsNT认证模式登录账号的建立与删除1.WindowsNT认证模式登录账号的建立1)通过企业管理器建立WindowsNT认证模式的登录账号

Windows2000本地计算机管理界面

Windows2000本地计算机创建新用户的界面16.2.1WindowsNT认证模式登录账号的建立与删除

在企业管理器中选择“登录”图标右击SQLServer信任连接设置窗口16.2.1WindowsNT认证模式登录账号的建立与删除2)通过调用系统存储过程建立WindowsNT认证模式的登录账号创建WindowsNT、Windows2000的用户或组后，使用系统存储过程sp_grantlogin也可将一个WindowsNT或Windows2000的用户或组的登录账号添加到SQLServer中，以便通过Windows身份验证连接到SQLServer。语法格式：

sp_grantlogin[@loginame=]'login'

参数含义：

@loginame=：原样输入的常量字符串注意：(1)不能在用户定义的事务内执行sp_grantlogin；(2)仅sysadmin

或securityadmin

固定服务器角色的成员可以执行sp_grantlogin。EXECsp_grantlogin‘WYATT\wy’16.2.1WindowsNT认证模式登录账号的建立与删除

2.WindowsNT认证模式登录账号的删除

步骤如下：

(1)以系统管理员身份进入企业管理器，并展开目录树；

(2)在目录树的“登录”节点下，选中待删除的“账号”节点，2)通过调用系统存储过程删除WindowsNT认证模式的登录账号通过执行系统存储过程sp_revokelogin可删除WindowsNT用户或组登录SQLServer的账号。1)通过企业管理器删除WindowsNT认证模式的登录账号16.2.2混合认证模式下SQLServer登录账号的建立与删除

(1)在企业管理器中，选择要登录的SQLServer服务器图标右击，出现一快捷菜单，选择菜单项“属性”，出现如图的SQLServer服务器属性配置窗口。

(2)选择“安全性”选项卡，如图所示，选择身份验证方式为“SQLServer与Windows”，选择“确定”按钮。SQLServer服务器属性配置窗口

SQLServer身份认证方式配置窗口

16.2.2混合认证模式下SQLServer登录账号的建立与删除1.通过企业管理器创建SQLServer登录账号

(1)在企业管理器中选择“登录”图标右击，出现快捷菜单，选择“新建登录”菜单项，进入如下图的界；

(2)输入账号名、密码，选择“SQLServer身份验证”方式，点击“确定”按钮。SQLServer登录账号新建窗口

16.2.2混合认证模式下SQLServer登录账号的建立与删除2.利用系统存储过程创建SQLServer登录账号语法格式：sp_addlogin[@loginame=]'login'

[,[@passwd=]'password']

[,[@defdb=]'database']

[,[@deflanguage=]'language']

[,[@sid=]sid]

[,[@encryptopt=]'encryption_option']16.2.2混合认证模式下SQLServer登录账号的建立与删除3.SQLServer登录账号的删除

利用sp_droplogin系统存储过程可删除SQLServer登录账号。语法格式：

sp_droplogin[@loginame=]'login'

参数含义：

'login'：将被删除的登录账号名。返回值：0（成功）或1（失败）。说明：

(1)若要删除一个数据库现有用户的登录账号，必须首先使用sp_dropuser

删除该用户。

(2)不能删除系统管理员(sa)的登录账号。

(3)不能在用户定义的事务内执行sp_droplogin。

(4)只有sysadmin

和securityadmin

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

服务器角色独立于各个数据库，如果在SQLServer中创建一个登录账号后，要赋予该登录者具有管理服务器的权限，此时可设置该登录账号为服务器角色的成员。SQLServer提共了如下固定服务器角色：Sysadmin:系统管理员，可对SQLServer服务器进行所有的管理工作，为最高管理角色。Securityadmin:安全管理员，可以管理登录和CREATEDATABASE权限，还可以读取错误码日志和更改密码。Serveradmin:服务器管理员，具有设置及关闭服务器的权限。Setupadmin:设置管理员，添加和删除链接服务器，并执行某些系统存储过程。Processadmin:进程管理员，可以管理运行在SQLServer中的进程。Diskadmin:可以管理磁盘文件。Dbcreator:数据库创建者，可以创建、更改和删除数据库。Bulkadmin:可执行bulkinsert语句。16.3.1固定服务器角色

(1)以系统管理员身份登录到SQLServer服务器，在登录图标对应的列表项中，选择登录账号“DLGC-YPOOLOLRW4\wy”的项目双击；

(2)选择“服务器角色”选项卡，选项卡中列出了SQLServer所有的固定服务器角色，将“Systemadministrators”服务器角色前的复选框选中。1.通过企业管理器添加服务器角色成员SQLServer服务器角色设置窗口16.3.1固定服务器角色

2.利用系统存储过程添加固定服务器角色成员

利用系统存储过程sp_addsrvrolemember可将一登录账号添加到某一固定服务器角色中，使其成为固定服务器角色的成员。说明：

(1)将登录账号添加到固定服务器角色时，该登录就会得到与此固定服务器角色相关的权限。

(2)不能更改sa角色成员资格。

(3)不能在用户定义的事务内执行sp_addsrvrolemember

存储过程。

(4)

sysadmin

固定服务器的成员可以将登录账号添加到任何固定服务器角色，其他固定服务器角色的成员可以执行sp_addsrvrolemember

将登录账号添加到同一个固定服务器角色。16.3.1固定服务器角色

3.利用系统存储过程删除固定服务器角色成员

利用sp_dropsrvrolemember系统存储过程可从固定服务器角色中删除SQLServer登录账号或WindowsNT用户或组。说明：

(1)不能删除sa

登录账号。

(2)不能在用户定义的事务内执行sp_dropsrvrolemember。

(3)sysadmin固定服务器角色的成员执行sp_dropsrvrolemember，可删除任意固定服务器角色中的登录账号，其他固定服务器角色的成员只可以删除相同固定服务器角色中的其他成员。16.3.2固定数据库角色

1.固定数据库角色(1)db_owner：数据库所有者，可执行数据库的所有管理操作。(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角色的成员。16.3.2固定数据库角色2.数据库用户的操作权限(1)在当前数据库中创建数据库对象及进行数据库备份的权限，主要有：创建表、视图、存储过程、规则、缺省值对象、函数的权限及备份数据库、日志文件的权限。(2)用户对数据库表的操作权限及执行存储过程的权限，主要有：SELECT：对表或视图执行SELECT语句的权限；INSERT：对表或视图执行INSERT语句的权限；UPDATE：对表或视图执行UPDATE语句的权限；DELETE：对表或视图执行DELETE

语句的权限；EXECUTE：执行存储过程的权限。(3)用户对数据库中指定表字段的操作权限，主要有：SELECT：对表字段进行查询操作的权限；UPDATE：对表字段进行更新操作的权限。16.3.3用户自定义数据库角色

1.通过企业管理器创建数据库角色

(1)创建数据库角色：以系统管理员身份登录SQLServer，并进入企业管理器，选中目录树school数据库结点的“角色”图标右击，出现一快捷菜单，选择“新建数据库角色”，进入如图的界面，输入角色名，选择确定按钮。新建数据库角色的属性界面

16.3.3用户自定义数据库角色

(2)创建数据库用户并加入数据库角色。即在某一数据库中为SQLServer服务器的登录账号或WindowsNT的登录账号创建一数据库用户账号，将数据库用户加入该数据库中的某一角色，即：使数据库用户成为某一角色的成员。

新建数据库用户的属性界面

16.3.3用户自定义数据库角色

(3)给数据库角色赋予创建数据库对象的权限：在企业管理器目录树中，选择school数据库结点右击，出现一快捷菜单，选择菜单项“属性”，进入如图所示的界面，选择“权限”选项卡，选中允许数据库角色或数据库用户执行的权限。

(4)

给数据库角色赋予表操作权限：在企业管理器的目录树中，选择school数据库结点下角色图标的项目“ROLE”双击，出现如图所示的界面。设置创建数据库对象权限的窗口

数据库角色的属性窗口

16.3.3用户自定义数据库角色

数据库表操作权限设置窗口

表的列操作权限设置窗口

16.3.3用户自定义数据库角色

2.通过SQL命令创建数据库角色1)定义数据库角色语法格式：sp_addrole[@rolename=]'role'

[,[@ownername=]'owner']说明：(1)角色名可以包括字母、符号及数字。但是不能含有反斜线(\)。(2)不能在用户定义的事务内使用sp_addrole。(3)只有sysadmin

固定服务器角色及db_securityadmin

和db_owner

固定数据库角色的成员才能执行sp_addrole。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的功能相同。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

固定数据库角色的成员可以将用户添加到任何用户定义的角色。16.3.3用户自定义数据库角色

4)数据库用户、角色操作权限的授予、拒绝和收回

(1)数据库用户、角色操作权限的授予利用GRANT语句可以给数据库用户或数据库角色赋予执行T-SQL语句的权限及对数据库对象进行操作的权限。语法格式：GRANT{ALL|statement[,...n]}TOsecurity_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}

}TOsecurity_account[,...n][WITHGRANTOPTION][AS{group|role}]16.3.3用户自定义数据库角色

3.数据库用户、角色的删除

1)删除数据库用户在企业管理器中删除数据库用户很简单，直接在目录树的某一数据库结点下，选中须删除的用户项目，按“Del”键即可。语法格式：

sp_revokedbaccess[@name_in_db=]'name'说明：(1)在用户定义事务内部不能执行sp_revokedbaccess。(2)只有sysadmin固定服务器角色成员及db_accessadmin

和db_owner

固定数据库角色成员才能执行sp_revokedbaccess。16.3.3用户自定义数据库角色

2)删除数据库角色

要删除用户自定义的数据库角色，首先应删