This content has been marked as final. Show 2 replies
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.
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
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.
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.