sql server课件第14讲权限管理

SQLServer2005

数据库管理系统第十四讲SQLServer权限管理引入

数据的安全性是指保护数据以防止因不合法的使用而造成数据的泄密和破坏，这就要采取一定的安全措施.。在数据库系统中,用检查口令等手段来检查用户身份,合法的用户才能进入数据库系统，当用户对数据库操作时,系统自动检查用户是否有权限执行这些操作。学习目标SQLServer的验证模式登录帐号的设置用户和角色的创建权限的设置学习完本课程，您应该能够：知识重、难点分析重点：

SQLServer的验证模式用户和角色的创建难点：

登录帐号的设置用户和角色的权限设置任务一SQLServer数据库的安全性层次1、客户机操作系统的安全性。2、SQLServer服务器登录的安全性。3、数据库的使用安全性。4、数据库对象的使用安全性。用户客户机SQLServer服务器数据库操作系统的安全性SQLServer登录安全性数据库访问权限数据库对象许可权表视图任务一SQLServer数据库的安全性层次任务一SQLServer数据库的安全性层次WindowsSQLServerdatabasetableWindows账号Windows账号SQL账号Pdb1db2dbnPPPSQLLoginsSQLRolesDbuserdbrolesDbuserdbrolesDbuserdbrolesPPPPPP任务一SQLServer数据库的安全性层次任务二SQLServer的认证模式Windows认证模式SQLServer数据库系统通常运行在Windows服务器平台上，Windows认证模式正是利用了这一用户安全性和帐号管理的机制，允许SQLServer也可以使用NT的用户名和口令。在这种模式下，用户只需要通过Windows认证，就可以连接到SQLServer，而SQLServer本身也就不需要管理一套登录数据。混合认证模式在混合认证模式下Windows认证和SQLServer认证模式都是可用的。NT的用户既可以使用NT认证，也可以使用SQLServer认证。在SQLServer认证模式下，用户在连接SQLServer时必须提供登录名和登录密码。这些登录信息存储在系统表syslogins中，与NT的登录账号无关，SQLServer自己执行认证。Windows认证模式的优点更先进的安全策略一组只需建一个用户更快捷的访问混合认证模式的优点非windows用户及Internet客户可以连接到数据库任务二SQLServer的认证模式利用企业管理器进行认证模式设置：1.打开企业管理器，用右键单击要设置认证模式的服务器，从快捷菜单中选择“属性（properties）”选项，则出现SQLServer属性对话框。2.在SQLServer属性对话框中选择安全性选项。任务二SQLServer的认证模式2.打开企业管理器，用右键单击要设置认证模式的服务器，从快捷菜单中选择“编辑SQLServer注册属性”选项，则出现“已注册SQLServer属性”对话框。任务二SQLServer的认证模式任务三利用企业管理器管理登录帐号其具体执行步骤如下：（１）打开企业管理器，单击需要登录的服务器左边的“+”号，然后展开安全性文件夹。（２）用右键单击登录图标，从快捷菜单中选择新建登录选项，则出现SQLServer登录属性—新建登录对话框，如图6-2所示。（3）在名称编辑框中输入登录名，在身份验证选项栏中选择新建的用户帐号是WindowsNT认证模式，还是SQLServer认证模式。任务三利用企业管理器管理登录帐号（４）选择服务器角色页框，如图6-3所示。在服务器角色列表框中，列出了系统的固定服务器角色。（５）选择数据库访问页框，如图6-4所示。上面的列表框列出了该帐号可以访问的数据库，单击数据库左边的复选框，表示该用户可以访问相应的数据库以及该帐号在数据库中的用户名。（６）设置完成后，单击“确定”按钮即可完成登录帐号的创建。

任务三利用企业管理器管理登录帐号任务三利用企业管理器管理登录帐号任务三利用企业管理器管理登录帐号1、创建SQLServer登录帐户

格式：sp_addlogin[@loginname]‘login’[,[@passwd=]‘password’][,[@defdb=]‘database’][,[@language=]‘language’][,[@sid=]‘sid’][,[@encryption=]‘encryption_option’]例如：execsp_addlogin'dbloginuser1','123'

execsp_addlogin'dbloginuser1','123',‘学生信息库’任务四利用T-SQL管理登录帐号2、修改SQLServer登录帐户

例如：修改登录帐户dbloginuser1的默认数据库。

execsp_defaultdb‘dbloginuser1’,‘学生管理数据库’

修改dbloginuser1的密码。

execsp_password‘password’,’newpassword’,‘dbloginuser1’

任务四利用T-SQL管理登录帐号4、删除SQLServer登录帐户

删除登录帐户dbloginuser1。

execsp_droplogindbloginuser1任务四利用T-SQL管理登录帐号Windows集成登录帐号1、使用系统存储过程使WindowsNT的用户或工作组成为SQLServer的登录帐户。格式：sp_grantlogin[@loginane=]’login’例如：将WindowsNT服务器上[servername]域的工作组[sqlgroup]映射为SQLServer登录帐户。

execsp_grantlogin[servername\sqlgroup]任务四利用T-SQL管理登录帐号Windows集成登录帐号2、撤消WindowsNT服务器上[servername]域的工作组[sqlgroup]内用户与SQLServer登录帐户的映射。execsp_revokelogin[servername\sqlgroup]任务四利用T-SQL管理登录帐号Windows集成登录帐号3、拒绝WindowsNT服务器上[servername]域的工作组[sqlgroup]内用户登录SQLServer服务器。execsp_denylogin[servername\sqlgroup]查看登录帐号信息。execsp_helplogin任务四利用T-SQL管理登录帐号任务五SQLServer数据库安全性在数据库中，一个用户或工作组取得合法的登录帐号，只表明该帐号通过了WindowsNT认证或者SQLServer认证，但不能表明其可以对数据库对象进行操作，只有当它同时拥有了用户帐号后，才能够访问数据库。数据库的访问权限是通过映射数据库的用户与登录帐号之间的关系实现的。每个登录账号在一个数据库中只能有一个用户账号,但是每个登录账号可以在不同的数据库中各有一个用户账号。如果在新建登录账号的过程中，指定对某个数据库具有存取权限，则在该数据库中将自动创建一个与该登录账号同名的用户账号。任务五使用企业管理器管理数据库用户1、添加数据库用户：

任务五使用企业管理器管理数据库用户2、删除数据库用户：任务五使用企业管理器管理数据库用户任务五使用T-SQL管理数据库用户1、添加数据库用户格式：sp_grantdbaccess[@loginame=]'账户名'[,[@name_in_db=]'数据库用户名’例如：在当前数据库中为WindowsNT账户servername\sqlgroup

添加用户，并取名为sqllogingroup。use学生管理数据库goexecsp_grantdbaccess[servername\sqlgroup],'sqllogingroup’goexecsp_grantdbaccess‘dbloginuser1’,’dbaccessuser1’任务五使用T-SQL管理数据库用户2、查看数据库用户use学生管理数据库goexecsp_helpuser任务五使用T-SQL管理数据库用户3、删除数据库用户格式：sp_revokedbaccess[@name_in_db=]'数据库用户名’例如：断开SQLServer登录帐户与数据库用户之间的映射。User学生管理数据库goexecsp_revokedbaccess‘sqllogingroup’goexecsp_revokedbaccess’dbaccessuser1’特殊数据库用户SQLServer2000的数据库级别上也存在着2个特殊的数据库用户，分别是dbo和guest。1、Dbo是数据库对象所有者，在安装SQLServer2000时被设置到model数据库中的，不能被删除，所以dbo在每个数据库中都存在，具有数据库的最高权力，可以在数据库范围内执行一切操作。Dbo用户对应于创建该数据库的登录用户，所以所有系统数据库的dbo都对应于sa账户。2、Guest允许没有账户的用户登录访问数据库。可以将权限应用到guest用户，就如同它是任何其他用户账户一样。可以在除master和tempdb外（在这两个数据库中它必须始终存在）的所有数据库中添加或删除guest用户。默认情况下，新建的数据库中没有guest用户账户。【例】将guest用户账户添加到名为Accounts的数据库中，意味着所有登录到SQLServer服务器的用户都可以访问该数据库，即使它还没有成为本数据库的用户。useaccountsgoexecsp_grantdbaccessguest特殊数据库用户任务六角色管理角色是SQLServer版本引进的新概念，它代替了以前版本中组的概念。利用角色，SQLServer管理者可以将某些用户设置为某一角色，这样只对角色进行权限设置便可以实现对所有用户权限的设置，大大减少管理员的工作量。SQLServer提供了用户通常管理工作的固定服务器角色和固定数据库角色。1、固定服务器角色

固定服务器角色是指根据SQLServer的管理任务，以及这些任务相对的重要性等级来把具有SQLServer管理职能的用户划分为不同的用户组，每一组所具有的管理SQLServer的权限都是SQLServer内置的，即不能对其进行添加、修改和删除，只能向其中加入用户或者其他角色。1、固定服务器角色

1、使用企业管理器管理服务器角色（1）展开指定的服务器节点。（2）展开服务器节点下的安全性子节点。（3）选择[服务器角色]节点，则右边窗口将列出所有的固定服务器角色。（4）选中指定的固定服务器角色，右击[属性]，单击[添加]按钮。1、固定服务器角色

2、使用T-SQL管理固定服务器角色1）查看固定服务器角色：Execsp_helpsrvrole

2）查看固定服务器角色权限：UsemastergoExecsp_srvrolepermissionsysadmin1、固定服务器角色

2、使用T-SQL管理服务器角色3）将固定服务器角色分配给某指定的登录帐户：execsp_addsrvrolemember[servername\user1],’sysadmin’goexecsp_addsrvrolemember‘dbloginuser1’,’sysadmin’1、固定服务器角色

2、使用T-SQL管理服务器角色4）收回分配给某指定的登录帐户的固定服务器角色：execsp_dropsrvrolemember[servername\user1],’sysadmin’Goexecsp_dropsrvrolemember‘dbloginuser1’,’sysadmin’1、固定服务器角色

2、使用T-SQL管理服务器角色5）查看固定服务器角色成员：execsp_helpsrvrolemember’sysadmin’七种常用的固定服务器角色

系统管理员：拥有SQLServer所有的权限许可。服务器管理员：管理SQLServer服务器端的设置。磁盘管理员：管理磁盘文件。进程管理员：管理SQLServer系统进程。安全管理员：管理和审核SQLServer系统登录。安装管理员：增加、删除连接服务器，建立数据库复制以及管理扩展存储过程。数据库创建者：创建数据库，并对数据库进行修改。数据库角色数据库角色是为某一用户或某一组用户授予不同级别的管理或访问数据库以及数据库对象的权限，这些权限是数据库专有的，并且还可以使一个用户具有属于同一数据库的多个角色。SQLServer提供了两种类型的数据库角色：即固定的数据库角色和用户自定义的数据库角色。

数据库角色1）查看固定数据库角色：use学生管理数据库goexecsp_helpdbfixedrole数据库角色2）将某固定数据库角色分配给某指定的访问帐户：use学生管理数据库goexecsp_addrolememberdb_owner,dbaccessuser1数据库角色3）收回分配给某指定的访问帐户的指定数据库角色：use学生管理数据库goexecsp_droprolememberdb_owner,dbaccessuser11、固定的数据库角色：Public:维护全部默认许可。数据库中的每个用户都属于public数据库角色。如果想让数据库中的每个用户都能有某个特定的权限，则将该权限指派给public角色。如果没有给用户专门授予对某个对象的权限，他们就使用指派给public角色的权限。Db_owner：数据库的所有者，可以对所拥有的数据库执行任何操作。Db_accessadmin：可以增加或删除数据库用户、工作组和角色。Db_addadmin：可以增加、删除和修改数据库中的任何对象。Db_securityadmin：执行语句许可和对象许可。数据库角色Db_backupoperator：可以备份和恢复数据库。Db_datareader：能且仅能对数据库中的任何表执行select操作，从而读取所有表的信息。Db_datawriter：能够增加、修改和删除表中的数据，但不能进行select操作。Db_denydatareader：不能读取数据库中任何表中的数据。Db_denydatawriter：不能对数据库中的任何表执行增加、修改和删除数据操作。数据库角色2、用户自定义数据库角色创建用户定义的数据库角色就是创建一组用户，这些用户具有相同的一组许可。如果一组用户需要执行在SQLServer中指定的一组操作并且不存在对应的WindowsNT组，或者没有管理WindowsNT用户帐号的许可，就可以在数据库中建立一个用户自定义的数据库角色。用户自定义的数据库角色有两种类型：即标准角色和应用程序角色。数据库角色标准角色通过对用户权限等级的认定而将用户划分为不用的用户组，使用户总是相对于一个或多个角色，从而实现管理的安全性。应用程序角色是一种特殊的角色。当让某些用户只能通过特定的应用程序间接地存取数据库中的数据而不是直接地存取数据库数据时，就应该考虑使用应用程序角色。当某一用户使用了应用程序角色时，他便放弃了已被赋予的所有数据库专有权限，他所拥有的只是应用程序角色被设置的角色。数据库角色数据库角色1、使用企业管理器管理用户自定义角色（1）在企业管理器中找到要创建角色的数据库。（2）展开数据库。用鼠标右键单击角色，在弹出的快捷菜单中选择“新建数据库角色”命令，如图8-11所示。（3）在打开的新建数据库角色对话框中，在名称框中输入数据库角色的名称，选中“标准角色”单选按钮，如图8-12所示。（4）在图8-12中单击“添加“按钮，可以为角色添加用户，也可省略创建一个暂无成员的角色。图8-11创建数据库角色图8-12创建新数据库角色2、使用T-SQL管理用户自定义数据库角色格式：Sp_addrole[@rolename=]’role’[,[@ownername=]’owner’]例如：use学生管理数据库goexecsp_addrole‘dbaccessrole1’goexecsp_helprole

goexecsp_droprole‘dbaccessrole1’数据库角色数据库角色成员1、使用企业管理器增删用户自定义数据库角色成员（1）在企业管理器中找到要添加角色的数据库。（2）展开该数据库。（3）用鼠标单击角色，右边的窗口显示了数据库所有的角色，双击要修改的成员角色，打开如图8-13所示的对话框。（4）在图8-13中单击“添加”按钮可以从当前的数据库用户的角色中选择角色成员，如图8-14所示。（5）在图8-14中的成员列表中选择一个成员，单击“确定”按钮就可以为该角色添加用户。（6）在图8-13中，选中用户，单击“删除”按钮，则可删除该角色的成员。图8-13数据库角色属性图8-14为数据库添加角色成员数据库角色成员2、使用T-SQL增删用户自定义数据库角色成员系统存储过程sp_addsrvrolemember可以将某一登录帐号加入到服务器角色中，使其成为该服务器角色的成员。格式：sp_addsrvrolememberaccess，role【例】将SQLServer的用户dbaccessuser1添加到当前数据库中的dbaccessrole1角色中。use学生管理数据库goexecsp_addrolemember‘dbaccessuser1',‘dbaccessrole1'数据库角色成员2、使用T-SQL查看用户自定义数据库角色成员use学生管理数据库goexecsp_helprolemember‘dbaccessrole1'数据库角色成员许可管理SQLServer提供了权限（permision）作为访问权限设置的最后一道关卡。数据库中每个对象都拥有自己的权限设置，以决定用户最终是否可以访问这些数据对象，以及可以对这些数据对象进行的操作。权限包括授予或废除执行以下活动的用户权限：（1）处理数据和执行过程（称为对象权限）。（2）创建数据库或数据库中的项目（称为语句权限）。（3）利用授予预定义角色的权限（称为暗示性权限）。1、对象许可处理数据或执行过程时的需要称为对象权限的权限类别。对象权限包括：SELECT、INSERT、UPDATE

和DELETE语句权限，它们可以应用到整个表或视图中。SELECT和UPDATE语句权限，它们可以有选择性地应用到表或视图中的单个列上。SELECT权限，它们可以应用到用户定义函数。INSERT和DELETE语句权限，它们会影响整行，因此只可以应用到表或视图中，而不能应用到单个列上。EXECUTE语句权限，它们可以影响存储过程和函数。许可管理2、语句许可表示对数据库的操作许可，也就是说，创建数据库或创建数据库中的其它内容所需要的许可类型称为语句许可：Createdatabase：创建数据库Createtable：创建表Createview：创建视图Createrule：创建规则Createdefault：创建缺省Createprocedure：创建存储过程Createindex：创建索引Backupdatabase：备份数据库Backuplog：备份事务日志许可管理3、预定义许可（暗示性权限）是指系统安装以后有些用户和角色不必授权就有的许可。暗示性权限控制那些只能由预定义系统角色的成员或数据库对象所有者执行的活动。例如，sysadmin固定服务器角色成员自动继承在SQLServer安装中进行操作或查看的全部权限。数据库对象所有者还有暗示性权限，可以对所拥有的对象执行一切活动。例如，拥有表的用户可以查看、添加或删除数据，更改表定义或控制允许其他用户对表进行操作的权限。许可管理1、使用SQLServer企业管理器管理许可通过企业管理器授予权限而允许访问的步骤是：（1）找到对象所属的数据库。（2）展开对象所属的数据库。（3）根据对象类型，单击下列对象之一：

表视图存储过程（4）在“详细信息”窗口中，右击授予权限所在的对象，指向“所有任务”，然后单击“管理权限”。（5）单击“列出全部用户/用户定义的数据库角色/public”，然后选择授予每位用户的权限。许可管理2、使用T-SQL管理许可Transaction_SQL语句使用grant、revoke和deny三种命令来实现管理权限。许可管理（１）对语句的许可Grant{all|statement[,…n]}tosecurity_account[,…n][WITHGRANTOPTION]许可管理（２）对对象的许可Grant

{{all[priviledges]|permission[,…n]}[（column[,…n]）]on{table|view}|on{table|view}[（column[,…n]）]|on{stored_procedure|extended_procedure}}tosecurity_account[,…n][WITHGRANTOPTION][as{group|role}]许可管理ALL表示授予所有可用的权限。对于语句权限，只有sysadmin角色成员可以使用ALL。对于对象权限，sysadmin和db_owner角色成员和数据库对象所有者都可以使用ALL。WITHGRANTOPTION表示可以将指定的对象权限授予其他用户。【例】为角色dbaccessrole1中成员授予在当前数据库中创建表权限。use学生管理数据库gograntcreatetabletodbaccessrole1goexecsp_helprotectnull,dbaccessrole1gograntselect,inserton学生表todbaccessrole1许可管理（１）否定语句的许可Deny{all|statement[,…n]}tosecurity_account[,…n]许可管理（２）否定对象的许可Deny

{{all[priviledges]|permission[,…n]}[（column[,…n]）]on{table|view}|on{table|view}[（column[,…n]）]|on{stored_procedure|extended_procedure}}tosecurity_account[,…n]许可管理【例】拒绝角色dbaccessrole1中成员在当前数据库中创建表权限，除非给他们显式授予权限。use学生管理数据库godenycreatetabletodbaccessrole1goexecsp_helprotectnull,dbaccessrole1godenyselect,inserton学生表todbaccessrole1许可管理（１）收回语句的许可Revoke{all|statement[,…n]}fromsecurity_account[,…n]许可管理