Home » RDBMS Server » Security » How to get statement_type from procedure called in handler_module [DBMS_FGA] (10.2.0.1.0)
How to get statement_type from procedure called in handler_module [DBMS_FGA] [message #466590] Tue, 20 July 2010 04:30 Go to next message
misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india
Senior Member

Hi, i created one policy to manage auditing of one table test_fga. I used handler_module
parameter to record local auditing information in one table.
it is recording suceessfully.
Now i want to record executed statement_type [INSERT, UPDATE, DELETE] in local table.
Please suggest how to TRAP statement_type from procedure.


=> creating table and package
create table t1(A number)
/

CREATE OR REPLACE PACKAGE pkg_fga_audit IS
  PROCEDURE fga_audit (sname VARCHAR2, 
                       tname VARCHAR2, 
                       pname VARCHAR2);
END pkg_fga_audit;
/

CREATE OR REPLACE PACKAGE BODY pkg_fga_audit IS
  PROCEDURE fga_audit (sname VARCHAR2, 
                       tname VARCHAR2, 
                       pname VARCHAR2) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO fga_log(txt)
    VALUES (' sname=' || sname || ' tname=' || tname || ' pname='|| pname);
    COMMIT;
  END fga_audit;
END pkg_fga_audit;
/

=> creating policy
BEGIN
  dbms_fga.add_policy(
            object_schema => 'GOPAL',
            object_name => 'TEST_FGA',
            policy_name => 'TEST_FGA_ACCESS',
            audit_column => null,
            audit_condition => null,
            statement_types=> 'INSERT, DELETE, UPDATE',
            handler_module  =>'pkg_fga_audit.fga_audit');
end;
/


[Updated on: Tue, 20 July 2010 04:33]

Report message to a moderator

Re: How to get statement_type from procedure called in handler_module [DBMS_FGA] [message #475753 is a reply to message #466590] Fri, 17 September 2010 06:23 Go to previous message
misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india
Senior Member

BEGIN
  dbms_fga.add_policy(
            object_schema => 'GOPAL',
            object_name => 'TEST_FGA',
            policy_name => 'TEST_FGA_ACCESS_INS',
            audit_column => null,
            audit_condition => null,
            statement_types=> 'INSERT',
            handler_module  =>'pkg_fga_audit.fga_audit');
end;

BEGIN
  dbms_fga.add_policy(
            object_schema => 'GOPAL',
            object_name => 'TEST_FGA',
            policy_name => 'TEST_FGA_ACCESS_DEL',
            audit_column => null,
            audit_condition => null,
            statement_types=> 'DELETE',
            handler_module  =>'pkg_fga_audit.fga_audit');
end;

BEGIN
  dbms_fga.add_policy(
            object_schema => 'GOPAL',
            object_name => 'TEST_FGA',
            policy_name => 'TEST_FGA_ACCESS_UPD',
            audit_column => null,
            audit_condition => null,
            statement_types=> 'UPDATE',
            handler_module  =>'pkg_fga_audit.fga_audit');
end;


So when handler_module get called it will get policy name.

substr('TEST_FGA_ACCESS_UPD',-3,3)= 'INS' = insert statement executed.
substr('TEST_FGA_ACCESS_UPD',-3,3)= 'UPD' = insert statement executed.
substr('TEST_FGA_ACCESS_UPD',-3,3)= 'DEL' = insert statement executed.
Previous Topic: Getting Internal Error while doing Audit_Trail.
Next Topic: system privileges
Goto Forum:
  


Current Time: Sun Oct 13 17:28:02 CDT 2019