Is it possible to DROP FILTER PREDICATE only if it exists?

user_4211890 0
Viewed 1917

I'm creating Row-Level Security in SQL server 2014.

It's possible to DROP FILTER PREDICATE on my table Client like this:

ALTER SECURITY POLICY rls.tenantAccessPolicy
    DROP FILTER PREDICATE ON dbo.Client,
    DROP BLOCK PREDICATE ON dbo.Client
GO

But if I execute this code twice in a row, I've got an error

"The security policy 'rls.tenantAccessPolicy' does not contain a predicate on table 'dbo.Client'."

Is there a way to DROP it only if it exists?

tsql sql-server sql
2 Answers

I would use approach IF EXISTS and check sys.security_predicates metadata table:

IF EXISTS( SELECT 1
           FROM sys.security_predicates sp
           WHERE sp.predicate_type = 0 -- filter_predicate
             AND OBJECT_ID('rls.tenantAccessPolicy') = sp.object_id
             AND sp.target_object_id = OBJECT_ID('dbo.Client'))
BEGIN    
    ALTER SECURITY POLICY rls.tenantAccessPolicy
        DROP FILTER PREDICATE ON dbo.Client; 
END;
GO

IF EXISTS( SELECT 1
           FROM sys.security_predicates sp
           WHERE sp.predicate_type = 1 -- block_predicate
             AND OBJECT_ID('rls.tenantAccessPolicy') = sp.object_id
             AND sp.target_object_id = OBJECT_ID('dbo.Client'))
BEGIN
    ALTER SECURITY POLICY rls.tenantAccessPolicy
        DROP BLOCK PREDICATE ON dbo.Client;    
END;
GO
user_50708790

I found the solution! I need to use dynamic SQL for this to work because the SECURITY POLICY keywords are executed before the SELECT or something which was causing my error.

This code only check if filter predicate is there, but since I'm always adding filter and block at the same time, the validation is fine with me:

IF EXISTS( SELECT 1
            FROM sys.security_predicates sp
            WHERE sp.predicate_type = 0 -- filter_predicate
                AND OBJECT_ID('rls.tenantAccessPolicy') = sp.object_id
                AND sp.target_object_id = OBJECT_ID('dbo.Client'))
BEGIN 
    DECLARE @Sql NVARCHAR(MAX);

    SET @Sql = N'ALTER SECURITY POLICY rls.tenantAccessPolicy
                DROP FILTER PREDICATE ON dbo.Client,
                DROP BLOCK PREDICATE ON dbo.Client'

    EXECUTE sp_executesql @Sql
END
ELSE
BEGIN
    SET @Sql = N'ALTER SECURITY POLICY rls.tenantAccessPolicy
                ADD FILTER PREDICATE rls.fn_tenantAccessPredicateWithSuperUser(CompanyID) ON dbo.Client,
                ADD BLOCK PREDICATE rls.fn_tenantAccessPredicateWithSuperUser(CompanyID) ON dbo.Client'

    EXECUTE sp_executesql @Sql
END
GO
user_42118900
Related