This is a quick post about beginning Oracle PL/SQL development. Long story short, there is a difference between invoker rights and definer rights in the way they respect role grants:

• definer rights PL/SQL blocks are the default, and none of the role grants you have are available
• code blocks defined with invoker rights however do respect role grants

Fun story: I helped troubleshoot a case of what appeared to be stubborn PL/SQL. The developer asked why the procedure wouldn’t compile. Oracle threw an ORA-942 in PL/SQL, whereas everything was fine in SQL*Plus.

Let’s reproduce: creating a test case

Let’s create the test case. The grant to select from MARTIN.CHARLIE is to be granted to ALICE via a role.

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 for a simple test:

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

PL/SQL procedure successfully completed.

Next let’s create the role to be granted to ALICE. It allows her to access MARTIN.CHARLIE. NB I initially complicated the scenario with another user, BOB, but decided that was overkill-hence the table is called CHARLIE.

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.
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.

Why does Oracle complain? What might be 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! The error is expected with definer rights code units. They are the default model, and they disable all role grants. I’d like to retain the definer rights mode, and therefore have to grant the select privilege directly to ALICE rather than via the role.

SQL> grant select on chalie to alice;

SQL> -- trying again as ALICE:

SQL> @f

Function created.

That’s it! The other option available is to switch to invoker rights.

Key takeaways and conclusion

• Definer’s Rights (Default): Executes with the creator’s privileges; roles are disabled for security. Use direct grants if this mode is required
• Invoker’s Rights: Executes with the invoker’s privileges; roles are enabled. Use AUTHID CURRENT_USER to enable this mode for better flexibility
• Choose the appropriate mode based on your application’s security and performance requirements
• For deeper insights into rights management, consult the Oracle Database Security Guide

The ORA-942 error in PL/SQL code units often stems from privilege issues tied to definer’s rights. By understanding the difference between definer’s and invoker’s rights, developers can apply the most suitable solution—whether through direct grants or switching to invoker’s rights. This ensures both functionality and adherence to security best practices in Oracle Database development.