Check if Table exists in Predicate Filter in SQL Server-CodePudding

I have written a security policy which contains a predicate filter to apply row level security in SQL Server. But, I want to apply the filter predicate only if the table exists in the database. Can anyone please suggest anyway I can to it? Below is the security policy:

IF OBJECT_ID('s.policyName') IS NOT NULL
BEGIN
DROP SECURITY POLICY s.policyName
END
GO  
    CREATE SECURITY POLICY s.policyName 
    ADD  FILTER PREDICATE s.policyName(ColA) ON TableA
    WITH (STATE = ON)       
GO

I did try adding an if condition before and after the Filter predicate, but its not working. Any help is really appreciated.

CodePudding user response：

Wouldn't you just want to make sure the table (and column!) exists before creating the policy, rather than making the policy check for the table every time? I don't know that you can drop a table that has a policy associated with it (you certainly can't if the policy function, which has to be schema-bound, references the table), but I think if people can drop tables after the policy is created there is a bigger issue.

IF EXISTS 
(
  SELECT 1 FROM sys.tables AS t
    INNER JOIN sys.columns AS c
    ON t.[object_id] = c.[object_id]
    WHERE t.name = N'TableA'
      -- AND t.[schema_id] = SCHEMA_ID(N's') -- 'dbo', 'other' ???
    AND c.name = N'ColA'
)
BEGIN
    CREATE SECURITY POLICY s.policyName 
    ADD  FILTER PREDICATE s.policyFunctionName(ColA) ON TableA
    WITH (STATE = ON);
END

And of course you can't create the policy in the first place if the table doesn't exist. It will fail with:

Msg 33268, Level 16, State 1
Cannot find the object "TableA" because it does not exist or you do not have permissions.

So you can use the IF EXISTS for nice behavior (you could put a friendly error message in an ELSE) or you could just wrap in TRY/CATCH and hope for the best.

You might also want to make sure that the function s.policyFunctionName exists for a similar reason (and of course you can't call the policy the same as the function), or just let it fail.

CodePudding user response：

SQL checks if the table exists, true the result is 1, if the table does not exist the result is zero. For Example:

IF EXISTS ( USE TableA
            SELECT 1 
            FROM INFORMATION_SCHEMA.TABLES 
            WHERE TABLE_TYPE='VIEW' 
            AND TABLE_NAME='TableA') 
   SELECT 1 AS res ELSE SELECT 0 AS res;
  
IF OBJECT_ID ('TableA.s.policyName') IS NOT NULL 
   SELECT 1 AS res ELSE SELECT 0 AS res;