Security 10：权限管理

在进行权限管理时，应遵守“最低权限”原则，即每个人只授予必需的最小权限。相对于授予的权限，数据库中还有一个特殊的权限，那就是所有权(Ownship)。

SQL Server 用于管理权限的TSQL命令有：GRANT用于授予权限，REVOKE 用于移除已经被GRANT/DENY的权限，而DENY用于防止安全主体通过GRANT获得权限。DENY一旦执行，Principal在Securable上的权限就被禁用了。但是，SQL Server的权限空间不是扁平的，是立体的，在不同的安全上下文(Security context)中，不同的权限空间(Permission Space)中，这三个命令的优先级是不同的。这就意味着，即使执行GRANT授予权限，用户不一定有权限，这是因为在特定的权限空间里，Deny命令禁用了用户的权限，同时Grant命令的优先级低于Deny。

一，管理权限的规则

在管理权限时，要注意权限的上下文、权限的立体空间和权限的优先级

1，安全上下文和权限空间

安全上下文(Security Context)，是跟user 或 login 相关的环境，用户可以通过EXECUTE AS 来切换安全上下文。安全上下文主要包括：Login、User、Role membership、Windows Group membership

权限空间，是指安全对象(Securable)和包含安全对象的所有安全对象类(Securable class)，比如，表包含在schema 中，schema是表的安全对象类；而database包含schema，database是schema的安全对象类。访问表的权限，受到表、schema和database的权限的影响，这三个对象构成一个权限空间，访问受到权限空间的约束。

2，权限的优先级

当这三个命令作用于同一个安全对象(Securable)时，情况会变得复杂，不仅需要考虑权限空间，还需要考虑权限的优先级。

• 在同一个安全主体范围内对同一个安全对象设置权限，GRANT子句会移除DENY和REVOKE子句设置的权限，这三个命令的优先级相同，后执行的语句会移除先执行语句的效果。
• 但是，当相同的权限作用于同一安全主体的不同范围时，如果DENY 作用于更高的范围内，那么DENY优先，但是在更高的范围内，REVOKE不优先。
• 这里有一个例外，列级别的GRANT语句，会覆盖Object级别的DENY语句，但是后续Object级别的DENY语句会覆盖列级别的GRANT语句。

权限是累积的，一个User可以通过多种途径(比如Grant、Role和Group memberhsip)来获得授权，Revoke只能回收某一个途径上的权限，但是Deny会禁止用户获得授权。

举个例子，一个User通过Grant获得表1的SELECT权限，通过Role获得表1的SELECT权限：

• 情况1：当使用REVOKE命令回收GRANT授予的SELECT 权限时，该User仍然可以通过Role的权限来查询表1。
• 情况2：当使用DENY命令拒绝表1的权限时，该User没有权限查询表1。

3，权限的层次结构

权限的层次结构是一种父子结构，拥有父级别对象的权限，默认拥有所有子级别对象的权限。举个例子，如果有数据库级别的SELECT权限，那么就有了数据库下所有Schema的SELECT权限；如果有Schema的SELECT权限，那么就有了Schema下所有对象的SELECT权限，这种权限的结构构成权限空间。

权限是一个覆盖式的权限，举个例子，Control表示所有的权限，当对一个对象授予Control权限，意味着授予所有其他的权限。

数据库级别的权限：

• 在数据库级别授予操作数据库对象的权限，比如 EXECUTE、DELETE、INSERT、SELECT、UPDATE、REFERENCES、VIEW DEFINITION，实际上，授予的是操作数据库中所有对象的权限。
• 数据库级别独有的权限：ALTER、BACKUP DATABASE、BACKUP LOG、CHECKPOINT、CONNECT、CREATE TABLE、CREATE VIEW、CREATE PROCEDURE等

二，权限管理的实现

权限管理涉及到Principal、Securable和Permission三个概念。Principal可以是单个User，也可以是多个User构成的Windows Group；Securable可以是单个数据库对象，也可以是包含多个数据库对象的schema；同时，User也可以通过role获得数据库对象的权限。

第一种方式，为每一个User设置单个Securable的权限

 第二种方式，创建Windows Group，把User分组，为每一个Windows Group设置单个Securable的权限，简少了授权用户的数量。

第三种方式，通过数据库 role来设置权限，通过Role来组织Securable，减少了授权对象的数量。

第四种方式，通过Schema来设置权限，在一个Schema下包含多个Securables，并通过Role来管理Scurable，通过Windows Group来管理User，这种方式虽然复杂，但是权限的管理非常精细和灵活。

三，所有权和所有权链

对象的所有者对一个对象拥有所有可能的权限，并且这些权限不能禁止。CONTROL权限可以执行与对象所有者几乎相同的操作，但是所有权和授权是不同的。对象的所有者通常是其创建者，但是可以在创建时使用AUTHORIZATION子句指定其他所有者，也可以把Ownship转移给其他Principal。

如何在不授予基础表访问权限的情况下，仅对视图或任何其他类型的程序授予SELECT权限呢？答案是使用所有权链(Ownership-chaining)。通常情况下，当用户从视图中查询数据时，系统做两次权限检查，第一次是检查用户是否有权限查询视图，第二次是在视图引用基础表时检查用户是否有权限查询基础表，由于用户没有基础表的权限，因此第二次权限检查失败。

所有权链(Ownership-chaining)通过绕过第二次权限检查来避免这种情况，否则将在视图引用基础表时进行第二次权限检查。当链接的对象(underlying table)与调用对象(view)具有相同的所有者时，权限检查将被完全绕开。

如果一个user在具有Ownership权限的Schema中创建视图，因为它视图的所有者，就是被视图引用的基表的所有者，这是一个链：我是Schema的所有者，那么Schema下的所有对象的Owner都是我，我有权限访问视图，但不能访问基础表。

 

所有权链只适用于SELECT, INSERT, DELETE, UPDATE 和MERGE，以及 SP和函数的EXECUTE 权限，出于安全考虑，不适用于使用动态SQL的程序中。要在SP、函数和触发器中执行动态SQL操作，需要使用使用WITH EXECUTE AS子句使用权限模拟。只要调用者对动态SQL中引用的数据库对象没有权限，就可以使用此EXECUTE AS子句，但要格外小心。它通过将执行上下文切换到模拟用户来实现。所有代码，甚至是嵌套模块，都将在模拟用户的安全上下文中执行。当前正在执行的批处理在执行代码时会临时获得例程所有者的权限，而不是所有者的身份。这样，用户只能通过更改正在执行的代码来将特权用于任何其他目的。仅在过程执行完成后或在REVERT语句上，执行上下文才还原为原始调用方。

所有权链的问题在于，除了第一次权限检查之外，所有权链接会完全绕过权限检查，甚至都优先于DENY ACCESS。

参考文档：

Getting Started with Database Engine Permissions

Schema-Based Access Control for SQL Server Databases

Permissions (Database Engine)