GRANT versus DENY and REVOKE

[TheBugSlayer]

Hi,

Can someone clearly explains what happens when a DENY clause is applied to a permission on a principal?

I am preparing for MCTS 70-432 and according to the book I am reading
You add permissions to an object with the GRANT statement. Access to an object is
prevented with the DENY statement. To access an object, permission must be granted explicitly.
Each time you issue a GRANT statement, SQL Server places an entry in a security table for the
corresponding permission granted. Each time you issue a DENY, an entry is placed in a security
table for the DENY. Because a DENY overrides any other permission, a DENY overrides a GRANT.
The REVOKE statement removes permission entries for the object referenced. For example,
if you issue a GRANT SELECT ON Person.Address TO Test, you can remove the access by
executing REVOKE SELECT ON Person.Address FROM Test. Similarly, if you issue DENY SELECT
ON Person.Address TO Test, you can remove the DENY by executing REVOKE SELECT ON
Person.Address FROM Test.

While I straighten out my install, here is a question I would like to ask: If you issue a DENY SELECT, the principal will not be able to select, OK. But if you did not explicitly GRANT SELECT on the principal, will it be able to select?

 

[DrSql]

I will try to explain with an example:
If you have an "EMP" table with following col's
PKID, FNAME, LNAME, SSN, DOB
Acct1==> FULL Privilege
(GRANT SELECT,INSERT, UPDATE, DELETE ON EMP TO ACCT1)
Acct2==> ONLY SELECT Privilege
GRANT SELECT ON EMP TO ACCT2
Acct3==> Have full privilege with the exception of update SSN
GRANT SELECT,INSERT, UPDATE, DELETE ON EMP TO ACCT3
DENY UPDATE (SSN) ON EMP TO ACCT3
Acct4==> REVOKE a single action.
REVOKE SELECT,INSERT, UPDATE, DELETE ON EMP TO ACCT4

Basically with DENY you can control access more granular compared to REVOKE.
If I come across any good article will post it.
Thanks


Dr.Sql
Good Luck.

 

[TheBugSlayer]

Hey DrSQL, thanks for the reply.

So you are saying that I can achieve with DENY what REVOKE does. But I believe REVOKE removes the entry to the sec tables whereas DENY keeps it there and just marks the action as not granted, true?

If you did not explicitly GRANT UPDATE to Acct3, would it be able to UPDATE?

I finally got the sample databases installed. What a useless challenge. The lecturer gave me his copy of refresh 1; my refresh 3 did not work.
Thanks again.