CODEWITHSUNDEEP
oracle-database
Harold S Rpo
Harold S Rpo

Reputation: 1

Oracle: ORA-01031: insufficient privileges

I'm trying insert data from a table to another table with a stored procedure as follows:

create or replace Procedure SP_PE_MIG_PT021

AS

BEGIN

savepoint inicio;

--Insersión de datos de seis meses atrás a la fecha actual

insert into tmptbl_fr59pt021
select * from (select * from crpdta.f59pt021 where jde_date(PT59DTPT) <= (select add_months(To_date(current_date),-6) from dual)
and trim(PTIDPTPY) = '0010490'
)
;

insert into crpdta.F59PT021_HTR
select * from tmptbl_fr59pt021;

--Eliminar datos de la tabla origen

delete from crpdta.f59pt021 where jde_date(PT59DTPT) <= (select add_months(To_date(current_date),-6) from dual)
and trim(PTIDPTPY) = '0010490';

commit;

end SP_PE_MIG_PT021;

The table tmptbl_fr59pt021 is a temporary table. And i have this error: ORA-01031: insufficient privileges

But when i make a insert in this table outside the stored procedure insert data seamlessly.

How do I fix this error?

Thank You!

Upvotes: 0

Views: 5494

Answers (3)

Himanshu Kandpal
Himanshu Kandpal

Reputation: 1616

when inserting into a table using a stored procedure you need direct rights and not through roles, in your case you are able to insert into tmptbl_fr59pt021 table outside the procedure because you have access through a Oracle role.

You can confirm this by saying Set Role none; Then try the insert statement and it will fail if you have access through a role.

Upvotes: 0

Roberto Hernandez
Roberto Hernandez

Reputation: 8528

Option A

We called the user running the procedure usera ( you did not specify it ) and the owner of the table is crpdta. I guess that the owner of the procedure is also the owner of the table tmptbl_fr59pt021.

PL/SQL privileges only work when they are granted directly, not by roles. So you need at least the following

grant select,insert,delete,update on crpdta.f59pt021 to usera;

Option B

Another option is using the AUTHID clause inside the procedure which instructs Oracle as to whether the routine is to be run with the invoker's rights (CURRENT_USER), or with the Owner rights (DEFINER). If the clause is not specified, Oracle will default to using the AUTHID DEFINER. In your case Oracle runs the procedure with the privileges of the owner of the procedure.

In your case you would change the procedure as follows

create or replace Procedure SP_PE_MIG_PT021 authid current_user

AS

BEGIN

savepoint inicio;

--Insersión de datos de seis meses atrás a la fecha actual

insert into tmptbl_fr59pt021
select * from (select * from crpdta.f59pt021 where jde_date(PT59DTPT) <= (select add_months(To_date(current_date),-6) from dual)
and trim(PTIDPTPY) = '0010490'
)
;

insert into crpdta.F59PT021_HTR
select * from tmptbl_fr59pt021;

--Eliminar datos de la tabla origen

delete from crpdta.f59pt021 where jde_date(PT59DTPT) <= (select add_months(To_date(current_date),-6) from dual)
and trim(PTIDPTPY) = '0010490';

commit;

end SP_PE_MIG_PT021;

In this case, as long as crpdta has executing privilege over the procedure and it has read/write privileges over the table tmptbl_fr59pt021, you would not need any DML privilege granted in the table owned by crpdta to another user ( usera ). It is a different way, which in your case I don't think I would use.

However, thing in an scenario where you have a procedure used for a lot of users, all of them are inserting in their own tables. You don't want to replicate the code, you can put the AUTHID as CURRENT_USER and the procedure will execute with the invoker's rights

Upvotes: 1

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59557

Inside a PL/SQL block (i.e. procedure) you have only the privileges which are granted directly to you. Privileges granted by ROLE (e.g. DBA) do not apply inside a procedure.

run

GRANT INSERT, DELETE, UPDATE ON tmptbl_fr59pt021 TO ...

or similar.

Upvotes: 2

Related Questions