-1

I get insufficient privileges error when executing a procedure inside a package, even though appropriate access has been granted.

CREATE OR REPLACE PACKAGE DEVELOPER.DDL_PACKS
AS
PROCEDURE disbcons
AS
  BEGIN
     EXECUTE IMMEDIATE 'ALTER TABLE TESTER.ADDRESS DISABLE CONSTRAINT PK4';
  END;
END;

GRANT ALTER ON TESTER.ADDRESS TO DEVELOPER;

EXEC developer.ddl.disbcons

EDIT: I tried adding AUTHID DEFINER in package header. But still i get the ORA-01031:insufficient privileges error when executed with DEVELOPER. user

Execution works good if i add AUTHID CURRENT_USER and execute with the same DEVELOPER user. I do not understand why oracle does not consider relevant access when executed with AUTHID DEFINER

Vivek
  • 4,452
  • 10
  • 27
  • 45
  • how to add `AUTHID DEFINER` for procedures within package? – Vivek Aug 14 '17 at 18:09
  • Possible duplicate of [Execute Immediate within a stored procedure keeps giving insufficient priviliges error](https://stackoverflow.com/questions/996198/execute-immediate-within-a-stored-procedure-keeps-giving-insufficient-priviliges) – Lukasz Szozda Aug 14 '17 at 18:21
  • @Vivek - If your procedure is within a package, you can only set `AUTHID` at the package level, not for individual procedures. – kfinity Aug 14 '17 at 20:36
  • I do not understand what is wrong here to down vote. A valid explanation will help to correct rather than silent down vote :| – Vivek Aug 15 '17 at 11:41
  • @lad2025 Thanks for sharing the question. My procedure is within package, my bad, i must have mentioned it straight forward. I thought the procedure in package or direct doesn't make any difference, but in actual it does. I have corrected my question. – Vivek Aug 15 '17 at 12:04
  • Isn't Authid definer the default pragma? I think changing to it won't solve your problem. I'm going for the more obvious question: are you sure you have the appropriate access? What were they? – Renato Afonso Aug 16 '17 at 12:36
  • I have edited my question. Please check `EDIT` part. – Vivek Aug 17 '17 at 07:26

1 Answers1

1

@Vivek you can add AUTHID DEFINER/CURRENT_USER within Package specification only, I think it will work after that, below is your package specification should be: 

CREATE OR REPLACE PACKAGE DEVELOPER.DDL_PACKS AUTHID CURRENT_USER
AS

   PROCEDURE disbcons;

END;
Mohammad Sabri
  • 26
  • 8