SQL Server 2016 Features – Row Level Security

Row-Level Security enables customers to control access to rows in a database table based on the characteristics of the user executing a query (e.g., group membership or execution context). Row-level security behaves very much like the WHERE clause of a query, but can encapsulate more complex logic.
The access restriction logic is located in the database tier rather than away from the data in another application tier. The database system applies the access restrictions every time that data access is attempted from any tier. Row-level security is both flexible and robust
Create database "RowLevelSecurityDb"

CREATE DATABASE RowLevelSecurityDb;
GO

USE [RowLevelSecurityDb]
GO

Create schema "security"

CREATE SCHEMA Security;
GO

Create Predicate function

CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname)  
    RETURNS TABLE  
WITH SCHEMABINDING  
AS  
    RETURN SELECT 1 AS fn_securitypredicate_result   
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager'; 

Create three users "Manager", "Sales1", "Sales2" and table "Sales"

CREATE USER Manager WITHOUT LOGIN;  
CREATE USER Sales1 WITHOUT LOGIN;  
CREATE USER Sales2 WITHOUT LOGIN

CREATE TABLE Sales  
    (  
    OrderID int,  
    SalesRep sysname,  
    Product varchar(10),  
    Qty int  
    );  
GO

Insert records to the Sales table

INSERT Sales VALUES   
(1, 'Sales1', 'Valve', 5),   
(2, 'Sales1', 'Wheel', 2),   
(3, 'Sales1', 'Valve', 4),  
(4, 'Sales2', 'Bracket', 2),   
(5, 'Sales2', 'Wheel', 5),   
(6, 'Sales2', 'Seat', 5);  
-- View the 6 rows in the table  
SELECT * FROM Sales; 

Grant select permissions to the three users "Manager", "Sales1", "Sales2"

GRANT SELECT ON Sales TO Manager;  
GRANT SELECT ON Sales TO Sales1;  
GRANT SELECT ON Sales TO Sales2;
GO

Create security policy and execute the select as individual users.

CREATE SECURITY POLICY SalesFilter  
ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep)   
ON dbo.Sales  
WITH (STATE = ON);  
GO

EXECUTE AS USER = 'Sales1';  
SELECT * FROM Sales;   
REVERT;  
  
EXECUTE AS USER = 'Sales2';  
SELECT * FROM Sales;   
REVERT;  
  
EXECUTE AS USER = 'Manager';  
SELECT * FROM Sales;   
REVERT;  

ALTER SECURITY POLICY SalesFilter  
WITH (STATE = OFF);  

Technorati Tags: SQL 2016,RLS,Security

Cross Database Ownership Chaining in SQL SERVER

Consider a scenario where you have a view object in a database named "Database A" tries to access a table from "Database A" as well as a table from "Database B". In this scenario to run the view the user must also be a user in Database B along with Database A. If these databases are tightly linked, cross database ownership chaining can be used where user can access the table from Database B only through the view from Database A but doesn't have explicit permissions to query the table in Database B.

Steps to enable Cross Database Ownership Chaining

Set both Database A and Database B in the chaining as trustworthy.

ALTER DATABASE Database1 SET TRUSTWORTHY ON;
ALTER DATABASE Database2 SET TRUSTWORTHY ON;

Make sure that both Tables inside the Database A and Database B belong to the same owner.

Enable Cross Database Ownership Chaining. This can be done on whole instance level or specific databases.

For instance level

Exec sp_configure 'cross db ownership chaining', 1;
RECONFIGURE;

For database level

ALTER DATABASE Database1 SET DB_CHAINING ON;
ALTER DATABASE Database2 SET DB_CHAINING ON;

Important: ASP.NET Security Vulnerability

ASP.Net uses encryption to hide sensitive data and protect it from tampering by the client. However, a vulnerability in the ASP.Net encryption implementation can allow an attacker to decrypt and tamper with this data.

But what can the attacker do with this capability? Part of the answer depends on the ASP.Net application being attacked. For example, if the ASP.Net application stores sensitive information, such as passwords or database connection strings, in the ViewState object this data could be compromised. The ViewState object is encrypted and sent to the client in a hidden form variable, so it is a possible target of this attack.

If the ASP.Net application is using ASP.Net 3.5 SP1 or above, the attacker could use this encryption vulnerability to request the contents of an arbitrary file within the ASP.Net application. The public disclosure demonstrated using this technique to retrieve the contents of web.config. Any file in the ASP.Net application which the worker process has access to will be returned to the attacker.

Workaround

The workaround for this vulnerability is to use the customErrors feature of ASP.NET to configure applications to return the same error page regardless of the error encountered on the server.

By following the steps in the advisory to map all error messages to a single error page, you make it difficult for the attacker to distinguish between the different types of errors, effectively limiting access to the oracle.