Home arrow Oracle arrow Page 6 - Row-Level Security with Virtual Private Database

Invalid SQL - Oracle

Oracle's Virtual Private Database technology provides a logical and elegant method for applying security to the data within database tables. This article explains how VPD can be used to provide row-level security. It was excerpted from chapter 11 of Effective Oracle Database 10g Security by Design, written by David C. Knox (McGraw-Hill/Osborne, 2004; ISBN: 0072231300).

TABLE OF CONTENTS:
  1. Row-Level Security with Virtual Private Database
  2. RLS In-Depth
  3. Creating the Policy Function
  4. The RLS Layer of Security
  5. Debugging RLS Policies
  6. Invalid SQL
  7. Null Application Context Values and Recursive Lookups
  8. VPD Performance
  9. SHARED_STATIC Caching
  10. Caching Caution
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 33
October 20, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

A second possible reason a policy may fail is because the SQL returned by the policy function creates an invalid SQL statement. Recall the policy’s return value is added to the original SQL. The database will try to parse and execute this final SQL statement. If the SQL is invalid, the policy will throw an error.

Unfortunately, the error message does not indicate how the SQL is malformed. (It’s not even clear what the problem is.) For complex policies, or objects that have multiple policies, this can create some frustration.

For this example, the policy function has been modified to support a policy that restricts DML to just the user’s record. Unfortunately, the SQL column NAME should really be ENAME. Because the return value is a string, this error does not manifest itself until runtime:

scott@KNOX10g> -- Create policy function that returns
scott@KNOX10g> -- records just for the user. scott@KNOX10g> CREATE OR REPLACE FUNCTION pred_function (
 
2    p_schema  IN  VARCHAR2 DEFAULT NULL,
  3    p_object  IN  VARCHAR2 DEFAULT NULL)
  4    RETURN VARCHAR2
  5  AS
  6  BEGIN
  7    -- Restricting records by mapping
   ENAME
  8    -- to authenticated database user's 
   name  
  9    RETURN '
name = user';
 10  END;
 11  /
Function created.
scott@KNOX10g> SELECT COUNT(*) FROM emp; SELECT COUNT(*) FROM emp
                     *
ERROR at line 1:
ORA-28113: policy predicate has error

An ORA-28113 is the indication that the SQL is bad. It’s impossible for the database to know why this is true. Your policy function could have derived the predicate string in an infinite number of ways. The only thing you know is that the resulting SQL is invalid. To debug it, first try to inspect the value returned by the policy function:

scott@KNOX10g> SELECT pred_function "Policy Predicate" FROM DUAL;
Policy Predicate
-------------------
name = user

Note you can query the DBA_POLICIES view (as shown later in the “Viewing the Original SQL and Predicate” section) to see the PREDICATE but only if the policy doesn’t throw an error.

At this point, your best alternative may be to access a trace file. To do this easily, create a simple view (based on another Tom Kyte contribution from asktom.oracle.com) that can determine the trace filename the database will be using:

