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:

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

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:

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:

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”

  1. 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. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *