It seems I should have run across this before, but it doesn’t seem to get talked about much. This is my new favorite thing when making sure user permissions are what I think they are.
If you have a schema where you want developers to have read-only access to all tables, but you don’t want to grant select any, keeping up with new tables can be a challenge. We wrap the following into a script that runs a few times a day. We have a DBA schema in each database that contains a table with a list of schemas and associated roles that we want kept up … you can get fancy with it if you want, but essentially all you need is this query:
select 'grant select on '||owner||'.'||object_name||' to xx_schemaname_read;' from dba_objects where owner = 'schemaname' and (object_type = 'TABLE' or object_type='VIEW') and last_ddl_time > sysdate -7
One caveat here is that you cannot run this on a database with a ton of objects, or it may run for a very long time and/or cause issues. It isn’t smart enough to know which have already been granted and just regrants everything. I spent some time working on a trigger-based grant like this that would only fire on table/view create, and insert into a table of “pending grants,” but haven’t had time to finish it. It’s tricky because you can’t grant in a trigger, breaks the “no commit/rollback in a trigger” rule. This looks like the way to do it though.