Table Permissions for users in multiple AD groups

Question

Hi,

We manage access to our SQL SERVER 2008 database using AD Groups.

I have two AD groups, ANALYSTS and DEVELOPERS.

I want to deny select access to a table1 for all users in the DEVELOPER group but GRANT SELECT to ANALYSTS.

The problem is that 1 or more DEVELOPERS are also in the ANALYSTS and DENY superseeds GRANT.

I am happy to GRANT ANALYSTS full ownership or control of table1 but it seems that if even if I grant ANALYSTS ownership they still can't select from the table if DEVELOPERS are DENIED SELECT.

We don't use SPs or Views in our DB so I don't want to go down that route.

Thanks in advance

Mat

Answer 1

If I understand correctly, you don't need to use deny at all.  Grant permissions only to ANALYSTS only and not to DEVELOPERS.  Only ANALYSTS and DEVELOPERS who are also members of ANALYSTS will have permissions.  IMHO, it's best to avoid deny with possible.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 2

Hi Dan,

thanks for replying so quickly. I haven't explicitly granted DEVELOPERS select permissions on the table but they still have the rights to select from the table. The DEVELOPER user is in the db_datareader/datawriter Database roles. There arne't any application specific roles as yet. The schema that research.table1 is in is owned by dbo. I'm not sure if that helps to clarify the situation?

Any suggests would be welcome.

Thanks

Mat

Answer 3

So you need to look at the overall picture. From what you have said so far it seems that generally you want DEVELOPERS to have access to tables, but you want to keep them away from table1. However, if a developer is also an analyst, he should have access.

Now, you indicate that the table in question is not in the dbo schema, and maybe that is a solution. Rather than adding developers to db_datareader, grant them SELECT on the dbo schema and any other schema you have general tables in. Then put these special secret analyst table in the research schema and grant SELECT on this schema only to analysts. Could this work for you?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

The DEVELOPER user is in the db_datareader/datawriter Database roles. There arne't any application specific roles as yet. The schema that research.table1 is in is owned by dbo. I'm not sure if that helps to clarify the situation?

The db_datareader and db_datawriter role memberships gives DEVELOPERS permissions on all tables in the database, but that is apparently not what you want.  Personally, I avoid those roles except in an all-or-nothing scenario. 

Erland's suggestion to grant permissions at the schema level will provide the security boundary you need.  If you need more granularity than schema, you'll need to grant permissions at the object level.  No big deal but you'll need to remember to grant permissions after each new object is created.

GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON SCHEMA::dbo TO ANALYSTS;
GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON SCHEMA::dbo TO DEVELOPERS;
GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON SCHEMA::Research TO ANALYSTS;

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 5

Thanks everyone for your suggestions. Clearly the error is using db_datareader / db_datawriter. I think the best thing to do will be to create the appropriate custom database roles and grant the appropriate permissions to those roles. For Instance, DeveloperDataReader with select on schema granted but revoked on table1. AnalystDataReader with select on all schemas. Does that seem reasonable?

Thanks again

Mat

Answer 6

DeveloperDataReader with select on schema granted but revoked on table1. AnalystDataReader with select on all schemas. Does that seem reasonable?

REVOKE only removes previously granted permissions.  Since permissions were granted at the schema level, the table-level revoke will have no effect and DeveloperDataReader will continue to have select permissions on the table via the schema-level grant.  You'll need to grant permissions at the object level if you need permissions more granular than schema when users are members of multiple groups. 

If DEVELOPERS permissions are actually a subset of ANALYSTS permissions (beyond just SQL), you could simply remove users from the DEVELOPERS AD group who perform both roles.  That will allow you to use database or schema-level grants along with deny to developers on sensitive objects.  Without AD membership changes, custom roles could also provide the behavior you want but you will need to add the individual user AD accounts as members so that developers who are also analysts are added only to the analyst database role.  The downside is that you'll need to maintain the role memberships as individuals are added, changed or removed.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 7

Helpful chart of the granular permissions.  http://go.microsoft.com/fwlink/?LinkId=229142

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty