In-depth Exploration of Row Level Security

Introduction

Organizations are becoming more and more concerned about how to reduce the cost of licensing database solutions using consolidation. Some consolidation can be achieved in SQL Server simply by taking advantage of the existing one-to-many relationship between instances and databases. However, there are cases where the solution demands that data is consolidated into one table. In such a case, there may be concerns about how to restrict access to the data.

Row Level Security was introduced in SQL Server 2016 as a solution to scenarios similar to the above. It allows you to restrict access to rows in a table based on conditions defined in an inline Table Valued Function called a Predicate Function. When a Predicate Function is applied to a user table containing consolidated data, the system can be configured to return different data sets to different users depending on their roles which in turn depends on their job descriptions or departments for example.

Scenario

We shall build a simple scenario to illustrate this concept using a financial institution. A bank has decided to consolidate accounts for all its customers on a single database and the Transactions table is a single partitioned table containing all transactions as is the Customers table for storing all the bank’s customers. The bank is located in several countries and is also expanding. Each country is identified by an AffiliateID column. The company is structured such that access to key tables is restricted based on seniority.

Identify Securables

We shall need to implement a Row Level Security solution that restricts access to the customer and transaction data based on roles and a Row Level Security policy. Our first step is to create the required tables. Listing 1 shows the DDL for the key tables we shall test. The entire database used for this test can be downloaded from here.

Listing 1 – Core Tables in West African Commercial Bank Database;
-- Customers;
create table Customers
(CustID int identity (1000,1) not null Primary Key
,FirstName varchar(100)
,LastName varchar(100)
,PhoneNo bigint
,ContactAddress varchar(4000)
,AffiliateID char(3) foreign key references Affiliates(AffiliateID)
,AccountNo1 bigint
,AccountNo2 bigint
,AccountNo3 bigint
,AccountNo1Curr char (3)
,AccountNo2Curr char (3)
,AccountNo3Curr char (3)
)
GO

-- Transactions;
create table Transactions
(TranID int identity (1000,1) not null 
,AcctID int foreign key references Accounts (AcctID)
,TranDate datetime 
,TranAmt money
,AffiliateID char(3) foreign key references Affiliates(AffiliateID)
,primary key (TranID,TranDate))
ON PartSch (TranDate)

-- Transaction_History;
create table Transaction_History
(TranID int identity (1000,1) not null 
,AcctID int foreign key references Accounts (AcctID)
,TranDate datetime 
,TranAmt money
,AffiliateID char(3) foreign key references Affiliates(AffiliateID)
,primary key (TranID,TranDate))
ON PartSch (TranDate)

We then create a set of tables that we can use to identify staff. In this setup, each staff has a ScopeID which determines to what extent he or she can view or manipulate data:

1. National – A staffer can only manipulate data in the staffer’s country (where he or she works)
2. Regional – A staffer can only manipulate data in the staffer’s region (e.g. West Africa)
3. Global – A staffer can manipulate data in any country where the bank will ever have a branch

Each scope is assigned to the staff based on their designation. A Group Manager’s scope is Global, a Country Manager’s scope is Regional and an Executive’s scope is National. The traditional way to restrict access to data is often the use of roles and permissions. Assigning permissions to a role and subsequently assigning the role to a user means the user has the permissions associated with that role for the entire data set in the table in question. Row Level Security gives us the opportunity to do something more granular: restrict the user’s SELECT/UPDATE/DELETE permissions to a subset of the data set in the table (fine-grained access control).

Fig. 1. StaffScope and Staff Tables

Database Roles and Users

Listing 2 shows the users and roles we have to create to proceed with our solution. The idea is that there is a relationship between the staff as stored in user tables Staff and StaffScope and the Database Principals that these staff will eventually use to access the data. Observe the column in Fig. 1 called DBUserID. This column is populated using the DATABASE_PRINCIPAL_ID function (see Listing 2)

Listing 2 – Staff, Database User IDs and Roles

-- Populate Staff Table
use WACB
go
insert into Staff values ('John','Edu',DATABASE_PRINCIPAL_ID(),'Manager','233205678493','2','Accra, Ghana','EGH');
insert into Staff values ('Margaret','Harrison',DATABASE_PRINCIPAL_ID(),'Group Manager','2348030006574','3','Lagos, Nigeria','ENG');
insert into Staff values ('Edward','Antwi',DATABASE_PRINCIPAL_ID(),'Executive','22824567493','1','Lome, Togo','ETG');
insert into Staff values ('Barbara','Orji',DATABASE_PRINCIPAL_ID(),'Executive','22424567493','1','Abuja, Nigeria','ENG');
GO

-- Create Database User IDs for Staff
create user jedu without login;
create user mharrison without login;
create user eantwi without login;
create user borji without login;

-- Associate Database Principal IDs with Staff
update staff set DBUserID=DATABASE_PRINCIPAL_ID(concat(left(firstname,1),lastname));


-- Create Database Roles
create role [National] ;
create role [Regional] ;
create role [Global] ;

-- Grant Permissions on Desired Tables to Database Roles
grant select on customers to [National];
grant select, update on customers to Regional;
grant select, update on customers to Global;
grant select on Transactions to Regional, Global;
grant select on Transaction_History to Regional, Global;
grant update on Transactions to Global;


-- Grant Database Roles to Database Users Associated with Staff
alter role [National] add member eantwi;
alter role [National] add member borji;
alter role [Regional] add member jedu;
alter role [Global] add member mharrison;

