The Jonathan Lewis Pages

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.

Back to Main Index of Topics

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

Back to Main Index of Topics