第5章安全与权限

1、 对于一个数据库管理员来说，安全性就意味着必须保证那些具有特殊数据访问权限的用户能够登录到SQL Server，并且能够访问数据以及对数据库对象实施各种权限范围内的操作；同时，他还要防止所有的非授权用户的非法操作。 SQL Server提供了既有效又容易的安全管理模式，这种安全管理模式是建立在安全身份验证和访问许可两者机制上的。重要概念：l主体：它是一个标识，用来访问SQL 服务器的对象，可能是用户或应用程序。l安全对象：在服务器和数据库中，可通过权限保护的对象。l权限：用来规定特定主体在访问安全对象时允许或不允许的行为。l安全级别：SQL Server 具有多个安全级别，各级别间具有层次关系

2、。l登录名：通过登录名可以连接到服务器。l用户：通过用户可以访问数据库。l角色：通过角色可以赋予访问数据库的权限。主 体主体Server 角色SQL Server 登录Windows 组域用户账户本地用户账户用户数据库角色应用程序角色组SQLServer数据库Windows安全对象权限安全对象主体Server 角色SQL Server 登录Windows 组域用户账户本地用户账户用户数据库角色应用程序角色组SQLServer数据库Windows安全对象权限文件密钥服务器架构数据库SQL Server 2008 权限主体Server 角色SQL Server 登录Windows 组域用户账户本地

3、用户账户用户数据库角色应用程序角色组SQLServer数据库Windows安全对象权限文件密钥服务器架构数据库CREATEALTERDROPCONTROLCONNECTSELECTEXECUTEUPDATEDELETEINSERTTAKE OWNERSHIPVIEW DEFINITIONBACKUP授予授予/撤销撤销/拒绝拒绝ACL登录到系统登录到系统 第一个安全性问题：当用户登录数据库系统时，如何确保只有合法的用户才能登录到系统中？这是一个最基本的安全性问题，也是数据库管理系统提供的基本功能。 在 SQL Server 2008中，通过身份验证模式和主体解决这个问题。安全身份验证 安全身份验

4、证用来确认登录SQL Server的用户的登录帐号和密码的正确性，由此来验证该用户是否具有连接SQL Server的权限。任何用户在使用SQL Server数据库之前，必须经过系统的安全身份验证。 SQL Server 2008提供了两种确认用户对数据库引擎服务的验证模式：Windows身份验证与SQL Server身份验证。 SQL Server数据库系统通常运行在Windows服务器上，而Windows作为网络操作系统，本身就具备管理登录、验证用户合法性的能力，因此Windows 验证模式正是利用了这一用户安全性和帐号管理的机制，允许SQL Server可以使用Windows的用户名和口令

5、。 Windows 身份验证模式 用户由 Windows 授权 通过登录而被授予 SQL Server 的访问权 利用SSMS可以进行认证模式的设置，步骤如下：（1）打开SQL 管理平台，右击要设置认证模式的【服务器】，从弹出的快捷菜单中选择【属性】选项，则出现【SQL Server属性】对话框。（2）在SQL Server属性对话框中选择【安全性】选项页。操作操作 第二个安全性问题：当用户登录到系统中，他可以执行第二个安全性问题：当用户登录到系统中，他可以执行哪些操作、使用哪些对象和资源？哪些操作、使用哪些对象和资源？ 这也是一个基本的安全问题，在这也是一个基本的安全问题，在SQL Serv

6、er 2008中，中，通过安全对象和权限设置来解决这个问题。通过安全对象和权限设置来解决这个问题。 通过认证并不代表用户就能访问SQL Server中的数据，还必须通过许可确认。用户再具有访问数据库的权限之后，才能够对服务器上的DB进行权限许可下的各种操作，这种用户访问数据库权限的设置是通过用户帐号实现的 。所有所有 第三个安全性问题：数据库中的对象由谁所有？如果是第三个安全性问题：数据库中的对象由谁所有？如果是由用户所有，那么当用户被删除时，其所拥有的对象怎么办，由用户所有，那么当用户被删除时，其所拥有的对象怎么办，难道数据库对象可以成为没有所有者的难道数据库对象可以成为没有所有者的“孤儿孤

7、儿”吗？吗？ 在在 SQL Server 2008中，这个问题是通过用户和架构分中，这个问题是通过用户和架构分离来解决的。离来解决的。2022年1月15日第12页 数据库对象 架构 用户 包含在 被拥有 表 视图 存储过程 函数 管理登录名包括创建登录名、设置密码策略、查看登录名管理登录名包括创建登录名、设置密码策略、查看登录名信息及修改和删除登录名等。信息及修改和删除登录名等。 注意：注意：sa是一个是一个 默认的默认的SQL Server登录名，拥有操作登录名，拥有操作SQL Server系统的所有权限。该登录名不能被删除。当采用混合模系统的所有权限。该登录名不能被删除。当采用混合模式安装

