Stored Procedures and Roles (Example 2)

Go back to example 1 

A colleague showed me another example of Oracle not recognising grants made to roles when creating stored procedures. I'm including it as it is slightly easier to follow than the first example. It was tested on an Oracle 9 database. First create a test user:

SQL> conn / as sysdba
Connected.
SQL> create user andrew identified by reid
  2  default tablespace user_data
  3  quota unlimited on user_data
  4  /

User created.

SQL> grant create session to andrew
  2  /

Grant succeeded.

SQL> 

Connect as the test user and try to create a table. This will fail as the user does not have the create table privilege:

SQL> conn andrew/reid
Connected.
SQL> create table test_table(one_col number)
  2  /
create table test_table(one_col number)
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> 

Create a role with the create table privilege and grant it to the test user:

SQL> conn / as sysdba
Connected.
SQL> create role table_creator
  2  /

Role created.

SQL> grant create table to table_creator
  2  /

Grant succeeded.

SQL> grant table_creator to andrew
  2  /

Grant succeeded.

SQL> 

Connect as the test user using SQL*Plus and show that this new role allows him to create a table:

SQL> conn andrew/reid
Connected.
SQL> create table test_table(one_col number)
  2  /

Table created.

SQL> drop table test_table
  2  /

Table dropped.

SQL>

Allow the test user to create stored code:

SQL> conn / as sysdba
Connected.
SQL> grant create procedure to andrew
  2  /

Grant succeeded.

SQL> 

Connect as the test user and create a procedure to recreate the same table. Run the procedure. It will fail as stored code ignores grant statements passed via roles:

SQL> conn andrew/reid
Connected.
SQL> create procedure role_tester
  2  is
  3  begin
  4  execute immediate
  5  ('create table test_table(one_col number)');
  6  end;
  7  /

Procedure created.

SQL> exec role_tester;
BEGIN role_tester; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "ANDREW.ROLE_TESTER", line 4
ORA-06512: at line 1

SQL> desc test_table
ERROR:
ORA-04043: object test_table does not exist

SQL>

Now grant the create table privilege directly to the user:

SQL> conn / as sysdba
Connected.
SQL> grant create table to andrew
  2  /

Grant succeeded.

SQL>

Connect as the test user. Run the procedure again. This time it will work:

SQL> conn andrew/reid
Connected.
SQL> exec role_tester;

PL/SQL procedure successfully completed.

SQL> desc test_table
Name                    Null?    Type
----------------------- -------- ----------------
ONE_COL                          NUMBER

SQL>