Apply Row-Level Security automatically to newly created tables

[raulgarciamsft]

First published on MSDN on May 22, 2015

We have discussed before that applications with multi-tenant databases, including those using Elastic Scale for sharding, commonly have a “TenantId” column in every table to indicate which rows belong to each tenant.

In that previous post, we shared with you a SP that could help you to streamline the creation of a security policy that would add a filter on the TenantId column for all existing tables, but a common request we have heard since then is to have a mechanism to apply the same policy automatically to newly created tables.

In order to help you streamline the create table scenario, it is possible to apply a DDL trigger to CREATE TABLE events, and automatically alter the policy to add the predicate we are using for all other TenantId-enabled tables.

Because by default triggers execute under the same context as the principal who ran the operation that invoked them, and the ALTER SECURITY POLICY DDL has very high permission requirements; we will need to use some tricks using impersonation & module signing to make sure all the trigger’s operations will work as expected.

In order to make sure we can potentially reuse the code to add the table to a policy (in case we need to reuse it on ALTER TABLE DDL, or in the future for other objects) I will separate the ALTER SECURITY POLICY DDL logic into a separate SP. We made the SP as generic as possible so it can be reused in any database. Notice that the SP assumes the existence of a SECURITY POLICY.

-- First create a sproc that our trigger will call to add a table to an existing security policy
-- This SP will perform the ALTER SECURITY POLICY DDL
--
CREATE PROCEDURE dbo.sp_add_table_to_policy (
@rlsPolicySchema sysname,
@rlsPolicy sysname,
@rlsPredicateSchema sysname,
@rlsPredicateName sysname,
@targetScehma sysname,
@targetTable sysname,
@targetColName sysname,
@forcePolicy bit = 0 -- Use this parameter to control whether the @taregetColName is mandatory or not
)
AS
BEGIN
IF( @forcePolicy = 0 )
BEGIN
IF( NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = object_id(quotename(@targetScehma) + N'.' + quotename(@targetTable)) AND name = @targetColName))
BEGIN
print 'Skipping policy creation since the table does not include the target column'
return;
END
END

DECLARE @cmd nvarchar(max);
SET @cmd = N'ALTER SECURITY POLICY ' + quotename(@rlsPolicySchema) + N'.' + quotename(@rlsPolicy) + N'
ADD FILTER PREDICATE ' + quotename(@rlsPredicateSchema) + N'.'+ quotename(@rlsPredicateName) + N'(' + quotename(@targetColName) + N')
ON ' + quotename(@targetScehma) + N'.' + quotename(@targetTable) + N';'
EXECUTE( @cmd )
END
go

-- Create certificate for special user, and use it to sign the sproc and make sure we will have the right permissions when executing it
--
CREATE CERTIFICATE cert_rls ENCRYPTION BY PASSWORD = '<<ThrowAway password124@>>' WITH SUBJECT = 'RLS policy trigger'
go
CREATE USER cert_rls FOR CERTIFICATE cert_rls
go
GRANT REFERENCES TO [cert_rls]
GRANT ALTER ANY SECURITY POLICY TO [cert_rls]
GRANT SELECT ON [rls].[fn_predicate_TenantId] TO [cert_rls]
GRANT ALTER ON [rls].[fn_predicate_TenantId] TO [cert_rls]
GRANT ALTER ON SCHEMA::[rls] TO [cert_rls]
go
ADD SIGNATURE TO [dbo].[sp_add_table_to_policy] BY CERTIFICATE [cert_rls] WITH PASSWORD = '<<ThrowAway password124@>>'
go
ALTER CERTIFICATE [cert_rls] REMOVE PRIVATE KEY
go



Then we create the actual DDL trigger, where we will specify the actual values that will be used on the previously created SP as well as the behavior (e.g. whether the existence of the “TenantId’ column is mandatory or optional). For example:

-- Create a special user with elevated permissions that the trigger can use to execute the sproc to apply the policy (least privilege)
CREATE USER [user_rls_trigger] WITHOUT LOGIN
go

GRANT EXECUTE ON [dbo].[sp_add_table_to_policy] TO [user_rls_trigger]
go

-- Create a trigger on CREATE TABLE DDL to add a filter predicate whenever a table is created
CREATE TRIGGER trig_apply_policy ON DATABASE
WITH EXECUTE AS 'user_rls_trigger'
AFTER CREATE_TABLE
AS
-- Change these params depending on your scenario
DECLARE @forcePolicy bit = 1 -- if 1, prevents you from creating a new table without the target column (e.g. tenantId)
DECLARE @targetColumnName sysname = 'tenantId'; -- target column for the filter predicate
DECLARE @rlsPolicySchema sysname = 'rls';
DECLARE @rlsPolicyName sysname = 'secpol_TenantId';
DECLARE @rlsPredicateSchema sysname = 'rls';
DECLARE @rlsPredicateName sysname = 'fn_predicate_TenantId';

DECLARE @schema sysname
DECLARE @tableName sysname
DECLARE @data xml
SET @data = EVENTDATA()
SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(256)')
SET @tableName = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(256)')
BEGIN TRY
EXEC [dbo].[sp_add_table_to_policy] @rlsPolicySchema, @rlsPolicyName, @rlsPredicateSchema, @rlsPredicateName, @schema, @tableName, @targetColumnName, @forcePolicy;
END TRY
BEGIN CATCH
declare @err int = error_number()
declare @msg nvarchar(256) = error_message()
raiserror( N'Table cannot be added to policy, it requires a column named %s in order to apply the filter predicate. Inner Error Number: %s',
12, 1, @targetColumnName, @msg )
END CATCH
go


For the full sample code, please visit: http://rlssamples.codeplex.com/SourceControl/latest#RlsTrigger.sql

I would also recommend reading a few additional articles that are relevant to module signing & dynamic SQL with module signatures:

• Module Signing (Database Engine)


• Dynamic SQL and digital signatures in SQL Server 2005


• How to distribute digitally signed SQL modules