8、式安装SQL Server系统之后，应该为系统之后，应该为sa指定一个密码。指定一个密码。CREATE LOGIN login_name WITH SQL_login_options | FROM WINDOWS WITH windows_login_options SQL Server 2008系统的密码策略问题系统的密码策略问题 密码复杂性和密码过期两大特征 密码的复杂性是指通过增加更多可能的密码数量来阻止黑客的攻击。 密码过期策略是指如何管理密码的使用期限。在创建SQL Server登录名时，如果使用密码过期策略，那么系统将提醒用户及时更改旧密码和登录名，并且禁止使用过期的密码。 在使用

9、CREATE LOGIN语句创建SQL Server登录名时，为了实施上述的密码策略，可以指定HASHED、MUST_CHANGE、 CHECK_EXPIRATION、CHECK_PLICY等关键字。 HASHED关键字用于描述如何处理密码的哈希运算。 使用CREATE LOGIN语句创建SQL 登录名时，如果在PASSWORD后面使用HASHED关键字，表示在作为密码的字符串存储到数据库之前，对其进行哈希运算。 如果在PASSWORD关键字后面没使用HASHED关键字，表示作为密码的字符串已经是经过哈希运算之后的字符串，因此在存储到数据库之前不再进行哈希运算了。 使用密码策略创建SQL Se

10、rver登录名维护登录名 登录名创建之后，可以根据需要修改登录名的名称、密码、密码策略、默认的数据库等信息，可以禁用或启用该登录名，甚至可以删除不需要的登录名。使用ALTER LOGIN修改登录名2022年1月15日第19页2022年1月15日第20页管理用户的方法l实现对单个数据库的访问 l可映射到单个用户登录或Windows组用户登录l用 SQL Server Management Studio 或 CREATE USER 语句创建用户创建登录名的数据库用户创建带有默认架构的数据库用户dbo 用户：用户：1、默认存在于所有数据库中2、sysadmin 角色 和 sa 登录成员 映射到 db

11、o3、任何由系统管理员创建的对象都自动属于 dbo 4、不能被删除guest 用户：1、默认存在于所有数据库中2、允许不使用用户账户的登录来访问数据库查看、dbo和guest 如果希望查看数据库用户的信息，可以使用sys.database_principals目录视图。该目录视图包含了有关数据库用户的名称、ID、类型、默认的架构、创建日期和最后修改日期等信息。维护用户 使用ALTER USER语句修改用户。修改用户包括两个方面，第一，可以修改用户名；第二可以修改用户的默认架构。 修改用户名与删除、重建用户是不同的。修改用户名仅仅是名称的改变，不是用户与登录名对应关系的改变，也不是用户与架构关系

12、的变化。修改用户名：修改和删除 使用ALTER USER语句修改指定用户的默认架构，可使用WITH DEFAULT_ SCHEMA子句。 用户不再需要，可以使用DROP USER语句删除数据库中的用户。服务器登录帐号SQL Server服务器登录管理：创建、管理SQL Server登录帐号，具体执行步骤如下：（1）打开SSMS在【对象资源管理器】中，展开【安全性】文件夹。（2）右击【登录名】，从弹出的快捷菜单中选择【新建登录名】选项，则出现【登录名新建】对话框，如下图。图10-3 服务器角色对话框 （3）选择【服务器角色】页框，如图。在框中，列出了系统的固定服务器角色。在这些固定服务器角色的左

13、端有相应的复选框，打勾的复选框表示该登录帐号是相应的服务器角色成员。 图10-4 用户映射对话框 （4）选择【用户映射】页框，如图。上面的列表框列出了【映射到此登录名的用户】，单击左边的复选框设定该登录账号可以访问的数据库以及该帐号在各个数据库中对应的用户名。在【数据库角色成员身份】中可指定该帐号所属的数据库角色。 5）选择【安全对象】页框，如图。安全对象是 SQL 数据库引擎授权系统控制对其进行访问的资源。点击【添加】按钮，可对不同类型的安全对象进行安全授予或拒绝；单击【确定】即可完成登录帐号的创建。用户帐号管理 在一个数据库中，用户帐号惟一标识一个用户，用户对数据库的访问权限以及对数据库对

14、象的所有关系都是通过用户帐号来控制的。利用SSMS可授予SQL Server登录访问数据库的许可权限： 打开SSMS，展开要登录的服务器和数据库文件夹，展开要创建用户的数据库及安全性文件夹。图10-6 新建数据库用户对话框 右击用户图标，从快捷菜单中选择【新建用户】选项，则出现【数据库用户新建】对话框，如图。图10-7 管理数据库用户对话框 在这个交互式多媒体中，你需要解决不能删除用户TOM的问题。你可以了解到用户和架构之间的关系以及处理方法。l 角色是SQL Server引进的用来集中管理数据库或服务器权限的概念。数据库管理员将操作数据库的权限赋予角色，就好像把职权赋予了一个官位。然后，数据

