Examples
A. Scenario for users who authenticate to the database
This short example creates three users, creates and populates a table with 6 rows, then creates an inline table valued function and a security policy for the table. The example shows how select statements are filtered for the various users.
Create three user accounts that will demonstrate different access capabilities.
CREATE USER Manager WITHOUT LOGIN;
CREATE USER Sales1 WITHOUT LOGIN;
CREATE USER Sales2 WITHOUT LOGIN;
Create a simple table to hold data.
CREATE TABLE Sales
(
OrderID int,
SalesRep sysname,
Product varchar(10),
Qty int
);
Populate the table with 6 rows of data, showing 3 orders for each sales representative.
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 read access on the table to each of the users.
GRANT SELECT ON Sales TO Manager;
GRANT SELECT ON Sales TO Sales1;
GRANT SELECT ON Sales TO Sales2;
Create a new schema, and an inline table valued function. The function returns 1 when a row in the SalesRep column is the same as the user executing the query (@SalesRep = USER_NAME()) or if the user executing the query is the Manager user (USER_NAME() = 'Manager').
CREATE SCHEMA Security;
GO
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 a security policy adding the function as a filter predicate. The state must be set to ON to enable the policy.
CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep)
ON dbo.Sales
WITH (STATE = ON);
Now test the filtering predicate, by selected from the Sales table as each user.
EXECUTE AS USER = 'Sales1';
SELECT * FROM Sales;
REVERT;
EXECUTE AS USER = 'Sales2';
SELECT * FROM Sales;
REVERT;
EXECUTE AS USER = 'Manager';
SELECT * FROM Sales;
REVERT;
The Manager should see all 6 rows. The Sales1 and Sales2 users should only see their own sales.
Alter the security policy to disable the policy.
ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);
Now the Sales1 and Sales2 users can see all 6 rows.
B. Scenario for users who connect to the database through a middle-tier application
This example shows how a middle-tier application can implement connection filtering, where application users (or tenants) share the same SQL Server user (the application). The application sets the current application user ID in
SESSION_CONTEXT (Transact-SQL) after connecting to the database, and then security policies transparently filter rows that shouldn't be visible to this ID, and also block the user from inserting rows for the wrong user ID. No other app changes are necessary .
Create a simple table to hold data.
CREATE TABLE Sales (
OrderId int,
AppUserId int,
Product varchar(10),
Qty int
);
Populate the table with 6 rows of data, showing 3 orders for each application user.
INSERT Sales VALUES
(1, 1, 'Valve', 5),
(2, 1, 'Wheel', 2),
(3, 1, 'Valve', 4),
(4, 2, 'Bracket', 2),
(5, 2, 'Wheel', 5),
(6, 2, 'Seat', 5);
Create a low-privileged user that the application will use to connect.
-- Without login only for demo
CREATE USER AppUser WITHOUT LOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON Sales TO AppUser;
-- Never allow updates on this column
DENY UPDATE ON Sales(AppUserId) TO AppUser;
Create a new schema and predicate function, which will use the application user ID stored in SESSION_CONTEXT to filter rows.
CREATE SCHEMA Security;
GO
CREATE FUNCTION Security.fn_securitypredicate(@AppUserId int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE
DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('AppUser')
AND CAST(SESSION_CONTEXT(N'UserId') AS int) = @AppUserId;
GO
Create a security policy that adds this function as a filter predicate and a block predicate on Sales. The block predicate only needs AFTER INSERT, because BEFORE UPDATE and BEFORE DELETE are already filtered, and AFTER UPDATE is unnecessary because the AppUserId column cannot be updated to other values, due to the column permission set earlier.
CREATE SECURITY POLICY Security.SalesFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(AppUserId)
ON dbo.Sales,
ADD BLOCK PREDICATE Security.fn_securitypredicate(AppUserId)
ON dbo.Sales AFTER INSERT
WITH (STATE = ON);
Now we can simulate the connection filtering by selecting from the Sales table after setting different user IDs in SESSION_CONTEXT. In practice, the application is responsible for setting the current user ID in SESSION_CONTEXT after opening a connection.
EXECUTE AS USER = 'AppUser';
EXEC sp_set_session_context @key=N'UserId', @value=1;
SELECT * FROM Sales;
GO
-- Note: @read_only prevents the value from changing again
-- until the connection is closed (returned to the connection pool)
EXEC sp_set_session_context @key=N'UserId', @value=2, @read_only=1;
SELECT * FROM Sales;
GO
INSERT INTO Sales VALUES (7, 1, 'Seat', 12); -- error: blocked from inserting row for the wrong user ID
GO
REVERT;
GO
