Row Level Security
At the end of January, Microsoft launched a new Row Level Security implementation in preview for Azure SQL.
Row Level Security in Azure SQL allows you to set policies using SQL statements which filter data based on the users identity. This can be done based on the logged in user connected directly to the database or through an application.
The basic idea of Row Level Security is the ability to filter queries based on user credentials either supplied directly when a user connects directly to the database or passed in by an application passing in a user identity.
The implementation details can be found in this article.
Another new feature available in Azure SQL in preview is Data Masking. The concept of data masking is that based on policies, you can prescribe that when a user selects data from a table the data can be returned as masked data, e.g. XXXXXXX instead of the original value. The policy you set allows you to specify:
- Who receives masked data and who receives original data
- Which tables and columns are masked
- Whether to mask based on the source table name / column name or the alias provided in the query
- Whether to restrict developers directly connected to the database
- Format of masking based on a set of masking functions
The masking function allows you to mask common types of sensitive information. For example:
- Credit Card: XXXX-XXXX-XXXX-1234
- Social Security Number: XXX-XX-XX12
- Email Address: aXX@XXXX.com
You can also use your own masking patterns using the Custom Text function.
Unlike Row Level Security, Data Masking is set up either through the Azure Portal or through a REST API, not through SQL.