SQL: How to control access to SQL Server tables by entries in another table

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:

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

USE tempdb;

CREATE TABLE dbo.OKTable

(

OKTableID int IDENTITY(1,1) NOT NULL

CONSTRAINT PK_dbo_OKTable PRIMARY KEY,

SecuredInfo varchar(100)

);

INSERT dbo.OKTable (SecuredInfo)

VALUES ('Very'), ('Secret'), ('Stuff');

CREATE TABLE dbo.NotOKTable

(

NotOKTableID int IDENTITY(1,1) NOT NULL

CONSTRAINT PK_dbo_NotOKTable PRIMARY KEY,

SecuredInfo varchar(100)

);

INSERT dbo.NotOKTable (SecuredInfo)

VALUES ('Other'), ('Important'), ('Things');

Next we need to create and populate the table of who can access what:

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

CREATE SCHEMA [Security] AUTHORIZATION dbo;

CREATE TABLE [Security].PermittedTableUsers

(

PermittedTableUsers int IDENTITY(1,1) NOT NULL

CONSTRAINT PK_Security_PermittedTableUsers

PRIMARY KEY,

UserName sysname,

SchemaName sysname,

TableName sysname

);

INSERT [Security].PermittedTableUsers (UserName, SchemaName, TableName)

VALUES (N'dbo', N'dbo', 'OKTable');

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:

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

CREATE FUNCTION [Security].CheckUserAccess

(

@SchemaName AS sysname,

@TableName AS sysname

)

RETURNS TABLE

WITH SCHEMABINDING

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);

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:

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

CREATE SECURITY POLICY OKTableAccessFilter

ADD FILTER PREDICATE [Security].CheckUserAccess (N'dbo', N'OKTable')

ON dbo.OKTable

WITH (STATE = ON);

CREATE SECURITY POLICY NotOKTableAccessFilter

ADD FILTER PREDICATE [Security].CheckUserAccess (N'dbo', N'NotOKTable')

ON dbo.NotOKTable

WITH (STATE = ON);

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.

4 thoughts on “SQL: How to control access to SQL Server tables by entries in another table”

Gavan says:

October 10, 2019 at 1:51 pm

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

1. greglowblog says:
  
  October 10, 2019 at 2:08 pm
  
  Hi Gavan,
  Thanks. Copied in an earlier version by mistake. Corrected now.
  
Heidi says:

October 13, 2019 at 11:43 pm

Fun features, I played with it a while back found it great to stop sysadmin server role access to all data too I believe.

1. greglowblog says:
  
  October 14, 2019 at 11:08 am
  
  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.

Example

4 thoughts on “SQL: How to control access to SQL Server tables by entries in another table”

Leave a Reply Cancel reply