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.

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).

StaffScope and Staff tables

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)

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.

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.

Staff List

Fig. 2. Staff List

Affiliate List

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.

SysAdmin sees no rows

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).

Executives see their affiliate rows

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.

Managers see their Region's rows

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.

No SELECT Permissions on Transactions table

Fig. 7. No SELECT Permissions on Transactions Table

Transaction table as seen by Executives

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

Kenneth Igiri

Kenneth Igiri is a Database Administrator with eProcess International S.A., Ecobank Group's Shared Services Centre. Kenneth has over eight years' experience with SQL Server and Oracle databases as well as related technologies. His interests include database performance, HADR, and recently, Cloud.
Also, Kenneth teaches at Children's Church and writes fiction. You can connect with Kenneth via his blog or social network account.
Kenneth Igiri