SQL Server 2016 Row Level Security (RLS) Implementation

Row Level Security Capability was introduced with 2016 version SQL Server. Same is available in Azure SQL Database also as of today. This blog will detail a novice example on how to implement.

This is the planned implementation flow:

  1. About the example
  2. Prepare Sample data
  3. Create Predicate function
  4. Apply Security Policy
  5. Test Security

1. Plot

For the purpose of example, we will take the case of an imaginary Super market. Let us assume there are Supervisors assigned to each department in the shop and we want each supervisor to see only items he is responsible for.

RLS is applied on tables but in this example we will apply to a VIEW, which makes more sense as it is close to the real world scenarios.

2. Prepare sample data

Find the schema and sample data I used for the example:

Table: dbo.Employee

[code lang=”sql”]
CREATE TABLE [dbo].[Employee](
[EmpID] [int] NULL,
[Department] [varchar](50) NULL,
[Name] [nvarchar](150) NULL,
[Username] [varchar](50) NULL
) ON [PRIMARY]
[/code]
1

Table: dbo.StockByDepartment

[code lang=”sql”]
CREATE TABLE [dbo].[StockByDepartment](
[Department] [varchar](50) NULL,
[Item] [nvarchar](100) NULL,
[UnitPrice] [money] NULL
) ON [PRIMARY]
[/code]
Screenshot[2]

View: dbo.Stock

[code lang=”sql”]
CREATE VIEW [dbo].[Stock]
WITH SCHEMABINDING
AS
SELECT e.Department, e.EmpID, e.Name, e.Username, d.Item, d.UnitPrice FROM dbo.StockByDepartment d
INNER JOIN dbo.Employee e
ON d.Department=e.Department
[/code]
Screenshot[3]

3. Create Predicate function

Run this script to create predicate function over dbo.Stock view.

[code lang=”sql”]
CREATE FUNCTION fn_SecurityPredicate(@username sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS [fn_SecurityPredicate_result]
FROM
dbo.Stock
WHERE
@username=USER_NAME();
[/code]

4. Apply Security Policy

Run this script to apply security policy over dbo.Stock view

[code lang=”sql”]
CREATE SECURITY POLICY DepartmentFilter
ADD FILTER PREDICATE dbo.fn_SecurityPredicate(Username)
ON dbo.Stock
[/code]

5. Test Security

Now it is time for us to try out the applied security with various users.

Create sample users with the name you have provided in “Login” field of dbo.Employee table, and login to SSMS using it and try SELECTing the records in dbo.Stock.

For demo purpose, below code will create some sample users and grant permission to dbo.Stock view:

[code lang=”sql”]
CREATE User paul WITHOUT LOGIN

GRANT SELECT on dbo.Stock TO paul
[/code]

Now, for the sake of testing, you can use the below code:

[code lang=”sql”]
EXECUTE(‘SELECT * FROM dbo.Stock’) as USER=’paul’
[/code]

You should see filtered data like the one below:

Screenshot[4]

Latency vs Throughput

Latency is about how fast a request can travel from one point to another.
Example: For sending 100Mb of data, the API will take one hour.

Throughput is how much data can travel from one point to another in a specific time frame.
Example: in one hour, this API can transmit 100Mb of data

 

Free eBook – Cloud Application Architecture Guide

Download from here.

Explore these best practices for any cloud.

No matter which cloud you choose, certain design principles apply to ensure your app is scalable, resilient and available. Take a structured approach to architecting cloud apps using the best practices, design review checklists and reference architectures in the Cloud Application Architecture Guide eBook.

T-SQL script to create SQL Job for daily database backup

This script will create an SQL Server Agent job, which will take daily database backup to a folder with date appended in the filename.

[code]
DECLARE @job varchar(100) = ‘Backup_testdb_daily’ — Name of Job
DECLARE @db varchar(100) = ‘testdb’ — DB to backup
DECLARE @bakfile varchar(100) = ‘d:\_temp\’ + @db — Backup file path

DECLARE @date varchar(8) = ‘20180720’ — Job Start date
DECLARE @time varchar(8) = ‘135400’ — Job run time. Eg: Run At 23rd hour

——————————————————————–

DECLARE @cmd varchar(200) = CONCAT (‘DECLARE @bakfile varchar(200) = ”’ , @bakfile , ”’ + ”_” + convert(varchar(100),GetDate(),112) + ”.bak”;’) +

CONCAT(‘BACKUP DATABASE ‘, @db, ‘ TO DISK = @bakfile’);

USE msdb

EXEC dbo.sp_add_job
@job_name = @job;

EXEC sp_add_jobstep
@job_name = @job,
@step_name = ‘Backup database’,
@subsystem = ‘TSQL’,
@command = @cmd

EXEC sp_add_jobschedule
@job_name = @job,
@name = ‘DB Backup Schedule’,
@freq_type = 4, — daily
@freq_interval = 1,
@active_start_date = @date,
@active_start_time = @time

EXEC dbo.sp_add_jobserver
@job_name = @job,
@server_name = @@SERVERNAME

[/code]

Change Server Mode in SSAS

You can change the Analysis Services mode from Multidimensional Mode to Tabular Mode or vice versa easily by following below steps. I did this on SQL Server Analysis Services 2016 version.

Step 1: Edit msmdsrv.ini file

Go to folder X:\Program Files\Microsoft SQL Server\MSAS13.MSSQLSERVER\OLAP\Config

Change X: with your correct installation drive.

Open file msmdsrv.ini in notepad

It is recommended to take a backup first. There is a chance this folder require additional permission to edit so I would suggest you to open the file “as Administrator”

Find the tag <DeploymentMode>0</DeploymentMode>

If your current mode is Multidimensional then the DeploymentMode value will be 0 or if Tabular, then it will be 2. Change it to 0 or 2 as per your requirement.

Step 2: Restart SSAS

Open SQL Server Configuration Manager from Start menu and
Right click on “SQL Server Analysis Services” and click “Restart” in the context menu.

Finished! Try connecting to SSAS instance in SSMS to test.