6 Replies Latest reply: May 20, 2013 8:09 AM by 840275 RSS

    audit, bind variable question

    840275
      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
          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
            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
              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
                - 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
                  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
                    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.