Sql Server来龙去脉系列 必须知道的权限控制核心篇

近写了《Sql Server来龙去脉系列  必须知道的权限控制基础篇》，感觉反响比较大。这可能也说明了很多程序猿对数据库权限控制方面比较感兴趣，或者某些技术点了解的没有很透彻。 有些人看了上篇感觉意犹未尽，介绍的都是基础方面，不够深入。那么本篇内容就比较符合大家的胃口，本篇包括了数据库常用的权限控制，例如服务角色以及数据库角色管理。

提几个问题

    在介绍权限控制之前先提下面几个问题，如果有回答不上来的问题，本篇内容你应该看。如果很清晰的回答出这些问题，那么本篇接下来的内容你可以直接忽略。

    1.登陆账号和用户有什么区别；

    2.服务角色和数据库角色有什么区别；

    3.主体、用户、架构之间的区别（Principal、User、Schema）；

温故而知新

    在《Sql Server来龙去脉系列  必须知道的权限控制基础篇》中，我们介绍了登陆账号的增删改查、数据库的增删该查以及这些元数据对应的系统视图和操作语法。但是，一个登陆账号被创建后并不能创建或者操作数据库，必须经过授予某些权限后才能操作管理数据库。如何给登陆账号授予正确的权限，就是本篇的主要内容。

让人混淆的几个概念

1.主体

    主体是可以请求SQL SERVER资源的实体。主体按照作用范围可分为三类主体：Windows级别主体、服务器级别主体、数据库级别主体。master数据库中有两张视图sys.server_principals、sys.server_principals用于存储主体数据。先来看看sys.server_principals视图，执行以下语句：

select principal_id, name, type, type_desc from sys.server_principals ,视图说明：https://msdn.microsoft.com/zh-cn/library/ms188786(v=sql.120).aspx

    执行结果如下：

    查询结果包含了Windows登录账号（WINDOWS_LOGINS）、SqlServer登录账号(SQL_LOGIN)、服务角色(SERVER_ROLE)。Windows级别主体就是我们的Windows登录账号，服务器级别主体就是Sql Server登录账号和服务角色。接下来我们再执行以下语句：

select principal_id, name, type, type_desc from sys.database_principals，视图说明：https://msdn.microsoft.com/zh-cn/library/ms187328(v=sql.120).aspx

  执行结果如下：

    结果中包含Sql Server用户(SQL_USER)、Windows用户(WINDOWS_USER)、数据库角色(DATABASE_ROLE)，这三种数据都属于数据库级别主体。通过下图我们可以很容易的看出主体具体包括哪些数据。

    需要注意的是，数据库级别主体中包含有Sql Server用户和Windows用户。那么什么是用户？用户就是我们接下来的第二个概念。

2.数据库用户

    数据库用户是数据库级别的主体，被用于访问数据库层面的对象。一个独立的登录账号默认是不能够链接数据库的，必须和数据库用户建立对应关系才能根据用户的权限访问数据库。但是，一个数据库用户也是不能独立存在的，它也必须关联一个登录账号。前面说过，用户是访问数据库层面的对象。那么，数据库、登录账号、用户之间有什么关系？我们先看SQL Server Management Studio的一个操作：查看登录账号test2的账号信息，选择User Mapping页签。如下图所示：

   分析上图，如果想给一个登录账号设置数据库权限，我们不能直接把登录账号和数据库做关联，数据库只能和数据库用户做关联，然后数据库用户和登录账号关联。登录账号和用户的存储是有区别的，登陆账号存储在系统数据库master中，而用户是存储在数据库中的，并且都是存储在系统视图sys.sysusers中。例如，我们查询上图的用户test。在系统数据库master和用户数据库TestDb中查询sys.sysusers（视图说明：https://msdn.microsoft.com/zh-cn/library/ms179871(v=sql.120).aspx）视图，对比如下：

    通过上图我们明显看出，创建的用户test存储在testDb数据库中。并且对于同一个数据库，一个用户只能对应一个登陆账号。不同的数据库可以有相同名称的用户名。数据库、用户以及登录账号的关系可通过下面的关系图表示：

    上图中，数据库DbAdmin中的存储有登录账号1对1关系的用户，而数据库TestDb中也单独存储有登录账号1对1关系的用户。但登录账号数据是的并且保存在系统数据库master中。之前在操作SQL Server Management Studio中登录账号属性User Mapping页签数据时，我们能看到每个数据库其实还对应了一个"Default Schema"，Schema也叫做架构。架构就是本篇需要说明的第三个概念。

3.数据库架构

   架构（Schema）相当于存储数据库对象的一个容器，它是SQL Server 2005之后版本引入的。你可以理解架构为一个命名空间。在SQL Server 2000中其实也有架构的概念，不同的是SQL Server 2000的架构和用户是绑定的。例如 ，我新建一个用户heavi，Sql Server自动分配了一个叫做heavi的架构，并且用户heavi和架构heavi之间的关系不能更改。如果新建一张表Teacher，则为heavi.Teacher。但heavi离职时，Teacher这站表就很难维护。

    但Sql Server 2005之后，用户和机构是分离的。我可以把Teacher表的架构heavi分配给其他用户，方便管理。访问一张表一般有三种方式：

select * from Teacher;
select  * from heavi.Teacher;
select * from TestDb.heavi.Teacher;

   种方式只能是当前登录账号对应的用户默认Schema正好是heavi的情况才能正常使用。例如用户heavi对应的登录账号为sa，那么只有sa账号才能直接使用select * from Teacher语句。其他账号必须加上架构名称。

    数据库包含的对象包括系统表、用户表、视图、存储过程等。这些对象都被“包含”在架构下。例如下图，数据库表、视图、存储过程都挂在架构为dbo下。

服务器角色

查询角色

   -之前介绍了什么是主体，我们知道主体包含服务器级别主体。而服务器级别主体又包含有服务器角色。服务器角色的存在，方便操作员对服务器级别的管理。例如给登录账号授予sysadmin服务器角色后，该登录账号拥有Sql Server服务器的任何操作权限。Sql Server包括哪些服务器角色？从前面介绍的系统视图sys.server_principals可以查询所有的服务器角色。执行查询语句：

select sp.name as Name, sp.principal_id as Id, sp.sid as Sid, sp.type as Type, sp.type_desc as Type_Desc from sys.server_principals sp where sp.type = 'R'

     查询结果如下：

    查询的结果集就是服务器上包含的所有服务器角色。具体每个服务器角色包含哪些权限可查看说明：https://msdn.microsoft.com/zh-cn/library/ms188659(v=sql.120).aspx。

查询授权角色

    在我的权限管理系统中，分别查询出了所有的登录账号和服务器角色，如下图所示：

    当我选择登录列表中的某个登录账号时，系统会从数据库中查询当前选中账号授予了哪些服务器角色。由于服务器角色是固定不变的，微软直接把账号的服务器角色授权数据保存在系统视图sys.syslogins中。在权限管理服务器执行的查询语句如下：

select 
sid as Sid, name as Name, dbname as DbName,password as Password, language as Laguage, createdate as CreateDate,
sysadmin as IsSysAdmin, securityadmin as IsSecurityAdmin, serveradmin as IsServerAdmin,
setupadmin as IsSetupAdmin, processadmin as IsProcessAdmin, diskadmin as IsDiskAdmin,
dbcreator as IsDbCreator, bulkadmin as IsBulkAdmin, 1 as IsPublic 
from sys.syslogins where name = 'sa'

    查询结果如下：

    结果中，1表示已授权、0表示未授权 。所以账号sa授予了sysadmin和public服务器角色，其中public是默认角色，新创建的登录账号默认拥有public角色。

修改授权角色

    每个登录账号授予的服务器角色我们能够查询出来，有些时候我们也需要修改账号的服务角色。例如新创建的dbAdmin需要管理用户的数据库权限，则必须给 dbAdmin授予securityadmin服务角色。像修改登陆账号或者数据库时我们都是使用DDL语句操作，修改服务角色也不例外。我们先看下修改服务角色的参考语句：

ALTER SERVER ROLE server_role_name 
{
    [ ADD MEMBER server_principal ]
  | [ DROP MEMBER server_principal ]
  | [ WITH NAME = new_server_role_name ]
} [ ; ]--说明：https://msdn.microsoft.com/zh-cn/library/ee677634(v=sql.120).aspx

server_role_name表示角色名称，server_principal可以是登录账号或者用户定义的服务器角色。接下来再看看权限管理系统中修改授权角色的代码：

