Is it possible to use GRANT inside a trigger in Oracle 18c?

I try to create a trigger which automatically grants select on all new tables for a specific schema, whenever a new table in this schema is created.

Background for this is IBM InfoSphere Information Server’s Exception Database. This tool creates new tables for exceptions that are created in DataStage Jobs and I want a group of developers to be able to query these tables without giving them permission to the owner of the schema.

So my idea was to create a trigger like this:

create or replace trigger set_permissions
 after create on schema
 DECLARE
 obj_name VARCHAR2(30) := DICTIONARY_OBJ_NAME;
 BEGIN
  IF DICTIONARY_OBJ_TYPE = 'TABLE'
  THEN
   GRANT SELECT ON c##ESDB_USER.obj_name TO c##DATASTAGE_USER;
  END IF;
 END set_permissions;

But I get error “PLS-00103” after compiling the trigger. It says, that “GRANT” is not expected and it expects one of the following instead:

( begin case declare exit for goto if loop mod null pragma    raise return select update while with <an identifier>    <a double-quoted delimited-identifier> <a bind variable> <<    continue close current delete fetch lock insert open rollback    savepoint set sql execute commit forall merge pipe purge    json_exists json_value json_query json_object json_array

Sounds to me that GRANT is not allowed inside a trigger. If that’s so, is there another way to automatically grant users select-permission to new tables inside a specific schema?

Answer

Error you got says that you can’t execute DDL (yes, grant is a DDL) like that – it has to be done as dynamic SQL, using execute immediate.

However, it won’t help in this case because DDL implicitly commits, and you can’t commit within a trigger.

Now you’ll say that you can create a trigger as an autonomous transaction. Well, yes – you can, but it wouldn’t help in this case because the table is yet to be created (i.e. it doesn’t exist yet).

---

Here’s a workaround; see if it helps. In a few words:

• create an auxiliary procedure (to make it simpler) which will, actually, perform grant operation
• let trigger submit a job which will call that procedure

Here’s how: I’m connected as Scott and will be granting privileges to user Mike (as I don’t have your users):

SQL> show user
USER is "SCOTT"
SQL>
SQL> -- Auxiliary procedure
SQL> create or replace procedure p_grant (par_str in varchar2) is
  2  begin
  3    execute immediate par_str;
  4  end;
  5  /

Procedure created.

SQL> -- Trigger
SQL> create or replace trigger set_permissions
  2    after create on schema
  3  declare
  4    l_job    number;
  5    l_str    varchar2(200);
  6    obj_name varchar2(30) := dictionary_obj_name;
  7  begin
  8    if dictionary_obj_type = 'TABLE'
  9    then
 10      l_str := 'GRANT SELECT ON ' ||obj_name || ' TO mike';
 11      dbms_job.submit
 12        (l_job,
 13         'begin p_grant(' || chr(39) || l_str || chr(39) ||'); end;',
 14          sysdate
 15        );
 16    end if;
 17  end set_permissions;
 18  /

Trigger created.

SQL>

Testing:

SQL> create table test (id number);

Table created.

SQL> insert into test values (222);

1 row created.

SQL> commit;

Commit complete.

Connect as Mike and check what it sees:

SQL> connect mike/lion
Connected.
SQL> select * from scott.test;

        ID
----------
       222

SQL>