Forum Stats

  • 3,840,344 Users
  • 2,262,590 Discussions
  • 7,901,233 Comments

Discussions

How to pass a condition dynamically while creating audit policies - 12c

Hi Team,


My current environment is in 12.1.0.1 and we are in mixed mode editing.

How to create an audit policy by excluding some specific hosts. I want to get the hostname dynamically instead of giving it manually.

Below is the sample code where I tried, but it is not working. Could you please provide your inputs as I m new to pl/sql.

Audit policy

===========

declare

v_machine      varchar2(99);

lv_stmt     varchar2(32767);

begin

select host_name into v_machine from v$instance;

lv_stmt := q'[create audit policy LOG_AUDIT ACTIONS LOGON when 'SYS_CONTEXT (''USERENV'', ''HOST'') NOT IN (''v_machine'')' evaluate per session]';

execute immediate lv_stmt;

end;

/



Note: v_machine is the variable i am getting for the hostname, but when it creates audit policy, it just print v_machine instead of actual hostname.


Expected Output:

===========

Audit_policy Action_name Audit _condition

=====================================

LOG_AUDIT LOGON (''USERENV'', ''HOST'') NOT IN (''prod.example.com'')'


Actual Output

==========

Audit_policy Action_name Audit _condition

=====================================

LOG_AUDIT LOGON (''USERENV'', ''HOST'') NOT IN (''V_Machine'')'


Thanks,

Bala

Answers

  • Veda
    Veda Member Posts: 9 Blue Ribbon

    Hi Bala,

    Try to replace the following line :

    lv_stmt := q'[create audit policy LOG_AUDIT ACTIONS LOGON when 'SYS_CONTEXT (''USERENV'', ''HOST'') NOT IN (''v_machine'')' evaluate per session]';

    with

    lv_stmt := q'[create audit policy LOG_AUDIT ACTIONS LOGON when 'SYS_CONTEXT (''USERENV'', ''HOST'') NOT IN (''&v_machine'')' evaluate per session]';

    Just add "&" before v_machine in your statement. Try this and update.

    Thanks

    Veda Santosh

  • User_KYWTC
    User_KYWTC Member Posts: 17 Green Ribbon

    Hi Veda,


    Thank you so much for your response...Your query is working but it prompts me to enter some input since we are using '&' in the code. My use case is just to run the pl/sql code with providing any inputs.

    [email protected]:SQL> declare


    v_machine      varchar2(99);

    lv_stmt     varchar2(32767);


    begin

    select host_name into v_machine from v$instance;

    lv_stmt := q'[create audit policy LOG_AUDIT ACTIONS LOGON when 'SYS_CONTEXT (''USERENV'', ''HOST'') NOT IN (''&v_machine'')' evaluate per session]';

     2  3  4  5  6  7  8  9 execute immediate lv_stmt;

    end;

    / 10  11

    Enter value for v_machine: it should not prompt for input