Procedures to grant special privileges to ordinary users.
One of the features of packages and procedures is that they execute with the privilege of the id that compiled the code, not with the privileges of the id that is running the code (until 8.1.5, where the programmer can choose).
This can cause some irrititation from time to time, but it does have a converient upside - it is possible for a highly privileged id to write a package that does an important, but non-threatening, job and allow a low-privilege id to execute it.
Using this strategy you can build a production database which has NO high-privilege (i.e. high-risk) ids able to connect to it under normal working conditions. (Did you ever feel really safe when the overnight operator was connecting as SYS to do some routine task ?)
The following package is a very simple example that allows any user to execute the 'flush the shared pool' command without having the 'alter system' privilege. I wrote this for a site running OPS where we had to flush the shared pool each night at the end of the batch run if we wanted to stop the database from crashing catastrophically during the following day.
rem Script: flush.sql
rem Author: Jonathan Lewis
rem Dated: 25th April 1997
rem Purpose: Create packaged procedure to flush shared pool
rem Notes: Script to be run by SYS or other user that has
rem received the ALTER SYSTEM privilege directly and
rem not through a role.
create or replace package flush_pool as
create or replace package body flush_pool as
procedure flush_pool is
flush_cursor := dbms_sql.open_cursor;
'alter system flush shared_pool',
m_junk := dbms_sql.execute(flush_cursor);
when others then
if dbms_sql.is_open(flush_cursor) then
end /* procedure */;
end /* package */;
create public synonym flush_pool for sys.flush_pool;
grant execute on flush_pool to public;
Sample of Use:
SQL> execute flush_pool.flush_pool