JL Computer Consultancy

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.

The script:            
复制

rem
复制

rem     Script:        flush.sql
复制

rem     Author:        Jonathan Lewis
复制

rem     Dated:         25th April 1997
复制

rem     Purpose:       Create packaged procedure to flush shared pool
复制

rem
复制

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.
复制

rem
复制

create or replace package flush_pool as
复制

        procedure flush_pool;
复制

end;
复制

/
复制

create or replace package body flush_pool as
复制

procedure flush_pool is
复制

        flush_cursor   integer;
复制

        m_junk         integer;
复制

begin
复制

        flush_cursor := dbms_sql.open_cursor;
复制

        dbms_sql.parse (flush_cursor, 
复制

                       'alter system flush shared_pool',
复制

                       dbms_sql.v7
复制

        );
复制

        m_junk := dbms_sql.execute(flush_cursor);
复制

        dbms_sql.close_cursor(flush_cursor);
复制

        exception
复制

               when others then
复制

                       if dbms_sql.is_open(flush_cursor) then
复制

                               dbms_sql.close_cursor(flush_cursor);
复制

                       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