Skip to content

Instantly share code, notes, and snippets.

@MindFlavor
Last active August 29, 2015 14:14
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save MindFlavor/61bf05e357cddbeffb02 to your computer and use it in GitHub Desktop.
HandsOn - SQL Database Row-Level Security blog post demo
-----------------------------
----------- Setup------------
------- Pick a test db ------
-----------------------------
SET NOCOUNT ON;
GO
CREATE SCHEMA Sec;
GO
CREATE TABLE [Sec].[SecurityLevel] (
[UserName] SYSNAME PRIMARY KEY CLUSTERED,
[GrantedLevel] INT
);
GO
CREATE USER VIP WITHOUT LOGIN;
GO
CREATE USER AverageGuy WITHOUT LOGIN;
GO
CREATE USER PowerlessGuy WITHOUT LOGIN;
GO
GRANT SELECT,INSERT,UPDATE,DELETE ON SCHEMA::[Sec] TO VIP;
GO
GRANT SELECT,INSERT,UPDATE,DELETE ON SCHEMA::[Sec] TO AverageGuy;
GO
GRANT SELECT,INSERT,UPDATE,DELETE ON SCHEMA::[Sec] TO PowerlessGuy;
GO
GRANT SHOWPLAN TO VIP
GO
GRANT SHOWPLAN TO AverageGuy
GO
GRANT SHOWPLAN TO PowerlessGuy
GO
INSERT INTO [Sec].[SecurityLevel]([UserName], [GrantedLevel]) VALUES('VIP', 10);
INSERT INTO [Sec].[SecurityLevel]([UserName], [GrantedLevel]) VALUES('AverageGuy', 5);
INSERT INTO [Sec].[SecurityLevel]([UserName], [GrantedLevel]) VALUES('PowerlessGuy', 1);
GO
CREATE FUNCTION [Sec].[fn_testSecurityLevel](@RequiredSecurityLevel AS INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS 'Result' FROM [Sec].[SecurityLevel] WHERE ([UserName] = USER_NAME() AND @RequiredSecurityLevel < [GrantedLevel]) OR (USER_NAME() = 'dbo');
GO
CREATE TABLE [Sec].[PrivilegeBasedTable] (
[ID] INT PRIMARY KEY CLUSTERED,
[SomeData] NVARCHAR(255),
[RequiredLevel] INT
);
GO
DECLARE @i INT;
SET @i = 0;
WHILE(@i < 10)
BEGIN
INSERT INTO [Sec].[PrivilegeBasedTable]([ID], [SomeData], [RequiredLevel]) VALUES(@i, 'Severity: ' + CONVERT(NVARCHAR, @i), @i);
SET @i = @i + 1;
END
CREATE SECURITY POLICY [Sec].[PrivilegeBasedTableFilter]
ADD FILTER PREDICATE [Sec].[fn_testSecurityLevel](RequiredLevel)
ON [Sec].[PrivilegeBasedTable]
WITH (STATE = ON);
CREATE TABLE [Sec].[PrivilegeReferenceTable] (
[ID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[PrivilegeBasedTable_ID] INT REFERENCES [Sec].[PrivilegeBasedTable]([ID]),
[SomeDetailData] NVARCHAR(255)
);
GO
DECLARE @i INT;
SET @i = 0;
WHILE(@i < 10)
BEGIN
INSERT INTO [Sec].[PrivilegeReferenceTable]([PrivilegeBasedTable_ID], [SomeDetailData]) VALUES(@i, 'Severity: ' + CONVERT(NVARCHAR, @i));
SET @i = @i + 1;
END
-----------------------------
----------- Test ------------
-----------------------------
SELECT * FROM [Sec].[SecurityLevel];
SELECT * FROM [Sec].[PrivilegeBasedTable];
GO
EXECUTE AS USER='VIP';
GO
SELECT * FROM [Sec].[PrivilegeBasedTable];
REVERT
EXECUTE AS USER='AverageGuy';
GO
SELECT COUNT(*) FROM [Sec].[PrivilegeBasedTable];
REVERT
EXECUTE AS USER='PowerlessGuy';
GO
SELECT * FROM [Sec].[PrivilegeBasedTable];
REVERT
SELECT * FROM [Sec].[PrivilegeReferenceTable] REF INNER JOIN [Sec].[PrivilegeBasedTable] TBL
ON TBL.[ID] = REF.[PrivilegeBasedTable_ID]
GO
EXECUTE AS USER='VIP';
GO
SELECT * FROM [Sec].[PrivilegeReferenceTable] REF INNER JOIN [Sec].[PrivilegeBasedTable] TBL
ON TBL.[ID] = REF.[PrivilegeBasedTable_ID]
REVERT
EXECUTE AS USER='AverageGuy';
GO
SELECT * FROM [Sec].[PrivilegeReferenceTable] REF INNER JOIN [Sec].[PrivilegeBasedTable] TBL
ON TBL.[ID] = REF.[PrivilegeBasedTable_ID]
REVERT
EXECUTE AS USER='PowerlessGuy';
GO
SELECT * FROM [Sec].[PrivilegeReferenceTable] REF INNER JOIN [Sec].[PrivilegeBasedTable] TBL
ON TBL.[ID] = REF.[PrivilegeBasedTable_ID]
REVERT
EXECUTE AS USER='PowerlessGuy';
GO
SELECT * FROM [Sec].[PrivilegeReferenceTable]
REVERT
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment