SQL Server 中深入理解GRANT、REVOKE 和 DENY 的区别_revoke在数据库中是什么意思

在SQL Server数据库登陆账户、数据库用户权限管理中，经常会授予权限、回收权限，有时还会拒绝权限。GRANT、REVOKE是我们常用的，但有时会遇到使用DENY的情形。从英文单词的字面意思来看，GRANT是赋予权限，REVOKE是收回我们已经授予的权限；而DENY是禁掉某个权限。这三者内部到底有什么联系、区别？什么情景下使用GRANT？什么情景下使用REVOKE？什么情景下使用DENY？单独从文字描述上，我们很难理解。接下来，本文将通过试验的方式，逐步揭示两者的秘密。

本打算将结论放在各试验后面揭示，写试验案例时发现写的又臭又长，最终还是决定将结论提前，让我们能找到读阅读的主线：

1. GRANT赋予主体各类权限。

2. 用户成为角色的成员可以继承角色的权限。

3. REVOKE、DENY同时可以收回GRANT赋予的权限，但两者影响不同，REVOKE仅仅收回了权限，DENY撤销GRANT赋予的权限后并拒绝该权限，具体影响见下面的6、8结论。

4. REVOKE可以撤销DENY拒绝的权限。

5. 赋予（GRANT）数据库SELECT权限，则获得数据库下所有子对象（如数据库的表、视图及所有列，包括新建的表和列）的SELECT权限，某些单独拒绝（DENY）SELECT的子对象除外。

6. 拒绝（DENY）数据库的SELECT权限，则拒绝父对象下所有子对象（如数据库的表、表的列，包括新建的表和列）的SELECT权限，即使单独赋予子对象的SELECT权限。

7. 赋予表SELECT权限，则对表的所有列（包括新建列）有SELECT权限，单独拒绝SELECT的列除外。

8. 拒绝表的SELECT权限，则拒绝表的所有列（包括新建列）SELECT权限，单独赋予SELECT的列除外。

值得注意的是，在EXEC AS user上下文中，无论是赋予表SELECT权限，还是拒绝表SELECT权限，对新增列都无SELECT权限（这是在SQL Server 2008（SP2）版本试验中发现）其他版本表现的有所不同，所以如果有使用这样情景，需要重点测试，以免出现不必要的影响。

应用场景：根据最小化用户权限原则，对重要信息表，我们可以拒绝所有用户的SELECT权限，而仅仅赋予特定用户查看特定列的SELECT权限，最大化避免信息泄漏。

到此本文的核心结论您已经了然，当然文字描述相对较为抽象，如果您有时间，可以看完此文。当然我建议您耐心看完后文，并亲自动手实践，一则可以加深我们对三者区别的理解；二则对于新的内容，我们可以使用试验方式去学习，这是一个很好的学习方式，避免人云亦云，做到真正的了然于胸。最后，本文仅仅针对表的SELECT权限进行说明，有兴趣的同学可以参照本文的测试过程，设计INSERT、DELETE、UPDATE、EXEC等权限的样例，进行试验学习。

测试的环境

数据库的版本为 SQL Server 2008（SP2）企业版：

SELECT @@VERSION

Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)   Sep 16 2010 19:43:16   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

测试准备：创建一个登陆名Jack，在test数据库下创建用户Jack，关联到登陆名Jack；然后在test 数据库下创建表test、test1，并在Jack用户的上下文下执行查询，脚本如下

USE master
GO
IF EXISTS(SELECT name FROM sys.database_principals WHERE name='Jack')
      DROP LOGIN Jack
CREATE LOGIN Jack WITH PASSWORD='Password'
    ,CHECK_POLICY=OFF,CHECK_EXPIRATION=OFF
USE test
GO
IF EXISTS(SELECT name FROM sys.sysusers WHERE name='Jack')
      DROP USER Jack
CREATE USER Jack FOR LOGIN Jack
EXEC sys.sp_helprotect NULL,Jack
USE test
CREATE TABLE [dbo].[test](
      [a] [int] IDENTITY(1,1) NOT NULL,
      [b] [varchar](30) NULL,
      [d] [datetime] NULL
)
CREATE TABLE [dbo].[test1](
      [e] [int] IDENTITY(1,1) NOT NULL,
      [f] [varchar](30) NULL,
      [g] [datetime] NULL
)
EXEC AS USER='Jack'
GO
SELECT * FROM test
GO
REVERT
GO

消息229，级别14，状态5，第1 行

拒绝了对对象'test' (数据库'test'，架构'dbo')的SELECT 权限。

