SQL Server 2016 CTP 2.3 Row Level Security for Memory Optimized Tables

CTP 2.3 supports Row Level Security for Memory Optimized Tables.

The function use with the security policy must be a natively compiled inline table valued function.

CREATE TABLE Sales
(
OrderID int PRIMARY KEY NONCLUSTERED,
SalesRep sysname,
Product varchar(10),
Qty int
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY);

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);

SELECT * FROM Sales;

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

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

CREATE SCHEMA Security;

DROP FUNCTION Security.fn_securitypredicate;

— For in-memory need Natively Complied Function

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

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

EXECUTE AS USER = ‘Sales1’;
SELECT * FROM Sales;
REVERT;

EXECUTE AS USER = ‘Sales2’;
SELECT * FROM Sales;
REVERT;

EXECUTE AS USER = ‘Manager’;
SELECT * FROM Sales;
REVERT;

DROP SECURITY POLICY SalesFilter;
DROP FUNCTION Security.fn_securitypredicate;
DROP TABLE Sales;

Advertisement

---