locked
Create Multiple Security Schemas for SQL Row Level Security RRS feed

  • Question

  • In the MS RLS documentation below it says as a best practice that its "...highly recommended to create a separate schema for the RLS objects, predicate function, and security policy."  My question is do we create multiple security schemas then if we have multiple security policies?   Or stated differently, what are the relationships between the functions, security policies, and the recommended security schemas?  Are they 1 to 1 to 1, or 1 to many to 1, or  1 to many to many, or???  The use case for this is Azure SQL Data Warehouse / Synapse.

    https://github.com/microsoft/sql-server-samples/tree/master/samples/databases/wide-world-importers/sample-scripts/row-level-security


    Karl


    • Edited by bingdaddy Tuesday, February 18, 2020 4:52 PM
    Tuesday, February 18, 2020 4:49 PM

All replies

  • I believe the intent in the documentation was to stress the creation of one schema to hold all security objects. However, it could be desirable to create multiple schemas, too. That decision would be up to you, and your needs/requirements.

    HTH

    Tuesday, February 18, 2020 6:49 PM
  • Hi 

    Please get back to us if you have any further questions.

    Thanks
    Navtej S

    Thursday, February 20, 2020 1:29 PM
  • So far there have been no answers, HTH made a comment but the answer to the relationship between them all has not actually been answered.

    Karl


    • Edited by bingdaddy Thursday, February 20, 2020 6:24 PM
    Thursday, February 20, 2020 6:24 PM
  • Thank you for asking. The question is totally valid.

    The background to having a dedicated, separate schema for the security policy + the predicate function, is, that endusers need permissions to use the functions (SELECT or even EXECUTE if scalar CLR functions are used within) while a security developer who creates the security policy needs ALTER ANY SECURITY POLICY, ALTER SCHEMA and permissions on the function. And yet another person (if separated) needs permissions to create the functions that are used as predicates.

    Therefore, to reach proper separation and not have too many unnecessary permissions on the actual user tables schema(s), it is recommended to create separate schemas that act as permission boundaries for those security objects (functions and policies).

    I many databases one schema for those is sufficient. Cases like multi-tenancy within a database or other scenarios where different teams may work on separate permission structures, separating those further is advisable. This would also apply to other objects generally and is not RLS specific.

    I hope that clarifies.


    Andreas Wolter (Blog | Twitter)
    Senior Program Manager SQL Server & Azure Security

    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012

    Wednesday, March 4, 2020 10:08 PM
  • RLS supports two types of security predicates.

    • Filter predicates silently filter the rows available to read operations (SELECT, UPDATE, and DELETE).

    • Block predicates explicitly block write operations (AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE) that violate the predicate.

    Access to row-level data in a table is restricted by a security predicate defined as an inline table-valued function. The function is then invoked and enforced by a security policy. For filter predicates, the application is unaware of rows that are filtered from the result set. If all rows are filtered, then a null set will be returned. For block predicates, any operations that violate the predicate will fail with an error.

    Filter predicates are applied while reading data from the base table. They affect all get operations: SELECTDELETE and UPDATE. The users can't select or delete rows that are filtered. The user can't update rows that are filtered. But, it's possible to update rows in such a way that they'll be filtered afterward. Block predicates affect all write operations.

    • AFTER INSERT and AFTER UPDATE predicates can prevent users from updating rows to values that violate the predicate.

    • BEFORE UPDATE predicates can prevent users from updating rows that currently violate the predicate.

    • BEFORE DELETE predicates can block delete operations.

    Both filter and block predicates and security policies have the following behavior:

    • You may define a predicate function that joins with another table and/or invokes a function. If the security policy is created with SCHEMABINDING = ON, then the join or function is accessible from the query and works as expected without any additional permission checks. If the security policy is created with SCHEMABINDING = OFF, then users will need SELECT or EXECUTE permissions on these additional tables and functions to query the target table.

    • You may issue a query against a table that has a security predicate defined but disabled. Any rows that are filtered or blocked aren't affected.

    • If a dbo user, a member of the db_owner role, or the table owner queries a table that has a security policy defined and enabled, the rows are filtered or blocked as defined by the security policy.

    • Attempts to alter the schema of a table bound by a schema bound security policy will result in an error. However, columns not referenced by the predicate can be altered.

    • Attempts to add a predicate on a table that already has one defined for the specified operation results in an error. This will happen whether the predicate is enabled or not.

    • Attempts to modify a function, that is used as a predicate on a table within a schema bound security policy, will result in an error.

    • Defining multiple active security policies that contain non-overlapping predicates, succeeds.

    Filter predicates have the following behavior:

    • Define a security policy that filters the rows of a table. The application is unaware of any rows that are filtered for SELECTUPDATE, and DELETE operations. Including situations where all the rows are filtered out. The application can INSERT rows, even if they will be filtered during any other operation.

    Block predicates have the following behavior:

    • Block predicates for UPDATE are split into separate operations for BEFORE and AFTER. Consequently, you can't, for example, block users from updating a row to have a value higher than the current one. If this kind of logic is required, you must use triggers with the DELETED and INSERTED intermediate tables to reference the old and new values together.

    • The optimizer will not check an AFTER UPDATE block predicate if the columns used by the predicate function weren't changed. For example: Alice shouldn't be able to change a salary to be greater than 100,000. Alice can change the address of an employee whose salary is already greater than 100,000 as long as the columns referenced in the predicate weren't changed.

    • No changes have been made to the bulk APIs, including BULK INSERT. This means that block predicates AFTER INSERT will apply to bulk insert operations just as they would regular insert operations.

    Wednesday, March 4, 2020 10:34 PM