第16章SQLServer的安全管理(新)

第16章SQLServer的安全管理16.1概述——三个安全性问题16.2管理登录名16.3角色16.4权限16.5架构16.1

概述——三个安全性问题

安全性是所有数据库管理系统的一个重要特征。理解安全性问题是理解数据库管理系统安全性机制的前提。SQLServer的数据安全机制中主要手段：角色架构用户权限三个安全性问题第一个安全性问题：当用户登录数据库系统时，如何确保只有合法的用户才能登录到系统中？第二个安全性问题：当用户登录到系统中，他可以执行哪些操作、使用哪些对象和资源？第三个安全性问题：数据库中的对象由谁所有？如果是由用户所有，那么当用户被删除时，其所拥有的对象怎么办，难道数据库对象可以成为没有所有者的“孤儿”吗？第一个安全性问题：当用户登录数据库系统时，如何确保只有合法的用户才能登录到系统中？这是一个最基本的安全性问题，也是数据库管理系统提供的基本功能。在MicrosoftSQLServer2008系统中，通过身份验证模式和主体解决这个问题。身份验证模式身份验证模式是DBMS验证客户端和服务器之间连接的方式。服务器级别的验证模式有：Windows身份验证模式和混合模式两种。主体主体是可以请求系统资源的个体、组合过程，用来访问SQL服务器的对象，可能是用户或应用程序。例如，数据库用户是一种主体，可以按照自己的权限在数据库中执行操作和使用相应的数据。主体的三个级别Windows级别的主体的作用范围是整个Windows操作系统。SQLServer级别的作用范围是整个SQL系统，所有数据库。数据库级别的作用范围是数据库内的各种资源。不同主体之间的关系是典型的层次结构关系。可以理解为“一级压一级”。第二个安全性问题：当用户登录到系统中，他可以执行哪些操作、使用哪些对象和资源？通过安全对象和权限设置来解决这个问题。安全对象：在服务器和数据库中，可通过权限保护的对象。主体发出请求访问安全对象。能不能访问呢？系统需要判断主体是否拥有访问安全对象的权限。服务器安全对象范围：SQLServer登录名、数据库等数据库安全对象范围：用户、应用程序角色、程序集等架构安全对象范围：类型、聚合、约束、过程、函数等主体和安全对象的关系Server角色SQLServer登录Windows组域用户账户本地用户账户用户数据库角色应用程序角色组SQLServer数据库Windows文件密钥服务器架构数据库安全对象权限主体SQLServer2008权限Server角色SQLServer登录Windows组域用户账户本地用户账户用户数据库角色应用程序角色组SQLServer数据库Windows文件密钥CREATEALTERDROPCONTROLCONNECTSELECTEXECUTEUPDATEDELETEINSERTTAKEOWNERSHIPVIEWDEFINITIONBACKUP授予/撤销/拒绝ACL服务器架构数据库安全对象权限主体第三个安全性问题：数据库中的对象由谁所有？如果是由用户所有，那么当用户被删除时，其所拥有的对象怎么办，难道数据库对象可以成为没有所有者的“孤儿”吗？这个问题是通过用户和架构分离来解决的。用户不拥有数据库对象，架构可以拥有数据库对象。用户通过架构来使用数据库对象。那么，删除用户时就不必修改数据库对象的所有者。数据库对象、架构和用户之间的关系示意图2023年2月1日第10页总结SQLServer2008的安全机制SQLServer2008的安全机制可以分为四个级别：操作系统的安全性SQLServer2008的登录安全性数据库的使用安全性数据库对象的使用安全性操作系统安全在使用客户计算机通过网络实现对SQLServer服务器的访问时，用户首先要获得客户计算机操作系统的使用权。操作系统安全性是操作系统管理员或者网络管理员的任务。SQLServer的安全性SQLServer的服务器级安全性建立在控制服务器登录账号和密码的基础上。SQLServer采用了标准SQLServer登录和集成Windows登录两种方式。用户在登录时提供的登录账号和密码，决定了用户能否获得SQLServer的访问权。管理和设计合理的登录方式是数据库管理员（DBA）的重要任务，是SQLServer安全体系中DBA可以发挥主动性的第一道防线。数据库的安全性在用户通过SQLServer服务器的安全性检验以后，将直接面对不同的数据库入口。在建立用户的登录账号信息时，SQLServer会提示用户选择默认的数据库。以后用户每次连接上服务器后，都会自动转到默认的数据库上。对任何用户来说，master数据库的门总是打开的，如果在设置登录账号时没有指定默认的数据库，则用户的权限将局限在master数据库以内。数据库的拥有者（owner）可以访问该数据库的对象，可以分配访问权给别的用户。在SQLServer中默认的情况表示所有的权利都可以自由转让和分配。SQLServer数据库对象的安全性数据库对象的安全性是核查用户权限的最后一个安全等级。在创建数据库对象时，SQLServer自动把该数据库对象的拥有权赋予该对象的创建者。对象的拥有者可以实现对该对象的完全控制。默认情况下，只有数据库的拥有者可以在该数据库下进行操作。当一个非数据库拥有者想访问数据库里的对象时，必须事先由数据库拥有者赋予用户对指定对象执行特定操作的权限。16.2管理登录名SQLServer对用户的访问进行两个阶段的检验：认证阶段：能否登录到服务器上

