Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted May 5, 2016

Row Level Security Part 3 – Disabling Row Level Security for DBAs and Database Owners

By Greg Larsen

In my last two articles (Allow Access to Only a Subset of Rows Using Row Level Security, and Blocking Updates at the Row Level) I discussed implementing Row Level Security using SQL Server 2016.  Row Level security is all about restricting database users from being able to view, or update rows based on who they are and what roles they are in.  But there are some users who you “might” not want to restrict access at the row level at all, like the database owner, or someone with sysadmin permissions.  In this article I will show you how to disable RLS for database administrators (users in the sysadmin role) and database owners. 

Business Problems

Ever since management asked to have Row Level Security implemented they keep tweaking the security model because of one problem or another.  With the current implementation of their row level security model on the customer contact table they found another problem.  They found out the managers could not run reports against the customer contact table to see which sales people were assigned to which customers.  The managers knew they had database owner permissions (dbo) so they assumed that they would be able to see all rows in the customer contact table.  But since all access to the customer contact table went through the RLS security profile the managers also where getting their rows filtered, even though they had dbo permissions.  Therefore they asked the DBAs to tweak the RLS security policy, to allow members of the db_owner role to be able to read and update any row in the customer contact table.  

Setting up Test Data and Filter/Block Predicate

 

In order to show you how to disable filter/block predicates I need to have some test data and one or more filter/block predicates.  Therefore I will set up for testing by using this following script:

SET NOCOUNT ON;
USE master;
GO
DROP DATABASE RLS_DEMO;
GO
-- Code from Part 1: 
USE master;
GO
 
CREATE DATABASE RLS_DEMO;
GO
 
USE RLS_DEMO;
GO
CREATE USER Jane WITHOUT LOGIN;
CREATE USER Dick WITHOUT LOGIN;
CREATE USER Sally WITHOUT LOGIN;
GO
 
CREATE TABLE Customer(
       CustomerName varchar(100) NULL,
       CustomerEmail varchar(100) NULL,
       SalesPersonUserName varchar(20) NULL
);  
GO
GRANT SELECT ON dbo.Customer TO Jane;
GRANT SELECT ON dbo.Customer TO Dick;
GRANT SELECT ON dbo.Customer TO Sally;
GO
 
INSERT INTO Customer VALUES 
   ('ABC Company','Manager@ABC.COM','Jane'),
   ('Info Services','info@AInfaSerice.COM','Jane'),
   ('Washing-R-Us','HeadWasher@washrus.COM','Dick'),
   ('Blue Water Utilities','marketing@bluewater.COM','Dick'),
   ('Star Brite','steve@starbright.COM','Jane'),
   ('Rainy Day Fund','Tom@rainydayfund','Sally');
