0

I would like to secure access to rows in tables. The problem is that I need to connect to data between databases and different schemas. I can more or less handle with it when my data is in one database and schema. The problem starts in views when they need to get data from other database and schemas that has different owners.

I've created a table where I granted acces to data depending on logins

Like this: PermissionTab

DepId Login

1 Login1

2 Login1

3 Login1

4 Login2

5 Login3

6 Login4

7 Login5

1 Login6

2 Login6

3 Login6

4 Login6

5 Login6

I've set data in schemas CDN, TBC, dbo and Optima unaccessible (those schemas have different owners CDN - CDN - the rest dbo). I've created schema Qlik (dbo owner) (accessible to users) where are views that gets data from tables (in different schemas). To get row level security in my views I've added something like that.

Where dv.DepId in (Select DepId From Qlik.PermissionTab Where Login=USER_NAME() ) OR
    IS_ROLEMEMBER('db_owner') = 1 OR
    IS_SRVROLEMEMBER('sysadmin') = 1

Those views must connect to many databases as well.

This is a sample View [Qlik].[DekretPomocniczyView] Select * from [CDN].[DekretyNag] inner join [CDN].[DekretyElem] on DeN_DeNId=DeE_DeNId left outer join [CDN].[DekretyKonta] on DeE_DeEId=DeK_DeEId left outer join Aplikacja.Qlik.MPKKontaView on DeK_AccId=Acc_AccId

Where (DepId in (Select DepId From Aplikacja.Qlik.PermissionTab Where
Login=USER_NAME() ) OR IS_ROLEMEMBER('db_owner') = 1 OR
IS_SRVROLEMEMBER('sysadmin') = 1

I've found it impossible to get row level permission using schemas and to connect between tables. Quite often the view from one database connects to view in another database, which also gets data from another database.

Because the problem was urgent for me, after many tests I've found I can get the effect using roles and setting permissions only to specific views, not schemas. Now (after I've asked the question) I wonder If I could get it using schemas and if my model is correct and safe. This is SQL 2012.

Maciek
  • 3
  • 2
  • Have you implemented RLS following [MS best practice](https://msdn.microsoft.com/en-us/library/dn765131.aspx?f=255&MSPPError=-2147217396)? Can you describe, in detail, why views are causing you a problem? Can you provide sample table structures etc to help us unpick your problem? – David Rushton Jan 31 '17 at 13:38
  • Hi, I've added more information to my question. I've finnaly found the solution but not using schemas which was my question. I've no idea why it didn't work. Thanks in advance for any clues. – Maciek Feb 01 '17 at 12:01

0 Answers0