0

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!

Improve this question

3 Answers 3

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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

Improve this answer

Comments

0

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.