Excluding a table from a Oracle role

Question

I have a role for human users through which they get access to all tables in main schema of oracle DB for my application. I want to create another role where i exclude few tables and keep access to remaining tables. My preference would be to create a new role and grant it access to the existing role and then i need to find a way to exclude the access to selected tables. Is this possible or if there there is any other way to achieve this without granting access to each table in new role separately.

Answer 1

You can GRANT a permission and once granted you can REVOKE that permission but there is no option for a user to have one role that GRANTs a permission and another role that overrides it and DENYs the permission as that functionality simply does not exist.

If you want user A to have permissions X, Y, Z and user B to have permissions X, Y but not Z then you can create two roles, one to grant the permissions X, Y and give that to both users and a second role to grant the permission Z and only give that to user A.