Insufficient INHERIT PRIVILEGES for a stored procedure ORACLE

Question

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.

Answer 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;