Home » SQL & PL/SQL » SQL & PL/SQL » oracle-dbms-fga-dynamically-create-an-audit-trail-or-policy (3 threads merged by bb)
oracle-dbms-fga-dynamically-create-an-audit-trail-or-policy (3 threads merged by bb) [message #661533] Wed, 22 March 2017 14:32 Go to next message
jokrasa
Messages: 14
Registered: March 2017
Junior Member
my goal is to run a select to a table and based on the certain rows dynamically create an audit trail or policy on them.

so..

I need to recreate the following stmt with binding parameters wrapped in a function to Loop with :


   BEGIN
         DBMS_FGA.ADD_POLICY (
          object_schema      =>  'I_SCHEMA',
          object_name        =>  'RECIPIENT',
          policy_name        =>  'CHK_I_SCHEMA_RECIPIENT',
          audit_column       =>  'CARRIER_NO',
          audit_condition       =>  'CARRIER_NO = ''20'' ',   
          handler_schema     =>  'SYSADMIN_FGA',
          handler_module     =>  'TEST_EMAIL_ALERT(''I_SCHEMA'',''RECIPIENT'',''TEST_CHK_SCHEMA_RECIPIENT'')',
          enable             =>   TRUE,
          statement_types    =>  'SELECT, UPDATE',
          audit_trail        =>   DBMS_FGA.DB + DBMS_FGA.EXTENDED); 
        END;
        /

First a procedure that calls the function...

   CREATE OR REPLACE PROCEDURE audit_slac_tables
    
    AS
     
     --DECLARE
      emailer VARCHAR2(40):='audit_email_alert';
      isSuccess VARCHAR2(40);
      
    
    CURSOR myCursor IS SELECT SCHEMA as sch,  TABLE_NAME as tab, FILTER_COLUMN as col, WHERE_COND as pred FROM SLAC_REDACTION_TABLE  slac where slac.table_name='RECIPIENT';
    
       
       BEGIN
    
       FOR curRec IN myCursor
        LOOP
          
           isSuccess := set_policy(curRec.sch ,curRec.tab, curRec.col, curRec.pred, emailer);
           
           DBMS_OUTPUT.PUT_LINE('isSuccess = :' || isSuccess);
           
        END LOOP;
       commit;
       END audit_slac_tables;


then the set_policy function that gets called/Looped:

    create or replace 
    function set_policy
           ( sch VARCHAR2 ,
             tab VARCHAR2,
             col VARCHAR2,
             pred VARCHAR2,
             emailer VARCHAR2
             )
             
     return VARCHAR2 is 
     
     policy_sql_stmt varchar2(1000);
     
       BEGIN         
        
    --policy_sql_stmt :=
    --        'BEGIN
    --                       DBMS_FGA.ADD_POLICY (
    --                       object_schema => '''||sch||''',
    --                       object_name => '''||tab||''',
    --                       policy_name => ''CHK_'||sch||'_'||tab||''',   
    --                       audit_column => '''||col||''',
    --                       audit_condition => '':pred'',
    --                       handler_schema => ''SYSADMIN_FGA'',
    --                       handler_module => '''||emailer||'('''||''||sch||''||''','''||''||tab||''||''',''''CHK_'||sch||'_'||tab||''')'',   
    --                       enable => TRUE,
    --                       statement_types => ''SELECT, UPDATE'',
    --                       audit_trail => DBMS_FGA.DB + DBMS_FGA.EXTENDED);
    --                       return policy_name
    --        END;
    --        get_policy';
       
       
     policy_sql_stmt :=
               'BEGIN
                              SYS.DBMS_FGA.ADD_POLICY (
                              object_schema => '':s'',
                              object_name => '':t'',
                              policy_name => ''CHK_:s_:t'',   
                              audit_column => '':c'',
                              audit_condition => '':p'',
                              handler_schema => ''SYSADMIN_FGA'',
                              handler_module => '''||emailer||'('''':s'''','''':t'''',''''CHK_:s_:t'''')'',   
                              enable => TRUE,
                              statement_types => ''SELECT, UPDATE'',
                              audit_trail => SYS.DBMS_FGA.DB + SYS.DBMS_FGA.EXTENDED);
               END;';  
       
               DBMS_OUTPUT.PUT_LINE('policy_sql_stmt = :' || policy_sql_stmt);
      
      
      EXECUTE IMMEDIATE policy_sql_stmt USING sch,tab,col,pred;
      
      
      
       RETURN 'success';
       END;


if I call it...

exec AUDIT_SLAC_TABLES;


I get the following bewildering error

Error starting at line : 6 in command -
exec AUDIT_SLAC_TABLES
Error report -
ORA-06550: line 12, column 18:
PLS-00201: identifier 'SYS.DBMS_FGA' must be declared
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored
ORA-06512: at "GAPLITE.SET_POLICY", line 51
ORA-06512: at "GAPLITE.AUDIT_SLAC_TABLES", line 27
ORA-06512: at line 1
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:


Why a reference problem where the script DBMS_FGA.ADD_POLICY never had a problem?

I can run this script ( listed 1st above ) but not dynamically... it loses trhe contextual reference to the SYS packages somehow ??
Re: oracle-dbms-fga-dynamically-create-an-audit-trail-or-policy [message #661536 is a reply to message #661533] Wed, 22 March 2017 15:03 Go to previous messageGo to next message
BlackSwan
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member
EXECUTE IMMEDIATE
Into a single VARCHAR2 variable construct the whole SQL statement.
Use DBMS_OUTPUT to print the string before issuing execute immediate
Cut & Paste the statement into SQL*Plus to see exactly what is wrong & where.
Debug the statement using SQL*PLUS & then correct your SP.
Repeat as many time as necessary.
Re: oracle-dbms-fga-dynamically-create-an-audit-trail-or-policy (3 threads merged by bb) [message #661538 is a reply to message #661533] Wed, 22 March 2017 15:54 Go to previous messageGo to next message
Michel Cadot
Messages: 65388
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Also always post your Oracle version, with 4 decimals, as solution depends on it.


And feedback in your previous topic BEFORE posting a new question.

Re: oracle-dbms-fga-dynamically-create-an-audit-trail-or-policy (3 threads merged by bb) [message #661583 is a reply to message #661538] Fri, 24 March 2017 04:44 Go to previous messageGo to next message
Bill B
Messages: 1718
Registered: December 2004
Senior Member
Is the dbms_fga in sys (what is your version) and has a grant been issued so that your schema can execute it?
Re: oracle-dbms-fga-dynamically-create-an-audit-trail-or-policy (3 threads merged by bb) [message #661584 is a reply to message #661583] Fri, 24 March 2017 04:49 Go to previous messageGo to next message
cookiemonster
Messages: 13016
Registered: September 2008
Location: Rainy Manchester
Senior Member
And is it a direct grant rather than a grant to a role?
Re: oracle-dbms-fga-dynamically-create-an-audit-trail-or-policy (3 threads merged by bb) [message #661599 is a reply to message #661533] Fri, 24 March 2017 11:35 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2707
Registered: January 2010
Location: Connecticut, USA
Senior Member
You don't need dynamic SQL, unless I'm missing something here:

create or replace 
  function set_policy(
                      sch VARCHAR2,
                      tab VARCHAR2,
                      col VARCHAR2,
                      pred VARCHAR2,
                      emailer VARCHAR2
                     )
    return VARCHAR2
    is 
        policy_sql_stmt varchar2(1000);
    begin         
        sys.dbms_fga.add_policy(
                                object_schema   => sch,
                                object_name     => tab,
                                policy_name     => 'CHK_|| sch || '_' || tab,
                                audit_column    => col,
                                audit_condition => pred,
                                handler_schema  => 'SYSADMIN_FGA',
                                handler_module  => emailer || '(''' || sch || ''',''' || tab || ''','''CHK_' || sch || '_' || tab || ''')',
                                enable          => TRUE,
                                statement_types => 'SELECT, UPDATE',
                                audit_trail     => SYS.DBMS_FGA.DB + SYS.DBMS_FGA.EXTENDED
                               );
       return 'success';
end;
/

SY.
Re: oracle-dbms-fga-dynamically-create-an-audit-trail-or-policy (3 threads merged by bb) [message #661749 is a reply to message #661599] Thu, 30 March 2017 10:04 Go to previous messageGo to next message
jokrasa
Messages: 14
Registered: March 2017
Junior Member
So I went with this..


THe function that gets called by the procedure to set the policy...
create or replace 
function set_policy
       ( sch VARCHAR2 ,
         tab VARCHAR2,
         colm VARCHAR2,
         pred VARCHAR2,
         emailer VARCHAR2
         )
         
 return VARCHAR2 is 
 
 policy_sql_stmt varchar2(1000);
 
 BEGIN         
       
           policy_sql_stmt :=
            'BEGIN
              SYS.DBMS_FGA.ADD_POLICY (
              object_schema => :s,
              object_name => :t,
              policy_name => ''CHK_:s_:t'',   
              audit_column => :c,
              audit_condition => :p,
              handler_schema => ''SYSADMIN_FGA'',
              handler_module => '''||emailer||'(:s,:t,''''CHK_:s_:t'''')'',   
              enable => TRUE,
              statement_types => ''SELECT, UPDATE'',
              audit_trail => SYS.DBMS_FGA.DB + SYS.DBMS_FGA.EXTENDED);
            END;';  
             
            --DBMS_OUTPUT.PUT_LINE('policy_sql_stmt = :' || policy_sql_stmt);
            
            BEGIN
                EXECUTE IMMEDIATE policy_sql_stmt USING sch,tab,colm,pred;
                --EXECUTE IMMEDIATE policy_sql_stmt USING pred;
                
                EXCEPTION  
                        WHEN OTHERS THEN
                        BEGIN
                          --dbms_output.put_line('set_policy error code: '||SQLCODE);
                          --dbms_output.put_line(DBMS_UTILITY.FORMAT_CALL_STACK);
                          RETURN ('set_policy error code: '||SQLCODE);
                        END;
             END;
             RETURN 'success';
   END;


The Procedure that calls it....

CREATE OR REPLACE PROCEDURE audit_slac_tables
AS
  --DECLARE
  emailer   VARCHAR2(40):='audit_email_alert';
  isSuccess VARCHAR2(40);
  CURSOR myCursor
  IS
    SELECT SCHEMA   AS sch,
      TABLE_NAME    AS tab,
      FILTER_COLUMN AS colm,
      WHERE_COND    AS pred
    FROM SLAC_REDACTION_TABLE slac;
    --WHERE slac.table_name IN ('RECIPIENT','CARD');
BEGIN
  FOR curRec IN myCursor
  LOOP
    BEGIN
            --emailer := getEmailer(curRec.sch ,curRec.tab);
            isSuccess := set_policy(curRec.sch ,curRec.tab, curRec.colm, curRec.pred, emailer);
            DBMS_OUTPUT.PUT_LINE('Proc isSuccess = :' || isSuccess);
          EXCEPTION
          WHEN OTHERS THEN
            dbms_output.put_line('Proc error code: '||SQLCODE);
            dbms_output.put_line('Proc error msg: '||SQLERRM);
            --dbms_output.put_line(DBMS_UTILITY.FORMAT_CALL_STACK);
            --dbms_output.put_line('================================================');
            CONTINUE;
    END;
    --dbms_output.put_line('================================================');
  END LOOP;
  COMMIT;
END audit_slac_tables; 


The emailer

create or replace 
function get_emailer
       ( sch VARCHAR2 ,
         tab VARCHAR2 
         )
     
 return VARCHAR2 is 
 
 emailer_sql_stmt varchar2(1000);
 
   BEGIN

emailer_sql_stmt :=

 'CREATE OR REPLACE PROCEDURE audit_email_alert (sch varchar2, tab varchar2, pol varchar2)
AS
msg varchar2(20000) := ''' || sch || '.' || tab || ' table violation. The time is: '';
BEGIN
msg := msg||to_char(SYSDATE, ''Day DD MON, YYYY HH24:MI:SS'');
SYS.UTL_MAIL.SEND (
sender => ''john.okrasa@alithya.com'',
recipients => ''john.okrasa@alithya.com'',
subject => ''Table modification on '||sch||'.'||tab||''',
message => msg);
END audit_email_alert;';


--DBMS_OUTPUT.PUT_LINE('emailer_sql_stmt = :' || emailer_sql_stmt);
EXECUTE IMMEDIATE emailer_sql_stmt;

  EXCEPTION 
            WHEN OTHERS 
            THEN dbms_output.put_line('get_emailer error code: '||SQLCODE); 

RETURN 'audit_email_alert';
END;


the key issue here was that I was assuming the user had implicit privileges on the sys.dbms_fga package, it was not the case... so I needed to Connect sys/xxx Grant execute on sys.dbms_fga to <username>;

Thanks !
Re: oracle-dbms-fga-dynamically-create-an-audit-trail-or-policy (3 threads merged by bb) [message #661753 is a reply to message #661749] Thu, 30 March 2017 10:54 Go to previous messageGo to next message
Michel Cadot
Messages: 65388
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

http://www.orafaq.com/forum/?t=rview&goto=661752#msg_661752

Re: oracle-dbms-fga-dynamically-create-an-audit-trail-or-policy (3 threads merged by bb) [message #661755 is a reply to message #661753] Thu, 30 March 2017 12:00 Go to previous messageGo to next message
BlackSwan
Messages: 25858
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/wiki/WHEN_OTHERS
Re: oracle-dbms-fga-dynamically-create-an-audit-trail-or-policy (3 threads merged by bb) [message #661771 is a reply to message #661755] Fri, 31 March 2017 11:45 Go to previous message
Bill B
Messages: 1718
Registered: December 2004
Senior Member
Why do you keep using execute immediate. It is totally unnecessary. look at the comment by Solomon Yakobson on March 24th.
Previous Topic: Re: ToDate function (split from hijacked thread http://www.orafaq.com/forum/t/56308/ by bb)
Next Topic: Can we replace any sub query using join
Goto Forum:
  


Current Time: Fri Feb 23 08:01:28 CST 2018

Total time taken to generate the page: 0.01556 seconds