GO
CREATE FUNCTION fn_RowLevelSecurity (@FilterColumnName sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 as fn_SecureCustomerData
-- filter out records based on database user name 
where @FilterColumnName = user_name();
GO
CREATE SECURITY POLICY FilterCustomer
ADD FILTER PREDICATE dbo.fn_RowLevelSecurity(SalesPersonUserName)
ON dbo.Customer,
ADD BLOCK PREDICATE dbo.fn_RowLevelSecurity(SalesPersonUserName)
ON dbo.Customer AFTER UPDATE, 
ADD BLOCK PREDICATE dbo.fn_RowLevelSecurity(SalesPersonUserName)
ON dbo.Customer AFTER INSERT 
WITH (STATE = ON); 
 
-- Granting UPDATE and INSERT access
GRANT UPDATE, INSERT ON dbo.Customer TO Jane;
GRANT UPDATE, INSERT ON dbo.Customer TO Dick;
GRANT UPDATE, INSERT ON dbo.Customer TO Sally;
GO

In this script I created a database, created a table and then populated it with test data.   Additionally I added my security profile and provided update and insert permissions to the sales people.   If you have run the demos in any of my prior articles about RLS, then you might find this script generates an error because you already have a database called RLS_DEMO. 

Demonstrating that Current Problem with Example RLS Implementation

With my test data and security policy in place I can now show you how managers and sysadmins are restricted from seeing any rows in the Customer table.  In order to show you the current issues as described in the Business Problem above I will first need to add two different database users.  The first user I will add will be a member of the sysadmin role.  The second user I will add will be a manager, who will be added to the db_owner role.  I will add these two users using the following script:

USE master;
GO
CREATE LOGIN DBA WITH PASSWORD=N'DBA', DEFAULT_DATABASE=master, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO
ALTER SERVER ROLE sysadmin ADD MEMBER DBA;
GO
USE RLS_DEMO;
GO
CREATE USER Manager WITHOUT LOGIN;
GO
ALTER ROLE db_owner ADD MEMBER Manager;
GO

To show you how the current implementation restricts the manager for reading any Customer table rows let me run the following code:

USE RLS_DEMO;
GO
EXECUTE AS USER = 'Manager';
SELECT CustomerName, CustomerEmail, SalesPersonUserName
FROM Customer;
REVERT;

When I run this code I get no records returned.  This happens, of course, because the user Manager is not identified in my filter predicate associated with my security policy named FilterCustomer. 

To verify the members of the sysadmin role also cannot see any records in the Customer table, because of my current RLS implementation, I will perform the following steps:

Step 1: Logon to a query window with the DBA login

Step 2: Run the following script to select records:

USE RLS_DEMO;
GO
SELECT CustomerName, CustomerEmail, SalesPersonUserName
FROM Customer;
 

When I run the code in step 2 above under the context of the DBA user, I still don’t get any records returned from the SELECT statement.

Methods to Disable RLS for Members of the db_owner or Sysadmin Role

Implementation of RLS in each situation is probably a little different.  When using filter and block predicates to implement RLS you might not at first expect that the database owners or sysadmin members would also need filter/blocking predicates to be able to see data that is secured by a row level security profile.    As of the SQL Server 2016 RC1 database owners and members of the sysadmin role do not have access to row level data unless they have specifically been given access to the row level data. 

Clearly one option to allow DBOs and sysadmins access to all the rows in a table could be accomplished by disabling the filter and blocking predicates of my RLS implementation.  This really isn’t a solution because by doing this you have to disabled RLS for all users.  But to be complete I’ll show you how to disable a security policy.

In the prior section I validated that the Manager user and the DBA login could not read any records from the Customer table.  To allow them to read all the records in the Customer table I will disable the RLS security policy by using the following command:

ALTER SECURITY POLICY FilterCustomer
WITH (STATE = OFF);

In the command above I just turned off the security policy that filtered my Customer table rows.  After I disabled my security policy I ran the following command to verify the Manager user name was able to see all the records in my Customer table:

USE RLS_DEMO;
GO
EXECUTE AS USER = 'Manager';
SELECT CustomerName, CustomerEmail, SalesPersonUserName
FROM Customer;
REVERT;

When I ran this command I got the following output:

CustomerName             CustomerEmail             SalesPersonUserName
------------------------ ------------------------- -------------------
ABC Company              Manager@ABC.COM           Jane
Info Services            info@AInfaSerice.COM      Jane
Washing-R-Us             HeadWasher@washrus.COM    Dick
Blue Water Utilities     marketing@bluewater.COM   Dick
Star Brite               steve@starbright.COM      Jane
Rainy Day Fund           Tom@rainydayfund          Sally

As you can see by the output, by disabling my security policy the Manager database user can now read all of the Customer table rows.  If I was to now login using the DBA login I would also see that the members of the sysadmin role can also see all the Customer table rows.  I’ll leave it up to you to verify that the DBA login can see all the rows in the Customer table now that the security policy is disabled.

To re-enable the security policy and my current implementation of RLS on the Customer table I just need to run the following command:

ALTER SECURITY POLICY FilterCustomer
WITH (STATE = ON);

After running this command the Manager database user is no longer able to see any Customer table records.

Another alternative to allowing the manager and DBA to read all the rows in the Customer table is to incorporate some additional rules into my table value function that is associated with my security policy.   What I will need to add is some logic in my table value function, named fn_RowLevelSecurity, which will allow the database owners and dba’s access to all rows in the Customer table.  I can add these additional RLS security rules by running the following code:

DROP SECURITY POLICY FilterCustomer;
GO
ALTER FUNCTION fn_RowLevelSecurity (@FilterColumnName sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 as fn_SecureCustomerData
-- filter out records based on database user name 
WHERE @FilterColumnName = user_name()
   or IS_ROLEMEMBER ('db_owner') = 1
   or IS_SRVROLEMEMBER ('sysadmin') = 1;
GO
CREATE SECURITY POLICY FilterCustomer
ADD FILTER PREDICATE dbo.fn_RowLevelSecurity(SalesPersonUserName)
ON dbo.Customer,
ADD BLOCK PREDICATE dbo.fn_RowLevelSecurity(SalesPersonUserName)
ON dbo.Customer AFTER UPDATE, 
ADD BLOCK PREDICATE dbo.fn_RowLevelSecurity(SalesPersonUserName)
ON dbo.Customer AFTER INSERT 
WITH (STATE = ON); 

If you review this code you can see I added some additional logic in my function fn_RowLevelSecurity. That additional logic returned a 1 from this function when a user was a member of with the db_owner or sysadmin role.

Once I’ve updated my fn_RowLevelSecurity function and recreated my security profile FilterCustomer I can then use the same script as above to verify that the Manager and DBA can now read all the records in the Customer table.  To verify this yourself run through the same steps as I did at the bottom of the “Demonstrating that Current Problem with Example RLS Implementation” section above.

Disabling Row Level Filtering

As demonstrated it isn’t that hard to disable the row level filtering by setting the state property of a security policy to off.   Additionally I showed how to enhance the row level filtering rules by modifying that table value function associated with a security policy to meet the new business requirements for row level filter. 

When implementing row level security using the new security policy feature of SQL Server 2016 I’d recommend you first get a good handle on how Microsoft has implemented row level security.  It surprised me to find that dbos and sysadmins where also restricted by the security policies I created.  Secondly I’d suggest you develop a list of your row level security requirements for row level security first.  Once you have good solid row level security requirements then you can determine how to implement those requirements using a table value function and filter/block predicates of a security profile.    In my next article on row level security I will explore different application security techniques that are commonly used today when interfacing with SQL Server, and then discuss how those different security models  will help or hinder your implementing row level security policies.

See all articles by Greg Larsen



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM