Maintain role permissions on new objects

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.