Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from 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. ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page