January 29, 2015

373 Views

Row Level Security in SQL

Row Level Security is the mechanism to set different access levels over data in any table for different Users/Security Groups. Row Level Security can be implemented at database, cube (SSAS) and report level. SSAS/Cube security is based on the attribute level i.e., row level at the database.

With Row Level Security, users can have access to a table without having access to rows on that table. This type of security is typically applied to tables that hold sensitive data. For example, you might want users to be able to review personal data for employees in their own department but not for people in other departments. You would give everyone access to the PERSONAL_DATA table, but would enforce row-level security so that they can only see rows where the DEPTID matches their own.

Here are the steps to implement Row Level Security in a database:

1.Create User/Security Groups (SG) in the Windows Directory:

Create security groups for row level access or use the Windows login account. For example, create SG: SGMKP_A to grant access to data of customer A.

2. Create login in the SQL DB for Users and Security Groups:

3. Create the database role for various access controls:

Create login/user corresponding to each SGs/login. Create the DB role corresponding to each user/SG and map it with the users.

4. Create Access Control Table in the Database

Create table DBAccessControl. This table will contain the entries of tables on which row level access is required.

Table Schema:

CREATE TABLE [dbo].[DBAccessControl](

                    [ID] [int] IDENTITY(1,1) NOT NULL,

                    [EntityName] [nvarchar](128) NOT NULL,

                    [EntityColumn] [nvarchar](128) NOT NULL,

                    [EntityColumnValue] [varchar](200) NULL,

                    [AccesibleDBRole] [nvarchar](128) NULL,

                    [IsActive] [bit] NULL,

                    [Description] [nvarchar](500) NULL)
  • EntityName: Table name for which row level access is required
  • EntityColumnName: Column on the basis of which we will limit access to data
  • EntityColumnValue: This field acts as the filter value
  • AccesibleDBRole: Name of the database role who has access to the above filter
  • IsActive: Filter is active or not

Sample data:

ID EntityName EntityColumnName EntityColumnValue AccessibleDBRole IsActive Description
8 DimMarketplace MarketplaceID 1 MKP1 1 NULL
9 DimMarketplace MarketplaceID 2 MKP2 1 Null

 

5. Create a view of the table for which access control is required

Grant Select permission to the DB role on the views only. Create a view on the table for which access control is required.

        CREATE View [dbo].[vwAccessControlDimMarketplace]
        AS
             Select

             SYSTEM_USER as UserName

             ,M.*

             FROM dbo.DimMarketplace M

             JOIN dbo. DBAccessControlACC

             ON ACC.EntityName='DimMarketplace'

             AND M.MarketplaceID=ACC.EntityColumnValue

             AND Is_Member(ACC.AccessibleDBRole)=1 --to check the access value
        GO

“IS_MEMBER” is an SQL function that determines if the current user is a member of the specified Windows NT group or SQL Server role.

This view gives access to the user as per their access defined in the DBAccessControl table.

Reference:

http://technet.microsoft.com/en-us/library/cc966395.aspx