How to GRANT SELECT to ANY Table in a Schema to a Role (Oracle)

Question

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.

https://stackoverflow.com/questions/187886/grant-select-on-all-tables-owned-by-specific-user — Lukasz Szozda, Apr 05 '19 at 16:11
Hi, thanks but this still requires me to run a script each time we add an object. I was hoping more for a grant which would automatically give access to all tables - current and future, without intervention. SELECT ON ANY TABLE is too high a privilege, I need this but at a schema level... — smackenzie, Apr 05 '19 at 16:14

score 1 · Answer 1 · answered Apr 05 '19 at 16:16

1

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

Your DBA is correct. You are searching for schema wide privileges(not implemented yet).

More: GRANT SELECT on all tables in a schema

answered Apr 05 '19 at 16:16

Lukasz Szozda

162,964
23
234
275

Thanks, that's a 2014 article...not implemented yet? – smackenzie Apr 05 '19 at 16:20
It is community suggestion, and it is simply not implemented, you could also check request ["ER: 16899440 - SCHEMA WIDE PRIVILEGES "](https://support.oracle.com/rs?type=bug&id=16899440) – Lukasz Szozda Apr 05 '19 at 16:22
1

Thanks...trust your DBA – smackenzie Apr 05 '19 at 16:32

score 0 · Answer 2 · answered Apr 05 '19 at 20:04

Your DBA is correct that there would need to be a grant for every object that is created. It is possible to automate this which gives you basically the behavior that you want. I've done this in the past. But I'd seriously question the requirement before implementing it.

In general, new tables aren't created in production automatically, they're created as part of a build process. Part of that build process involves doing things like figuring out who should have access to the tables. Assuming that you're going through some sort of source control system, that means that you've got a history of the grants on objects and you can tie back changes in those grants to user stories. That's a useful thing to have.

If you have applications that are creating new tables in production dynamically (like an off the shelf app I was supporting) or doing something else where giving grants as part of a build process would be problematic, you can automate the process with a DDL trigger.

Create a DDL trigger that fires when a table is created
In that DDL trigger, use dbms_job to run a grant statement immediately after the CREATE TABLE statement commits

Something like this should work (untested)

CREATE OR REPLACE TRIGGER ddl_create
   AFTER CREATE ON SCHEMA
DECLARE
   l_job         BINARY_INTEGER;
   l_sql         VARCHAR2(4000);
BEGIN
   IF ora_dict_obj_type = 'TABLE' 
  THEN
      l_sql := 'grant select on ' ||
                 ora_dict_obj_owner || '.' || ora_dict_obj_name ||
                 ' to analytics';

      DBMS_JOB.SUBMIT (
          job => l_job,
         what => l_sql );
   END IF;
END;

Since the job runs asynchronously, it is possible that it will take a second or two to run after the table is created which may be an issue if the application immediately tries to run a query on the object. It is also possible that your DBA may disable jobs or limit the number of jobs that can run simultaneously which could further delay the grant. And if something goes wrong with the trigger or the job, this is a sufficiently obscure approach that it is going to take people a while to figure out where the problem is. DBAs aren't in the habit of looking for DDL triggers that may be granting privileges when someone decides, say, that they want a new role to automatically get insert access on the new table or when a grant didn't happen because jobs were disabled.

@smackenzie - You can't commit inside the trigger. So you can't run DDL inside the trigger. You need to spawn the job so that the job can run in a separate transaction context. — Justin Cave, Apr 05 '19 at 21:15
Sorry, I see this is an old question but, wouldn't GRANT SELECT ANY TABLE TO ; work as requested? [EDIT] Yeah I see now that OP requested a schema wide privilege, while ANY TABLE is DB wide... — Francesco Pegoraro, Jun 19 '20 at 13:10

How to GRANT SELECT to ANY Table in a Schema to a Role (Oracle)

2 Answers2