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

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

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

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

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.

2019-10-10