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