《数据库应用基础（SQL Server 2016）》项目9 系统安全管理

项目9系统安全管理掌握设置身份验证模式的方法掌握创建和管理用户账户的方法掌握角色管理的方法掌握数据库权限管理的方法掌握创建和使用架构的方法项目目标9.1.1理解身份验证模式1.Windows身份验证模式当用户通过MicrosoftWindows用户账户连接时，SQLServer使用Windows操作系统中的信息验证账户名和密码。Windows身份验证使用Kerberos安全协议，通过强密码的复杂性验证提供密码策略强制，提供账户锁定支持，并且支持密码过期。2.混合模式混合模式是指允许用户使用Windows身份验证或SQLServer身份验证进行连接。通过Windows用户账户连接的用户可以使用Windows验证的受信任连接。如果必须选择混合模式身份验证并要求使用SQLServer登录以适应旧式应用程序，则必须为所有SQLServer账户设置强密码。这对于属于sysadmin角色的账户（特别是sa账户）尤其重要。提供SQLServer身份验证只是为了向后兼容。建议尽可能使用Windows身份验证。任务9.1设置身份验证模式9.1.2设置服务器身份验证模式1.使用SSMS设置身份验证模式：在对象资源管理器中右键单击SQLServer数据库引擎实例，择“属性”。在“服务器属性-<实例名>”对话框中选择“安全性”页，选择下列模式之一：若要使用Windows身份验证对所尝试的连接进行验证，则选择“Windows身份验证模式”选项。若要使用混合模式的身份验证对所尝试的连接进行验证，则选择“SQLServer和Windows身份验证模式”选项。2.使用扩展存储过程xp_instance_regwrite来设置服务器身份验证模式：xp_instance_regwriteN'HKEY_LOCAL_MACHINE',N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer','LoginMode',N'REG_DWORD',mode;任务9.1设置身份验证模式9.1.3SQLServer安全机制1.服务器级别在服务器级别上，安全对象主要包括登录名和固定服务器角色等，其中登录名用于连接和登录数据库服务器，固定服务器角色则用于对登录名赋予所需的服务器权限。2.数据库级别在数据库级别上，安全对象主要包括用户、角色、证书、对称密钥、非对称密钥、程序集、全文目录、DDL事件以及架构等。3.架构级别在架构级别上，安全对象主要包括表、视图、函数、存储过程以及数据类型等。创建这些对象时可以设置架构。如果未设置，则默认架构为dbo。dbo架构是所有用户的默认架构，除非指定了其他某个架构。dbo架构是无法删除的。数据库用户只能对属于其架构中的对象执行操作，具体的操作权限则由数据库角色决定。任务9.1设置身份验证模式9.2.1创建登录账户1.使用SSMS创建登录账户在对象资源管理中依次展开数据库引擎实例实例和“安全性”，右键单击“登录名”，选择“新建登录名”。在“登录名-新建”对话框中，对相关选项进行设置。单击“确定”按钮。2.使用SQL语句创建登录账户要基于Windows主体创建登录账户：CREATELOGIN[域名\用户名]FROMWINDOWSWITHDEFAULT_DATABASE=默认数据库;创建保存在SQLServer数据库中的登录名：CREATELOGIN登录名WITHPASSWORD='密码'DEFAULT_DATABASE=默认数据库;任务9.2登录账户管理9.2.3删除登录账户使用SSMS删除登录账户：（1）在对象资源管理器中展开数据库引擎实例，依次展开“安全性”和“登录名”。（2）右键单击要删除的登录账户，选择“删除”。（3）在“删除对象”对话框中，单击“确定”。使用DROPLOGIN语句删除登录名：DROPLOGIN登录名任务9.2登录账户管理9.3.1创建数据库用户使用SSMS创建数据库用户（1）在对象资源管理器中展开SQLServer数据库引擎实例该实例，展开“数据库”，展开要在其中创建用户的数据库。（2）展开“安全性”，右键单击“用户”，然后选择“新建用户”。（3）在“数据库用户-新建”对话框中，选择用户类型，填写用户名、登录名和默认架构。（4）单击“确定”按钮。2.使用Transact-SQL语句创建数据库用户CREATEUSER用户名[{FOR|FROM}{LOGIN登录名}

｜WITHOUTLOGIN][WITHDEFAULT_SCHEMA=架构名]任务9.3数据库用户管理9.3.2删除数据库用户使用SSMS删除数据库用户：（1）在对象资源管理器中依次展开用户所在的数据库、“安全性”和“用户”。（2）右键单击要删除的用户，然后选择“删除”。（3）在“删除对象”对话框中，单击“确定”按钮。使用DROPUSER语句可以从当前数据库中删除用户：DROPUSER[IFEXISTS]用户名任务9.3数据库用户管理9.4.1管理固定服务器角色1.固定服务器角色的权限任务9.4角色管理9.4.1管理固定服务器角色2.添加固定服务器角色成员使用SSMS添加固定服务器角色成员：（1）在对象资源管理器中，右键单击登录账户并选择“属性”。（2）在“登录属性”对话框中选择“服务器角色”页，在“服务器角色”列表中勾选相应的复选框。（3）单击“确定”按钮。使用ALTERSERVERROLE语句向固定服务器角色添加登录账户：ALTERSERVERROLE服务器角色名ADDMEMBER服务器主体3.删除固定服务器角色成员使用SSMS删除固定服务器成员：（1）在对象资源管理器中展开数据库引擎实例，展开“安全性”和“服务器角色”。（2）右键单击要从中删除成员的服务器角色，选择“属性”。（3）在“服务器角色属性”对话框中单击要删除的成员，然后单击“删除”按钮。使用ALTERSERVERROLE语句从固定服务器角色删除成员：ALTERSERVERROLE服务器角色名DROPMEMBER服务器主体任务9.4角色管理9.4.2管理固定数据库角色1.固定数据库角色的权限任务9.4角色管理9.4.2管理固定数据库角色2.添加固定数据库角色成员使用SSMS添加固定数据库角色：（1）在对象资源管理器中展开数据库，展开“安全性”和“用户”。（2）右键单击数据库用户，然后选择“属性”命令。（3）在如图9.8所示的“数据库用户”对话框中，选择“成员身份”页，然后在“角色成员”列表框中勾选一个或多个固定数据库角色。（4）单击“确定”按钮。使用sp_addrolemember系统存储过程添加固定数据库角色成员：sp_addrolemember'角色名','安全账户'任务9.4角色管理9.4.3管理自定义数据库角色1.使用SSMS创建数据库角色（1）在对象资源管理器中展开要在其中创建数据库角色的数据库，依次展开“安全性”和“角色”。（2）右键单击“数据库角色”，然后选择“新建数据库角色”。（3）在“数据库角色-新建”对话框的“常规”页中，指定数据库角色名称和所有者。（4）单击“添加”按钮。（5）单击“确定”按钮。2.使用Transact-SQL语句创建数据库角色CREATEROLE角色名称[AUTHORIZATION所有者名称]任务9.4角色管理9.4.3管理自定义数据库角色1.使用SSMS创建数据库角色（1）在对象资源管理器中展开要在其中创建数据库角色的数据库，依次展开“安全性”和“角色”。（2）右键单击“数据库角色”，然后选择“新建数据库角色”。（3）在“数据库角色-新建”对话框的“常规”页中，指定数据库角色名称和所有者。（4）单击“添加”按钮。（5）单击“确定”按钮。2.使用Transact-SQL语句创建数据库角色CREATEROLE角色名称[AUTHORIZATION所有者名称]任务9.4角色管理3.删除自定义数据库角色使用SSMS删除自定义数据库角色：（1）在对象资源管理器中展开数据库，展开“安全性”、“角色”和“数据库角色”。（2）右键单击要删除的数据库角色，然后选择“删除”。（3）在“删除对象”对话框中，单击“确定”按钮。使用DROPROLE语句从数据库中删除角色：DROPROLE[IFEXISTS]角色名称任务9.4角色管理9.5.1授予权限任务9.5数据库权限管理9.5.1授予权限1.使用SSMS授予权限使用SSMS授予数据库权限：（1）在对象资源管理器中右键单击要设置权限的数据库，选择“属性”。（2）在“数据库属性”对话框中，选择“权限”页，选择要授予权限的用户或角色，在权限列表中勾选相应的“授予”复选框。（3）单击“确定”按钮。使用SSMS授予数据库对象权限：（1）在对象资源管理器中右键单击要设置权限的数据库对象，然后选择“属性”。（2）在数据库对象属性对话框中选择“权限”页，选择要授予权限的用户或角色，在权限列表中勾选相应“授予”复选框。（3）单击“确定”按钮。任务9.5数据库权限管理9.5.1授予权限2.使用SQL语句授予权限使用GRANT语句将安全对象的权限授予主体：GRANT{ALL[PRIVILEGES]}|权限[(列[,...])][,...][ON安全对象]TO主体[,...][WITHGRANTOPTION][AS主体]其中ALL指定授予所有可用的权限。包含PRIVILEGES参数可以符合SQL-92标准。权限指定权限的名称。列指定表、视图或表值函数中将授予其权限的列的名称。安全对象指定将授予其权限的安全对象。安全对象为数据库时，无需使用ON子句。主体指定主体的名称。可为其授予安全对象权限的主体随安全对象而异。GRANTOPTION指示被授权者在获得指定权限的同时还可以将指定权限授予其他主体。AS主体指定在当前数据库中执行GRANT语句的主体所属的角色或组名。任务9.5数据库权限管理9.5.2拒绝权限要拒绝给主体授予的权限并防止主体通过其组或角色成员身份继承权限，可以使用SSMS图形界面或Transact-SQL语句来实现。若要使用SSMS拒绝权限，可在数据库或数据库对象属性对话框中选择用户或角色，勾选相应的“拒绝”复选框，然后单击“确定”按钮。在Transact-SQL中，可以使用DENY语句拒绝授予主体的权限，语法格式如下。DENY{ALL[PRIVILEGES]}|权限[(列[,...])][,...][ON安全对象]TO主体[,...][CASCADE][AS主体]其中CASCADE指示拒绝授予指定主体该权限，同时，对该主体授予了该权限的所有其他主体，也拒绝授予该权限。当主体具有带GRANTOPTION的权限时，这是必选项。其他子句和参数与GRANT语句中的相同。如果使用DENY语句禁止用户获得某个权限，则将其添加到具有该权限的角色或组时，该用户仍然不能使用这个权限。任务9.5数据库权限管理9.5.3撤销权限若要使用SSMS撤销以前授予或拒绝的权限，可在数据库或数据库对象属性对话框中选择用户或角色，勾选相应的“授予”或“拒绝”复选框，然后单击“确定”按钮。使用REVOKE语句可以撤销以前授予或拒绝了的权限：REVOKE[GRANTOPTIONFOR]{[ALL[PRIVILEGES]]|权限[(列[,...])][,...]}[ON安全对象]{TO|FROM}主体[,...][CASCADE][AS主体]任务9.5数据库权限管理9.6.1创建架构1.使用SSMS创建架构使用SSMS创建架构的操作方法如下。（1）在对象资源管理器中展开“数据库”文件夹，展开要在其中创建架构的数据库。（2）展开“安全性”文件夹，右键单击“构架”，选择“新建架构”。（3）在“架构-新建”对话框中选择“常规”页，在“架构名称”框中输入新架构的名称；在“架构所有者”框中输入要拥有该架构的数据库用户或角色的名称，或者单击“搜索”按钮以选择用户或角色。（4）单击“确定”按钮。任务9.6架构管理9.6.1创建架构2.使用SQL语句创建架构CREATESCHEMA<架构名称子句>[<架构元素>