因为test数据库下新创建的Jack用户，没有查询权限，所以报了如上错误。

GRANT、REVOKE和DENY三者之间的关系

GRANT赋予用户各种权利，REVOKE、DENY收回GRANT权限。

赋予Jack用户SELECT权限，并使用sp_helprotect查看Jack的权限，脚本如下：

--赋予Jack用户SELECT权限
​GRANT SELECT TO Jack
--查看Jack用户拥有的权限
EXEC sys.sp_helprotect NULL,Jack

从结果可以看到，数据库用户Jack拥有SELECT权限（刚刚赋予的），同时还有CONNECT连接权限（创建数据库用户时默认赋予的）。

此时在Jack 上下文中执行对test表的查询是OK的：

 

试验1：使用REVOKE收回SELECT权限，再次查看Jack拥有的权限

--试验1：
--收回Jack SELECT 权限
REVOKE SELECT FROM Jack
--查看Jack用户拥有的权限
EXEC sys.sp_helprotect NULL,Jack

从结果可以看到Jack的SELECT权限没有了。

试验2：重新赋予Jack 用户SELECT权限，使用DENY 拒绝掉其SELECT权限，并查看Jack的权限：

--试验2：
--重新赋予Jack用户SELECT 权限
GRANT SELECT TO Jack
--查看Jack用户拥有的权限
EXEC sys.sp_helprotect NULL,Jack
--拒绝Jack 的SELECT 权限
DENY SELECT TO Jack
--查看Jack用户拥有的权限
EXEC sys.sp_helprotect NULL,Jack

比较REVOKE和DENY 的结果，可以看出REVOKE后，Jack拥有的SELECT权限直接没有了；而DENY的结果是将GRANT 改为了DENY。此时我们再次在Jack用户的上下文中执行test表的查询，结果如下：

这个错误和上面没有SELECT权限相同。同样说明了REVOKE和DENY均有收回GRANT赋予的权限的功能。那么是不是说明DENY是多余的呢？我们继续下面的试验：

--使用REVOKE 收回DENY 拒绝的权限
REVOKE SELECT FROM Jack
--查看Jack用户拥有的权限
EXEC sys.sp_helprotect NULL,Jack

从结果可以看到，REVOKE不仅可以收回GRANT赋予的权限，同样可以收回 DENY 拒绝的权限；同时DENY可以收回GRANT赋予的权限。这虽然反应了REVOKE和DENY的一点不同，但还不能体现其真正内在的区别。下面我继续设计稍显复杂的试验，继续深入。

新增加列对权限的继承

GRANT SELECT TO Jack
EXEC sp_helprotect NULL,Jack
--增加列c
ALTER TABLE test ADD c int
EXEC AS USER='Jack'
GO
SELECT c FROM test
REVERT
GO

--删除增加的列
ALTER TABLE test DROP column  c
DENY SELECT TO Jack
EXEC sp_helprotect NULL,Jack
ALTER TABLE test ADD c int
EXEC AS USER='Jack'
GO
SELECT c FROM test
REVERT
GO

结论：赋予/拒绝数据库的SELECT的权限，则会赋予/拒绝新表或者表的新列的SELECT权限。

--删除增加的列
ALTER TABLE test DROP column  c
GRANT SELECT ON OBJECT::test TO Jack
EXEC sp_helprotect NULL,Jack
--增加列c
ALTER TABLE test ADD c int
EXEC AS USER='Jack'
GO
SELECT c FROM test
REVERT
GO

--删除增加的列
ALTER TABLE test DROP column  c
DENY SELECT ON OBJECT::test TO Jack
EXEC sp_helprotect NULL,Jack
ALTER TABLE test ADD c int
EXEC AS USER='Jack'
GO
SELECT c FROM test
REVERT
GO

结论：赋予/拒绝表的SELECT的权限，则会赋予/拒绝表的新列的SELECT权限。

赋予/拒绝数据库或表SELECT权限

试验3：赋予Jack用户整个数据库的SELECT权限，拒绝Jack用户对表test 的SELECT权限，查看Jack的权限，并在用户Jack上下文中查询表test。

--试验3：
GRANT SELECT TO Jack
DENY SELECT ON OBJECT::test TO Jack
EXEC sys.sp_helprotect  NULL ,Jack
EXEC AS USER='Jack'
GO
SELECT * FROM test
SELECT * FROM test1
GO
REVERT
GO

结论：赋予用户（Jack）父级对象（本试验指数据库）SELECT权限，则用户具有除单独拒绝的子对象（这里拒绝了test表的SELECT权限），对其余子对象均有SELECT权限。

--恢复Jack权限
REVOKE SELECT TO Jack
REVOKE SELECT ON OBJECT::test TO Jack
EXEC sys.sp_helprotect  NULL ,Jack

试验4：反之拒绝了Jack对数据库的SELECT权限，赋予Jack 对数据库下表test 的SELECT权限。

--试验4：
DENY SELECT TO Jack
GRANT SELECT ON OBJECT::test TO Jack
EXEC sys.sp_helprotect  NULL ,Jack
EXEC AS USER='Jack'
GO
SELECT * FROM test
SELECT * FROM test1
GO
REVERT
GO

从试验3、4获得这样的一个结论：拒绝用户数据库SELECT权限，则会拒绝用户对数据库所有对象的SELECT权限，即使再单独赋予该对象SELECT权限。而赋予用户数据库SELECT权限，仅仅拒绝用户对数据库中的某些对象的SELECT权限，则用户对除拒绝对象外的其他对象仍然有SELECT权限。

恢复Jack的权限

--恢复Jack权限
REVOKE SELECT TO Jack
REVOKE SELECT ON OBJECT::test TO Jack

赋予/拒绝数据库或表列的SELECT权限

试验5：赋予Jack用户SELECT权限，拒绝Jack用户对表test的字段a、d的SELECT权限，然后在Jack用户的上下文中，分别查询test全表、test的字段b，试验脚本如下。

--试验5：
--赋予Jack用户SELECT权限
GRANT SELECT TO Jack
--禁止用户Jack 查看表test字段a、d
DENY SELECT ON OBJECT::dbo.test(a,d) TO Jack
--查看Jack用户拥有的权限
EXEC sys.sp_helprotect NULL,Jack
EXEC AS USER='Jack'
GO
SELECT * FROM test
GO
SELECT b FROM test
REVERT
GO

显然，对test的全表查询报错，在消息下可以看到的错误如下：

这样即使通过GRANT给予了用户Jack数据库的查询权限，但拒绝了Jack用户对表test 的字段a、d的SELECT权限，Jack 用户只能查询test表的b字段，而不能查询test表的a、d字段。

恢复Jack权限：

REVOKE SELECT FROM Jack
REVOKE SELECT ON OBJECT::test(a,d) FROM Jack

试验6：反过来，拒绝Jack的SELECT权限，赋予Jack用户对表test 的字段a、d的SELECT权限

--试验6：
--赋予Jack用户SELECT权限
DENY SELECT TO Jack
--禁止用户Jack 查看表test字段a、d
GRANT SELECT ON OBJECT::dbo.test(a,d) TO Jack
--查看Jack用户拥有的权限
EXEC sys.sp_helprotect NULL,Jack
EXEC AS USER='Jack'
GO
SELECT * FROM test
GO
SELECT a,d FROM test
REVERT

这样即使Jack用户对表test的字段a、d有SELECT权限，但是由于对Jack拒绝了SELECT权限，所以无论是对test表的a、d字段，还是对test表都没有了SELECT权限。即拒绝父对象（这里只数据库）的SELECT权限，则会拒绝父对象的所有子对象的SELECT权限，即使另外赋予该子对象（这里只表test的字段a、d列）SELECT权限。

恢复Jack权限：

REVOKE SELECT FROM Jack
REVOKE SELECT ON OBJECT::test(a,d) FROM Jack

赋予/拒绝表或表列的SELECT权限

试验7：拒绝用户Jack对表test 的SELECT 权限，赋予用户Jack对表test字段a、d的SELECT权限：

--试验7：
--赋予Jack用户SELECT权限
DENY SELECT ON OBJECT::dbo.test TO Jack
--禁止用户Jack 查看表test字段a、d
GRANT SELECT ON OBJECT::dbo.test(a,d) TO Jack
--查看Jack用户拥有的权限
EXEC sys.sp_helprotect NULL,Jack
EXEC AS USER='Jack'
GO
SELECT * FROM test
GO
SELECT a,d FROM test
REVERT
GO

拒绝父对象（这里指表test）的SELECT权限，则拒绝除另外赋予了SELECT的子对象（这里指test表的列a、d）之外的所有子对象，包括对象新增的子对象（新增字段），下面的试验8刚好说明了这一点。

试验8：在试验7的基础上为表test增加一列c，然后在Jack上下文中查看c列。

