One of the benefits of using Azure-based Platform-as-a-Service and Software-as-a-Service offerings is considerably quicker access to newly developed capabilities, frequently becoming available long before they are incorporated into traditional, full-fledged products. In addition, the trend manifested by lack of feature parity between these two areas, with the cloud apps placing second behind their well-established on-premises counterparts is consistently declining and in some cases even reversing, with the cloud taking over as the dominant force in software development and implementation efforts. This trend is reflected by the number of features introduced recently in Azure SQL Database. We will focus in our article on one of them, known as row-level security, included in both Azure SQL Database V12 and SQL Server 2016 Community Technology Preview 2 (CTP2).
Row-level security implements a predicate-based access control. Effectively, this means that its underlying mechanism relies on an arbitrary condition to evaluate whether requests to perform specific actions on individual rows of a database table should be granted or denied. These actions are determined based on the type of predicate. A filter predicate, currently implemented in Azure SQL Database V12 and SQL Server 2016 CTP2 affects the ability to carry out SELECT, DELETE, and UPDATE operations on rows which satisfy filtering criteria. Interestingly, note that it is possible to perform an UPDATE that will result in the newly updated row becoming filtered. Preventing such updates can be accomplished by using a blocking predicate, which is not yet available in the current implementation of the row-level security (if that is your intention, then you will need to resort to traditional SQL Server constructs, such as constraints or triggers).
From the functionality standpoint, predicates are equivalent to the T-SQL WHERE clause. As a matter of fact, historically, it was common to limit the visibility of SQL Server-resident data by creating user- or role-specific views (or stored procedures) and incorporating such clause into the middle or front end application tier, which first identified the user querying data and then generated a T-SQL statement that targeted a result set based on the relevant view (or stored procedure) and identity of that user. With the introduction of row-level security, this capability is built directly into the data tier. This not only simplifies implementing data security (potentialy eliminating the need for application-level changes), but also facilitates scenarios in which users have direct access to a database.
A filter predicate takes the form of an inline table valued function, defined by using the CREATE FUNCTION T-SQL statement with the RETURNS TABLE and WITH SCHEMABINDING clauses (ensuring that the objects referenced in the function definition cannot be modified unless the function is also modified). The predictate might involve a join with another table in the same database or a call to another function, as long as existing permissions are sufficient to carry out either of these operations. The function needs to be referenced by a security policy linked to the target table, established by invoking the CREATE SECURITY POLICY T-SQL statement with the ADD FILTER PREDICATE clause. Functions used by predicates and the corresponding policies should be created in a dedicated schema. A policy can be enabled or disabled by setting the value of its STATE attribute to (respectively) ON or OFF. Once enabled, the filtering will be enforced for all security principals, including the dbo user and those assigned the db_owner or the table owner roles. Privileged users will also be prevented from modifying any function used by predicates referenced by an existing security policy, although they will be allowed to create multiple security policies with non-overlapping predicates and associate them with the same table.
In order to better understand these provisions, let's step through an example of configuring row-level security on a table hosted by an Azure SQL Database. We will use for this purpose the AdventureWorksLT [V12] sample database available directly from the Azure Portal. Once you've signed in with your organizational or Microsoft credentials, click the New+ button in the upper left corner, select Data + Storage in the Create blade, followed by the SQL Database entry in the Data + Storage blade. In the SQL Database blade, type in an arbitrary name that will be assigned to the newly created database (we will call it AdventureWorksLTV12). Next, select the Server entry, which will expose the Server blade. Click Create a new server (unless you have an existing one you intend to use, in which case, select it from the list of servers displayed under the Use an existing server entry) and, in the New Server blade, type in the Server name, Server admin login, and the corresponding Password (which you will need to retype in the Confirm password textbox), as well as choose the location (we will provision it in the East US), and ensure that the Create V12 server switch is in the Yes position. Leave the Allow azure services to access server checkbox enabled and click OK. This will take you back to the SQL Database blade. At this point, click Select source and, in the Select Source blade, select Sample, which will automatically default to the AdventureWorksLT [V12]. Finally, set the Pricing tier to Basic, leave the remaining options unchanged, and click Create.
Once the database is provisioned, the portal will automatically activate the resulting AdventureWorksLTV12 blade, showing its most essential settings. Note the Server name entry since you will be using it shortly. First though, you need to configure the server's firewall to allow management connections from your local computer. To accomplish this, click Open in Visual Studio icon in the toolbar (even though we will be using SQL Server Management Studio instead, we can still take advantage of this shortcut). In the Open in Visual Studio blade, click the Configure your firewall link. This will open the Firewall Settings blade, where you can ensure that connection attempts from your current public IP address will be allowed by clicking the Add Client IP icon in the toolbar, followed by Save.
At this point, you are ready to manage Azure SQL Database from your local computer. Launch SQL Server Management Studio and ensure that the Connect to Server dialog box with the Server type set to Database Engine is displayed. In the Server name textbox, type in the name of the server in the same format in which it appears on the AdventureWorksLTV12 blade in the Azure Portal, select SQL Server Authentication in the Authentication drop-down list, type in the Login and Password values (matching those you specified when creating the server in the Azure Portal), and click Connect. Once the connection is established, you should see the server node in the Object Explorer window. Navigate through the object hierarchy via the Databases folder to the AdventureWorksLTV12 database and, from its context sensitive menu, select New Query entry.
To illustrate a sample implementation of row-level security, we will leverage the [SalesLT].[Customer] table, which contains data about customers of the AdventureWorks company. The table, in addition to data for each customer, includes also the username of the respective salesperson, presented in the format adventureworks\username. For example, here is a partial extract of its content:
SELECT [CustomerID] ,[Title] ,[FirstName] ,[MiddleName] ,[LastName] ,[CompanyName] ,[SalesPerson] FROM [SalesLT].[Customer] 1 Mr. Orlando N. Gee A Bike Store adventure-works\pamela0 2 Mr. Keith NULL Harris Progressive Sports adventure-works\david8 3 Ms. Donna F. Carreras Advanced Bike Components adventure-works\jillian0
Let's imagine a scenario where we want to restrict the visibility of individual rows to a salesperson who is assigned to the corresponding customer only, while preserving the ability to view the entire table by the dbo user. In order to accomplish this by relying on row-level security, we will first create user accounts for each of salesperson and grant them the SELECT permissions on the [SalesLT].[Customer] table (for the sake of simplicity, we will limit changes to two salespersons only, namely pamela0 and david8). Once these steps are completed, each of them should be able to query all rows in the target table.
CREATE USER pamela0 WITHOUT LOGIN; CREATE USER david8 WITHOUT LOGIN; GRANT SELECT ON [SalesLT].[Customer] to pamela0; GRANT SELECT ON [SalesLT].[Customer] to david8;
To implement row-level security, we will start by creating a dedicated schema. Next we will define an inline table valued function that will be subsequently used by the filter predicate. In our case, the function identifies the name of the current user and compares it (taking into account potential adventureworks\ prefix) against the value of the entry in the SalesPerson column. If there is a match or the user is the dbo, the outcome is successful.
CREATE SCHEMA Security; GO CREATE FUNCTION Security.fn_securitypredicate(@SalesPerson AS sysname) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS fn_securitypredicate_result WHERE @SalesPerson = 'adventure-works\' + USER_NAME() OR USER_NAME() = 'dbo';
To complete the implementation, we will create a security policy containing a filter predicate referencing the newly defined function and enable it by setting its STATE to ON.
CREATE SECURITY POLICY SalesFilter ADD FILTER PREDICATE Security.fn_securitypredicate(SalesPerson) ON [SalesLT].[Customer] WITH (STATE = ON);
To verify the outcome, we will execute SELECT statement against the [SalesLT].[Customer] table in the security context of the two salespersons user accounts and the dbo user. The first of them should return the total of 74 rows (with the pamela0 value in the SalesPerson column), the second 73 rows (with the david8 value in the SalesPerson column), and the third all of 847 rows.
EXECUTE AS USER = 'pamela0'; SELECT * FROM [SalesLT].[Customer]; REVERT; EXECUTE AS USER = 'david8'; SELECT * FROM [SalesLT].[Customer]; REVERT; EXECUTE AS USER = 'dbo'; SELECT * FROM [SalesLT].[Customer]; REVERT;
To deactivate the policy, you can simply set its STATE to OFF.
ALTER SECURITY POLICY SalesFilter WITH (STATE = OFF);
This concludes our introduction to row level security in Azure SQL Database. In our upcoming articles, we will focus on more advanced scenarios that take advantage of this functionality as well as cover other, recently introduced security-related features.