《SQLServer数据库应用与维护》-第21讲 用户管理2

上节内容回顾回顾

8.1SQLServer的安全机制概述8.2创建登录账户8.3创建数据库用户上节内容提问回顾SQLServer安全性机制中涉及几个安全性问题，分别是？安全性机制的五个等级分别是？创建Windows登录帐户的方法有？创建SQLServer登录帐户的方法有？如何创建数据库用户，有哪些方法？8.1SQLServer的安全机制概述安全性是所有数据库管理系统的一个重要特征。理解安全性问题是理解数据库管理系统安全性机制的前提。第一个安全性问题：当用户登录数据库系统时，如何确保只有合法的用户才能登录到系统中？在MicrosoftSQLServer系统中，通过身份验证模式和主体解决这个问题。第二个安全性问题：当用户登录到系统中，他可以执行哪些操作、使用哪些对象和资源？第三个安全性问题：数据库中的对象由谁所有？如果是由用户所有，那么当用户被删除时，其所拥有的对象怎么办，难道数据库对象可以成为没有所有者的“孤儿”吗？在MicrosoftSQLServer系统中，这个问题是通过用户和架构分离来解决的。在MicrosoftSQLServer系统中，通过安全对象和权限设置来解决这个问题。8.1SQLServer的安全机制概述安全机制的5个等级客户机安全机制网络传输安全机制实例级别安全机制数据库级别安全机制对象级别安全机制8.2

创建登录帐户一、创建Windows登录帐户方法1：用可视化创建Windows登录账户方法2：用T-SQL语句创建Windows登录账户CREATELOGIN[nameofWindowsUser]FROMWINDOWS8.2

创建登录帐户二、创建SQLServer登录帐户方法1：用可视化创建混合身份登录账户方法2：用T-SQL语句创建混合身份登录账户CREATELOGIN<loginname>

WITHPASSWORD='<password>’方法3：用系统存储过程创建Windows登录账户execsp_addlogin<loginname>,'<password>'8.3

创建数据库用户一、通过设置“用户映射”指明数据库用户二、创建数据库用户三、通过Transact-SQL创建数据库用户create

user<数据库用户名>for

login<登录名>with

default_schema=dbo用户管理第8章目的与要求：掌握SQLServer数据库的用户管理，包括创建管理用户，管理用户权限和角色等。

掌握用户与角色的权限管理重点与难点：SQLServer2012的安全机制创建登录账户（重点）创建数据库用户（重点）角色与权限（难点）本讲主要内容

8.1SQLServer的安全机制概述8.2创建登录账户8.3创建数据库用户

8.4角色8.5权限第8章8.4角色8.4

角色SQLServer提供了用户通常管理工作的预定义服务器角色和数据库角色。用户还可以创建自己的数据库角色，以便表示某一类进行同样操作的用户。当用户需要执行不同的操作时，只需将该用户加入不同的角色中即可，而不必对该用户反复授权许可和收回许可。固定服务器角色数据库角色8.4

角色一、固定服务器角色固定服务器角色是服务器级别的主体，它们的作用范围是整个服务器。固定服务器角色已经具备了执行指定操作的权限，可以把其他登录名作为成员添加到固定服务器角色中，这样该登录名可以继承固定服务器角色的权限。1.固定服务器角色的特点在MicrosoftSQLServer系统中，可以把登录名添加到固定服务器角色中，使登录名作为固定服务器角色的成员继承固定服务器角色的权限。对于登录名来说，可以选择其是否成为某个固定服务器角色的成员。8.4

角色一、固定服务器角色2.服务器角色按照从最低级别的角色（bulkadmin）到最高级别的角色（sysadmin）的顺序进行描述。8.4

角色一、固定服务器角色2.服务器角色系统管理员（sysadmin）：拥有SQLServer所有的权限许可；服务器管理员（Serveradmin）：管理SQLServer服务器端的设置；磁盘管理员（diskadmin）：管理磁盘文件；进程管理员（processadmin）：管理SQLServer系统进程；安全管理员（securityadmin）：管理和审核SQLServer系统登录；安装管理员（setupadmin）：增加、删除连接服务器，建立数据库复制以及管理扩展存储过程；数据库创建者（dbcreator）：创建数据库，并对数据库进行修改。批量数据输入管理员（bulkadmin）：管理同时输入大量数据的操作。8.4

角色一、固定服务器角色3.使用操作平台管理服务器角色1）查看服务器角色的属性①启动MicrosoftSQLServerManagementStudio，在“对象资源管理器”中依次展开“安全性|服务器角色”节点8.4

角色一、固定服务器角色3.使用操作平台管理服务器角色1）查看服务器角色的属性②选择其中的一个服务器，在其上单击右键，在弹出的快捷菜单中选择“属性”选项。例如选择sysadmin这个服务器并右击，在快捷菜单中单击“属性”选项，打开如图所示“服务器角色属性”对话框，在该对话框中就可以查看sysadmin这个服务器角色的属性了。8.4

角色一、固定服务器角色3.使用操作平台管理服务器角色2）添加服务器角色的角色成员①为服务器角色添加“角色成员”，可以在服务器角色的“服务器角色属性”对话框中单击“添加”按钮。②单击“浏览”按钮，弹出“查找对象”对话框，单击要添加的登录名左边的复选框，单击“确定”按钮即可将选中的角色成员添加进来。8.4

角色一、固定服务器角色3.使用操作平台管理服务器角色3）创建服务器角色CREATESERVERROLE<role_name>可视化创建固定服务器角色通过Transact-SQL新建服务器角色：8.4

角色一、固定服务器角色3.使用操作平台管理服务器角色4）删除服务器角色要删除一个已经存在的角色成员，只需要选中该角色成员并在其上单击鼠标右键，然后在弹出的快捷菜单中选择“删除”选项，即可删除服务器角色。上机实验任务一实验8第一题：【创建登录名】5-6题10分钟8.4

角色二、数据库角色数据库角色是为某一用户或某一组用户授予不同级别的管理或访问数据库以及数据库对象的权限，这些权限是数据库专有的，并且还可以使一个用户具有属于同一数据库的多个角色。三种类型的数据库角色:固定数据库角色：微软提供的作为系统一部分的角色；用户定义的标准数据库角色：你自己定义的角色，将Windows用户以一组自定义的权限分组；应用程序角色：用来授予应用程序专门的权限，而非授予用户组或者单独用户。8.4

角色二、数据库角色1.固定数据库角色微软提供了10个内置的角色，以便于在数据库级别授予用户特殊的权限集合：public：每个数据库用户都属于public数据库角色，当尚未对某个用户授予或拒绝对安全对象的特定权限时，则该用户将继承授予该安全对象的public角色的权限；db_owner：可以执行数据库的所有配置和维护活动；db_accessadmin：可以增加或者删除数据库用户、工作组和角色；db_ddladmin：可以在数据库中运行任何数据定义语言(DDL)命令；db_securityadmin：可以修改角色成员身份和管理权限；db_backupoperator：可以备份和恢复数据库；db_datareader：能且仅能对数据库中的任何表执行select操作，从而读取所有表的信息；db_datawriter：能够增加、修改和删除表中的数据，但不能进行SELECT操作；db_denydatareader：不能读取数据库中任何表中的数据；db_denydatawriter：不能对数据库中的任何表执行增加、修改和删除数据操作。8.4

角色二、数据库角色2.用户定义的标准数据库角色创建用户定义的数据库角色就是创建一组用户，这些用户具有相同的一组许可。如果一组用户需要执行在SQLServer中指定的一组操作并且不存在对应的Windows组，或者没有管理Windows用户帐号的许可，就可以在数据库中建立一个用户自定义的数据库角色。标准角色通过对用户权限等级的认定而将用户划分为不同的用户组，使用户总是相对于一个或多个角色，从而实现管理的安全性。所有的固定的数据库角色或SQLServer管理者自定义的某一角色都是标准角色。1）可视化添加数据角色8.4

角色二、数据库角色2.用户定义的标准数据库角色添加数据库角色成员8.4

角色二、数据库角色2.用户定义的标准数据库角色添加数据库角色成员Exec

sp_addrolemember<数据库角色>,<数据库用户>2）通过Transact-SQL将用户添加到数据库角色中8.4

角色二、数据库角色2.用户定义的标准数据库角色新建数据库角色CREATEROLE<数据库角色>2）通过Transact-SQL新建数据库角色1）可视化新建数据角色8.4

角色三、应用程序角色应用程序角色允许用户为特定的应用程序创建密码保护的角色。应用程序角色是一种比较特殊的角色。当我们打算让某些用户只能通过特定的应用程序间接地存取数据库中的数据而不是直接地存取数据库数据时，就应该考虑使用应用程序角色。当某一用户使用了应用程序角色时，他便放弃了已被赋予的所有数据库专有权限，他所拥有的只是应用程序角色被设置的角色。通过应用程序角色，能够以可控制方式来限定用户的语句或者对象许可。上机实验任务二实验8第二题：【创建数据库用户及角色】3-6题15分钟8.5权限8.5

权限一、常用的权限安全对象常用权限数据库CREATEDATABASE、CREATEDEFAULT、CREATEFUNCTION、CREATEPROCEDURE、CREATEVIEW、CREATETABLE、CREATERULE、BACKUPDATABASE、BACKUPLOG表SELECT、DELETE、INSERT、UPDATE、REFERENCES表值函数SELECT、DELETE、INSERT、UPDATE、REFERENCES视图SELECT、DELETE、INSERT、UPDATE、REFERENCES存储过程EXECUTE、SYNONYM标量函数EXECUTE、REFERENCES8.5

权限二

、操作权限权限分为3种状态：授予、拒绝、撤销，可以使用如下的语句来修改权限的状态。（1）授予权限（GRANT）：授予权限以执行相关的操作。通过角色，所有该角色的成员继承此权限。（2）撤销权限（REVOKE）：撤销授予的权限，但不会显示阻止用户或角色执行操作。用户或角色仍然能继承其他角色的GRANT权限。（3）拒绝权限（DENY）：显式拒绝执行操作的权限，并阻止用户或角色继承权限，该语句优先于其他授予的权限。8.5

权限二

、操作权限1.授予权限利用GRANT语句可以授予权限，基本语法如下：语句权限：GRANT{ALL|statement[,...n]}

TOsecurity_account[,...n]ALL：表示希望给该类型的对象授予所有可用的权限。不推荐使用此选项，保留些选项仅用于向后兼容。Statement：表示可以授予权限的命令，例如，CREATEDATABASE。security_account：表示定义被授予权限的用户单位。security_account可以是SQLServer的数据库用户，可以是SQLServer的角色，也可以是Windows的用户或工作组。对象权限：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}]8.5

权限二

、操作权限1.授予权限例1：使用GRANT命令授予角色“po_mag”对”PO”数据库中“物料”表的DELETE、INSERT、UPDATE权限。USE

POGo

GRANT

DELETE,

INSERT,

UPDATE

ON

物料

TO

po_magGO8.5

权限二

、操作权限2.撤销权限利用REVOKE语句可以撤销权限，基本语法如下：REVOKE权限组[ON对象]FROM用户组|public权限组：对象权限或语句权限对象：表、列（字段）、视图、…8.5

权限二

、操作权限2.撤销权限例2：使用REVOKE语句撤销“po_mag”角色对“物料”表所拥有的DELETE、INSERT、UPDATE权限。USE

POGO

REVOKE

DELETE,INSERT,UPDATEON

物料FROM

po_mag

CASCADEGO8.5

权限二

、操作权限3.拒绝权限利用DENY语句可以拒绝权限，基本语法如下：DENY权限组[ON对象]TO用户组|public权限组：对象权限或语句权限对象：表、列（字段）、视图、…8.5

权限二

、操作权限3.拒绝权限例3：在数据库“PO”的“物料”表中执行