Home » SQL & PL/SQL » SQL & PL/SQL » DDL in PL/SQL => insufficient privileges [SOLVED]
icon5.gif  DDL in PL/SQL => insufficient privileges [SOLVED] [message #197460] Wed, 11 October 2006 05:51 Go to next message
bwetkstr
Messages: 114
Registered: August 2005
Senior Member
I wrote a pl/sql procedure to drop a table and the recreate the table from a select statement

here is the code
CREATE OR REPLACE  PROCEDURE "IXPR"."UPDATE_WORKFLOWUSERS$TEMP" 
    as
 result  varchar2(100);
begin

		 
		 		 execute immediate 'drop table ixpr.workflowusers$temp';
 		 		 execute immediate 'create table ixpr.workflowusers$temp
as (
select z.lid, sname, Datum from (
		select 
			  b.LID wfitem
		,      to_number(str_manipulation_userfrom(getlong(''tblpwinode'', ''macceptor'', to_char(a.LID)))) as User_Id
		,	   to_date(LPAD(c.SVALUE,8),''YYYYMMDD'') Datum
			from tblpwinode a, TBLPWIFLOW b, TBLPWIDATA c
			where macceptor is not null
			and b.LID=a.LFLOWINSTANCEID
			and c.SNAME = ''datInvoiceDate''
			and c.LFLOWINSTANCEID = b.LID
			and LPAD(c.SVALUE,8) > ''20051231''
		union all
		select 
			  b.LID wfitem
		,	   to_number(str_manipulation_userto(getlong(''tblpwinode'', ''macceptor'', to_char(a.LID)))) as User_Id
		,	   to_date(LPAD(c.SVALUE,8),''YYYYMMDD'') Datum
			from tblpwinode a, TBLPWIFLOW b, TBLPWIDATA c
			where macceptor is not null
			and b.LID=a.LFLOWINSTANCEID
			and c.SNAME = ''datInvoiceDate''
			and c.LFLOWINSTANCEID = b.LID
			and LPAD(c.SVALUE,8) > ''20051231''
		), IXOR.TBLPWOUSER z
where user_id = z.lid
)	
	';
		 
end;


Now when I want to exe the code I get the following :
Quote:

Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
JServer Release 8.1.7.3.0 - Production

SQL> show user
USER is "SYS"
SQL> exec ixpr.update_workflowusers$temp
BEGIN ixpr.update_workflowusers$temp; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges"
ORA-06512: at "IXPR.UPDATE_WORKFLOWUSERS$TEMP", line 7
ORA-06512: at line 1


As you can see I'm executing this as the sys user , but still I recieve the error "insufficient privileges".


Does anyone know why?

Kr
Karel.

[Updated on: Wed, 11 October 2006 06:29]

Report message to a moderator

Re: DDL in PL/SQL => insufficient privileges [message #197462 is a reply to message #197460] Wed, 11 October 2006 05:56 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Does SYS own those tables (tblpwinode a, TBLPWIFLOW b, TBLPWIDATA) too?
Re: DDL in PL/SQL => insufficient privileges [message #197464 is a reply to message #197460] Wed, 11 October 2006 05:59 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Yes, but SYS doesn't own the SP, does it ? It is owned by IXPR and it is IXPR that doesn't have the right privileges. SPs are run under the privileges of the user that created them unless you have "authid current_user" in the "create procedure" statement.

[Updated on: Wed, 11 October 2006 06:00]

Report message to a moderator

Re: DDL in PL/SQL => insufficient privileges [message #197468 is a reply to message #197460] Wed, 11 October 2006 06:14 Go to previous messageGo to next message
bwetkstr
Messages: 114
Registered: August 2005
Senior Member
@Littlefoot : Sys doesn't own those tables, but I that would be the problem then I would expect the error to read "table or view not found".

@Cthulhu : so procedures are run under the owner and not under the user who calls them? I didn't knew that. I've check the rights of the IXPR user and the user has the "dba" role and the "create any procedure" and "execute any procedure" privilege.

Am I missing one? Or witch one do I need?

Kr
Karel.

Re: DDL in PL/SQL => insufficient privileges [message #197471 is a reply to message #197460] Wed, 11 October 2006 06:20 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
The problem there is that those grants are given through a role and role-granted privs are no use when you want to create objects like views, procs or tables which access another table. IPXR will need a direct grant to all the tables on which the dynamic "create table" statement is based. It may also need to have "create table" granted explicitly.
Re: DDL in PL/SQL => insufficient privileges [message #197473 is a reply to message #197471] Wed, 11 October 2006 06:28 Go to previous messageGo to next message
bwetkstr
Messages: 114
Registered: August 2005
Senior Member
Cthulhu wrote on Wed, 11 October 2006 13:20

The problem there is that those grants are given through a role and role-granted privs are no use when you want to create objects like views, procs or tables which access another table. IPXR will need a direct grant to all the tables on which the dynamic "create table" statement is based. It may also need to have "create table" granted explicitly.



@Cthulhu : Again I didn't knew that roles did not matter when procedures are involved. Is this the same with packages and functions??

ps: I've added the 'create table' privilege to the IXPR user, now it works perfectly. Thx very much.


Kr
Karel.
Re: DDL in PL/SQL => insufficient privileges [SOLVED] [message #197474 is a reply to message #197460] Wed, 11 October 2006 06:31 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Yes, it will apply to packages and functions. It will also apply to a "create table" or "create view" statement outside of PL/SQL if the only access to the tables involved in the statement comes through a role rather than as direct grant.
Previous Topic: query giving duplicate result
Next Topic: see my problem in query
Goto Forum:
  


Current Time: Sat Dec 10 22:33:59 CST 2016

Total time taken to generate the page: 0.10083 seconds