There was an interesting question in the forums the other day. The poster wanted to be able to put entries in a table to determine who could access data in the other tables in the database.
There are two basic ways to do this. If you want an error thrown, you'd be best just using GRANT/DENY/REVOKE as permissions and not using your own table to control it. However, if you want no error, but just no data, then the Row Level Security (RLS) added in SQL Server 2016 could do the job. Let's take a look:
Example
We'll start by creating two tables: one that I'll later be able to access and one that I won't:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
USE tempdb; GO CREATE TABLE dbo.OKTable ( OKTableID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_dbo_OKTable PRIMARY KEY, SecuredInfo varchar(100) ); GO INSERT dbo.OKTable (SecuredInfo) VALUES ('Very'), ('Secret'), ('Stuff'); GO CREATE TABLE dbo.NotOKTable ( NotOKTableID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_dbo_NotOKTable PRIMARY KEY, SecuredInfo varchar(100) ); GO INSERT dbo.NotOKTable (SecuredInfo) VALUES ('Other'), ('Important'), ('Things'); GO |
Next we need to create and populate the table of who can access what:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE SCHEMA [Security] AUTHORIZATION dbo; GO CREATE TABLE [Security].PermittedTableUsers ( PermittedTableUsers int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Security_PermittedTableUsers PRIMARY KEY, UserName sysname, SchemaName sysname, TableName sysname ); GO INSERT [Security].PermittedTableUsers (UserName, SchemaName, TableName) VALUES (N'dbo', N'dbo', 'OKTable'); GO |
Notice that I've placed it in a separate Security schema to make it easier to control who has access to all the security-related code and objects.
Then I create a function that determines if permission is granted:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE FUNCTION [Security].CheckUserAccess ( @SchemaName AS sysname, @TableName AS sysname ) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS CheckUserAccessOutcome WHERE EXISTS (SELECT 1 FROM [Security].PermittedTableUsers AS ptu WHERE ptu.UserName = USER_NAME() AND ptu.SchemaName = @SchemaName AND ptu.TableName = @TableName); GO |
The function checks if the logged on user has an entry for the table (schema name and table name used to identify the table).
Note that I've used the USER_NAME() function to find out who's logged on. It can be susceptible to impersonation issues so you might consider making it logon names instead of user names and using the ORIGINAL_LOGIN() function instead. But this will do for now.
Next, I apply the RLS security policy to every table:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE SECURITY POLICY OKTableAccessFilter ADD FILTER PREDICATE [Security].CheckUserAccess (N'dbo', N'OKTable') ON dbo.OKTable WITH (STATE = ON); GO CREATE SECURITY POLICY NotOKTableAccessFilter ADD FILTER PREDICATE [Security].CheckUserAccess (N'dbo', N'NotOKTable') ON dbo.NotOKTable WITH (STATE = ON); GO |
And then we're done. I'm logged on a dbo user, so if I try to access the tables, I see the outcome in the main image above. I can see the data in the OKTable but I can't see the data in the NotOKTable.
You can find out more about Row Level Security here.
Hi Greg,
I've just taken a look at your latest blog post, and am a little confused by the second data insert statement table – it's inserting into OKTable, but based on the pattern, I think it should be inserting into NotOKTable so you have data in both tables.
The image at the top of the page shows all the data in the one same table, but for the scenario you're trying to demonstrate, wouldn't you want data in both tables (and have it only show the three rows in OKTable?
CREATE TABLE dbo.NotOKTable( NotOKTableID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_dbo_NotOKTable PRIMARY KEY, SecuredInfo varchar(100));GO
INSERT /**HERE**/ dbo.OKTable /**HERE**/ (SecuredInfo)VALUES ('Other'), ('Important'), ('Things');GO
Hi Gavan,
Thanks. Copied in an earlier version by mistake. Corrected now.
Fun features, I played with it a while back found it great to stop sysadmin server role access to all data too I believe.
Hi Heidi,
Yes, but keep in mind that those same people can change both the function and the policy, so it'd only stop them if they aren't across how it all works.