数据库设计开发案例教程(九)

CONFIDENTIAL数据库设计开发技术案例教程（九）SQLServer权限与安全2北京亚思晟科技有限公司主要内容1，用户权限管理的操作2，固定服务器角色的操作3，固定数据库角色的操作4，自定义数据库角色的操作3北京亚思晟科技有限公司9.1用户管理1默认数据库用户1）.数据库所有者（DataBaseOwner，dbo）dbo是数据库的所有者，拥有数据库中的所有对象。每个数据库都有dbo，sysadmin服务器角色的成员映射成dbo。无法删除dbo用户，且此用户始终出现在每个数据库中。通常，登录名sa映射为库中的用户dbo。另外，有固定服务器角色sysadmin的任何成员创建的任何对象都自动属于dbo。2）Guest用户Guest用户账户允许没有用户账户的登录名访问数据库。当登录名没有被映射到一个用户名上时，如果在数据库中存在Guest用户，登录名将自动映射成Guest，并获得相应的数据库访问权限。Guest用户可以和其他用户一样设置权限，不能删除Guest用户，但可在除master和tempdb之外的人和数据库中禁用Guest用户。3）Information_schema和sys用户每个数据库中都含有Information_schema和sys用户，它们位于目录视图中，用来获取有关数据库的元数据信息。4北京亚思晟科技有限公司9.1用户管理2.创建数据库用户1）使用SQLServerManagementStudio创建数据库用户（1）在SQLServerManagementStudio的“对象资源管理器”中，展开“服务器”下的“数据库”节点。（2）展开要在其中创建新数据库用户的数据库。（3）右击“安全性”节点，从弹出的快捷菜单中选择“新建”下的“用户”选项，弹出“数据库用户-新建”对话框，如图9-1所示。（4）在“常规”选择页的“用户名”框中输入新用户的名称。在“登录名”框中输入或选择要映射到数据库用户的Windows或SQLServer登录名的名称。（5）如果不设置“默认构架”，系统会自动设置dbo为此数据库用户的默认构架。（6）单击“确定”按钮，完成数据库用户的创建。如图9-15北京亚思晟科技有限公司9.1用户管理2）使用CREATEUSER语句创建数据库用户其语法格式如下：CREATEUSERuser_name{FOR|FROM}LOGINlogin_nameUser_name:指定在此数据库中创建的新用户名称，其长度最多是128个字符。LOGINlogin_name:指定要创建的数据库用户的登录名。Login_name必须是服务器中有效的登录名。以下示例：在Myschool数据库中创建数据库用户zhou，其登陆名为[Zhou-PC\Zhou]USEMyschoolGOCREATEUSERzhouFROMLOGIN[Zhou-PC\Zhou]GO显示结果，如图9-2所示。如图9-26北京亚思晟科技有限公司9.2角色和权限管理1.角色管理角色是为了易于管理而按相识的工作属性对用户进行分组的一种方式。在SQLServer中，组是通过角色来实现的。角色分为服务器角色（又称为固定服务器角色）和数据库角色两种，其中服务器是服务器级别的一个对象，只能包含登录名；数据库角色是数据库级别的一个对象，只能包含数据库用户名，数据库角色又分为固定数据库角色和自定义数据库角色两种。1）固定服务器角色固定服务器角色存在于服务器级别并处于数据库之外。在按装完SQLServer后，系统自动创建8个固定的服务器角色，如表9-1所示。表9-1固定服务器角色描述SysadminServeradminSetupadminSecurityadminProcessadminDbcreatorDiskadminbulkadmin在SQLServer中执行任何活动配置服务器范围的设置添加和删除链接服务器，并执行某些系统存储过程管理服务器登录管理在SQLServer实例中运行的进程创建和改变数据库管理磁盘文件执行BULKINSERT语句7北京亚思晟科技有限公司9.2角色和权限管理使用SQLServerManagementStudio将登录账户添加到固定服务器角色在SQLServerManagementStudio的“对象资源管理器”中依次展开“服务器”、“安全性”、“服务器角色”节点，可以看到8个固定的服务器角色，如图9-3所示。图9-38北京亚思晟科技有限公司9.2角色和权限管理双击需要添加登录账户的服务器角色，将弹出“服务器角色属性”对话框，如图9-4所示。图9-49北京亚思晟科技有限公司9.2角色和权限管理双击需要添加登录账户的服务器角色，将弹出“服务器角色属性”对话框，如图9-4所示。单击“添加”按钮，选择要添加的登录名。单击“确定”按钮，完成操作。也可以在登录账户的“属性”对话框中，选择“服务器角色”选择页，选中相应的服务器角色的复选框，将登录账户添加到该服务器角色中。B．使用存储过程sp_addsrvrolemember将登录账户添加到固定服务器角色存储过程sp_addsrvrolemember用来添加登录账户，使其成为服务器角色的成员。其语法格式为：sp_addsrvrolemember‘login’,’role’其中：Login：添加到固定服务器角色中的登录名。Role：要添加登录名的固定服务器角色的名称。图9-410北京亚思晟科技有限公司9.2角色和权限管理2）固定数据库角色每个数据库中都有数据库角色，数据库角色分为固定数据库角色和自定义数据库角色。安装完SQLServer后，系统在每个数据库中自动创建了10个固定的数据库角色，如表9-2所示。表9-2固定数据库角色描述Db_owerDb_accessadminDb_datareaderDb_datawriterDb_ddladminDb_securityadminDb_backupoperatorDb_denydatareaderDb_denydatawriterpublic数据库所有者，可以执行所有数据库角色的活动，以及数据库中的其他维护和配置活动在数据库中添加或删除Windows组合用户以及SQLServer用户查看来自数据库中所有用户表的全部数据添加、删除或修改来自数据库中所有用户表的数据添加、修改或删除数据库中的对象（运行DDL）管理数据库角色的角色和成员，并管理数据库中的语句和对象权限有备份数据库的权限不允许查看数据库数据不允许更改数据库数据数据库中的用户的所有默认权限11北京亚思晟科技有限公司9.2角色和权限管理Public角色是一个特殊的数据库角色，每个数据库用户都属于public数据库角色。当尚未对某个用户授予或拒绝对某对象的特定权限时，该用户将继承授予该对象的public角色的权限。当需要提供一种权限给所有用户时，可以利用public角色。例如，如果想让数据库的所有用户在某个表上都能执行SELECT操作，那么可以将这个表的SELECT权限分配给public角色，这样所有的用户都拥有了这个权限。A.使用SQLServerManagementStudio将用户添加到固定数据库角色a.在SQLServerManagementStudio的“对象资源管理器”中，依次展开“服务器”、“数据库”节点，然后展开需要添加用户的数据库角色的数据库。b.再依次展开该数据库下的“安全性”、“角色”、“数据库角色”节点，可以看到当前数据库的所有数据库角色，如图9-5所示。图9-512北京亚思晟科技有限公司9.2角色和权限管理c.双击需要添加用户的数据库角色，弹出“数据库角色属性”对话框，如图9-6所示。d.单击“添加”按钮，选择要添加的用户。e.单击“确定”按钮，完成操作。也可以在用户的“属性”对话框中，选中相应的数据库角色的复选框，将用户添加到该数据库角色中。使用存储过程sp_addrolemember将用户添加到固定数据库角色存储过程sp_addrolemember用来添加用户，使其成为服务器角色的成员。其语法格式为：sp_addrolemember‘role’,’security_account’其中：Role：当期数据库中的数据库角色的名称。security_account:添加到该角色的用户。图9-613北京亚思晟科技有限公司9.2角色和权限管理3）自定义数据库角色当一组用户需要在SQLServer中执行一组指定的活动，为了方便管理，可以创建用户自定义的数据角色。使用SQLServerManagementStudio创建用户自定义数据库角色ａ．在SQLServerManagementStudio的“对象资源管理器”中，展开“服务器”下的“数据库”节点，然后展开需要创建新数据库角色的数据库。ｂ．右击“安全性”，选择“新建”下的“数据库角色”选项，弹出“数据库角色－新建”对话框。如图9-7所示。c.在“常规”选择页的“角色名称”框中输入新角色的名称。若不指定“所有者”，则创建此角色的用户是其所有者。d.单击“添加”按钮，选择数据库用户或角色成为此角色的成员。e．单击“确定”按钮，完成操作。B．使用CREATEROLE语句创建数据库角色其语法格式如下：CREATEROLErole_name[AUTHORIZATIONowner_name]图9-614北京亚思晟科技有限公司9.2角色和权限管理4）应用程序角色当不允许用户任何工具来对数据库进行某些操作，而只能用特定的应用程序来处理时就可以建立应用程序角色。应用程序角色不包含成员；默认情况下，应用程序角色是非活动的，需要用密码激活。再激活应用程序角色以后，当前用户原来的所有权限自动消失，而获得了该应用程序角色的权限。可以通过图形界面创建应用程序角色，也可以通过CREATEAPPLICATIONROLE语句来创建。15北京亚思晟科技有限公司9.2角色和权限管理2.权限管理1）.权限类型权限用来控制用户如何访问数据库对象。一个用户可以直接分配到权限，也可以作为一个角色成员来间接得到权限。一个用户可以同时属于具有不同权限的多个角色。权限分为：对象权限、语句权限、暗示性权限。①对象权限对象权限是之用户访问和操作数据库中的表、视图、存储过程等对象的权限。有五个对象权限：查询（SELECT）、插入（INSERT）、更新（UPDATE）、删除（DELETE）、执行（EXECUTE）。前四个权限用于表和视图，执行只用于存储过程。②语句权限语句权限是之用户创建数据库，或者在数据库中创建或修改对象、执行数据库或事务日志备份的权限。语句权限有：BACKUPDATABASEBACKUPLOGCREATEDATABASECREATEDEFAULTCREATEFUNCTIONCREATEPROCEDURECREATERULECREATETABLECREATEVIEW③暗示性权限暗示性权限是指系统预定义角色或数据库对象所有者所拥有的权限。例如，sysadmin固定服务器角色成员自动继承在SQLServer中进行操作或查看的全部权限。数据库对象所有者可以对所拥有的对象执行一切活动。例如，拥有表的用户可以查看、添加或删除数据，更改表定义，或控制其他用户对表进行操作的权限。16北京亚思晟科技有限公司9.2角色和权限管理2）.权限管理操作一个用户或角色的权限可以有三种存在的形式：授予（granted）、拒绝（denied）、废除（revoked）。授予是赋予用户某权限；拒绝是禁止用户的某权限；废除是撤销以前授予或拒绝的权限。（1）.使用SQLServerManagementStudio管理权限下面分别介绍对象权限和语句权限的管理对象权限可以从用户/角色的角度管理，即管理一个用户能对那些对象执行哪些操作；也可以从对象的角度管理，即设置一个数据库对象能被哪些用户执行哪些操作。以下实例：为Myschool数据库用户zhou赋予查询Class表记录的权限。在SQLServerManagementStudio的“对象资源管理器”中，展开Clas