SQL Server

专业 班 级 教 师 授课时间

教学课题 教 学 目 的 教 材 分 析 教 学 方 法 教 学 手 段 第七讲 数据库安全性 学时 2 1、认识SQL Server2000数据库的安全管理机制 2、了解Windows认证和混合认证两种认证模式 3、掌握登录帐号、用户的创建以及权限设置 4、掌握角色的作用和创建 重 点 难 点 1、登录帐号和用户的创建以及权限设置 2、登录帐号和用户的区别 3、应用程序角色的概念和使用 登录帐号和用户的创建 登录帐号和用户的区别 采用“项目教学法” 多媒体教学 ★ 复习巩固，问题牵引、导入新课。提问： 5min （1）银行、证券等业务数据如何保证安全性？在ATM机上我们是否可以查询到其它帐号的存款额？（2）张老师是'00电子商务'班的班主任，现允许他可以查看全校的课程信息以及本班学生的选课教 学 过 程 及 时 间 分 配 信息，如何实现？（备注：赵老师不是SQL Server的登录帐户） 7．1SQL SERVER的验证模式 10min 7．2登录管理 20min 系统管理员帐户 创建、查看、删除SQL SERVER登录帐户 7．3用户管理 20min 创建、查看、删除数据库用户 7．4角色管理 20min 服务器角色 数据库角色 7．5 许可管理 15min 权限的种类 权限的管理 ★小结及作业布置 5min

【教学内容】

第七章 数据库安全性

数据库安全性的定义：

是指保护数据库中的各种数据，以防止因非法使用而造成数据的泄密和破坏。简单的说就是保护数据库以防止不合法的使用所造成的数据泄露。 7．1SQL SERVER的验证模式

验证模式分为：身份验证模式和登录验证模式 内容包括：

确认用户帐号是否有效； 能否访问系统； 能访问系统的哪些数据。 7．1．1身份验证方式

身份验证是指当用户访问系统时，系统对该用户的账号和口令的确认过程。SQL SERVER能识别两种类型的身份验证方式： WINDOWS身份验证方式； SQL SERVER身份验证方式。

用户和客户机在连接SQL时，可任选其一。 1、WINDOWS身份验证方式：

用户身份由WINDOWS NT域建立，只要能登录Windows NT/2000就可连接SQL SERVER。 2、SQL SERVER身份验证：

由SQL SERVER系统管理员定义SQL 的登录帐号和密码，用户连接时必须提供帐号和口令。 设置步骤：

在“服务器”/ “安全性”/“登录”中新建登录名；

右键单击“服务器”，选择“属性”/“安全性”标签，并设置“混合模式”以设置服务登录的帐户；

右键单击“服务器”，选择“编辑注册”，选择“SQL身份验证”，并输入登录名称及密码。 7．1．2登录验证机制

登录验证机制指当用户登录时，SQL SERVER采用什么系统来验证用户身份。 1、NT验证模式：

功能：指要登录到SQL SERVER系统的用户身份由NT系统来进行验证，此时无法以SQL SERVER帐号登录。 登录过程：

用户登录到WINDOWS NT/2000，此时建立该用户的网络安全特性；

用户连接SQL时，无需输入密码和用户名，SQL自动从其SYSxLOGINS表中提取网络登录ID； 适用对象：该方式适用于只在部门访问的数据库的情况。 特点：

用户和组由WINDOWS NT管理及维护，数据库管理员的工作仅是管理数据库； WINDOWS NT具有很强的管理用户帐户的工具； 可以在SQL SERVER增加用户组。 2、混合安全模式

功能：指用户登录时，其身份由WINDOWS NT和SQL SERVER共同认证。 适用对象：适合用于外界用户访问数据库或不能登录到WINDOWS域时使用。 登录过程：

用户登录SQL SERVER时，SQL SERVER检测输入的登录名和密码是否与系统sysxlogins表不记录的登录名和密码相匹配，并进行身份验证；

如果不存在该用户的登录帐户，则身份验证失败，用户将会收到错误信息。用户只有提供正确的登录名和密码，才能通过SQL SERVER验证。 特点：

混合模式允许非WINDOWS客户、Internet客户和混合的客户组连接到SQL SERVER ； 增加了安全性方面的选择。

验证模式的设置：

右键单击“服务器”，选择“属性”/“安全性”标签，并设置适合的验证模式 7．2登录管理

管理员可以从WINDOWS用户或用户组中创建登录帐号或者创建一个新的SQL SERVER登录帐号。

登录帐号存储在主数据库的sysxlogins系统表中；

在创建帐号过程中，管理员可以为每个用户指定一个默认数据库； 7．2．1系统管理员帐户

1、默认的系统管理员帐户，均具有SQL SERVER系统和所有数据库的全部权限：

sa：特殊登录名，代表混合验证机制下SQL SERVER的系统管理员，其始终关联DBO用户。 BUILTIN\\Administrator：是NT系统的系统管理员组。 2、主要负责： 创建登录名； 配置服务器； 创建、删除数据库；

无须考虑所有权和权限，可以操作各种数据库对象； 停止、启动服务器；

停止在服务器上运行的无效过程，

7．2．2用T-SQL语句创建、查看、删除SQL SERVER登录帐户。

1、将系统中已存在的NT登录增加到SQL SERVER系统登录中（WINDOWS身份验证） 允许NT用户连接SQL SERVER：Sp_grantlogin ‘域名\\帐户名’ 例：sp_grantlogin 'pl\\guest'

阻止NT用户连接SQL SERVER：Sp_denylogin ‘域名\\帐户名’ 删除NT用户连接SQL SERVER：Sp_revokelogin ‘域名\\帐户名’ 注：

以上3个存储过程不能放在同一个批中执行。 帐户名前面要加上域名及\\；

删除windows NT组或用户，不会删除SQL SERVER中对应的登录帐户；反之废除登录帐号，不会删除NT中的用户。

（5）企业管理器中设置：服务器/安全性/登录，右键单击NT组或用户，选择“属性”/“数

据库访问”标签，并选择可访问数据库和所担当的角色。

2、使用T-SQL 语言创建、查看、删除SQL SERVER登录帐户（SQL 身份验证） 创建登录帐户格式：SP_addlogin ‘帐户名’ [,新帐户密码][,‘默认数据库’] 例：sp_addlogin ‘abc’,123,jwgl

查看登录帐户的信息：sp_helplogins [帐户名] 例：sp_helplogins abc，若省略帐户名，则查看所有的帐户信息。

删除登录帐户：删除一个登录帐户，必须确认该登录帐户无关联的用户存在于数据库系统中，即不存在孤儿型的用户（没有任何登录名与其映射）。删除步骤： 先删除用户：sp_revokedbaccess ‘用户名’ 再删除帐户：sp_droplogin ‘帐户名’

注：不能删除系统管理者sa以及当前连接到SQL的登录。 用企业管理器设置：

服务器/安全性/登录/新建登录

在弹出窗口的“常规”标签中：①若选择NT身份验证，则需选择域名及域帐户②基选择SQL SERVER验证模式，则需输入登录帐户名、密码及确认密码。 单击“服务器角色”：授予该登录用户服务器范围内的权限； 单击“数据库访问”：指定该登录用户可以访问的数据库。 7．3用户管理

用户是基于数据库的名称，是和登录帐户相关联的。 1、登录名和数据库用户名的关系：

登录名是访问SQL SERVER的通行证，其本身并不能证用户访问服务器中的数据库；用户名是登录名在数据库中使用的名称，一个用户名必须和一个登录名相关联； 登录帐户和数据库用户是SQL SERVER进行权限管理的两种不同的对象。

一个登录帐户可与服务器上的所有数据库进行关联，产生多个数据库用户,但在一个数据库中只能拥有一个用户；

而一个数据库用户只能映射到一个登录帐户。

SQL SERVER允许数据库为每个用户对象分配不同的权限，为数据库用户授权的过程也就是为登录对象提供对数据库的访问权限的过程。 7．3．1用T-SQL语句创建、查看、删除数据库用户

创建步骤：（1）创建登录帐户；（2）将登录帐户映射为数据库中同名的用户。

1、在当前数据库中创建用户：sp_grantdbaccess ‘登录名’ [,当前数据库用户名] 例：sp_grantdbaccess ‘abc’,abc1

注：若省略‘用户名’，则创建一个和登录名同名的用户名，且一个登录名在同一数据库中只能创建一个用户。

2、查看数据库用户：sp_helpuser [用户名] 注：省略用户名则查看所有用户信息。 3、删除数据库用户：sp_revokedbaccess ‘用户名’ 注，该过程不能删除以下角色，用户： public角色、DBO、数据库中的固定角色； master和tempdb数据库中的guest用户帐户； Windows NT组中的Windows NT用户。 7．3．2用企业管理器设置数据库用户：

“服务器”/“数据库”/“目标数据库”/“用户”，右键菜单中选择“新建数据库用户”然后在弹出窗口中进行设置。 7．3．3改变数据库所有权

一个数据库只能有一个数据库所有者，其不能被删除，默认情况下，sa帐户映射到库中的用户是dbo，改变所有权步骤： 打开要更改所有权的数据库：

更改所有权：sp_changedbowner 登录号 例：将JWGL的所有权授序abc帐号：use jwgl go

sp_changedbowner abc

7．4角色管理

1、定义：角色类似于组，一般将具有相同权限的一群用户群添加为某角色成员，然后给这个角色授予适当的权限。这样该用户群的所有用户就都具有了该角色的权限，而没有必要逐个对每一个用户去授予相同的权限 2、优点：避免大量重复的工作，简化和方便对用户的管理。 3、分类：

服务器角色：是服务器级的一个对象，主要用于对登录名设置其对服务器的管理权限 数据库角色：是数据库级的一个对象，主要用于对数据库用户设置其对数据库的管理权限•注：同一用户可属于多个角色。 7．4．1固定服务器角色

SQL安装完成后，系统自动创建8个固定的服务器角色，对于服务器角色来说，数据库管理员只能完成以下两个操作：添加和删除服务器角色中的成员，而不能删除服务预定义的角色。 1．固定服务器角色及功能： •查看固定服务器角色： sp_helpsrvrole

•查看某个固定服务器角色的权限：sp_srvrolepermission 服务器角色 2、使用T-SQL命令管理服务器角色

将登录帐户添加到服务器角色内，使其成为该角色的成员：sp_addsrvrolemember ‘登录名’,’服务器角色名’•注：角色设定好后，必须重启方能生效 将某一登录者从某一服务器角色中删除，使其不再具有该角色权限sp_dropsrvrolemember ‘登录名’,’服务器角色名’ •例：为‘login2’登录授予固定服务器角色为‘sysadmin’,成功后再将其从该服务器角色删除，使其不再具有该角色权限。

•sp_addsrvrolemember 'login2','sysadmin‘--此时用login2重新连接后，可执行SYSADMIN的所有功能。 •Go

•Sp_dropsrvrolemember ‘login2’,’sysadmin’•Go --此时只有访问权限

•例：创建user01登录帐户，使其能在服务器中创建、管理、审核登录帐户 7．4．2数据库角色

•数据库角色的作用域都只在其对应的数据库内，数据库角色分为固定数据库角色和自定数据库角色（一）固定数据库角色•固定数据库角色的权限由系统固定，用户不能更改，

•其中PUBLIC角色是一个特殊的数据库角色，每位数据库用户都是该角色成员，主要负责维护数据库中用户的全部默认许可，不能将用户和组或角色指定为PUBLIC角色。•注：•数据库角色在数据库级别上被定义，存在于数据库之内，存储在每个数据库sysusers表中•固定角色不能被删除、修改和创建•固定数据库角色可指定给其他登录帐户。 将固定数据库角色指定给其他用户格式：

sp_addrolemember ‘固定数据库角色名’,’用户名’将固定数据库角色指定给其他用户格式步骤：•将数据库切换到需要指定角色的目标数据库中；•为登录名在该数据库中添加用户；•为用户指定固定数据库角色。

•例：为登录名login2分配固定数据库角色db_datareader 第一步：为登录名在数据库中授予用户

Sp_grantdbaccess ‘login2’,’abcd’第二步：为该用户分配固定数据库角色 Sp_addrolemember ‘db_datareader’,’abcd’ 将用户从固定数据库角色中删除：