sec_mgr@KNOX10g> -- Create view to display session's current trace file
sec_mgr@KNOX10g> CREATE OR REPLACE VIEW get_trace_filename
  2  AS
  3    SELECT    c.VALUE
  4           || decode(instr
   (c.value,'\'),0,'/','\')
  5           || INSTANCE
 
6           || '_ora_'
  7           || LTRIM (TO_CHAR (a.spid,
   'fm99999'))
  8           || '.trc' filename
  9      FROM v$process a,
 10           v$session b,
 11           v$parameter c,
 12           v$thread c
 13     WHERE a.addr = b.paddr
 14       AND b.audsid = USERENV
   ('sessionid')
 15       AND c.NAME = 'user_dump_dest';
View created.
sec_mgr@KNOX10g> GRANT SELECT ON get_trace_filename TO PUBLIC;
Grant succeeded.
sec_mgr@KNOX10g> CREATE PUBLIC SYNONYM get_trace_filename
  2                 FOR get_trace_filename;
Synonym created.

Connect as a user and reproduce the query that caused the policy error. This is done here as SCOTT:

scott@KNOX10g> -- Enable tracing scott@KNOX10g> ALTER SESSION SET EVENTS
  2    '10730 trace name context forever, level 12';
Session altered.
scott@KNOX10g> -- Reproduce error. scott@KNOX10g> -- Issue query; note only selecting ENAME
scott@KNOX10g> SELECT ename FROM emp;
SELECT ename FROM emp
                     
*
ERROR at line 1:
ORA-28113: policy predicate has error
scott@KNOX10g> -- view trace file scott@KNOX10g> COL filename format a50 scott@KNOX10g> SELECT * FROM get_trace_filename;
FILENAME
--------------------------------------------
C:\ORACLE\ADMIN\KNOX10G\UDUMP\knox10g_ora_3740.trc
scott@KNOX10g> -- fix the policy function scott@KNOX10g> -- Create policy function that returns
scott@KNOX10g> -- records just for the user. scott@KNOX10g> CREATE OR REPLACE FUNCTION pred_function (
 
2    p_schema  IN  VARCHAR2 DEFAULT NULL,  
  3    p_object  IN  VARCHAR2 DEFAULT  NULL)   
  4    RETURN VARCHAR2
  5  AS
  6  BEGIN
  7   -- Restricting records by mapping 
   ENAME
  8   -- to authenticated database user's
   name
  9   RETURN '
ename = user';
 10  END;
 11  /
Function created.
scott@KNOX10g> -- Re-query to verify policy works
scott@KNOX10g> SELECT ename FROM emp;
ENAME
---------
SCOTT

Viewing the contents of the trace file with the name returned from GET_TRACE_FILENAME provides helpful information. The relevant information from the trace file just generated from the previous session is displayed here:

--------------------------------------------
Logon user     : SCOTT
Table/View     : SCOTT.EMP
Policy name    : DEBUG
Policy function: SCOTT.PRED_FUNCTION
RLS view :
SELECT "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL",
"COMM","DEPTNO" FROM
SCOTT"."EMP" "EMP" WHERE (name = user)
*** 2004-04-05 13:32:02.874
--------------------------------------------
Error information for ORA-28113:
Logon user     : SCOTT
Table/View     : SCOTT.EMP
Policy name    : DEBUG
Policy function: SCOTT.PRED_FUNCTION
RLS predicate:
name = user
ORA-00904: "NAME": invalid identifier
---------------------------------------
Logon user     : SCOTT
Table/View     : SCOTT.EMP
Policy name    : DEBUG
Policy function: SCOTT.PRED_FUNCTION
RLS view :
SELECT "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL",
"COMM","DEPTNO" FROM
SCOTT"."EMP" "EMP" WHERE (ename = user)

 

This file is very useful, especially the section that displays information on the ORA-28113 error. The RLS predicate record (bolded) pinpoints the erroneous SQL.

Be careful, there is a very misleading fact printed, too.

CAUTION

The RLS view is not the actual SQL the database executes.

Many incorrectly believe it is. This should be obvious because the original SQL statement asked only for the ENAME column; the RLS view would have you believe all columns were selected.

Viewing the Original SQL and Predicate

Another view you may find helpful in inspecting your VPD implementations is the V$VPD_ POLICY view. This will tell you what policies have been successfully applied to your SQL statements. The PREDICATE column will actually show the SQL string that was appended to the query. You can join this record with V$SQL, which has the original SQL statement as follows:

sec_mgr@KNOX10g> -- Nothing in pool - a fresh database
sec_mgr@KNOX10g> SELECT * FROM v$vpd_policy;
no rows selected
sec_mgr@KNOX10g> CONN scott/tiger
Connected.
scott@KNOX10g> -- this will seed the v$vpd_policy view
scott@KNOX10g> SELECT COUNT (*) FROM emp;
COUNT(*)
--------
      
1
scott@KNOX10g> CONN sec_mgr/oracle10g Connected.
sec_mgr@KNOX10g> -- show VPD Policy results sec_mgr@KNOX10g> COL object_owner format a12 sec_mgr@KNOX10g> COL object_name format a12 sec_mgr@KNOX10g> COL policy format a6 sec_mgr@KNOX10g> COL sql_fulltext format a26 sec_mgr@KNOX10g> COL predicate format a12 sec_mgr@KNOX10g> SELECT object_owner,
 
2         object_name,
  3         policy,
  4         sql_fulltext,
  5         predicate
  6    FROM v$vpd_policy p, v$sql s
  7   WHERE p.sql_id = s.sql_id
  8    AND predicate IS NOT NULL;

OBJECT_OWNER OBJECT_NAME POLICY SQL_FULLTEXT PREDICATE

------------ ------------ ------ -------------------------

SCOTT EMP DEBUG SELECT COUNT (*) FROM emp ename = user


This is a good way of inspecting the policies, but there is one important caveat: no records are recorded if the policy throws an error. For policy errors, you will have to use tracing, and even then the real SQL is not given.



 
 
>>> More Oracle Articles          >>> More By McGraw-Hill/Osborne
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: