This discussion is archived
2 Replies Latest reply: Feb 7, 2013 3:22 PM by rp0428 RSS

Privileges differ between SQL*Plus and Stored Procedure

421131 Newbie
Currently Being Moderated
A user with dba role can acces dba_role_privs. But in a stored procedure created by the same user this is not possible.
Normally the example should work properly
But with Oracle XE 11.02 - the cost free database with some limitations - I get error 942

I show a simple example in SQL*Plus:

SQL> r
1* select distinct grantee from dba_role_privs where grantee like 'SY%'

GRANTEE
------------------------------
SYSTEM
SYS

SQL> create or replace procedure x as
2 begin
3 for rec in ( select distinct grantee from dba_role_privs where grantee like 'SY%')
4 loop
5 null;
6 end loop;
7 end;
8 /

Warnung: Prozedur wurde mit Kompilierungsfehlern erstellt.
English translation: Procedure created with compile-errors

SQL> show error

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/14 PL/SQL: SQL Statement ignored
3/43 PL/SQL: ORA-00942: table or view does not exist
SQL>

How can I fix this?
  • 1. Re: Privileges differ between SQL*Plus and Stored Procedure
    Justin Cave Oracle ACE
    Currently Being Moderated
    Why do you believe the example should work properly?

    Normally, regardless of the version, privileges that are granted via roles (like DBA) are not available to stored procedures. A definer's rights stored procedure can only take advantage of privileges that are granted directly to the procedure owner. An invoker's rights stored procedure can take advantage of privileges granted through a role but that requires that the caller have independent privileges to query the table in question (and, in this case, would require using dynamic SQL).

    The simplest approach would generally be to grant the procedure owner the SELECT ANY DICTIONARY privilege.

    Justin
  • 2. Re: Privileges differ between SQL*Plus and Stored Procedure
    rp0428 Guru
    Currently Being Moderated
    >
    A user with dba role can acces dba_role_privs. But in a stored procedure created by the same user this is not possible.
    Normally the example should work properly
    But with Oracle XE 11.02 - the cost free database with some limitations - I get error 942
    >
    No - your example should not 'work properly' - it should, and does, give an exception.

    Roles work differently in anonymous blocks (your sql*plus example) than they do in 'named' PL/SQL blocks (your stored procedure).

    Yours is a question that comes up so frequently in the forum that it should be added as a special entry to the FAQ list so we don't need to keep explaining it over and over and providing the same doc link.

    Justin tried to explain this but see the Database Security Guide for the full explantion of how roles work in PL/SQL
    http://docs.oracle.com/cd/B28359_01/network.111/b28531/authorization.htm#i1007304
    >
    How Roles Work in PL/SQL Blocks
    The use of roles in a PL/SQL block depends on whether it is an anonymous block or a named block (stored procedure, function, or trigger), and whether it executes with definer's rights or invoker's rights.

    Roles Used in Named Blocks with Definer's Rights
    All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer's rights. Roles are not used for privilege checking and you cannot set roles within a definer's rights procedure.

    The SESSION_ROLES view shows all roles that are currently enabled. If a named PL/SQL block that executes with definer's rights queries SESSION_ROLES, then the query does not return any rows.

    See Also:

    Oracle Database Reference
    Roles Used in Named Blocks with Invoker's Rights and Anonymous PL/SQL Blocks
    Named PL/SQL blocks that execute with invoker's rights and anonymous PL/SQL blocks are executed based on privileges granted through enabled roles. Current roles are used for privilege checking within an invoker's rights PL/SQL block. You can use dynamic SQL to set a role in the session.
    >
    Read, and bookmark, that document section.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points