Row Level Security examples including CASE and IF

Question

We are currently testing this functionality. What would be helpful is some more examples that show what can be included in the row level security in terms of SQL language. I.e. can we include 'case' or 'if when' clauses to distinguish between users?

Answer 1

The row-security expressions must evaluate to true or false. The expression syntax is the same as when you create a regular where clause. The only difference is that fields from the current table must be bracketed by {[ and ]}.

Anything that is normally permitted in a SQL where clause should work including:

• %username% = 'Admin'
• {[ProductId]} > 7
• {[DiscontinuedDate]} is not null
• {[StandardCost]} between 0 and 100
• {[CategoryId]} in (select CategoryId from Category where name = 'Bikes')
• {[ID]} = case {[StatusId]} when 0 then 30 when 1 then 42 end

CASE Statements that return a Boolean

If your SQL server understands the keywords TRUE and FALSE then you can write a CASE statement as follows:

• case when {[Color]}= 'red' then TRUE else FALSE end

Otherwise you can write it as follows:

• (case when {[Color]}= 'red' then 1 else 0 end) = 1

Stored Functions

If you have even more complex requirements then your SQL server likely supports the use of Stored Functions.

More details

• Row Level Security