登录SQLServer时进行的检查。如果验证通过，用户就可以连接到SQLServer上；否则，服务器将拒绝用户的登录。从而保证了系统安全性。Windows模式混合模式（Windows登录和SQLServer身份认证）许可确认阶段：能否访问数据库登录到SQLServer上以后，系统检查用户是否有访问服务器上数据的权限。Windows验证模式——Windows登录认证在windows验证模式下，SQLServer检测当前使用的Windows用户帐户，确定该账户是否有权限登录。该模式不必提交登录名和密码。好处：数据库管理员的工作可以集中在管理数据库上面，而不是管理用户账户。对用户账户的管理可以交给Windows去完成。Windows有着更强的用户账户管理工具。可以设置账户锁定、密码期限等。SQLServer验证模式

——登录账号有两种类型一类是登录服务器的登录账号（即服务器登录账号或用户登录账号，其名称就是登录名，必须设置登录密码）；另外一类是使用数据库的用户账号（即数据库用户账号或用户账号，其名称就是用户名）。比较：登录账号是指能登录到SQLServer的账号，属于服务器的层面，本身并不能让用户访问服务器中的数据库，而登录者要使用服务器中的数据库时，必须要有数据库用户账号才能存取数据库。数据库用户是登录名在数据库中的映射SQLServer的两种类型账号SQL登录账号登录服务器SQLServer数据库数据库用户账号访问数据库SQLServer登录账号和数据库用户账号SQLServer登录账号和数据库用户账号数据库用户名与登录名可以不一致，两者建立映射即可。dbuser1登录名的操作创建SQL登录名并指定默认数据库CREATELOGINlognameWITHPASSWORD=[]DEFAULT_DATABASE‘databasename’创建Window登录名(已存在的windows账户)CREATELOGINlognameFROMWINDOWSWITHDEFAULT_DATABASE‘databasename’修改登录名ALTERLOGINlognameWITHNAME=‘newname’修改登录密码ALTERLOGINlognameWITHPASSWORD=‘newname’启用和禁用登录名ALTERLOGINlognameDISABLE|ENABLE数据库用户账户的操作创建数据库用户CREATEUSERdbuser[FORLOGINlogname]省略映射的登录名，表示创建一个和登录名相同的数据库用户创建没有映射到登录名的用户CREATEUSERdbuserWITHOUTLOGIN禁用某个数据库用户对数据库的访问REVOKECONNECTTOdbuser修改数据库用户修改用户名ALTERUSERdbuserWITHNAME=‘newname’修改用户的默认框架ALTERUSERdbuserWITHDEFAULT_SCHEMA=‘schemaname’删除数据库用户

