数据库应用基础（SQL Server 2019）（AIGC赋能版） 课件 项目9 系统安全管理

项目9系统安全管理《数据库应用基础（SQLServer2019）》项目目标掌握设置身份验证模式的方法掌握创建和管理用户账户的方法掌握角色管理的方法掌握数据库权限管理的方法掌握创建和使用架构的方法本章目录9.1设置身份验证模式9.2登录账户管理9.3数据库用户管理9.4角色管理9.5数据库权限管理9.6架构管理设置身份验证模式9.1任务9.1设置身份验证模式当用户连接SQLServer服务器时，首先需要对用户进行身份验证，以确定用户是否具有连接SQLServer服务器的权限。如果身份验证成功，则允许用户连接到SQLServer服务器，否则拒绝连接。通过本任务将掌握SQLServer2019中的两种身份验证模式及其设置方法。任务目标理解SQLServer身份验证模式掌握设置身份验证模式的方法理解SQLServer安全机制9.1.1理解身份验证模式身份验证模式SQLServer验证客户端与服务器连接的方式两种验证模式Windows身份验证模式：使用Windows操作系统信息验证账户名和密码采用Kerberos安全协议，支持密码策略强制、账户锁定、密码过期混合模式：允许使用Windows身份验证或SQLServer身份验证连接为向后兼容而提供，建议尽可能使用Windows身份验证若必须使用SQLServer身份验证，需为所有账户设置强密码（特别是sa账户）9.1.2设置服务器身份验证模式（1/2）使用SSMS设置连接数据库引擎实例右键单击实例→“属性”选择“安全性”页→“服务器身份验证”选择“Windows身份验证模式”或“SQLServer和Windows身份验证模式”若sa密码为空，则设置sa密码更改后需要重新启动服务若启用混合模式，需手动启用sa登录账户：

ALTERLOGINsaENABLE;9.1.2设置服务器身份验证模式（1/2）使用注册表存储过程设置：xp_instance_regwriteN'HKEY_LOCAL_MACHINE',N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer','LoginMode',N'REG_DWORD',mode;mode=1：Windows身份验证模式mode=2：混合验证模式注意：sa登录名是服务器级主体，属于sysadmin固定服务器角色，拥有所有权限且无法删除，但可以禁用。9.1.3SQLServer安全机制安全性主体级别关键点：登录名→连接服务器数据库用户→访问数据库角色→赋予权限架构→组织对象，用户只能操作所属架构中的对象安全层次：登录名→数据库用户→角色→架构→对象级别安全对象说明服务器级别登录名、固定服务器角色登录名用于连接服务器（Windows登录名或SQLServer登录名）；固定服务器角色赋予登录名服务器权限数据库级别用户、角色、证书、密钥、程序集、架构等用户用于访问数据库；只有登录名没有对应数据库用户则无法访问数据库；默认具有public角色架构级别表、视图、函数、存储过程、数据类型默认架构为dbo（所有用户的默认架构，不可删除）；用户只能操作所属架构中的对象，具体权限由数据库角色决定登录账户管理9.2任务9.2登录账户管理登录账户即登录名，它是控制访问SQLServer服务器的账户，分为Windows账户和SQLServer账户。如果事先没有创建有效的登录账户，便不能连接和登录SQLServer服务器，也就不能访问数据库。通过本任务将掌握创建和管理登录账户的方法。任务目标掌握创建登录账户的方法掌握修改登录账户的方法掌握删除登录账户的方法9.2.1创建登录账户（1/2）登录账户类型：Windows登录账户：基于Windows主体SQLServer登录账户：保存在SQLServer中使用SSMS创建登录账户展开“安全性”→右键单击“登录名”→“新建登录名”输入登录名（或通过“搜索”选择Windows用户/组）选择身份验证类型：Windows身份验证：基于Windows主体SQLServer身份验证：输入密码，可设置强制密码策略、密码过期、用户下次登录必须更改密码可选项：映射到证书、指定默认数据库（默认master）、默认语言单击“确定”9.2.1创建登录账户（2/2）使用T-SQL创建登录账户：--Windows登录账户CREATELOGIN[域\用户名]FROMWINDOWSWITHDEFAULT_DATABASE=数据库名;--SQLServer登录账户CREATELOGIN登录名WITHPASSWORD='密码',DEFAULT_DATABASE=数据库名;新登录名保存在master数据库的syslogins表中例9.1：创建登录账户Windows登录账户AAA\AndySQLServer登录账户Mary默认数据库均为教务管理9.2.2修改登录账户使用SSMS修改右键单击登录名→“属性”→修改密码、默认数据库、启用/禁用等使用T-SQL修改（ALTERLOGIN）：--禁用登录账户ALTERLOGINMaryDISABLE;--启用登录账户ALTERLOGINMaryENABLE;--更改密码ALTERLOGINMaryWITHPASSWORD='新密码';--重命名登录名ALTERLOGINMaryWITHNAME=Rose;--更改默认数据库ALTERLOGIN[ABC\Andy]WITHDEFAULT_DATABASE=master;9.2.3删除登录账户使用SSMS删除：展开“安全性”→“登录名”→右键单击登录名→“删除”→确认使用T-SQL删除（DROPLOGIN）：

