Is it possible to monitor role and privilege grant/revoke using triggers?

Question

Is it possible to monitor role and privilege grant/revoke using triggers? I' aware of doing that using Oracle audit tools however, it's interesting is it possible to do that using triggers.

Answer 1

You could do that with DDL triggers ([before|after] grant or revoke).

The docs are in the Oracle® Database PL/SQL Language Reference. You should look carefully at the event attribute functions table, and the Event Attribute Functions for Client Event Triggers section to know what information is available in those contexts.

Here's an example trigger (probably incomplete, this is just an example) that would log basic information about grant and revoke events, assuming a log table with appropriate columns:

create or replace trigger trigger1 after grant or revoke 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 log values
          (
            systimestamp,
            ora_sysevent,
            who(j),
            priv(i),
            ora_dict_obj_owner,
            ora_dict_obj_name
          );
      end loop;
    end loop;
  end;

Example:

SQL> grant select on log to bar;
Grant succeeded.

SQL> revoke select on log from bar;
Revoke succeeded.

SQL> select * from log;
DT                           WHAT   WHO PRIV   OWN OBJ
25-NOV-12 05.29.19.095403 PM GRANT  BAR SELECT MAT LOG
25-NOV-12 05.29.27.004610 PM REVOKE BAR SELECT MAT LOG