DROPUSERdbuser16.3角色权限、角色、用户16.3.1固定服务器角色16.3.2数据库角色16.3.3管理数据库角色16.3.4应用程序角色权限、角色、用户权限：用来规定特定主体（登录账号或数据库用户）在访问安全对象（服务器或数据库或数据对象<包括数据表、列、存储过程等>）时允许或不允许的行为。权限太多了，为每个用户授予对每个对象的操作权限太繁琐了，所以，用角色来管理。角色现实生活中，角色是起相同作用的人群角色是一种对权限集中管理的机制，每个角色都设定了对SQLServer进行的操作类型即某些权限。用户用户通过被赋予角色，继承角色的权限；角色权限变更，用户权限同时变更。SQL角色的种类及其操作服务器角色：独立于各个数据库。在SQLServer中创建一个登录账号后，要赋予该登录者具有管理服务器的权限，此时可设置该登录账号为服务器角色的成员。只有固定服务器角色，可进行的操作：查看某登录名是否是某服务器角色向某个服务器角色添加一个登录名，将一个登录名从服务器角色中删除数据库角色固定（默认）数据库角色用户自定义，可进行的操作：创建数据库角色添加和删除角色成员查看数据库角色信息16.3.1固定服务器角色角色描述sysadmin可执行任何操作

dbcreator创建和修改数据库

diskadmin管理磁盘文件

serveradmin配置服务器级的设置

securityadmin管理和审核服务器登录

processadmin管理SQLServer进程

bulkadmin执行BULKINSERT语句

setupadmin配置和复制已链接的服务器

16.3.2数据库角色固定数据库角色授予了管理公共数据库任务的权限

用户定义的数据库角色相同数据库权限的多个用户

应用程序角色包含数据库中所有用户固定的数据库角色db_owner数据库的拥有者。可对数据库和其对象执行所有管理工作，此角色的权限可包括以下其他角色的权限。db_accessadmin可新建和删除Windows2000组、Windows2000用户和数据库用户。db_datareader可看到数据库所有用户创建的表内的数据。db_datawriter可新建、修改和删除数据库中的所有用户创建的表数据。db_ddladmin可新建、修改和删除数据库中对象。db_securityadmin可管理数据库内的权限控制，如管理数据库的角色和角色内的成员，管理对数据库对象的访问控制。db_denydatareader看不到数据库内任何数据。db_denydatawriter无法更改数据库内的任何数据。public每个数据库用户是public角色的成员之一。当用户被许可访问数据库时，用户将自动地变成public角色的的一个成员。16.3.3管理数据库角色——创建角色已经有了固定数据库角色，为什么还要建立角色？因为有数据库用户，就要对用户权限进行管理，将用户加入到特定数据库角色方便管理用户权限。有特定的需求，需要对数据库用户进行分组，具有相同权限的用户一组。创建数据库角色

CREATEROLErole_name[AUTHORIZATIONowner_name]role_name将要创建的角色的名称owner_name该角色拥有者的名字，默认为dbo。其中owner_name必须是当前数据库里已有用户或角色（固定数据库角色）在新角色中添加数据库用户EXECsp_addrolemember‘角色名’,’数据库用户名’16.3.3管理数据库角色——查看、删除角色查看数据库角色信息查询系统函数IS_MUMBER判断当前数据库用户是否属于某个数据库用户SELECTIS_MEMBER(‘db_owner’)删除数据库角色SQL不允许删除含有成员的角色。所以，必须先删除角色下的所有用户，才能删除角色先将数据库用户从角色集合中去除EXECsp_droprolemember‘角色名’,’数据库用户名’DROPROLE角色名16.3.4应用程序角色——概念

应用程序角色是用户定义数据库角色的一种形式，与固定数据库角色不同。仅允许特定用户来访问数据库中的特定数据，不使用这些特定连接，无法访问这些数据，从而实现安全管理的目的。例如，管理员允许雇员使用雇员处理程序录入新员工、离职员工和打印统计报表等。三个特点：默认情况下，应用程序角色不包含任何成员应用程序角色必须激活才能发挥作用应用程序角色有密码，拥有密码的用户才能激活该角色16.3.4应用程序角色——操作创建应用程序角色CREATEAPPLICATIONROLEapproleWITHPASSWORD=‘password’激活应用程序角色EXECsp_setapprole‘approle’,’password’解除被激活的应用程序角色EXECsp_unsetapprole‘approle’删除应用程序角色DROPAPPLICATIONROLE‘approle’16.4权限权限是执行操作、访问数据的通行证。主体只有拥有对某安全对象的指定权限，才能对该对象执行相应的操作。安全对象权限主体例如：安全对象是表，对表的操作包括增删改查等四个权限。16.4.1权限概述预先定义权限和预先未定义权限预先定义权限是在系统安装之后，不必通过授予即拥有的权限。预先未定义的权限是指那些需要经过授权或继承才能得到的权限。针对所有对象的权限和针对特殊对象的权限。针对所有对象的权限：可以针对SQLServer系统中所有的对象，例如，CONTROL权限是所有对象都有的权限，表示拥有对象所定义的所有权限。针对特殊对象的权限：是指某些权限只能在指定的对象上起作用，例如INSERT可以是表的权限，但是不能是存储过程的权限，而EXECUTE可以是存储过程的权限，但是不能是表的权限。安全对象很多种，权限也不同安全对象的常用权限对象可以被授权或拒绝的操作数据表SELECT、UPDATE、DELETE、INSERT、REFERENCE列SELECT、UPDATE视图SELECT、UPDATE、INSERT、DELETE存储过程EXECUTE16.4.2管理权限执行权限管理操作包括1．授予权限

GRANT2．撤销权限REVOKE3．拒绝权限DENY安全主体获得权限的方式第一种：直接使用GRANT语句为其授予权限。第二种：通过作为角色成员继承角色的权限，使用REVOKE语句只能删除安全主体通过GRANT得到的权限，要想彻底删除安全主体的特定权限必须使用DENY语句。用户和角色的权限以记录的形式存储在各个数据库的sysprotects系统表中。16.4.2管理权限——授予权限为哪个安全对象授予什么权限，可以转授给哪个用户

GRANT语法格式：

GRANT{ALL|statement[,...n]}TOsecurity_account[,...n]授予对数据库对象操作的权限。语法格式：

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.4.3管理对SQLServer实例和数据库的访问

控制登录操作（为用户授予权限）例：为登录名Marylogin授予查看数据库状态的权限使用GRANT命令GRANTVIEWSERVERSTATETOMarylogin为数据库角色授予权限例：如为数据库角色Auditorsrole授予BACKUPDATABASE(备份数据库)权限：使用GRANT命令：GRANTBACKUPDATABASETOAuditorsrole16.4.4管理对表和列的访问——表权限更改对表的访问：表的常用权限权

限描

述ALTER可以更改表属性CONTROL提供所有权之类的权限DELETE可以从表中删除行INSERT可以向表中插入行REFERENCES可以通过外键引用其他表SELECT可以在表中选择行TAKEOWNERSHIP可以取得表的所有权UPDATE可以在表中更新行VIEWDEFINITION可以访问表的元数据16.4.4管理对表和列的访问——表权限授权数据库用户或角色对表的访问使用GRANT语句授权数据库用户或者角色对表的访问。如授予用户Peteruse对表Adminschema.Student的SELECT、INSERT和UPDATE权限：GRANTSELECT,INSERT,UPDATE

ONAdminschema.StudentTOPeteruse;清除之前授予的表的访权限如清除之前授予用户Peteruse对表student的查询权：

REVOKESELECTONAdminschema.StudentTOPeteruse16.4.4管理对表和列的访问——列权限更改对列的访问：列的常用权限权

限描

述SELECT可以选择列UPDATE可以更新列REFERENCE可以通过外键引用列16.4.4管理对表和列的访问——列权限例如为Peteruse用户授予了在表Adminschema.Student的StudentDate列上SELECT和UPDATE的权限。

GRANTSELECT,UPDATE(StudentDate)ONAdminschema.StudentTOPeteruse取消对列的访问授权，可在用户或角色的属性窗口设置也可使用REVOKE语句来实现，如:REVOKEUPDATE(StudentDate)ONAdminschema.StudentTOPeteruse16.4.5管理对可编程对象的访问—存储过程可编程对象安全性包括：存储过程和用户定义函数存储过程权限使用命令GRANT，如为数据库用户Peteruse授予存储过程dbo.uspGetBillOfMaterials的EXECUTE权限：GRANTEXECUTEOndbo.uspGetBillOfMaterialsTOpeteruseALTER可以更改存储过程属性CONTROL可以提供所有权之类的权限EXECUTE可以执行存储过程TAKEOWNERSHIP可以取得存储过程的所有权VIEWDEFINITION可以查看存储过程的元数据16.4.5管理对可编程对象的访问—函数用户定义函数权限：包括标量函数和表值函数ALTER可以更改函数属性CONTROL可以提供所有权之类的权限TAKEOWNERSHIP可以取得函数的所有权VIEWDEFINITION可以查看函数的元数据EXECUTE可以执行用户定义函数(只对标量函数有效