Oracle PL/SQL for DBAs

Recording Bind Variables

Now I have laid a nice trap for Scott, the user who has been accessing the salaries of various highly paid executives of our organization (and my salary, as well). In the future, every time Scott queries a salary, that fact will be recorded in the audit trails.

Suppose, however, that after all this careful planning, Scott smells foul and somehow realizes what I’ve done. Being exceptionally clever, he decides to vary his SELECT statement, using a bind variable, in hopes of avoiding the audit, like this:

    SQL> variable EMPID number
    SQL> execute :EMPID := 100
    SQL>SELECT salary FROM emp WHERE empid = :EMPID;

His attempt will fail, because FGA captures the values of bind variables , in addition to the SQL text issued. The recorded values can be seen in the column SQL_BIND in the view DBA_FGA_AUDIT_TRAIL. In the above case, this is what will be recorded:

    SQL> SELECT sql_text,sql_bind FROM dba_fga_audit_trail;

    SQL_TEXT                                       SQL_BIND
    ---------------------------------------------- -----------------------
    select * from hr.emp where empid = :empid      #1(3):100

Notice how the bind variable is captured in the format:

    #1(3):100

where:

#1

indicates the first bind variable. If the query had more than one bind variable, the others would have been shown as #2, #3, and so on.

(3)

Indicates the actual length of the value of the bind variable. In this example, Scott used 100 as the value, so the length is 3.

:100

Indicates the actual value of the bind variable, which, in this case, is 100. ...