A new feature coming with Microsoft SQL Server 2016 is already available in Azure SQL Database: row level permission.
I can’t count how many times I’ve heard this question in the classroom: “How can I prevent people from seeing certain rows in a table?” This is a capability that has been requested by SQL database administrators for many years. Today, DBAs and developers must create code if they want to check for permissions and control a user’s row level access.
I’ll show you how to do this right now in Azure SQL Database, so that you’ll be ready to do the same thing when Microsoft SQL Server 2016 comes out. (If you don’t want to wait, you can try the latest public preview version for free by downloading it from here.)
Let’s assume you already have an Azure account subscription. Let’s also assume you’ve created a new database and, when creating it, added the AdventureWorks (V12) sample database schema. The demo will use tables and data from this sample database. To run the demo yourself, you must create a server login and database users.
Once you’ve set up the environment, configuring Row Level Security is pretty simple. You can do it in three steps.
1. Create The Predicate Function
Row Level Security is accomplished by creating a table level function that will be used to evaluate which rows can be retrieved by a particular user.
In this example, we want a sales person to have access only to customers associated with companies to which the sales person is assigned. For instance, the sales person David8 manages the account of Basic Bike Company (and no other company account); he therefore can see only data related to customers of that company.
The following table-valued function implements this test:
alter function rls.fn_SalesPersonSecurityTest(@custId int)
return select 1 as predicateResult
from dbo.SalesPerson_Company spc
inner join SalesLT.Customer c on c.CompanyName = spc.CompanyName
where database_principal_id(spc.SalesID) = database_principal_id()
and c.CustomerID = @custId;
The table dbo.SalesPerson_Company describes the relationship between sales persons and companies. On the other hand, SalesLT.Customer associates customers with the company that a sales person works for.
2. Create a Security Policy
Now we apply this test to the table that we want to secure. In this case, we want to secure sales data, so we create security policy for the SalesLT.Sales table as follows:
create security policy rls.companySecurityPolicy
add filter predicate rls.fn_SalesPersonSecurityTest(CustomerID) on SalesLT.SalesOrderHeader
From now on, every time a query referring SalesLT.SalesOrderHeader is submitted, Azure SQL Database will check, on a per-row basis, whether the principal ID submitting the query has permission to retrieve data from the table. SQL does this by doing a semi-join with the function we’ve defined. Not even database owners can get around this.
The screenshot below shows that linda3 can retrieve nine rows when she requests data from the SalesLT.SalesOrderHeader table:
On the other hand, David8 has not yet placed an order for any of his customers. So he can’t retrieve any row from the same table:
Pretty nifty new security feature, isn’t it?