sql-server t-sql sql-server-2016 rbac row-level-security

Multiple Security Predicates (FILTER|BLOCK) on same Table

I'm implementing RBAC-RLS security to my database and I have a question. Here is the scenario:

I have a function that has one input parameter @PermissionId which checks if the logged user has that privilege. Each Role in the database is associated with multiple permissions, and each user can have multiple roles.

Let's say I have a table Products and I want only users that have permission ReadProducts to be able to view the contents of the table.

CREATE SECURITY POLICY ReadProducts
ADD FILTER PREDICATE HasPermission('ReadProducts') ON Products

And that is OK, but now I also have Permission SystemAdministrator and he needs to also have permissions to view the contents of the Products table.

If I add another policy

CREATE SECURITY POLICY ReadProducts
ADD FILTER PREDICATE HasPermission('SystemAdministrator') ON Products

Would it work like it is supposed to, or will there be conflict between the 2 policies?

Or other situation, what if I add another filter predicate in the first policy. So it's gonna be like:

CREATE SECURITY POLICY ReadProducts
ADD FILTER PREDICATE HasPermission('ReadProducts') ON Products
ADD FILTER PREDICATE HasPermission('SystemAdmin') ON Products

Will this be alright? I mean if I as a user have one of these 2 privileges will I be able to see the contents of the table Products?

Solution

I figured it out. So here it is:

You can't have 2 security policies on the same database table, it won't let you create the second one. - You'll get an error.

Also, you can't have 2 FILTER predicates on the same table in the same Security Policy. - You'll get an error

Solution: Create Security Policy for each table in the database with 1 filter and 3-4 block predicates like this:

CREATE SECURITY POLICY [Log]
ADD FILTER PREDICATE [dbo].[HasSecurityPermission]('ReadLog') ON [dbo].[table],
ADD BLOCK  PREDICATE [dbo].[HasSecurityPermission]('AddLog') ON [dbo].[table] AFTER INSERT,
ADD BLOCK  PREDICATE [dbo].[HasSecurityPermission]('EditLog') ON [dbo].[table] BEFORE UPDATE,
ADD BLOCK  PREDICATE [dbo].[HasSecurityPermission]('RemoveLog') ON [dbo].[table] BEFORE DELETE

So you need 4 Permissions for each database table and at the end you create as many roles as they are user types (public user, demo, system Administrator, superuser ...) and associate them with permissions you want them to have.

That's it!