public void Update(string name, IList<string> roles)

        {
            string sql = string.Empty;
           foreach(var role in roles)
            {
                if (string.IsNullOrEmpty(role))
                {
                    throw new Exception("更新服务角色失败，参数有误。");
                }
                string[] arr = role.Split(':');
                bool value;
                if(arr.Length != 2 || string.IsNullOrEmpty(arr[]) || !bool.TryParse(arr[1], out value))
                {
                    throw new Exception("更新服务角色失败，参数不匹配");
                }
                if(arr[] == "public")
                {
                    continue;
                }

                string opt = value ? "ADD" : "DROP";
                sql += string.Format(@"ALTER SERVER ROLE [{0}]  {1} MEMBER [{2}] ;", arr[], opt, name);
            }
            DbHelper.Instance().ExecuteNonQuery(sql);
        }

     代码Update方法的参数name表示登录账号，roles表示服务器角色和操作项数据，例如其中一个role为：sysadmin:ADD。每个角色都会遍历执行一次。如删除sa的sysadmin角色，执行语句为：ALTER SERVER ROLE [sysadmin] DROP MEMBER [sa]。需要注意的是，public是默认授予的服务角色，不能修改。

数据库角色

查询角色

  和服务器角色相似，主体也包含了数据库级别主体，而数据库级别主体又包含了数据库角色。我们可以从系统视图sys.database_principals查询。执行查询语句：

select sid as Sid, name as Name, principal_id as Id, type as Type, type_desc as Type_Desc from sys.database_principals where type = 'R'

    查询结果如下：

   结果集中包含了所有的数据库角色，具体每个数据库角色包含哪些权限，可查看说明：https://msdn.microsoft.com/zh-cn/library/ms189121(v=sql.120).aspx。

查询架构

    架构的存储和数据用户存储相似，都是存储在数据库中。我们可以通过系统视图sys.schemas查询数据库中存在的架构数据。执行下面的语句：

USE [数据库];
select sc.name as SchemaName,sc.schema_id as Id, ps.type as Type,ps.type_desc as Type_Desc, ps.principal_id as PrincipalId, ps.name as PriincipalName 
from sys.schemas as sc 
inner join sys.database_principals ps on sc.principal_id = ps.principal_id
where ps.type = 'S'

    在执行查询之前必须要做数据库切换，切换到当前需要查询的数据库。执行结果如下：

    Schema是和用户关联的，因为每个用户都有一个默认的Schema。而用户的主体数据存储在database_principals中。系统视图sys.schemas的详细描述请查看：https://msdn.microsoft.com/zh-cn/library/ms176011(v=sql.120).aspx。

查询数据库用户和默认架构

    首先看下权限管理系统的数据库权限管理界面，如下图所示：

    整个界面包含了五个部分，分别是数据库列表、登录账号、数据库用户、默认架构、数据库角色。通过之前介绍的知识我们已经知道数据库和登录账号怎样查询了。查询数据库授权角色的步骤是先选择某个数据库，然后从登录账号下拉列表中选中某个登录账号。选择登录账号后，权限管理系统会根据选择的数据库和登录账号到数据库查询对应的数据库用户、默认架构以及授予了哪些数据库角色。

    首先分析怎样查询数据库用户和默认架构，这些数据关联了sys.sql_logins、sys.sysusers、sys.database_principals、sys.schemas等系统视图。它们之间的关系如下图所示：

    查询当前数据库中登录账号、数据库用户、架构数据的sql代码如下：

string sql = string.Format("USE {};select lo.sid as Sid, lo.name as LoginName, su.uid as UserId, su.name as UserName, sc.schema_id as SchemaId, pr.default_schema_name as SchemaName from sys.sql_logins lo " +
                        "inner join sys.sysusers su on lo.sid = su.sid " +
                        "inner join sys.database_principals pr on lo.sid = pr.sid " +
                        "left join sys.schemas sc on pr.default_schema_name = sc.name " +
                        "where lo.name = '{1}';", dbName, loginName);

    例如，我当前查询数据库testDb下登录账号为test2的用户和默认shcema数据，结果如下：

    查询结果就包含了当前登录账号test2的数据库用户test以及用户test默认的schema为t。

查询授权角色

    在同一个数据库中，不同的用户有不同的数据库角色。数据库用户和数据库角色同属于数据库级别主体，都存储在系统视图sys.database_principals中。数据库用户类型为S，数据库角色类型为R。如果用户授予了某些角色，用户和角色必定有关联关系，这些关联关系存储在系统视图sys.database_role_members。执行以下SQL语句：

select * from sys.database_role_members，视图说明：https://msdn.microsoft.com/zh-cn/library/ms189780(v=sql.120).aspx

    查询结果如下图所示：

    sys.database_role_members视图包含两个字段，role_principal_id表示角色主体ID，member_pricinpal_id表示用户主体ID。如果需要查询指定数据库下某个用户授予的数据库角色，可通过权限管理系统执行的SQL代码查询：

USE testDb;
select dbp1.principal_id as Id, dbp1.name as Name, dbp1.type as Type 
from sys.database_role_members rm 
inner join sys.database_principals dbp1 on rm.role_principal_id = dbp1.principal_id 
inner join sys.database_principals dbp2 on rm.member_principal_id = dbp2.principal_id 
where dbp2.name = 'test'

    在查询前必须切换到需要查询的数据库，因为数据库角色、用户数据都存储在数据库中。例如，我们需要查询testDb（dbName = 'testDb'）数据库中用户为test（userName = 'test'）的数据库角色。查询结果如下：

    查询结果表明test用户授予了db_owner和db_datareader两个数据库角色。而我们的权限管理系统查询结果界面为：

    上面表示的整个流程为数据库testDb下，登录账号test2对应的用户为test，而用户test默认的架构为t。并且用户test拥有db_owner和db_datareader数据库角色。public是每个用户默认拥有的角色。那么查询的数据到底是否正确，我们可以通过SQL Server Management Studio工具中的login Properties校验。打开登录账号test2的login Properties窗口，结果如下图所示：

    通过对比可以看出，testDb下登录账号test2的用户、默认架构以及数据库角色和我们权限管理系统查询结果完全一致。

修改权限角色

    1.登录账号对应的用户没有改变，只改变了默认Schema或者角色

    我们可以查询数据库下某个账号对应的用户和默认架构，那么我们也可以修改对应的用户和架构。我们应该记得，像操作系统级别元数据，我们一般都使用数据定义语言（DDL）。修改用户的 DDL语言定义如下：

-- SQL Server Syntax
ALTER USER userName  
     WITH <set_item> [ ,...n ]
[;]
<set_item> ::= 
      NAME = newUserName 
    | DEFAULT_SCHEMA = { schemaName | NULL }
    | LOGIN = loginName
    | PASSWORD = 'password' [ OLD_PASSWORD = 'oldpassword' ]
    | DEFAULT_LANGUAGE = { NONE | <lcid> | <language name> | <language alias> }--说明：https://msdn.microsoft.com/zh-cn/library/ms176060(v=sql.120).aspx

    用户不变情况下我们只用修改默认架构即可，SQL语句比较简单，如下所示：

ALTER USER {} WITH DEFAULT_SCHEMA = {1} --{0}表示用户，{1}表示默认架构名称

    默认架构修改完了 ，我们还得修改用户对应的数据库角色。修改用户的数据库角色和修改登录账号的服务器角色相似，都是遍历所有的数据库角色，按照每个角色的勾选情况删除或者增加每个角色的用户成员。数据库角色增加或删除成员的方式和服务器角色不同，是通过系统存储过程操作，增加成员的存储过程为sp_addrolemember（说明：https://msdn.microsoft.com/zh-cn/library/ms187750(v=sql.120).aspx），删除成员的存储过程为sp_droprolemember（说明：https://msdn.microsoft.com/zh-cn/library/ms188369(v=sql.120).aspx）。权限管理系统中操作数据库角色成员的代码如下：

private string CreateExecuteDbRoleSql(DatabaseUserMapping mapping)

        {
            string setDbRoleSql = string.Empty;
            if (mapping.RoleList != null && mapping.RoleList.Count > )
            {
                setDbRoleSql = string.Format("USE {0};", mapping.DbName);
                foreach (var role in mapping.RoleList)
                {
                    if(role.Name == "public")
                    {
                        continue;
                    }
                    var produreName = role.IsAuthorized ? "sp_addrolemember" : "sp_droprolemember";
                    setDbRoleSql += string.Format("EXEC {2} @rolename = '{0}',@membername = '{1}';", role.Name, mapping.UserName, produreName);
                }
            }
            return setDbRoleSql;
        }

    步操作还是使用USE {0}切换到当前数据库，然后遍历角色集合，如果当前用户授予了角色（role.IsAuthorized为true），则执行sp_addrolemember。否则执行sp_droprolemember。

   2.登录账号对应的用户已改变

    之前的内容我们有讲到同一个数据库下，登录账号和用户是一一对应关系。也就是说同一个用户不能关联两个用户。在这样的一个背景下，现在我们修改了登录账号对应的用户，那么之前的用户就被丢弃了。例如，登录账号Tom，对应用户为tom。现在我把Tom对应的用户修改为joly，之前的用户tom就被丢弃了。所以，如果登录账号对应的用户改变后，我们需要做一系列的操作。具体步骤如下：

    步骤1，删除之前用户对应的数据库角色

    首先把之前用户授予的数据库角色查询出来（在"查询授权角色"已经讲过怎样查询授权的数据库角色）。然后逐个遍历角色，执行删除语句：

EXEC sp_droprolemember @rolename = '{0}' , @membername =  '{1}' --{0}表示角色名称，{1}表示用户名称

    步骤2，删除之前的用户

    删除用户使用DDL的DROP操作，执行语句如下：

USE {};DROP USER {1}，--{0}表示数据，{1}表示用户名，DROP说明：https://msdn.microsoft.com/zh-cn/library/ms189438(v=sql.120).aspx

   步骤3，创建新用户

    创建用户使用DDL的CREATE USER操作，执行语句如下：

CREATE USER {} FOR LOGIN {1} WITH DEFAULT_SCHEMA = {2}，CREATE说明：https://msdn.microsoft.com/zh-cn/library/ms173463(v=sql.120).aspx

    操作语句其中的{0}表示用户名称，{1}表示登录账号，{2}表示默认架构名称。

    步骤4，给新用户授予数据库角色

    给新用户授予数据库角色方式和前面一样，直接遍历角色，然后调用存储过程sp_addrolemember为数据库角色增加用户成员。

重点

    前面介绍的内容更偏重于怎样授权，接下来举个实际的例子来说明怎样使用这些授权。首先使用我们的权限管理系统创建数据库OperationDb，然后创建两个登录账号opt1和opt2。刚创建的登录账号opt1和opt2是没有任何权限的，所以需要给这两个账号授权。

    登录账号opt1授权如下：

    登录账号opt2授权如下：

    两个账号都只授予了db_accessadmin数据库角色。那么现在我用opt1创建一张表OptTable1会是怎样的结果？执行结果如下：

   执行结果说明opt1账号没有CREATE TABLE的权限，现在我们再为账号opt1和opt2都授予DDL操作权限db_ddladmin角色。然后我们再通过opt1创建OptTable1表，通过opt2创建OptTable2表，都能创建成功。我们可通过数据库管理工具查看到这两张表，如下图所示：

    分析上图，OptTable1表前面标注了opt1，而OptTable2表前面标注了opt2。opt1表示的是登录账号opt1对应的用户opt1的默认架构opt1,opt2表示登录账号opt2对应的用户opt2的默认架构opt2。下面的图片表示的比较直观：

    表创建好了后，分别使用登录账号opt1和opt2查询opt1.OptTable1、opt2.OptTable2表。opt1和opt2查询结果对比如下：

    通过结果可看出在ddladmin角色权限下，账号opt1没有权限查看Schema为opt2下面的表数据，而opt2没有权限查看Schema为opt1下面的表数据。之前说过架构相当于一个容器，而架构opt1和架构opt2相当于同级的两个容器。整个层次关系如下图所示：

    登录账号Opt1只能操作Schema为opt1下面的对象，而登录账号opt2只能操作Schema为opt2下面的对象。如果要想让opt1同时拥有操作Schema为opt1和opt2下面的表，我们需要把权限提高到数据库层面上。而数据库角色db_datareader，可以访问数据库下面的所有Schema。所以分别为账号opt1和opt2授予db_datareader后，现在可以访问OperationDb下面的所有表数据了。

总结

   通过本篇的内容介绍，在回顾篇头提出的几个问题，现在应该能够回答个一二。本篇先介绍了几个常用到的概念，包括主体、数据库用户、数据库架构。然后分别介绍了服务器角色和数据库角色授权的查询、修改、删除功能。在”数据库角色“中也介绍了用户的增删改查以及登录账号、用户和Schema的关系。

本文来源https://www.cnblogs.com/w-wanglei/p/5104266.html