How to GRANT or DENY access to an object based on metadata for that object in SQL Server?

Question

We are in the process of reviewing our database permissions among various applications and business groups on SQL Server in a Windows Active Directory domain.

We currently do not use separate schemas for access control, and permissions are assigned at the database level via roles and windows groups. We have web applications that access the databases, and internal users who access the databases directly. Everything runs on at least SQL Server 2017 Enterprise. Using schemas may be a future practice, but should be outside the scope of recommendations for this question.

To start this process we classified all database tables based on several parameters. Such as the type of data it contains and it's use within our applications. These properties were mapped to user roles. We "tagged" the databases with this extra metadata using extended properties so that we can audit our systems. So, for instance, we might flag a table as containing PHI, and being used for SYSTEM or REPORTING. Finally, "Group1" may not be able to access any tables with PHI. Or, "Group2" may have SELECT only access to SYSTEM tables.

Now, we would like to implement a method of programmatically granting and restricting access to these tables based on this metadata. That way, when a developer adds a new table, they can set the appropriate metadata, and more granular permissions will be applied without them having intimate knowledge of our roles / groups / permissions as defined by the business and admin teams.

Now, I know there are other ways to accomplish things like this. But, this is one method that makes the most sense to us right now based on development requirements and resources. i.e. Achieve the best security value with as little application re-work as possible from the development team.

• Can you provide any guidance on how to do this?
• Any feature within SQL server that might allow us to do this in a native way?
• Or, is my only option to periodically run a job that reads the metadata and updates permissions?
• Do you foresee any problems with this method?

Answer 1

is my only option to periodically run a job that reads the metadata and updates permissions?

You could alternatively use Row Level Security to enforce security predicates dynamically at runtime. But that has additional complexity and runtime cost.

Do you foresee any problems with this method?

For those roles you should not mix manual changes to the permissions and permissions generated based on your metadata.

Answer 2

You don't mention what you are using to manage your database project source control, but I'm assuming Visual Studio.

If you are, then I would advocate for having the database role defined there (not it's members, that gets messy) but the role itself. Then on each object (table, procedure, view, etc.), add commands for the permissions the role should have on it. SELECT, UPDATE, etc.

During deployment (again, assuming you publish from Visual Studio) the engine will compare the permissions you have assigned against what's in the database and generate commands to bring the target into compliance.

Then the DBA maps windows users/groups or SQL Logins to the database role membership.

You don't go into much detail, but even an lare number of groups to map all possible types would be ok.

Role Examples:

• SYSTEM
• SYSTEM_PHI
• REPORTING
• REPORTING_PHI

Then add SYSTEM_PHI as a member of SYSTEM, etc.

Then on a table that should be used by SYSTEM_PHI only add the permissions that members of that role should able to do.

It's not as straightforward as mapping and if you have a truly large set of categories this may be impractical, but incorporating it into source control at least carries advantages of not having to make something to do this for you, it's about as "native" as you are going to get.

Advantages:

• schema compare to see deviations.
• corrections on deployments.
• native
• source control tracking