So far in summary what we have done is:

1. Create/identify the tables we need to secure
2. Create tables that indicate the criteria we shall use to restrict access to data at row level (Scope)
3. Created database roles and users we shall apply restrictions to
4. Restricted access to the core tables (“Table Level Security”) using roles and permissions

Prediction Function and Security Policy

So far we have what we may call Table Level Security implemented using roles and permissions. Now we want to go deeper. We want two principals who have SELECT privileges on a table to be able to query the table but see different datasets based on conditions that we set up. Listing 3 shows us how we achieve this.

Listing 3 - Implement Row Level Security

-- Create Predicate Function
create schema rls;
go

create function rls.AccessPredicate (@AffiliateID char(3))
returns table
with schemabinding
as
return select 1 as access 
from dbo.Staff as s 
join dbo.StaffScope ss on s.ScopeID=ss.ScopeID 
join dbo.Affiliates a on s.AffiliateID=a.AffiliateID
where (
IS_MEMBER('National')=1
and s.DBUserID=DATABASE_PRINCIPAL_ID()
and @AffiliateID=s.AffiliateID
)
OR
(
IS_MEMBER('Regional')=1
and @AffiliateID in (select a.AffiliateID from dbo.Affiliates where Region='West Africa')
)
OR
(
IS_MEMBER('Global')=1
);
GO

-- Create Security Policy
create security policy rls.dataSecurityPol
add filter predicate rls.AccessPredicate (AffiliateID) on dbo.Customers,
add filter predicate rls.AccessPredicate (AffiliateID) on dbo.Transactions,
add filter predicate rls.AccessPredicate (AffiliateID) on dbo.Transaction_History,
add block predicate rls.AccessPredicate (AffiliateID) on dbo.Customers after update,
add block predicate rls.AccessPredicate (AffiliateID) on dbo.Transactions after update,
add block predicate rls.AccessPredicate (AffiliateID) on dbo.Transaction_History after update;
GO

-- Alter Security Policy
alter security policy rls.dataSecurityPol
add filter predicate rls.AccessPredicate (AffiliateID) on dbo.Transaction_History,
add block predicate rls.AccessPredicate (AffiliateID) on dbo.Transaction_History after update;
GO

The predicate function defines the conditions that must be met for a principal to see a subset of the interesting data. In this function, there are three conditions:

1. The Staff’s database user is a member of the National role and the AffiliateID matches that of the staff OR
2. The Staff’s database user is a member of the Regional role and the AffiliateID matches the list of AffiliateID’s belonging to the West African Region OR
3. The Staff’s database user is a member of the Global

This implies that a member of the Global role sees all data simply because he or she belongs to that role. However, members of the other two roles must fulfill additional criteria bordering on the AffiliateIDs.

For the function to be useful, apply this to tables as either FILTER predicates or BLOCK predicates. FILTER predicates restrict what the principal can see while BLOCK predicates ensure that the principal cannot manipulate any data outside that which is presented to him/her by the restrictions defined in the function. A Security Policy is a container in which we specify the FILTER and BLOCK predicates for all tables we are interested in. Take a look at Listing 3 again.

One very interesting aspect of this approach is the modularity. We can apply the predicates to additional tables in Security Policy without affecting the existing defined tables, we can add new database principals (Staff) by creating database users and granting them the appropriate roles. When Staff movement occurs, we can update the role assignments and so on.

Testing the Implementation

So now that we are done, we can impersonate the database principals to determine whether we have the expected results using the code in Listing 4. Before we look at that, let’s see the roles associated with each staff and their affiliates in Fig. 2.

Fig. 2. Staff List

Listing 4 – Testing the Implementation

select * from Customers;
execute ('select * from Customers') as user='eantwi';
execute ('select * from Customers') as user='borji';
execute ('select * from Customers') as user='jedu';
execute ('select * from Customers') as user='mharrison';

In the first line, I query the Customers table as a sysadmin, but I get NO ROWS. Meaning even an administrator cannot override the effects of RLS without impersonation.

Fig. 4. SysAdmin Sees No Rows

Barbara and Edward are both Executives and belong to the National Scope but they work in different countries so they see the Customers associated with their respective affiliates. (See Staff names in Fig. 1).

Fig. 5. Executives see their Affiliate’s Rows

John and Margaret are Country and Group Managers. They belong to the Regional and Global Scopes respectively. John sees data for West Africa, while Margaret sees data for all regions.

Fig. 6. Managers see their Region’s Rows

The results are the same for all other tables to which the Security Policy has been applied. Observe that without permissions on the Transactions table, Row Level Security is of no value.

Fig. 7. No SELECT Permissions on Transactions Table

Listing 5 – Permissions on Transactions Table
grant select on dbo.Transactions to [National];

Fig. 8. Transactions Table as Seen by Executives

Conclusion

Row Level Security is a powerful method of exploiting SQL Server’s fine grained access control capability. Using this feature requires that you are running SQL Server 2016 or higher. As the name implies, the objective is to restrict access to rows within a table using complex queries after you have taken care of “table level security”. The scenarios in which this capability can be applied are endless, thus it is very useful for a wide range of environments. Do well to explore and see what it can do for you.

References

Isakov, V. (2018). Exam Ref 70-764 Administering a SQL Database Infrastructure. Pearson Education

Row-Level Security