My PL/SQL procedure fails with 'insufficient privileges', but all the SQL works in SQL*Plus. What's going wrong ?

The Oracle (tm) Users' Co-Operative FAQ

I have a PL/SQL procedure which won't work because of 'insufficient privileges', but it works in SQL*Plus it all seems to work. Why ?

The quick and dirty answer is - it is all down to roles. When any PL/SQL procedure, function whether stand alone or part of a package is run, it disables all roles. When your role is disabled, all privileges that it had are gone as well and you can no longer access the object in question. Oracle states that 'roles were created to facilitate user maintenance'

How to test for the possible appearance of this problem is reasonably simple. In an SQL*Plus session enter the following command :

	set role none;

From now on, any SQL commands you execute will be run in an identical manner to what will happen under PL/SQL. To solve the problem, you need to grant the appropriate privileges directly to the user and not through a role. Even the SYSTEM user suffers from this problem as the following example shows (assumes connected as SYSTEM user):

	Select table_name from all_tables where owner = 'OTHER_USER';

This works in SQL*Plus, and produces the expected results - a list of all tables owned by other_user. Now create a small procedure to carry out a similar task under PL/SQL.

	create or replace procedure Test (iUserName in varchar2) is
	BEGIN
	    dbms_output.enable(100000);
		for TableList in (select table_name from sys.all_tables where owner = upper(iUserName))
		loop
			dbms_output.put_line(TableList.table_name);
		end loop;
	END;
	/

The first problem is simply that you will get an error, PLS_00201 sys.all_tables must be declared. This is because the compiler is checking the privileges that you as a 'no-role' user have to access sys.all_tables. You don't have any, so you cannot even compile the procedure. If you get granted select any table You will be able to compile the procedure. Once compiled and error free, revoke the select any table privilege from system, and execute it as follows :

	set serverout on
	execute test('OTHER_USER');

All you get in return is a message to say 'PL/SQL procedure successfully completed'. You might be lucky, and get a few table names listed - these are tables which your user has been explicitly granted select privileges on.

If you now again, as SYS, grant select any table to system;, you will be able to run the procedure again, and this time, the results will be as expected.

Points to remember

• Never forget that roles are switched off when running a PL/SQL procedure, function etc.
• When running an anonymous block, they are left turned on - just to make things even more confusing.
• Calling a procedure or function (whether inside a package or not) owned by another user, to which you have been granted execute access, causes it to run as though you were the owning user. From Oracle 8i, this can be avoided by using authid current_user as part of the package/procedure/function declaration.

Further reading:

Seems to be a difficult subject this. There is no mention in the PL/SQL User Guide and Reference about this problem. There is note 27287.1 on metalink though and this can be found by searching for PLS-00201. As ever, MetaLink needs a logon.