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
Startup Weekends are 54-hour events where developers, designers, marketers, product managers and startup enthusiasts come together to share ideas, form teams, build products, and launch startups!
