This discussion is archived
6 Replies Latest reply: May 20, 2013 6:09 AM by 840275 RSS

audit, bind variable question

840275 Newbie
Currently Being Moderated
Hi!

I want to audit the selects on a table, but i don't get all of the bind variables.

audit select on myschema.mytable by access;

The original select is HQL and the select converted to SQL in the AUD$ table looks like:

select...
where exists
(select id
     from
     myschema.mytable
     where
     mytable.mycolumn1=:4
     and mytable.mycolumn2=:5)

mycolumn1 is VARCHAR2 (255 Char), mycolumn2 is TIMESTAMP(6)

In the AUD$ table I have the followign bind variables listed:
.... #4(24):myvalue #5(2):20 .....

Any date value i search for, there is the number 20 as a bind variable.
Why? How i can get the real date value?

Thanks in advance:
Bianca

Edited by: 837272 on 20-may-2013 0:46
  • 1. Re: audit, bind variable question
    DK2010 Guru
    Currently Being Moderated
    Hi,

    Please share the database version and show parameter audit;

    You can also check the link below, may it help you
    http://www.oracle-base.com/articles/10g/auditing-10gr2.php
    Any date value i search for, there is the number 20 as a bind variable.
    Why? How i can get the real date value?
    could you please elaborate more the above Quote
  • 2. Re: audit, bind variable question
    Girish Sharma Guru
    Currently Being Moderated
    See this demo : Oracle 11.2.0.1 Windows
    SQL> CREATE TABLE t (c1 TIMESTAMP);
    
    Table created.
    
    SQL> audit select on t by access;
    
    Audit succeeded.
    
    SQL> INSERT INTO t VALUES (TRUNC(SYSTIMESTAMP));
    
    1 row created.
    
    SQL> insert into t values (systimestamp);
    
    1 row created.
    
    SQL> select * from t;
    
    C1
    ---------------------------------------------------------------------------
    20-MAY-13 12.00.00.000000 AM
    20-MAY-13 03.12.04.890000 PM
    
    SQL> var bind_var varchar2(20);
    SQL> exec :bind_var:='22-May-2013';
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from t where c1=:bind_var;
    
    no rows selected
    
    SQL> select sql_bind from dba_audit_trail
      2  where sql_text like 'select * from t where c1=:bind_var%'
      3  /
    
    SQL_BIND
    --------------------------------------------------------------------------------
     #1(11):22-May-2013
    
    SQL>
    So, I think you should query sql_bind column of dba_audit_trail and it will tell you the value of bind varaible of timestamp.

    Regards
    Girish Sharma
  • 3. Re: audit, bind variable question
    840275 Newbie
    Currently Being Moderated
    Thank you but my problem is that in the sql_bind column i don't have the correct value.

    - if i execute manually a select i have the bind variables in the sql_bind column.
    - if the programs sends it's HQL to the databse, i can see the SQL which is executed but the bind varables are a bit strange in case on date/timestamp columns. Any other type of bind variables can be seen perfectly in the sql_bind column. In case of date, there is ALWAYS the value of 20 there.
    That's the problem.


    (the oracle is a 10.2.0.1.0 Standarr Edition so FGA is not available there)

    Edited by: 837272 on 20-may-2013 3:40
  • 4. Re: audit, bind variable question
    Girish Sharma Guru
    Currently Being Moderated
    - if i execute manually a select i have the bind variables in the sql_bind column.
    It means, there is nothing with Oracle, now problem workaround can be found with HQL only. I guess, HQL is not properly binding timestamp data type. If you just google around "hql bind variables timestamp" or something like then I hope you may get the clue/answer. Oracle is no more concern here, if Oracle gets the correct value from HQL, it will write in sql_bind column of dba_audit_trail.

    Regards
    Girish Sharma
  • 5. Re: audit, bind variable question
    840275 Newbie
    Currently Being Moderated
    The program functions correctly. It returns the correct values of the select.
    So i deduce that HQL sends the date values to oracle and it executes the query.
    My question is why i cannot see correctly these bind variables.
    Oracle executes the query well.

    What is strange is that in the V_$SQL_BIND_CAPTURE view i have all bind variables of this query EXCEPT the timestamp.
    But somehow oracle should get this value because it executes well the query.
    Where is this timestamp value hidden?

    Edited by: 837272 on 20-may-2013 4:22
  • 6. Re: audit, bind variable question
    840275 Newbie
    Currently Being Moderated
    I have found the following:
    444551.1
    The bind value for the column which is defined timestamp(3) is not captured and it is giving NULL for value_string. The bind value is obtained for all other columns having datatypes except timestamp. The timestamp datatype is captured as WAS_CAPTURED column of V$SQL_BIND_CAPTURE is returned YES.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points