sp_droprolemember '固定数据库角色名',’用户名' （二）用户自定义数据库角色

自定义数据角色当打算为某些数据库用户设置相同的权限但预定义的数据库角色不能满足所实际要求的权限时，就通过自定义新数据库角色来满足这一要求，从而使这些用户能够在数据库中实现某一特定功能。 （1）特点：

在同一数据数据库中用户或具有多个不同的自定义角色，可任意组合，角色可以进行嵌套，从而实现不同级别的安全性 （2）分类：

•①标准角色：通过对用户权限等级的认定而将用户划分为不同的用户组，使用户总是相对于一个或多个角色，从而实现管理的安全性。所有的预定义的数据库角色或SQL管理者自定义的某一角色都是标准角色。•②应用角色：当想让某些用户只能通过特定的应用程序间接地存取数据库中的数据而不是直接地存取数据库数据时就应该考虑使用应用角色。当某一用户使用了应用角色时，它便放弃了已被赋予的所有数据库专有权限，它所拥有的只是应用角色设置的权限。通过应用角色，能以可控制方式来限定用户的语句或对象权限。 两者的区别：

•应用角色不像标准角色那样具有组的含义，因此不能包含成员；•当用户在数据库中激活应用角色时，必须提供密码，而标准角色并不受口令保护。

•应用程序角色默认设置为未激活状态，要由其他标准角色激活后才能使用。 用T-SQL语言创建、删除标准角色 1、创建标准角色：

Sp_addrole ‘标准角色名’2、删除标准角色：

Sp_droprole ‘标准角色名’3、将数据库用户添加至标准角色中： Sp_addrolemember ‘标准角色名’,‘用户名’

例：为JWGL库创建一个名为‘role1’的标准角色，并为帐号‘login2’创建一个用户‘a1’,要求将‘a1’帐号添加至‘role1’角色中。Use jwgl Go

Sp_addrole ‘role1’Go

Sp_grantdbaccess ‘login2’,’a1’Go Sp_addrolemember ‘role1’,’a1’go 用T-SQL语言创建、删除应用角色

1、打开数据库 2、创建应用角色：

Sp_addapprole ‘应用角色名’,‘密码’3、删除标准角色： Sp_dropapprole ‘应用角色名’4、为应用角色授予权限 5、激活应用角色：

Sp_setapprole ‘应用角色名’,‘密码’ 7．5 许可管理

•在SQL Server 中，每个对象都是用户所有的。对象的所有者由用户帐户（也称数据库用户标识符）标识。当创建对象时，可以访问该对象的惟一用户帐户就是所有者的用户帐户。对于任何想要访问该对象的其他用户，所有者必须给该用户授予访问权限。如果所有者只想让特定的用户访问该对象，可以只给这些特定的用户授予权限。•将一个登录帐户映射为数据库中的用户帐户，并将该用户帐户添加到某种数据库角色中。其实都是为了对数据库的访问权限进行设置，以便让各用户进行适合于其工作职能的操作。 1、权限的种类

•在SQL Server 中，权限分为三类：•对象权限（Object Permission）； •语句权限（Statement Permission ）；•隐含权限（Implied Permission ） ①对象权限

•是指用户对数据库中的表、视图、存储过程等对象的操作权限，相当于数据库操作语言（DML）的语句权限，例如是否允许查询、添加、删除和修改数据等。对象权限的具体内容包括以下3个方面：•a.对于表和视图：是否允许执行Select、Insert、Update、Delete语句。•b.对于表和视图的字段：是否允许执行Select和Update语句。•c.对于存储过程：是否允许执行Execute语句。 ②语句权限

相当于数据定义语言（DLL）的语句权限，这种权限专指是否允许执行下列语句：•Create Table Create Default

•Create Procedure Create Rule •Create View Backup Database •Backup Log •③隐含权限 是指由SQL Server 预定义的服务器角色、数据库所有者（dbo）和数据库对象所有者（dboo）所拥有的权限，隐含权限相当于内置权限，并不需要明确地授予这些权限。•例如，服务器角色Sysadmin的成员可在整个服务器范围内从事任何操作，数据库所有者dbo可对本数据库进行任何操作 2、权限的管理