15、库管理员可以将角色再赋给数据库用户或登录账户，从而使数据库用户或登录账户拥有了相应的权限。l SQL Server提供了用户通常管理工作的预定义服务器角色和数据库角色；还可创建自己的数据库角色。l 当用户需要执行不同的操作时，只需将该用户加入不同的角色中即可，而不必对该用户反复授权许可和收回许可。管理角色：管理角色： 服务器角色： 固定服务器角色是SQL Server在安装时就创建好的用于分配服务器级管理权限的实体。 该角色信息存储在master数据库的sysxlogins表中，当用户将某个登录账户增加到服务器角色中的时候，sysxlogins表中该登录账户的相应行将进行更新，以指示该登录账户

16、属于服务器角色的成员，同时具有该角色的许可权限。 SQL 提供了八种常用的固定服务器角色如下：系统管理员（sysadmin）服务器管理员（Serveradmin）磁盘管理员（diskadmin）进程管理员（processadmin）安全管理员（securityadmin）安装管理员（setupadmin）数据库创建者（dbcreator）批量数据输入管理员（bulkadmin） 数据库角色分为固定数据库角色、用户定义角色和应用程序角色。数据库角色分为固定数据库角色、用户定义角色和应用程序角色。 固定数据库角色预定义了数据库的安全管理权限和对数据对象的访问固定数据库角色预定义了数据库的安全管理权

17、限和对数据对象的访问权限；用户定义角色由管理员创建并且定义了对数据对象的访问权限；应用权限；用户定义角色由管理员创建并且定义了对数据对象的访问权限；应用程序角色规定了某个应用程序的安全性，用来控制通过某个应用程序对数据程序角色规定了某个应用程序的安全性，用来控制通过某个应用程序对数据的间接访问。的间接访问。 数据库角色： 当在当在SQL Server中添加新用户账户或者更改现有用户的权限时，可以中添加新用户账户或者更改现有用户的权限时，可以向向SQL 数据库角色添加此用户，而不要直接将权限应用到账户上。用户账户数据库角色添加此用户，而不要直接将权限应用到账户上。用户账户可以是同一数据库中任意多

18、个角色的成员，并且可持有每个角色的适当权限。可以是同一数据库中任意多个角色的成员，并且可持有每个角色的适当权限。SQL提供了十种常用的固定数据库角色来授予组合数据库级管理员权限： public db_owner db_accessadmin db_ ddladmin db_securityadmin db_backupoperator db_datareader db_datawriter db_denydatareader db_denydatawriter用户自定义角色： 创建用户定义的数据库角色就是创建一组用户，这些用户具有相同的一组许可。应用程序角色： 它是一种比较特殊的角色。当我们打

19、算让某些用户只能通过特定的应用程序间接地存取数据库中的数据而不是直接地存取数据库数据时，就应该使用应用程序角色。 通过应用程序角色，能够以可控制方式来限定用户的语句或者对象许可。 使用SSMS管理角色：（1）管理服务器角色。 打开SSMS,展开指定的服务器，单击【安全性】文件夹，再单击【服务器角色】，在右边的页框中右击所要的角色，从弹出菜单中选择【属性】，则出现【服务器角色属性】对话框，如图。在该对话框可看到属于该角色的成员。单击【添加】或单击【删除】按钮。图10-16 “数据库角色新建”对话框 （2）管理数据库角色。 在SSMS中，展开【服务器】、【数据库】和【安全性】文件夹，右击【数据库角

20、色】，从弹出菜单中选择【新建数据库角色】，则出现【新建数据库角色】对话框，如图。 使用存储过程管理角色：（1）管理服务器角色 在SQL Server中，管理服务器角色的存储过程主要有两个：1、sp_addsrvrolemember ：添加服务器角色的成员。其语法为： sp_addsrvrolemember loginame=login，rolename=role2、sp_dropsrvrolemember：从服务器角色中删除成员。其语法为： sp_dropsrvrolemember loginame=login，rolename=role（2）管理数据库角色（六种）： create role：

21、 create role role_name AUTHORIZATION owner_name droprole：Drop role role_name sp_helprole：sp_helprole role sp_addrolemember：sp_addrolemember role，security_account sp_droprolemember：sp_droprolemember role，security_account sp_helprolemember：sp_helprolemember role 使用架构使用架构 命名空间将相关对象组织在一起，从而使复杂的对象列表更加方便管

