CODEWITHSUNDEEP
sqlsql-server
MariPlaza
MariPlaza

Reputation: 387

SQL Server Grantting permission for specific rows

I am doing the BI reports for a group of 5 companies. Since the information is more or less the same for all the companies, I am consolidating all the data of the 5 companies in one DB, restructuring the important data, indexing the tables (I can not do that in the original DB because ERP restrictions) and creating the views with all the information required.

In the group, I have some corporate roles that would be benefit of having the data of the 5 companies in one view, nevertheless, I am not interested that an employee of company 1 see the information of company 2, neither in the other way. There is any way to grant permissions restricting the information to the rows that contain employee´s company name in a specific column?.

I know that I could replicate the view and filtering the information using the WHERE clause, but I really want to avoid this. Please help. Thanks!

Upvotes: 1

Views: 1849

Answers (3)

George Raveen
George Raveen

Reputation: 451

If you want to restrict view data using the where clause, the easiest way is to create a view and then assign permission to the user. example:

CREATE VIEW emp AS SELECT Name, Bdate, Address FROM EMPLOYEE WHERE id=5;
GRANT SELECT ON emp TO user

Upvotes: 0

Georg Jung
Georg Jung

Reputation: 1167

As of SQL Server 2016 there is support specifically for this problem. The MSDN link in the accepted answer already forwards to the right article. I decided to post again though as the relevant answer changed.

You can now create security policies which implement row level permissions like this (code from MSDN; assuming per-user permissions and a column named UserName in your table):

CREATE SCHEMA Security
GO

CREATE FUNCTION Security.userAccessPredicate(@UserName sysname)
    RETURNS TABLE
    WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS accessResult
    WHERE @UserName = SUSER_SNAME()
GO

CREATE SECURITY POLICY Security.userAccessPolicy
    ADD FILTER PREDICATE Security.userAccessPredicate(UserName) ON dbo.MyTable,
    ADD BLOCK PREDICATE Security.userAccessPredicate(UserName) ON dbo.MyTable
GO

Furthermore it's advisable to create stored procedures which check permission too for accessing the data as a second layer of security, as users might otherwise find out details about existing data they don't have access to i.e. by trying to violate constraints. For details see the MSDN article, which is exactly on this topic.

It points out workarounds for older versions of SQL Server too.

Upvotes: 1

CRAFTY DBA
CRAFTY DBA

Reputation: 14925

What you are talking about is row level security. There is little to no support out of the product for this.

Here are a couple articles on design patterns that can be used.

http://sqlserverlst.codeplex.com/

http://msdn.microsoft.com/en-us/library/bb669076(v=vs.110).aspx

What is the goal of consolidating all the companies into one database?

Here are some ideas.

1 - Separate databases makes it easier to secure data; However, hard to aggregate data.

Also, duplication of all objects.

2 - Use schema's to separate the data. Security can be given out at the schema level.

This does have the same duplicate objects, less the database container, but a super user group can see all schema's and write aggregated reports.

I think schema's are under used by DBA's and developers.

3 - Code either stored procedures and/or duplicate views to ensure security. While tables are not duplicated, some code is.

Again there is no silver bullet for this problem.

However, this is a green field project and you can dictate which way you want to implement it.

Upvotes: 3

Related Questions