--试验8：
ALTER TABLE test ADD  c int
--查看Jack用户拥有的权限
EXEC sys.sp_helprotect NULL,Jack
EXEC AS USER='Jack'
GO
SELECT * FROM test
GO
SELECT c FROM test
REVERT
GO

用户Jack使用DENY拒绝表的SELECT权限，表新增的字段的SELECT权限对用户Jack也是拒绝的。

从试验7、8这两个试验我们可以得到DENY的一个应用场景，对于特定的信息（字段）如涉及用户隐私的信息（姓名、身份证号、手机号、银行卡号、密码、虚拟币余额等等），一般用户可以赋予表的查询权限，而拒绝其查询、更新特定隐私信息的权限，从而使用户权限最小化。

恢复用户Jack 权限，并恢复test表结构：

REVOKE SELECT ON OBJECT::test FROM Jack
REVOKE SELECT ON OBJECT::test(a,d) FROM Jack
--删除增加的列
ALTER TABLE test DROP column  c

试验9：赋予用户Jack对表test的SELECT权限，禁止用户Jack查看表test的字段a、d列：

--试验9：
--赋予Jack用户SELECT权限
GRANT SELECT ON OBJECT::dbo.test TO Jack
--禁止用户Jack 查看表test字段a、d
DENY SELECT ON OBJECT::dbo.test(a,d) TO Jack
--查看Jack用户拥有的权限
EXEC sys.sp_helprotect NULL,Jack
EXEC AS USER='Jack'
GO
SELECT * FROM test
GO
SELECT b FROM test
REVERT
GO

赋予表test的SELECT权限，拒绝对表的字段a、d的SELECT权限，用户对表test的其他字段（如试验中的b列）仍有SELECT权限。

试验10：在试验9的基础上，为表test新增字段c，检验其是否具有SELECT权限

--试验10：
--表test 增加字段c
ALTER TABLE test ADD  c int
--查看Jack用户拥有的权限
EXEC sys.sp_helprotect NULL,Jack
EXEC AS USER='Jack'
GO
SELECT * FROM test
GO
SELECT c FROM test
REVERT
GO

由于使用EXEC AS user='Jack' 上下文的问题，使用GRANT 为用户Jack赋予表test 的SELECT权限，用户Jack获得了新增列c的SELECT权限。但此时用户并没有列c的SELECT权限（试验环境SQL Server 2008（SP2） 这应该是SQL Server的一个Bug。SQL Server 2016（RTM-GDR）版本只要对列DENY了SELECT权限，对所有表都没有了SELECT权限；SQL Server 2016（SP1）又恢复了SQL Server 2008（SP2）情景 ）。当我使用Jack用户直接登录服务器，连接数据库test，查询新列c是有SELECT权限的。对于使用 EXEC AS user 的情景，此时需要对新列单独赋予SELECT权限：

GRANT SELECT ON OBJECT::test(c) TO Jack

试验7、8、9、10表明，根据最小化权限原则，如果想要用户查看表尽可能少的信息，我们可以拒绝表的SELECT权限，再赋予用户特定字段的SELECT权限。以避免新增加的字段被不必要的用户看到，造成信息泄漏。

所以要慎用DENY，原因为SQL Server 中使用EXEC AS user上下文的情景和直接登录时，对新列的权限界定不一致。

恢复用户Jack 权限，并恢复test表结构：

REVOKE SELECT ON OBJECT::test FROM Jack
REVOKE SELECT ON OBJECT::test(a,d) FROM Jack
--删除增加的列
ALTER TABLE test DROP column  c

数据库角色成员继承角色权限的试验

用户除了可以直接对其赋予、拒绝权限外，还可以通过成为角色的成员，继承其权限，接下来我将试验用户直接获得的权限和从角色中继承的权限或者不同角色权限之间的影响。

首先在数据库test中创建两个角色，GrantRole和DenyRole，并查看其具有的权限：

--创建数据库角色
USE test
GO
CREATE ROLE GrantRole
CREATE ROLE DenyRole
--查看数据库角色权限
EXEC sys.sp_helprotect  NULL ,GrantRole
EXEC sys.sp_helprotect  NULL ,DenyRole

可以看到，数据库角色刚创建的时候是没有任何权限的，所以报没有要报告的匹配行错误。

分别对角色/用户赋予或拒绝相同对象的权限测试

试验11：赋予角色对数据库的SELECT权限，将Jack加入为其成员，拒绝Jack对数据库的SELECT权限，检验在用户Jack上下文中执行对表test的查询情况

