JL Computer Consultancy

Procedures to grant special privileges to ordinary users.

April 1997

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.

Back to Main Index of Topics

The script:            
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
        procedure flush_pool;
create or replace package body flush_pool as
procedure flush_pool is
        flush_cursor   integer;
        m_junk         integer;
        flush_cursor := dbms_sql.open_cursor;
        dbms_sql.parse (flush_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 if;
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

Back to Main Index of Topics