The difference between a direct grant and a role in PL/SQL

A little post about “beginning Oracle development”. Some people I work with sometimes struggle to grasp the permission-granted-via-role PL/SQL problem. But it’s so simple! One of the testers scratched his head many times asking himself why the procedure doesn’t compile. ORA-942 in the procedure, but in SQL Plus he can select from it. Let’s create the test case. As user martin, owner of table “charlie” we grant select on the table via role to alice.

mbach@pc:~> sqlplus martin@dev

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 27 15:10:59 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter password: *****

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user alice identified by xxxx;

User created.

SQL> grant connect to alice;

Grant succeeded.

This is the table Alice requires access to.

SQL> create table charlie (id number);

Table created.

Now let’s populate the table so we can test.

SQL> begin for i in 1..100 loop insert into charlie values (i); end loop; end;
 2  /

PL/SQL procedure successfully completed.

This is the role we initially create to grant Alice access to table martin.charlie. NB I initially complicated the scenario with “bob” but decided that was overkill-hence the table is called charlie. First we create the role, then we grant the select privilege on martin.charlie to it and finally allow Alice to make use of it

SQL> create role delta;

Role created.

SQL> grant select on martin.charlie to delta;

Grant succeeded.

SQL> grant delta to alice;

Grant succeeded.

SQL> grant resource to alice;

Grant succeeded.

With this initial work done, let’s see what Alice can access.

mbach@pc:~> sqlplus alice/xxxx@dev

SQL*Plus: Release 11.2.0.1.0 Production on Thu May 27 15:12:53 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> sho user
USER is "ALICE"
SQL> select * from martin.charlie
 2  /
select * from martin.charlie
 *
ERROR at line 1:
ORA-00942: table or view does not exist

Oops- forgot to enable the role.

SQL> set role all;

Role set.
With the role 
SQL> select * from martin.charlie
 2  /

 ID
----------
 1
 2
 3
 4
...
 98
 99

 ID
----------
 100

100 rows selected.

This is better. Now let’s think of a function f which returns the ID from table charlie for a given input parameter. Yes, the example is silly and nothing you’d ever see, but it helps to get the idea.

SQL> get f
 1  create or replace function alice.f(pi_nbr number) return number
 2  as
 3    v_id number;
 4  begin
 5    select id into v_id from martin.charlie
 6    where id = pi_nbr;
 7  exception
 8    when no_data_found then
 9     raise_application_error(-20001, 'no data found for id ' || pi_nbr);
 10* end;

Let's try to run the script

SQL> @f

Warning: Function created with compilation errors.

What’s the problem?

SQL> show err
Errors for FUNCTION F:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3     PL/SQL: SQL Statement ignored
4/38     PL/SQL: ORA-00942: table or view does not exist

Remember that the select in sqlplus actually worked! This is what gets many people’s head in. But we know better-now grant select on the table directly as the owner of the table (martin)

SQL> grant select on chalie to alice;

As alice, try again:

SQL> @f

Function created.

Lesson learned:

• When you grant access to a table via a role, you can select it from clients such as sqlplus
• If you want to use the object you are granted access to in PL/SQL, then the grant via the role isn’t enough
• Access needs to be granted _directly_ to the user, not through a role