--试验11：
GRANT SELECT TO GrantRole
EXEC sys.sp_addrolemember GrantRole,Jack
DENY SELECT TO Jack
EXEC sp_helprotect  NULL ,Jack
EXEC sp_helprotect  NULL ,GrantRole
EXEC AS USER='Jack'
GO
SELECT * FROM test
GO
REVERT
GO

 

试验12：拒绝角色GrantRole对数据库的SELECT权限，赋予用户Jack对数据库的SELECT权限，检验在用户Jack上下文中执行对表test的查询情况

--试验12：
DENY SELECT TO GrantRole
GRANT SELECT TO Jack
EXEC sp_helprotect  NULL ,Jack
EXEC sp_helprotect  NULL ,GrantRole
EXEC AS USER='Jack'
GO
SELECT * FROM test
GO
REVERT
GO

结论：用户权限和用户从数据库角色中继承的权限互斥时（GRANT<<=>>DENY），用户最终获得的是拒绝权限

恢复角色RrantRole和用户Jack的权限：

REVOKE SELECT FROM GrantRole
REVOKE SELECT FROM Jack

试验13：赋予角色GrantRole对数据库的SELECT权限，拒绝用户Jack对数据库的SELECT权限，检验在用户Jack上下文中执行对表test的查询情况

--试验13：
GRANT SELECT ON OBJECT::test TO GrantRole
DENY SELECT ON OBJECT::test TO Jack
EXEC sp_helprotect  NULL ,Jack
EXEC sp_helprotect  NULL ,GrantRole
EXEC AS USER='Jack'
GO
SELECT * FROM test
GO
REVERT
GO

结论同试验12.

 

试验14：拒绝角色GrantRole对数据库对象表test的SELECT权限，赋予用户Jack对数据库对象表test的SELECT权限，检验在用户Jack上下文中执行对表test的查询情况

--试验14：
DENY SELECT ON OBJECT::test TO GrantRole
GRANT SELECT ON OBJECT::test TO Jack
EXEC sp_helprotect  NULL ,Jack
EXEC sp_helprotect  NULL ,GrantRole
EXEC AS USER='Jack'
GO
SELECT * FROM test
GO
REVERT
GO

结论同试验12.

恢复角色GrantRole、用户Jack的权限

REVOKE SELECT ON OBJECT::test TO GrantRole
REVOKE SELECT ON OBJECT::test TO Jack

试验15：拒绝角色GrantRole对数据库对象表test的a、d列SELECT权限，赋予用户Jack对数据库对象表test的a、d列SELECT权限，检验在用户Jack上下文中执行对表test的查询情况

--试验15：
DENY SELECT ON OBJECT::test(a,d) TO GrantRole
GRANT SELECT ON OBJECT::test(a,d) TO Jack
EXEC sp_helprotect  NULL ,Jack
EXEC sp_helprotect  NULL ,GrantRole
EXEC AS USER='Jack'
GO
SELECT * FROM test
GO
REVERT
GO

结论同试验12.

试验16：赋予角色GrantRole对数据库对象表test的a、d列SELECT权限，拒绝用户Jack对数据库对象表test的a、d列SELECT权限，检验在用户Jack上下文中执行对表test的查询情况

--试验16：
GRANT SELECT ON OBJECT::test(a,d) TO GrantRole
DENY SELECT ON OBJECT::test(a,d) TO Jack
EXEC sp_helprotect  NULL ,Jack
EXEC sp_helprotect  NULL ,GrantRole
EXEC AS USER='Jack'
GO
SELECT * FROM test
GO
REVERT
GO

试验11~16说明，赋予或拒绝角色或用户对相同对象的权限时，拒绝权限具有较高优先级

恢复角色GrantRole、用户Jack权限：

REVOKE SELECT ON OBJECT::test(a,d) TO GrantRole
REVOKE SELECT ON OBJECT::test(a,d) TO Jack

用户从不同角色中继承权限的相互影响

试验17：赋予角色GrantRole对数据库SELECT权限，拒绝角色DenyRole对数据库SELECT权限，检验在用户Jack上下文中执行对表test的查询情况

--试验17：
EXEC sys.sp_addrolemember DenyRole,Jack
GRANT SELECT TO GrantRole
DENY SELECT TO DenyRole
EXEC sys.sp_helprotect NULL,GrantRole
EXEC sys.sp_helprotect NULL,DenyRole
EXEC AS USER='Jack'
GO
SELECT * FROM test
GO
REVERT
GO

结论：从不同角色继承对相同对象的互斥权限（GRANT<<==>>DENY），用户最终获得的是拒绝权限。

恢复角色GrantRole、用户Jack权限：

REVOKE SELECT TO GrantRole
REVOKE SELECT TO DenyRole

试验18：赋予角色GrantRole对数据库对象表test的SELECT权限，拒绝角色DenyRole对数据库对象表test的SELECT权限，检验在用户Jack上下文中执行对表test的查询情况

--试验18:
GRANT SELECT ON OBJECT::test TO GrantRole
DENY SELECT ON OBJECT::test TO DenyRole
EXEC sys.sp_helprotect NULL,GrantRole
EXEC sys.sp_helprotect NULL,DenyRole
EXEC AS USER='Jack'
GO
SELECT * FROM test
GO
REVERT
GO

结论同试验17.

恢复角色GrantRole、用户Jack权限：

REVOKE SELECT ON OBJECT::test TO GrantRole
REVOKE SELECT ON OBJECT::test TO DenyRole

试验19：赋予角色GrantRole对数据库对象表test的a、d列SELECT权限，拒绝角色DenyRole对数据库对象表test的a、d列SELECT权限，检验在用户Jack上下文中执行对表test的查询情况

--试验19:
GRANT SELECT ON OBJECT::test(a,d) TO GrantRole
DENY SELECT ON OBJECT::test(a,d) TO DenyRole
EXEC sys.sp_helprotect NULL,GrantRole
EXEC sys.sp_helprotect NULL,DenyRole
EXEC AS USER='Jack'
GO
SELECT * FROM test
GO
REVERT
GO

试验17~19说明，Jack用户从两个角色中继承相反同对象的权限，仍然是拒绝权限占较高优先级，同试验11~16结论相同。

恢复角色GrantRole、用户Jack权限：

REVOKE SELECT ON OBJECT::test(a,d) TO GrantRole
REVOKE SELECT ON OBJECT::test(a,d) TO DenyRole

同时先删除DenyRole的成Jack，避免测试时其他因素影响：

EXEC sys.sp_droprolemember DenyRole,Jack

分别对角色/用户赋予或拒绝父子对象的权限测试

试验20：赋予角色GrantRole对数据库SELECT权限，拒绝用户Jack对数据库对象表test的SELECT权限，检验在用户Jack上下文中执行对表test的查询情况

--试验20：
GRANT SELECT TO GrantRole
DENY SELECT ON OBJECT::test TO Jack
EXEC sp_helprotect  NULL ,Jack
EXEC sp_helprotect  NULL ,GrantRole
EXEC AS USER='Jack'
GO
SELECT * FROM test
SELECT * FROM test1
GO
REVERT
GO

因为对角色GrantRole赋予了数据库的SELECT权限，对用户Jack仅拒绝了表test的SELECT权限，所以Jack用户仍然具有查看数据库下除test表以外的对象的权限。

试验21：拒绝角色GrantRole对数据库SELECT权限，赋予用户Jack对数据库对象表test的SELECT权限，检验在用户Jack上下文中执行对表test的查询情况

--试验21：
DENY SELECT TO GrantRole
GRANT SELECT ON OBJECT::test TO Jack
EXEC sp_helprotect  NULL ,Jack
EXEC sp_helprotect  NULL ,GrantRole
EXEC AS USER='Jack'
GO
SELECT * FROM test
SELECT * FROM test1
GO
REVERT
GO

正在上传…重新上传取消

正在上传…重新上传取消

拒绝父级对象（这里是数据库）的SELECT权限、则拒绝所有子级（这里是数据库中的表test）对象的SELECT权限，即使单独为子级对象赋予了SELECT权限

恢复角色GrantRole、用户Jack权限：

拒绝父级对象（这里是数据库）的SELECT权限、则拒绝所有子级（这里是数据库中的表test）对象的SELECT权限，即使单独为子级对象赋予了SELECT权限

恢复角色GrantRole、用户Jack权限：

REVOKE SELECT TO GrantRole
REVOKE SELECT ON OBJECT::test TO Jack

试验22：拒绝角色GrantRole对数据库SELECT权限，赋予用户Jack对数据库对象表test的a、d列SELECT权限，检验在用户Jack上下文中执行对表test的查询情况

--试验22：
DENY SELECT TO GrantRole
GRANT SELECT ON OBJECT::test(a,d) TO Jack
EXEC sp_helprotect  NULL ,GrantRole
EXEC sp_helprotect  NULL ,Jack
EXEC AS USER='Jack'
GO
SELECT a,d FROM test
SELECT b FROM test
GO
REVERT
GO

拒绝父级对象（这里是数据库）的SELECT权限、则拒绝所有子级（这里是数据库中表test的字段a、d）对象的SELECT权限，即使单独为子级对象赋予了SELECT权限

试验23：赋予角色GrantRole对数据库SELECT权限，拒绝用户Jack对数据库对象表test的a、d列SELECT权限，检验在用户Jack上下文中执行对表test的查询情况

--试验23：
GRANT SELECT TO GrantRole
DENY SELECT ON OBJECT::test(a,d) TO Jack
EXEC sp_helprotect  NULL ,GrantRole
EXEC sp_helprotect  NULL ,Jack
EXEC AS USER='Jack'
GO
SELECT a,d FROM test
SELECT b FROM test
GO
REVERT
GO

赋予父级对象（这里指数据库）SELECT权限，则仅仅拒绝子级对象中被拒绝的子级对象（这里拒绝了子级对象表test的字段a、d列），对其他子级对象仍然有SELECT权限。

恢复角色GrantRole、用户Jack权限：

REVOKE SELECT TO GrantRole
REVOKE SELECT ON OBJECT::test(a,d) TO Jack

试验24：赋予角色GrantRole对数据库对象表test的SELECT权限，拒绝用户Jack对数据库对象表test的a、d列SELECT权限，检验在用户Jack上下文中执行对表test的查询情况

--试验24：
GRANT SELECT ON OBJECT::test TO GrantRole
DENY SELECT ON OBJECT::test(a,d) TO Jack
EXEC sp_helprotect  NULL ,GrantRole
EXEC sp_helprotect  NULL ,Jack
EXEC AS USER='Jack'
GO
SELECT a,d FROM test
SELECT b FROM test
GO
REVERT
GO

赋予父级对象（这里指表test）SELECT权限，则仅仅拒绝子级对象中被拒绝的子级对象（这里拒绝了子级对象表test的字段a、d列），对其他子级对象仍然有SELECT权限。

试验25：在试验24的基础上增加字段e列，检验赋予权限后，新增加的对权限的继承情况

--试验25：
ALTER TABLE test ADD e int
EXEC sp_helprotect  NULL ,GrantRole
EXEC sp_helprotect  NULL ,Jack
EXEC AS USER='Jack'
GO
SELECT e FROM test
GO
REVERT
GO

角色GrantRole对表test有SELECT权限，并且对于新增的列（在赋予权限后创建的列）也有SELECT权限，但此时和10一样，对新增的列拒绝了SELECT权限。

解决方案为重新单独为其赋予权限。

GRANT SELECT ON OBJECT::test(e) TO GrantRole
EXEC sp_helprotect  NULL ,GrantRole
EXEC sp_helprotect  NULL ,Jack
EXEC AS USER='Jack'
GO
SELECT e FROM test
GO
REVERT
GO

从结果来看，赋予角色GrantRole权限并没有单独增加对test表字段e的SELECT权限信息，而我们此时已经有了对test表字段e的SELECT权限（如图中的结果）。

删除e列，再重新创建e列，重新在Jack上下问中执行对e列的查询

ALTER TABLE test DROP column e
ALTER TABLE test ADD e int
EXEC sp_helprotect  NULL ,GrantRole
EXEC sp_helprotect  NULL ,Jack
EXEC AS USER='Jack'
GO
SELECT e FROM test
GO
REVERT
GO

发现又报上面的错误了。这也许算是SQL Server中EXEC AS user的一个Bug吧！！！

同样在使用账户登录服务器，连接数据库test时，查询新列e，有SELECT权限。

恢复test表结构：

ALTER TABLE test DROP COLUMN c,e

试验26：拒绝角色GrantRole对数据库对象表test的SELECT权限，赋予用户Jack对数据库对象表test的a、d列SELECT权限，检验在用户Jack上下文中执行对表test的查询情况

--试验26：
DENY SELECT ON OBJECT::test TO GrantRole
GRANT SELECT ON OBJECT::test(a,d) TO Jack
EXEC sp_helprotect  NULL ,GrantRole
EXEC sp_helprotect  NULL ,Jack
EXEC AS USER='Jack'
GO
SELECT a,d FROM test
SELECT b FROM test
GO
REVERT
GO

拒绝父级对象（这里指test表）的SELECT权限，则拒绝其所有子级对象（这里列出test表字段a、d）的SELECT权限，即使再单独赋予子级对象SELECT权限。

恢复角色GrantRole、用户Jack权限：

REVOKE SELECT ON OBJECT::test TO GrantRole
REVOKE SELECT ON OBJECT::test(a,d) TO Jack

