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.
References: https://msdn.microsoft.com/en-us/library/dn765131.aspx
No comments:
Post a Comment