What's up with anonymous blocks?

TORTURE is when you get an idea, you test it (it works), you put it in a package or enter it into a job request, and then it fails. A common reason is roles, or lack thereof. If you test your code in an anonymous block of code, all of your roles supply permissions which may be key to its working. When you enter it into job request or named block of code, the roles are turned off. Only those rights that you received via a direct grant are still in force. Note that procedures, functions, and packages are not the only placed where roles are disabled, views, jobs, etc. also turn them off. Also note that we can sometimes get information from session tables/views that is the same as data denied us in v$views.

Code Blocks and Rights
Understanding named and anonymous blocks should be coincidental to understanding user and definer rights, because we change the way our code works by resetting the rights of named block of code so that the user's roles apply when the definers rights are not appropriate.

What is an anonymous block of code? It can be a block wrapped in "Begin ... End" (with no procedure
for function name), but it has no name, and is not going to be saved unless it is included with some named code.

What is a named block of code? You have already guessed, but it is a stored procedure, a description of a job request, or something else that is saved, like a view.

The Basics: When a procedure has definer rights (the normal procedure), if the person who wrote it, the definer, has directly granted rights to run it, then it will work for another user who has been granted permission to run it, and both users will share a single cursor, meaning there will be only one hard parse.

If the procedure has dynamic sql in it, like a function wrapped in execute immediate ' (do something)' that part of the code will not be checked until runtime, so it may appear to work but can fail later.

If the procedure uses invoker rights ("create or replace procedure payout_bonus authid current_user is ...") the code will not be checked until it is run, but (a big butt), this code will not share cursors, so every person who uses it will generate a hard parse to see if they have permission. Definer rights will share a single cursor as, to Oracle, only a single person is using it, the definer.

Summary:
Invoker rights: one hard parse for each invoker, one copy in the sga for each user.
Definer rights: one hard parse for the definer, only one copy in memory.

Examples:

system > select * from session_roles;

ROLE
------------------------------
PLUSTRACE
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
DATAPUMP_EXP_FULL_DATABASE
DATAPUMP_IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN
AQ_ADMINISTRATOR_ROLE

13 rows selected.

SQL> create or replace procedure show_privs -- named block, roles disabled
2 as
3 begin
4 dbms_output.put_line('ROLES: ');
5 for rec in (select * from session_roles) -- roles not needed to from select session_roles.
6 loop
7 dbms_output.put_line(rec.role);
8 end loop;
9* end;
system > /

Procedure created.

system > set serveroutput on
system > exec show_privs

ROLES: -- nothing, no roles active

PL/SQL procedure successfully completed.

Using set role to turn rolls from all to none and back.
confused@macie> set role none;

Role set.

confused@macie> select * from session_roles;

no rows selected

confused@macie> select name from v$statname;
select name from v$statname
*
ERROR at line 1:
ORA-00942: table or view does not exist

confused@macie> set role all;

Role set.
SQL> select name from v$statname
2 where rownum <= 5
/
NAME ----------------------------------------------------------------
logons
cumulative logons
current opened cursors
cumulative opened cursors
current user commits

Roles set back on; this works. Named block (view) only directly granted rights apply; no roles.

create or replace view stats
as select 'STAT...' || a.name name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
union all
select 'LATCH.' || name, gets
from v$latch
union all
select 'STAT...Elapsed Time', hsecs from v$timer
/
from v$statname a, v$mystat b
*
ERROR at line 3:
ORA-01031: insufficient privileges


Rules on roles in named blocks apply to jobs, views and any named block.

(Connor McDonald, Tom Kyte, and others go into this in a lot of detail. Search on invoker rights)