试验从不同角色中继承对不同对象权限的相互影响

试验27：赋予角色GrantRole对数据库的SELECT权限，拒绝角色DenyRole对数据库对象表test的SELECT权限，检验在用户Jack上下文中执行对表test、test1的查询情况

--试验27：
EXEC sp_addrolemember DenyRole,Jack
GRANT SELECT TO GrantRole
DENY SELECT ON OBJECT::test TO DenyRole
EXEC sp_helprotect  NULL ,GrantRole
EXEC sp_helprotect  NULL ,DenyRole
EXEC sp_helprotect  NULL ,Jack
EXEC AS USER='Jack'
GO
SELECT * FROM test
SELECT * FROM test1
GO
REVERT
GO

继承角色对数据库test的SELECT权限，同时继承了拒绝表test的SELECT权限，在用户有对数据库的所有对象有SELECT权限，除拒绝的对象外。

试验28：拒绝角色GrantRole对数据库的SELECT权限，赋予角色DenyRole对数据库对象表test的SELECT权限，检验在用户Jack上下文中执行对表test、test1的查询情况

--试验28：
DENY SELECT TO GrantRole
GRANT SELECT ON OBJECT::test TO DenyRole
EXEC sp_helprotect  NULL ,GrantRole
EXEC sp_helprotect  NULL ,DenyRole
EXEC AS USER='Jack'
GO
SELECT * FROM test
SELECT * FROM test1
GO
REVERT
GO

用户从角色中继承了拒绝数据库test的SELECT权限，并继承角色对表test的SELECT权限，可以看到拒绝了数据库的SELECT权限，则拒绝了数据库所有对象的SELECT权限，即使单独给这个对象赋予SELECT权限。

REVOKE SELECT TO GrantRole
REVOKE SELECT ON OBJECT::test TO DenyRole

试验29：赋予角色GrantRole对数据库的SELECT权限，拒绝角色DenyRole对数据库对象表test的列a、d的SELECT权限，检验在用户Jack上下文中执行对表test各列的查询情况

--试验29：
GRANT SELECT TO GrantRole
DENY SELECT ON OBJECT::test(a,d) TO DenyRole
EXEC sp_helprotect  NULL ,GrantRole
EXEC sp_helprotect  NULL ,DenyRole
EXEC AS USER='Jack'
GO
SELECT b FROM test
SELECT a,d FROM test
GO
REVERT
GO

用户继承了角色对数据库test的SELECT权限，同时继承了拒绝表test的a、d列的SELECT权限。用户对数据库有SELECT权限，在用户对除数据库中拒绝的对象以外的所有对象有SELECT权限。

REVOKE SELECT TO GrantRole
REVOKE SELECT ON OBJECT::test(a,d) TO DenyRole

试验30：拒绝角色GrantRole对表test的SELECT权限，赋予角色DenyRole对数据库对象表test的列a、d的SELECT权限，检验在用户Jack上下文中执行对表test各列的查询情况

--试验30：
DENY SELECT ON OBJECT::test TO GrantRole
GRANT SELECT ON OBJECT::test(a,d) TO DenyRole
EXEC sp_helprotect  NULL ,GrantRole
EXEC sp_helprotect  NULL ,DenyRole
EXEC sp_helprotect  NULL ,Jack
EXEC AS USER='Jack'
GO
SELECT b FROM test
SELECT a,d FROM test
GO
REVERT
GO

用户继承了角色拒绝表test的SELECT权限，同时继承了赋予角色对表test的a、d列的SELECT权限。用户拒绝表test的SELECT权限，在用户对拒绝表中所有列的SELECT权限（包括新增列），即使再单独赋予用户对某些列的SELECT权限。

试验31：赋予角色GrantRole对数据库的SELECT权限，拒绝角色DenyRole对数据库对象表test的列a、d的SELECT权限，检验在用户Jack上下文中执行对表test各列的查询情况

--试验31：
GRANT SELECT ON OBJECT::test TO GrantRole
DENY SELECT ON OBJECT::test(a,d) TO DenyRole
EXEC sp_helprotect  NULL ,GrantRole
EXEC sp_helprotect  NULL ,DenyRole
EXEC sp_helprotect  NULL ,Jack
EXEC AS USER='Jack'
GO
SELECT b FROM test
SELECT a,d FROM test
GO

用户继承了赋予角色对表test的SELECT权限，同时继承了拒绝角色对表test的a、d列的SELECT权限。用户对表test有SELECT权限，则用户对表中的所有列有SELECT权限，除单独拒绝的某些列外。