22、理。SQL Server 2008使用架构对数据库对象实现了类似的概念。举例：我们以每一届的学生建立架构，要查找 2007 级的学生学号时，在这个数据库中就可以通过 2007.student.id 来找到合适的表了。架构：是数据库对象的命名空间。换言之，架构定义了一个边界，边界内的所有名称都是唯一的。数据库中每一个对象的唯一完全限定名称为server.database.schema.object。提问： 在数据库中专门有名为 student_id 的表用来统计学生的学号和姓名，现在的问题就是每一届的学生数据都会存在这样一张同名的表，这可能会给查询带来困难。举例：针对上面的情况，数据库将不知道如

23、何区分 2006 级的学生和 2007 级的学生。如何解决这一问题呢？PersonContact(Server1.AdventureWorks.Person.Contact)SalesCustomer(Server1.AdventureWorks.Sales.Customer)AdventureWorksdboErrorLog(Server1.AdventureWorks.dbo.ErrorLog)l 数据库对象的命名空间：数据库对象的命名空间： 创建架构，在SSMS 中通过【对象资源管理器】，展开数据库下的【安全性】，就可创建架构。使用使用CREATE SCHEMA语句创建架构语句创建架构：

24、CREATE SCHEMA schema_name | AUTHORIZATION owner_name 举例：创建名为 marketing 的架构，该架构由名为 Lance 的用户拥有；在新建的架构中创建名为 promotions 的表。 该语句将架构中的SELECT权限授予给Don的用户，并拒绝将SELECT 权限授予给名为 Erik 的用户。-创建名为marketing的架构，该架构由名为Lance的用户拥有CREATE SCHEMA Marketing AUTHORIZATION Lance -在架构中创建promotions的表（ 三列：source cost promonumber

25、 ；都为整型）CREATE TABLE promotions( source int , cost int , promonumber int )- 授予给Don的用户的SELECT权限GRANT SELECT TO Don - 拒绝将SELECT权限授予给Erik的用户的 DENY SELECT TO Erik Go 对象许可表示对特定的数据库对象的操作许可，它决定了能对表、视图等数据库对象执行哪些操作。 语句许可表示对数据库的操作许可。 语句许可针对的是某个SQL语句，而不是数据库中已经创建的特定的数据库对象。 预定义许可是指系统安装以后有些用户和角色不必授权就有的许可。其中的角色包括固定

26、服务器角色和固定数据库角色，用户包括数据库对象所有者。 只有固定角色或者数据库对象所有者的成员才可以执行某些操作。 用户在登录到SQL后，其用户帐号所归属的Windwos 组或角色所被赋予的许可（权限）决定了该用户能够对哪些数据库对象执行哪种操作以及能够访问、修改哪些数据。 在SQL中包括三种类型的许可：即对象许可、语句许可和预定义许可。 T-SQL 语句使用 GRANT、REVOKR 和 DENY 三种命令来管理权限： GRANT：1、语句权限与角色的授予、语句权限与角色的授予 ：使用该语句为用户授予语句权限的语法为：使用该语句为用户授予语句权限的语法为： GRANT | ,| TO |PU

27、BLIC,| WITH ADMIN OPTION举例：举例：为用户为用户ZhangYiLin授予授予CREATE TABLE的语句权限。的语句权限。程序清单如下：程序清单如下： GRANT CREATE TABLE TO ZhangYiLin2、对象权限与角色的授予：使用该语句为用户授予对象权限，其语法为：、对象权限与角色的授予：使用该语句为用户授予对象权限，其语法为： GRANT ALL|(列名列名,列名列名),ON TO |PUBLIC,| WITH ADMIN OPTION 举例：举例：在在S表中给所有用户授予表中给所有用户授予 SELECT 权限，再将权限，再将INSERT, UPDA

28、TE, DELETE权限授予用户权限授予用户 Mary，John 和和 Tom。程序清单如下：GRANT SELECT ON s TO public GO GRANT INSERT, UPDATE, DELETE ON s TO Mary, John, TomGOREVOKE：能够将以前在当前数据库内的用户或者角色上授予或拒绝的权限删除，但是该语句并不影响用户或者角色从其他角色中作为成员继承过来的权限 语句权限或对象权限与角色的收回：DBA使用该语句收回其相应权限，语法为： 1、 REVOKE | ,| FROM |PUBLIC,| 2、REVOKE | ,| ON FROM |PUBLIC,|举例：收回用户ZHANGYILIN所拥有的CREATE TABLE的语句权限。 REVOKE CREATE TABLE FROM ZHANGYILIN举例：收回用户USER1对C表的查询权限。 REVOKE SELECT ON C FROM USER1 DENY：用于拒绝给当前数据库内的用户或者角色授予权限，并防止用户或角色通过其组或角色成员继承权限。 否定语句权限的语法形式为： DENY ALL| ,| TO |PUBLIC,| 否定对象权限的语法形式为：