Sunday, August 21, 2016

Row-Level Security Limitations in SQL Server 2016

Data Security is the top most priority for all organisations where they always want to hide their sensitive data from the unrelated users. So, Microsoft introduced Row-Level Security (RLS) feature with SQL Server 2016 which is a centralized security feature within the database and helping us to maintain a consistent data access policy and reduce the risk of accidental data leakage. RLS works transparently and ability to control both read and writes operations to specific rows of data. It supports flexible access criteria means provides Fine-Grained Access Control on our database and could help organisations more easily to support organisational or industry compliance policies.

Limitations of Row Level Security
Although, row-level security will does great work as expected across features but, there are a few exceptions. There are several notes and warnings for using row-level security with certain other features of SQL Server.
DBCC SHOW_STATISTICS reports statistics on unfiltered data, and thus can leak information otherwise protected by a security policy. For this reason, in order to view a statistics object for a table with a row-level security policy, the user must own the table or the user must be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

Filestream- in SQL Server, FILESTREAM data is secured just like other data is secured, by granting permissions at the table or column levels. If a user has permission to the FILESTREAM column in a table, the user can open the associated files. Row-level security is incompatible with Filestream.

Polybase- Polybase is built into SQL Server 2016 and Polybase operations remain transparent to the querying application because the external data source is incorporated into the database schema. Row-level security is incompatible with Polybase.

Memory-Optimized Tables- The inline table-valued function used as a security predicate on a memory-optimized table must be defined using the WITH NATIVE_COMPILATION option. With this option, language features not supported by memory-optimized tables will be banned and the appropriate error will be issued at creation time.
Indexed views - In general, security policies can be created on top of views, and views can be created on top of tables that are bound by security policies. It doesn’t allow you to generate an indexed view of a table that has a defined security policy because row lookups via the index would bypass the policy.
Change Data Capture - Change Data Capture can leak entire rows that should be filtered to members of db_owner or users who are members of the "gating" role specified when CDC is enabled for a table.

Change Tracking - Change Tracking can leak the primary key of rows that should be filtered to users with both SELECT and VIEW CHANGE TRACKING permissions. Actual data values are not leaked; only the fact that column A was updated/inserted/deleted for the row with B primary key. This is problematic if the primary key contains a confidential element, such as a Social Security Number. However, in practice, this CHANGETABLE is almost always joined with the original table in order to get the latest data.

Full-Text Search - A performance hit is expected for queries using the following Full-Text Search and Semantic Search functions, because of an extra join introduced to apply row-level security and avoid leaking the primary keys of rows that should be filtered: CONTAINSTABLE, FREETEXTTABLE, semantickeyphrasetable, semanticsimilaritydetailstable, semanticsimilaritytable.
ColumnStore Indexes - RLS is compatible with both clustered and non-clustered ColumnStore indexes. However, because row-level security applies a function, it is possible that the optimiser may modify the query plan such that it does not use batch mode.

Partitioned Views - Block predicates cannot be defined on partitioned views, and partitioned views cannot be created on top of tables that use block predicates. Filter predicates are compatible with partitioned views.
Temporal tables are compatible with RLS. However, security predicates on the current table are not automatically replicated to the history table. To apply a security policy to both the current and the history tables, we must individually add a security predicate on each table.


No comments:

Post a Comment