Grant permissions to only specific tables in a Schema

Question

I am having a schema where I wanted to grant permissions in the following manner. What is the right way of doing this? To keep this simple, I am putting an example.

SCHEMA - HR TABLES:

• GEO_EMPLOYEE
• GEO_SALARY
• REGION_EMPLOYEE
• REGION_SALARY

Now, I have two roles HR_EXEC and HR_GEN. The HR_EXEC role will have access to the entire schema which I kind of grant at the schema level. But for the HR_GEN role, I would like to inherit from HR_EXEC role and at the same time have access to schema and all the tables except denied to 'GEO_' tables. How do I do it in a better way? Should I be creating some DENY statements for each 'GEO_' tables in this case? I have 100 tables like this.

I am looking specifically for a SnowSQL based approach here but I guess this concept would be same for any SQL environment.

Answer 1

In the standard SQL there is no way to explicitly exclude some privileges. Some RDBMS have the DENY command (MS SQL Server as an example) to do that.