As
the names imply, V$SQLAREA contains the SQL statements currently in the shared pool, along
with current execution statistics. The view V$VPD_POLICY lists all the policies currently being
Chapter 9: Database Security and Auditing 329
enforced in the database, along with the predicate. Joining the two tables, as in the following
example, gives us the information we need to help debug any problems we??™re having with the
query results:
SQL> select s.sql_text, v.object_name, v.policy, v.predicate
2 from v$sqlarea s, v$vpd_policy v
3 where s.hash_value = v.sql_hash;
SQL_TEXT OBJECT_NAM POLICY PREDICATE
------------------------- ---------- ------------------- -------------------
select employee_id, first EMPLOYEES EMP_SELECT_RESTRICT EMPLOYEE_ID = 199
_name, last_name, email, OR MANAGER_ID = 199
job_id, salary, manager_i
d from hr.employees
select employee_id, first EMPLOYEES EMP_SELECT_RESTRICT
_name, last_name, email,
job_id, salary, manager_i
d from hr.employees
SQL>
If we add a join to V$SESSION in this query, we can identify which user was running the SQL.
This is especially important in the second SQL statement: there is no predicate applied to the SQL
statement; therefore, all we can infer is that one of the HR employees ran the query. There is a
downside to this method: If the database is extremely busy, the SQL commands may be flushed from
the shared pool for other SQL commands before you get a chance to run this query.
Pages:
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542