CODEWITHSUNDEEP
oracle-databasepackage
user3819801
user3819801

Reputation: 41

Insufficient Priviledges error when trying to execute the procedure from package

Step 1 : I have created one package with procedures to create context and set value to the context.

create or replace PACKAGE Context_check AS

    PROCEDURE set_context_vpd_proc (V_ISID in varchar2);
    procedure set_context  (v_isid_a  in varchar2);

END Context_check;

create or replace PACKAGE BODY Context_check AS
 PROCEDURE set_context_vpd_proc (V_ISID in varchar2)
    AS
    v_STAT VARCHAR2(200);
    v_chk varchar2(2000);
  BEGIN
         DBMS_SESSION.SET_CONTEXT('VPD_CTX', 'ISID', V_ISID );
    --v_STAT := '';
  EXCEPTION
   WHEN NO_DATA_FOUND THEN NULL;
  END;

procedure set_context  (v_isid_a  in varchar2)
as
begin

EXECUTE IMMEDIATE  'CREATE OR REPLACE CONTEXT VPD_CTX using set_context_vpd_proc';

set_context_vpd_proc (v_isid_a);

EXCEPTION
   WHEN NO_DATA_FOUND THEN NULL;
end set_context;

END Context_check;

Step 2: When I am trying to executing the procedure I am getting an error

EXECUTE Context_check.set_context('Ana');

Error starting at line 43 in command:
EXECUTE Context_check.set_context('Ana')
Error report:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 114
ORA-06512: at "SEC_ADMIN.CONTEXT_CHECK", line 8
ORA-06512: at "SEC_ADMIN.CONTEXT_CHECK", line 20
ORA-06512: at line 1
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to change the current username or password
           without the appropriate privilege. This error also occurs if
           attempting to install a database without the necessary operating
           system privileges.
           When Trusted Oracle is configure in DBMS MAC, this error may occur
           if the user was granted the necessary privilege at a higher label
           than the current login.
*Action:   Ask the database administrator to perform the operation or grant
           the required privileges.
           For Trusted Oracle users getting this error although granted the
           the appropriate privilege at a higher label, ask the database
           administrator to regrant the privilege at the appropriate label.

I have already given all the grants on that package.Still I am not able to execute this procedure.

Note : If I create the same procedures as stand alone ,its working fine and setting the context.

Upvotes: 2

Views: 12402

Answers (1)

Stephane Maarek
Stephane Maarek

Reputation: 5352

You need to create a context using a package, not using a procedure inside of a package.

Instead of 

EXECUTE IMMEDIATE  'CREATE OR REPLACE CONTEXT VPD_CTX using set_context_vpd_proc';

Write

EXECUTE IMMEDIATE  'CREATE OR REPLACE CONTEXT VPD_CTX using Context_check';

Upvotes: 1

Related Questions