DROPLOGIN登录名;示例

DROPLOGINMary;注意删除登录账户前应确认该账户不再使用避免影响依赖该登录名的数据库用户和权限数据库用户管理9.3任务9.3数据库用户管理用户是数据库级别安全主体。登录名必须映射到数据库用户才能连接到数据库。一个登录名可以作为不同用户映射到不同的数据库，但在每个数据库中只能作为一个用户进行映射。在部分包含数据库中，也可以创建不具有登录名的用户。如果在数据库中启用了guest用户，未映射到数据库用户的登录名可以作为guest用户进入该数据库。通过本任务将学习和掌握创建和删除数据库用户的方法。任务目标掌握创建数据库用户的方法掌握删除数据库用户的方法9.3.1创建数据库用户（1/2）用户与登录名的关系登录名（服务器级）必须映射到数据库用户才能连接数据库一个登录名可在不同数据库映射为不同用户，但每个数据库中只能映射为一个用户未映射的登录名可通过guest用户进入数据库（若启用）部分包含数据库中可创建无登录名的用户使用SSMS创建数据库用户展开数据库→“安全性”→右键单击“用户”→“新建用户”选择用户类型（Windows用户、带登录名的SQL用户）填写：用户名、登录名、默认架构单击“确定”9.3.1创建数据库用户（2/2）使用T-SQL创建用户（CREATEUSER）CREATEUSER用户名[FOR|FROM]{LOGIN登录名|WITHOUTLOGIN][WITHDEFAULT_SCHEMA=架构名]用户名：数据库内唯一标识，最多128字符LOGIN登录名：必须为服务器中有效登录名WITHOUTLOGIN：创建不映射到登录名的用户DEFAULT_SCHEMA：默认架构，未指定则使用dbo例9.2：在教务管理数据库中创建用户Mary（映射SQL登录名）Andy（映射Windows登录名）默认架构均为dbo，并查询用户列表9.3.2删除数据库用户使用SSMS删除展开数据库→“安全性”→“用户”右键单击要删除的用户→“删除”在“删除对象”对话框中单击“确定”使用T-SQL删除（DROPUSER）： DROPUSER[IFEXISTS]用户名IFEXISTS：SQLServer2019新增，仅当用户存在时才删除，避免错误例9.3：在教务管理数据库中检查用户Anna是否存在，若存在则删除USE教务管理;GODROPUSERIFEXISTSAnna;GO角色管理9.4任务9.4角色管理为了方便权限管理，可以将一些安全账户集中到一个单元中并对该单元设定权限，这样的单元称为角色。当安全账户成为角色成员时权限就会自动生效。在SQLServer中，角色主要包括固定服务器角色、数据库角色和应用程序角色，而数据库角色又分为固定数据库角色和自定义数据库角色。通过本任务将学习和掌握管理角色的方法。任务目标掌握管理固定服务器角色的方法掌握管理固定数据库角色的方法掌握管理自定义数据库角色的方法9.4.1管理固定服务器角色（1/2）固定服务器角色服务器级别，独立于数据库，不能添加、删除或更改每个成员可向角色添加其他登录名常用固定服务器角色权限角色名称权限sysadmin在服务器中执行任何活动securityadmin管理登录账户、权限，重置密码serveradmin配置和关闭服务器setupadmin添加/删除链接服务器processadmin终止运行中的进程diskadmin管理磁盘文件dbcreator创建、更改、删除、还原任何数据库bulkadmin运行BULKINSERT语句public查看任何数据库（每个登录名自动属于该角色）9.4.1管理固定服务器角色（2/2）添加成员

ALTERSERVERROLE角色名ADDMEMBER登录名删除成员

