Oracle audit grant dba role

Question

Running Oracle 11gR1 in an XP SP2 virtual machine. Full disclosure: This is for an assignment.

I'm attempting to audit whenever a user is granted the DBA role and fire off an email when the event occurs.

I believe the command AUDIT DBA; will audit all actions performed upon the DBA role. I have a fully working procedure which will take care of the email portion, but I'm not aware of a way for standard auditing to trigger the procedure in the same way a fine-grained auditing policy can.

I've tried using the policy

begin
dbms_fga.drop_policy
    (object_schema => 'SYS',
    object_name => 'DBA_ROLE_PRIVS',
    policy_name => 'EXAMPLE');
dbms_fga.add_policy
    (object_schema => 'SYS',
    object_name => 'DBA_ROLE_PRIVS',
    policy_name => 'EXAMPLE',
    audit_condition => 'GRANTED_ROLE = DBA',
    audit_column => 'GRANTED_ROLE',
    handler_schema => 'SYS',
    handler_module => 'FGA_NOTIFY');
end;

Where FGA_NOTIFY is the email procedure. But I get the notice "adding a policy to an object owned by SYS is not allowed." Searching through Oracle's documentation I have found no way around this.

My question is: can anyone suggest a method for auditing and Oracle database for when a user gains DBA role which can also trigger an email notification?

Thanks in advance for your help!

score 4 · Answer 1 · answered Jan 04 '13 at 12:51

You could enable AUDIT_TRAIL and create a job that periodically querys the DBA_AUDIT_TRAIL view looking for the types of grants you want to check:

   select os_username, username, userhost, terminal, timestamp, grantee  
   from   dba_audit_trail
   where  action_name = 'GRANT ROLE'
   and    obj_name    = 'DBA'
   and    timestamp   >= (last_time_check_was_done)

score 1 · Answer 2 · answered Jan 04 '13 at 14:03

You could also create a database trigger for that:

 CREATE OR REPLACE TRIGGER TG_GRANTS_DATABASE
    BEFORE GRANT ON DATABASE
 DECLARE
    V_NUM_GRANTEES   BINARY_INTEGER;
    V_GRANTEE_LIST   ORA_NAME_LIST_T;
    V_NUM_PRIVS      BINARY_INTEGER;
    V_PRIV_LIST      ORA_NAME_LIST_T;

    VB_AUDIT_PRIV    BOOLEAN;
    VB_AUDIT_GRANTEE BOOLEAN;
 BEGIN
    V_NUM_GRANTEES := ORA_GRANTEE (V_GRANTEE_LIST);
    V_NUM_PRIVS    := ORA_PRIVILEGE_LIST (V_PRIV_LIST);

    -- Verify the privilege
    VB_AUDIT_PRIV := FALSE;
    FOR COUNTER IN 1 .. V_NUM_PRIVS
    LOOP
       IF  V_PRIV_LIST (COUNTER) IN ('DBA') THEN
           VB_AUDIT_PRIV := TRUE;
           EXIT;
       END IF;
    END LOOP;

    -- Verify the user
    VB_AUDIT_GRANTEE := FALSE;
    FOR COUNTER IN 1 .. V_NUM_GRANTEES
    LOOP
       IF  V_GRANTEE_LIST (COUNTER) IN ('PUBLIC') THEN
           VB_AUDIT_GRANTEE := TRUE;
           EXIT;
       END IF;
    END LOOP;

    --   Prevent the statement
    --   or audit it (BEST DONE  on 'AFTER GRANT ON DATABASE trigger')
    IF   VB_AUDIT_GRANTEE AND VB_AUDIT_PRIV  THEN
         RAISE_APPLICATION_ERROR(-20001,'Sorry, this can''t be done.');
    END  IF;
 END;

This was an adaptation based on: http://examples.oreilly.com/oraclep3/individual_files/what_privs.sql

Thanks for both replies, and the link. The trigger is a perfect solution for what I was trying to do. — HawatT, Feb 06 '13 at 05:38

Oracle audit grant dba role

2 Answers2