•在上述三种权限中，隐含权限是由系统预定义的，这类权限是不需要、也不能够进行设置的。因此，权限的设置实际上就是指对象权限和语句权限的设置。权限可由数据库所有者和角色进行管理。

权限管理的内容包括以下3个方面的内容 •a.授予权限（Grant）：即允许某个用户或角色对一个对象执行某种操作或某种语句。•b.剥夺权限（Revoke）：即不允许某个用户或角色对一个对象执行某种操作或某种语句，或者收回曾经授予的某种权限，这与授予权限正好相反。•c.拒绝访问（Deny）：即拒绝某个用户或角色访问某个对象，即使该用户或角色被授予这种权限，或者由于继承而获得这种权限，仍然不允许执行相应的操作

1) 使用企业管理器管理数据库用户的权限

•a.启动企业管理器，在“树”窗格中，展开该用户所在的数据库，单击“用户”节点。•b.在内容窗格中右击想要设置权限的用户帐户，并从弹出菜单中选择“所有任务/ 管理权限”命令。•c.在数据库用户权限管理对话框中，选择下列操作之一：• 若要授予用户对某个数据库对象的访问权限，请在对象列表中单击相应的方框，使其中出现Ö标记。• 若要剥夺用户对某个数据库对象的访问权限，请在对象列表中单击相应的方框，使其中出现´标记。• 若要禁止用户对某个数据库对象的访问权限，请在对象列表中单击相应的方框，使该方框变成空白的。•d. 单击“确定”或“应用”按钮，使所做的设置生效。• 2）使用企业管理器管理数据库角色的权限

•.启动企业管理器，在“树”窗格中，展开该角色所在的数据库，单击“角色”节点。•b.在内容窗格中右击想要设置权限的数据库角色，并从弹出菜单中选择“属性”命令。•c.在数据库角色属性对话框中，单击“权限”按钮。•d. 出现数据库角色权限管理对话框以后，在对象列表中单击适当的方框，以便授予、剥夺或禁止该角色对某个数据库对象的访问权限。•d. 单击“确定”或“应用”按钮，使所做的设置生效。 3）使用T-SQL语句管理语句的权限

在T-SQL语言中，与权限管理有关的语句有以下三个：• Grant语句：用于授予权限； • Revoke语句：用于剥夺权限； • Deny语句：用于禁止权限 语法为：

•{ Grant | Deny } 语句名称 [ , „n ] TO 用户帐户名称或角色名称 [ , „n ] •Revoke 语句名称 [ , „n ] FROM 用户帐户名称或角色名称 [ , „n ] 需要注意的是，管理语句权限的语句只能在系统数据库master中执行 •例如：

•Grant Create Database, Create Table TO Mary, John •Revoke Create Table, Create Default FROM Mary, John •Deny Create Database, Create Table TO Mary, John 4）使用T-SQL语句管理对象的权限

•{Grant | Deny} 权限名称 [ ,„ n ] ON 表名称 | 视图名称 | 存储过程名称 TO 用户帐户或角色•Revoke 权限名称 [ , „n ] ON { 表名称 | 视图名称 | 存储过程名称 } FROM 用户帐户 或角色 •例如：下面的语句首先给Public角色授予Select权限，然后将特定的权限授予用户Mary、John和Tom，使这些用户拥有对authors表的所有权限。•USE pubs •GO

•Grant select ON authors TO public •GO

•Grant Insert , Update , Delete ON authors TO Mary , John , Tom 小结：

•这里讨论了SQL Server 的安全性管理。 SQL Server 的安全机制建立在登录、用户、角色的基础之上。一个用户若要连接到SQL Server，就必须使用特定的登录帐户标识自己，登录帐户为该用户提供“连接权”。•一个登录帐户可以在不同的数据库中映射为不同的用户帐户，而且用户帐户在经过授权之后才能获得“访问权”，从而允许查看经过授权可以查看的表和视图，允许执行经过授权可以执行的存储过程以及其他管理功能。•在SQL Server 中，角色是管理权限的有力工具。将一些用户添加到具体某种权限的角色中，权限在用户成为角色成员时自动生效。•