Geek Answers Handbook

Backup dba support in Oracle

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

I try to audit whenever the user is given the role of database administrator and the e-mail message is deleted when an event occurs.

I am sure the AUDIT DBA; team AUDIT DBA; will check all actions performed on the DBA role. I have a fully working procedure that takes care of part of the email, but I don’t know about the standard audit method to invoke the procedure in the same way as the small-scale audit policy.

I tried to use 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 a notification that "adding a policy to an object owned by SYS is not allowed." I did not find a search on the Oracle documentation in this way.

My question is: can someone suggest an audit method and an Oracle database when a user receives a DBA role that can also trigger an email notification?

Thanks in advance for your help!

+4
source share
2 answers

You can enable AUDIT_TRAIL and create a task that periodically queries the DBA_AUDIT_TRAIL view by 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)
+4
source

You can also create a database trigger for this:

  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;

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

+1
source

More articles:


All Articles