We wish to create an ANALYTICS role on our Oracle database. Anyone in this ANALYTICS role should be able to SELECT across any views in one (or more) schemas, but not ALL schemas.
We have a schema called ARIEL for example, and when we create new tables and views, we don't want to have to constantly apply GRANTS to the ANALYTICS role, and so we are hoping there is a way to apply a grant like this "GRANT SELECT ON ALL TABLES IN SCHEMA_X TO ANALYTICS"…note ANALYTICS is a role, not a schema.
Our DBA is saying this is not possible, and any future objects we create will need to have a grant applied, giving access to the ROLE.
Best Answer
Your DBA is correct. You are searching for schema wide privileges(not implemented yet).
More: GRANT SELECT on all tables in a schema