0

I have n user roles that shall restrict the degree of access, e. g.

  1. Reader (read only)
  2. Author (select, insert, update, delete)
  3. Maintainer (e. g. create new tables or views for their organisational unit)

in each of our m organisational units (divisions), e. g. sales departments for the countries

  1. USA
  2. JAPAN
  3. United Kingdom
  4. Germany

which shall restrict the visibiblity of tables and data rows (row-level security).

Question: What is the best set up to fulfill my access control requirements

  • without creating too many logins, DB users or DB roles (e. g. creating the full matrix of n * m is not an option, n + m would be perfect).
  • allow users to see their tables to use standard query/reporting tools (views are acceptable)
  • to minimize the DB changes required for user changes (I am using an Active Directory)
  • to limit the creation of new tables by the maintainer user role to e. g. the (country?) schemas s/he has access to?

I thought of using database roles based on windows AD groups but do not see a way to avoid the n * m explosion of roles (and therefor DB logins to reference the AD groups).

Note: I saw a similar question but it is related to group organisational units into (overlapping) hierarchies (but did not differentiate between user roles):

SQL Server row level security - many to many

I am using the Microsoft SQL Server 2012 and user and groups are maintained in an Active Directory...

A solution like described in in the white paper of Row level security in SQL Server 2012 seems to be too complicated for me (until it is the only option ;-)

Community
  • 1
  • 1
R Yoda
  • 8,358
  • 2
  • 50
  • 87
  • My current idea is one schema per organisational unit + one database role per org unit and role combination (n * m) + ownership chaining in stored procedures and views to achieve row-level security, is there any better solution (n + m)...? – R Yoda Dec 07 '15 at 17:39
  • OK, I have found a good tutorial (with code!) that explains [how to implement row-level security](https://www.mssqltips.com/sqlservertip/2186/how-to-setup-row-level-security-for-sql-server/) with database roles + the IS_Member function + an "access control list" table. This should work but not solve the n*m vs. n+m problem... Has anybody an idea to solve this? – R Yoda Dec 13 '15 at 18:38

0 Answers0