asked on

PL/SQL ORA-01031 Insufficient Privileges Procedure

Ok&this should be simple

I am logged in to SCHEMA1 who has DBA privileges.
I am trying to create a procedure in SCHEMA2 which contains series of INSERT AND UPDATE statements for the tables lying in SCHEMA3.

Example is

CURRENT SCHEMA
SCHEMA1
-------------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE SCHEMA2.TESTING

AS

BEGIN
INSERT INTO SCHEMA3.TABLE1 VALUES (, , ,)

END;

Its giving COMPILATION error : PL/SQL ORA-01031 Insufficient Privileges

The interesting is when I try to execute
INSERT INTO SCHEMA3.TABLE1 VALUES (, , ,)
From SQL Prompt is working all fine.

Any Ideas?

Guy Hengel [angelIII / a3]

for that to work, you have to grant SCHEMA2 the INSERT (and eventually SELECT) permissions on TABLE1 of SCHEMA3.
it does not matter if SCHEMA1 has the permission or not.

ajexpert

ASKER

Hi angelllll:
Since its working fine from the SQL Prompt, i suppose it does have the INSERT and SELECT grants.
Correct me if I am wrong.

Thanks,
Ajay M

Guy Hengel [angelIII / a3]

>Since its working fine from the SQL Prompt,
when logged in as SCHEMA2 or SCHEMA1?

as I said, as you create the procedure with SCHEMA2 as owner, it will be SCHEMA2 you need to test the SQL (INSERT) with...

ajexpert

ASKER

I have logged with with SCHEMA1 and ran the same INSERT SQL which is encapsulated in procedure.
This INSERT SQL works properly.

Also, I have checked that SCHEMA2 has all the SELECT, INSERT and UPDATE previlages.

Thanks

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial