SQL Server 2012数据库原理与应用第十一讲 数据库安全性管理

第十一讲数据库安全性管理教学内容SQLServer安全机制Windows身份验证模式SQLServer身份验证模式数据库登录账号和角色管理登录账号数据库用户dbo和guest用户管理数据库用户角色服务器角色数据库角色权限对象权限语句权限11.1SQLServer的帐号和存取权限数据的安全性是指保护数据以防止因不合理的使用而造成数据的泄密和破坏在数据库管理系统中使用检查口令等手段来检查用户身份，通过检查的用户才能进入数据库系统中当用户对数据库执行操作时，系统自动检查用户是否有权限执行这些操作11.1.1SQLServer的验证模式两种验证模式Windows验证模式混合验证模式SQLServer对用户访问检验的两个阶段（1）验证阶段（Authentication）用户在SQLServer上获得对任何数据库的访问权限之前，必须合法登录SQLServerSQLServer或Windows身份验证对用户进行验证验证通过，用户连接到SQLServer，保证了系统安全（2）许可确认阶段（PermissionValidation）用户验证通过后，登录到SQLServer上系统检查用户是否有访问服务器上数据的权限ManagementStudio→属性→安全性（3）设置验证模式11.2帐号和角色在SQLServer中，两种帐号登录服务器的登录帐号（loginname）使用数据库的用户帐号（username）登录帐号指能登录到SQLServer的帐号，属于服务器层面本身不能让用户访问服务器中的数据库登录者必须使用用户帐号才能存取数据库服务器中的数据库如同公司门口先刷卡进入（登录服务器），然后再拿钥匙打开自己的办公室（进入数据库）一样用户帐号用户在特定的数据库内创建关联一个登录名在创建一个用户时，必须关联一个登录名注意事项用户信息存放在数据库的sysusers表中用户没有密码同它相关联通过授权指定用户可以访问的数据库对象权限11.2.1服务器的登录帐号使用ManagementStudio查看登录帐号默认登录帐号NTAuthority\SystemWindows操作系统中NTAuthority\System的帐号都允许登录SQLServersa超级管理员帐号允许SQLServer的系统管理员登录此SQLServer的管理员不一定是Windows操作系统的管理员使用存储过程查看登录帐号语法SP_HelpLogins功能查看有关每个数据库中的登录及相关用户的信息示例ExecSP_HelpLogins11.2.2角色角色（role）是一组用户所构成的组可分为服务器角色与数据库角色服务器角色负责管理与维护SQLServer的组一般指定管理服务器的登录帐号属于服务器角色固定的服务器角色

sysadmin（SystemAdministrators）可以在SQLServer中执行任何活动Serveradmin（ServerAdministrators）可以设置服务器范围的配置选项，关闭服务器Setupadmin（SetupAdministrators）可以管理链接服务器和启动过程Securityadmin（SecurityAdministrators）可以管理登录和创建数据库的权限可以读取错误日志和更改密码固定的服务器角色Processadmin（ProcessAdministrators）可以管理在SQLServer中运行的进程Dbcreator（DatabaseCreators）可以创建、更改和除去数据库Diskadmin（DiskAdministrators）可以管理磁盘文件Bulkadmin（BulkInsertAdministrators）可以执行BULKINSERT（大容量插入）语句注意属于Windows操作系统Administrators组的帐号被自动设置为sysadmin服务器角色使用ManagementStudio创建登录帐号SSMS→安全性→登录→右键→新建登录服务器角色使用存储过程创建一个登录帐号语法sp_addlogin[@loginame=]'login'[,[@passwd=]'password'][,[@defdb=]'database']功能创建登录帐号示例execsp_addlogin'Teacher'execsp_addlogin'Student','swfcxs','Master'将登录帐号加入服务器角色语法sp_addsrvrolemember'登录帐号','服务器角色名称'功能将登录帐号加入服务器的角色示例EXECsp_addsrvrolemember'Teacher','dbcreator'更改登录帐号密码语法sp_password'旧密码','新密码','登录帐号'示例Execsp_password'swfcxs','swfc','Student'忘记密码→重置新密码SSMS→安全性→登录→双击用户→更改密码Execsp_passwordnull,'swfcjs','teacher'删除登录使用SSMSSSMS→安全性→登录→选择帐号→右键删除使用存储过程删除登录语法sp_droplogin'登录帐号'示例EXECsp_droplogin'Teacher'数据库的用户一个SQLServer的登录帐号只有成为该数据库的用户时，对该数据库才有访问权限在安装SQLServer后，默认数据库master、tempdb、msdb等包含两个用户：dbo和guest任何一个登录帐号可以通过guest用户帐号存取相应的数据库当新建一个数据库时，默认只有dbo用户帐号而没有guest用户帐号数据库的用户每个登录帐号在一个数据库中只能有一个用户帐号每个登录帐号可以在不同的数据库中各有一个用户帐号新建登录帐号时指定对某个数据库具有存取权限，则在该数据库中将自动创建一个与该登录帐号同名的用户帐号用户帐号master、tempdb中的guest用户帐号不能删除其他数据库中的guest用户帐号可以删除登录帐号具有对某个数据库的访问权限，并不表示对该数据库具有存取的权限设置用户帐号的权限可对数据库的对象进行插入、更新等操作使用SSMS查看用户SSMS→数据库→安全性→用户利用存储过程查看用户语法SP_HelpUser功能查看用户和数据库角色的信息示例ExecSP_HelpUser使用SSMS创建数据库的用户SSMS→数据库→用户→右键新建数据库用户使用存储过程创建数据库的用户语法sp_adduser'登录帐号',['用户帐号'],['数据库角色']功能创建数据库用户并添加数据库角色示例EXECsp_adduser'Student','Stud','db_owner'11.3权限11.3.1对象权限11.3.2语句权限11.3.3暗示性权限11.3.1对象权限对象权限是执行与表、视图和存储过程等对象有关的行为的权限数据库对象的所有者将对象权限授予指定的数据库用户表和视图INSERT、UPDATE、DELETESELECTREFERENCESALL存储过程所有者可以授予其执行（EXECUTE）权限11.3.2语句权限语句权限是对用于创建数据库和数据库中的对象（如表、视图和存储过程等）的T-SQL语句所拥有的权限语句权限语句Createdatabase：创建数据库Createtable：创建表Createview：创建视图Createrule：创建规则Createdefault：创建缺省Createprocedure：创建存储过程Createindex：创建索引Backupdatabase：备份数据库Backuplog：备份事务日志11.3.3暗示性权限指由SQLServer预定义的服务器角色、数据库的所有者和数据库对象所有者所拥有的权限sysadmin固定服务器角色成员可以在服务器范围内从事所有的操作活动数据库所有者dbo可在数据库范围内进行任何操作数据库对象的所有者可对拥有的对象执行一切活动设置数据库用户帐号的权限SSMS→用户帐号→鼠标右键属性→安全对象设置数据库用户帐号的权限数据库对象的操作SELECT：对表、视图的查询INSERT：在表、视图中插入记录UPDATE：对表、视图中的数据修改DELETE：删除表、视图中的数据EXEC：执行存储过程DRI：DeclarativeReferentialIntegrity，可对表的外键加上限制，以达成表的参照完整性权限设置——操作复选框对某个数据库对象而言，如果选中对应的复选框，则表示具有对该对象进行相应操作的权限。复选框的3种状态空白：表示未指定权限，或权限还原到未设置状态√：表示具有该权限×：表示不具有该权限11.3.2对象权限用户权限设置语句权限对象权限两种权限设置途径面向单一用户面向数据库对象T-SQL语句使用grant、revoke和deny三种命令来完成权限的授予、禁止和剥夺授予权限GRANT语法GRANT权限名称[,…n]ON表名|视图名|存储过程名TO用户账号示例GrantSELECTONStudInfoToTeacher,StudGrantSelect,InsertONClassInfoToStudGrantSELECT,UPDATEONStudInfo(StudName,StudSex)ToTeacher回收权限Revoke语法REVOKE权限名称[,…n]ON表名|视图名|存储过程名FROM用户账号示例RevokeInsertOnClassInfoFromStud禁止权限DENY语法DENY权限名称[,…n]ON表名|视图名|存储过程名TO用户账号示例DENYSELECTONClassInfoTOStud11.3.3语句权限使用GRANT、DENY和REVOKE语句完成语句权限的授予、禁止和剥夺语法GRANT语句名称[,…n]TO用户账号[,…n]DENY语句名称[,…n]TO用户账号[,…n]REVOKE语句名称[,…n]FROM用户账号[,…n]权限示例授予创表权限GRANTCREATETABLETOTeacher拒绝用户账号Teacher使用CREATEVIEW语句DENYCREATEVIEWTOTeacher删除一个DENY权限REVOKEALLFROMTeacher删除数据库用户帐号使用SSMSSSMS→数据库→用户→鼠标右键→删除使用存储过程格式EXECsp_dropuser'用户帐号'示例EXECsp_dropuser'Teacher'数据库角色角色是一个强大的工具，可以将用户集中到一个单元中，然后对该单元应用权限对一个角色授予、拒绝或废除的权限也适用于该角色的任何成员和登录帐号类似，用户帐号也可以分成组，称为数据库角色（DatabaseRoles）数据库角色应用于单个数据库数据库角色分类标准角色由数据库成员所组成的组数据库成员可以是用户或者其他的数据库角色应用程序角色用来控制应用程序存取数据库的本身并不包括任何成员10个固定的标准角色public是最基本的数据库角色db_owner在数据库中有全部权限db_accessadmin可以添加或删除用户IDdb_securityadmin可以管理全部权限、对象所有权、角色和角色成员db_ddladmin可以发出ALLDDL，但不能发出GRANT、REVOKE或DENY语句10个固定的标准角色db_backupoperator可以发出DBCC、CHECKPOINT和BACKUP语句db_datareader可以选择数据库内任何用户表中所有数据db_datawriter可以更改数据库内任何用户表中的所有数据db_denydatareader不能选择数据库内任何用户表中的任何数据db_denydatawriter不能更改数据库内任何用户表中的任何数据创建角色使用SSMS创建新的角色SSMS→数据库→角色→鼠标右键→新建角色使用存储过程创建新的角色语法sp_addrole'角色名','拥有者'sp_addrolemember'角色名','拥有者'示例EXECsp_addrole'Stud_Role','Stud'EXECsp_addrole'Tea_Role'EXECsp_addrolemember'Tea_Role','Teacher'授权角色GrantSelect,UpdateOnStudInfoToStud_RoleGrantCreateTable,CreateViewToStud_RoleEXECsp_addrolemember'Stud_Role','Stud'删除角色使用SSMS删除角色SSMS→数据库→角色→鼠标右键→删除使用存储过程删除角色语法sp_droprole'角色名'示例EXECsp_dr