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?