0

I am having a stored procedure in user META. This SP truncates a table in user STAGE.

CREATE PROCEDURE META.sp_truncate_tablex
  AUTHID CURRENT_USER
as
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE STAGE.Tablex';
END;

When I run the SP I get RA-06598: insufficient INHERIT PRIVILEGES.

I have tried to make both users to DBA. This means if I run SELECT grantee

WHERE  granted_role = 'DBA'
ORDER BY grantee;

it shows me that META dn STAGE are DBA. I have also tried: GRANT INHERIT PRIVILEGES ON USER STAGE TO META; But I still get the same error in Oracle 18.

Amir
  • 399
  • 1
  • 7
  • 25
  • [Does this help?](https://oracle-base.com/articles/12c/control-invoker-rights-privileges-for-plsql-code-12cr1) – Alex Poole Jun 14 '21 at 09:46
  • @AlexPoole Yes I have read that already and tried everything. I granted all privileges to my META. It just does not help. I continue get the same error. – Amir Jun 14 '21 at 12:21
  • Which user are you executing the procedure as? If that is as META, why do you have the `authid` clause? Making both, or possibly either, user DBA seems like overkill though. – Alex Poole Jun 14 '21 at 12:43
  • The `authid` I have because it was written on the page which you sent. But I just realized when I don't use it it runs without problem. The "OVER KILL" was the problem. – Amir Jun 14 '21 at 12:58

1 Answers1

1

Your problem is this clause:

  AUTHID CURRENT_USER

This means the user who executes the procedure does so with their own privileges. So a user who is not META cannot run the procedure unless they have the DROP ANY TABLE privilege, in which case they don't need to run the procedure as they can truncate the table anyway.

The solution is to declare the procedure with

  AUTHID DEFINER

Now the procedure executes with the privileges of its owner - META - who presumably owns the target table. Except they don't. The table is actually owned by STAGE. So STAGE needs to own the procedure as well.

As it happens, DEFINER rights are the default so we don't need to explicitly define them, except for clarity.

CREATE PROCEDURE STAGE.sp_truncate_tablex
AS
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE STAGE.Tablex';
END;
APC
  • 144,005
  • 19
  • 170
  • 281