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;

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s