ORA-6502 with Grant Logging Trigger

I have a new project I’m working on where I want to have an Oracle job revoke privileges I granted to IT staff that are older than 30 days. Our IT staff needs occasional access to a few production tables to troubleshoot problems. We grant SELECT privs on the tables that person needs, but no one ever tells me when they are done with their task and those privileges sit out there forever. I wanted to have a system automatically revoke privileges older than 30 days so that I wouldn’t have to remember to do it. Before I could revoke privileges, I needed a way of tracking those privileges. So I created a trigger that gets fired whenever a GRANT is issued and logs the details to a table. Later, an Oracle job will scan that table and revoke privileges it finds that are too old. My trigger code is as follows:

create or replace trigger sys.grant_logging_trig after grant on database
  declare
    priv  dbms_standard.ora_name_list_t;
    who   dbms_standard.ora_name_list_t;
    npriv pls_integer;
    nwho  pls_integer;
  begin
    npriv := ora_privilege_list(priv);
    if (ora_sysevent = 'GRANT') then
      nwho := ora_grantee(who);
    else
      nwho := ora_revokee(who);
    end if;
     for i in 1..npriv
     loop
       for j in 1..nwho
       loop  
        insert into system.grant_logging values
          ( systimestamp,
            ora_login_user,
            ora_sysevent,
            who(j),
            priv(i),
            ora_dict_obj_owner,
            ora_dict_obj_name
          );
      end loop;
    end loop; 
end;
 / 

The code above is not original. I found a good example on the Internet and modified a few things. After testing the code for 3 weeks, I rolled the trigger into production. It only took a few days for me to receive an error.

SQL> CREATE USER bob IDENTIFIED BY password;

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04088: error during execution of trigger 'SYS.GRANT_LOGGING_TRIG'
ORA-00604: error occurred at recursive SQL level 2
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 28

Hmmm…I’m creating a user not granting anything. But we can sure see my trigger is having a problem executing. So why is this trigger firing if all I’m doing is creating a user? A simple SQL trace showed me what was going on with that recursive SQL. Behind the scenes, Oracle is issuing the following on my behalf:

GRANT INHERIT PRIVILEGES ON USER “BOB” to PUBLIC;

Ok…so at this point, I know that there is a GRANT being issued when I create a user but why is this failing? I tested this trigger with system privileges and it worked just fine. Granted, I didn’t test INHERIT PRIVILEGES, so this is kind of an edge case.

After a good amount of debugging effort, I determined that the ora_privilege_list function call is returning an empty set to the collection named “priv”. As such, npriv is getting set to a NULL value. Because NPRIV is NULL, the line where is says “for i in 1..npriv” doesn’t make much sense, hence the error.

In my opinion, ora_privilege_list should return one item, “INHERIT PRIVILEGES” and I believe that it not returning that list to be a bug. However, if ora_privilege_list is going to return an empty collection, then the output from the function should be zero and then npriv would get a more proper value. For education purposes, ora_privilege_list is a synonym for DBMS_STANDARD.PRIVILEGE_LIST.

All that being said, I cannot control the Oracle function. And I do not want to wait for Oracle to change their code in DBMS_STANDARD to what I think it should be. So I will just code my trigger to handle the issue. Adding two simple lines solved my problem (seen below in bold).

create or replace trigger sys.grant_logging_trig after grant on database
  declare
    priv  dbms_standard.ora_name_list_t;
    who   dbms_standard.ora_name_list_t;
    npriv pls_integer;
    nwho  pls_integer;
  begin
    npriv := ora_privilege_list(priv);
    if (ora_sysevent = 'GRANT') then
      nwho := ora_grantee(who);
    else
      nwho := ora_revokee(who);
    end if;
   if to_char(npriv) is not null then 
     for i in 1..npriv
     loop
       for j in 1..nwho
       loop  
        insert into system.grant_logging values
          ( systimestamp,
            ora_login_user,
            ora_sysevent,
            who(j),
            priv(i),
            ora_dict_obj_owner,
            ora_dict_obj_name
          );
      end loop;
    end loop; 
  end if;
end;
 / 

So the fix is pretty simple. Only perform the two FOR loops if NPRIV is not null.