ALTERSERVERROLE角色名DROPMEMBER登录名例9.4将[AAA\Andy]添加到sysadmin，将Mary添加到dbcreator例9.5：从sysadmin删除[AAA\Andy]，从dbcreator删除Mary9.4.2管理固定数据库角色（1/2）固定数据库角色每个数据库自动包含，不能修改权限常用固定数据库角色权限角色名称权限db_owner执行数据库所有配置和维护，可删除数据库db_securityadmin修改角色成员身份和管理权限db_accessadmin添加/删除数据库访问权限db_backupoperator备份数据库db_ddladmin运行任何DDL命令db_datatwriter在所有用户表中添加/删除/更改数据db_datareader从所有用户表读取所有数据db_denydatawriter不能添加/修改/删除任何用户表数据db_denydatareader不能读取任何用户表数据public特殊角色，每个数据库用户自动属于它，不能删除9.4.2管理固定数据库角色（2/2）添加成员sp_addrolemember'角色名','安全账户'例9.6：将Andy添加到db_owner，将Mary添加到db_securityadmin9.4.3管理自定义数据库角色（1/2）用途当固定数据库角色权限不满足需求时，创建自定义角色使用SSMS创建展开数据库→“安全性”→“角色”→右键“数据库角色”→“新建数据库角色”指定角色名称、所有者（默认dbo）、拥有的架构单击“添加”将数据库用户加入角色使用T-SQL创建CREATEROLE角色名称[AUTHORIZATION所有者名称]所有者可以是数据库用户或角色，默认为执行者9.4.3管理自定义数据库角色（2/2）添加成员

EXECsp_addrolemember'角色名','用户名'删除自定义数据库角色：SSMS：右键角色→“删除”T-SQL：DROPROLE[IFEXISTS]角色名称限制：不能删除拥有安全对象或成员的角色，需先转移所有权或删除成员例9.7：创建角色MyDBRole（所有者为dbo），将Andy和Mary加入该角色数据库权限管理9.5任务9.5数据库权限管理数据库权限指明用户能够获得对哪些数据库对象的使用权，以及能够对哪些数据库对象执行何种操作。用户要在数据库中执行某种操作，则需要被授予相应的权限。如果不具备所需要的权限，则不能执行相应的操作。通过本任务将学习和掌握对用户授予、撤销和拒绝数据库权限的方法。任务目标掌握授予权限的方法掌握拒绝权限的方法掌握撤销权限的方法9.5.1授予权限（1/2）权限类型隐含权限：通过固定数据库角色自动继承（如db_owner成员自动拥有所有数据库操作权限）对象权限：需显式授予，适用于特定对象（表、视图、存储过程等）常用权限安全对象常用权限数据库BACKUPDATABASE,CREATETABLE,CREATEVIEW,CREATEPROCEDURE等表SELECT,INSERT,UPDATE,DELETE,REFERENCES视图SELECT,INSERT,UPDATE,DELETE,REFERENCES存储过程EXECUTE标量函数EXECUTE,REFERENCES9.5.1授予权限（2/2）使用SSMS授予权限：数据库权限：右键数据库→“属性”→“权限”页→勾选“授予”对象权限：右键对象→“属性”→“权限”页→勾选“授予”使用T-SQL授予权限（GRANT）：GRANT权限[(列[,...])][ON安全对象]TO主体[,...][WITHGRANTOPTION][AS主体]WITHGRANTOPTION：允许主体将权限再授予其他主体例9.8：授予用户Andy创建表的权限GRANTCREATETABLETOAndy;例9.9：授予Andy和Mary对学生表的SELECT权限；授予Andy创建视图权限并允许其授予他人GRANTCREATEVIEWTOAndyWITHGRANTOPTION;9.5.2拒绝权限作用明确禁止主体执行操作，且主体不能通过角色或组继承该权限使用SSMS在权限页中勾选“拒绝”复选框使用T-SQL（DENY）：DENY{ALL|权限[(列[,...])]}[ON安全对象]TO主体[,...][CASCADE][AS主体]CASCADE：同时拒绝授予该权限的其他主体例9.10：拒绝Andy创建表的权限DENYCREATETABLETOAndy;例9.11：拒绝Mary对教师表的SELECT、INSERT、UPDATE、DELETE权限9.5.3撤销权限作用：取消以前授予或拒绝的权限（移除GRANT或DENY）使用SSMS：在权限页中取消“授予”或“拒绝”复选框的勾选使用T-SQL（REVOKE）：REVOKE[GRANTOPTIONFOR]{ALL|权限[(列[,...])]}[ON安全对象][TO|FROM]主体[,...][CASCADE][AS主体]GRANTOPTIONFOR：仅撤销授予权限的能力，不撤销权限本身例9.12：撤销Andy创建表的权限

REVOKECREATETABLEFROMAndy;例9.13：撤销Mary对成绩表的UPDATE权限REVOKEUPDATEON成绩FROMMary;架构管理9.6任务9.6架构管理在SQLServer2019中，数据库中的所有对象都定位在架构中，不归各个用户所有。每个架构可以归角色所有，允许多个用户管理数据库对象。现在只需要针对架构调整所有权，不针对每个对象。一个架构只能有一个所有者，所有者可以是数据库用户、数据库角色等。架构的所有者可以访问架构中的对象，并且可以对其他用户授予访问该架构的权限。通过本任务将学习和掌握创建、修改和删除架构的方法。任务目标掌握创建架构的方法掌握修改架构的方法掌握删除架构的方法9.6.1