OraFAQ Forum: Security » How to get statement_type from procedure called in handler_module [DBMS

Home » RDBMS Server » Security » How to get statement_type from procedure called in handler_module [DBMS_FGA] (10.2.0.1.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to get statement_type from procedure called in handler_module [DBMS_FGA] [message #466590]

Tue, 20 July 2010 04:30

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

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.

Report message to a moderator

Previous Topic:	Getting Internal Error while doing Audit_Trail.
Next Topic:	system privileges

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 04 02:44:11 CST 2024