ORA-01031: insufficient privileges at "SYS.DBMS_SESSION" when using a package on 11g

Question

I have a package with a procedure to create context and set the value to context. It works very well on 10g but on 11g I get the following error also with DBA role.

ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 101
ORA-06512: at "REDIS_DATA.DYNAMICSQL_CONTEXT", line 7
ORA-06512: at "REDIS_DATA.FESTSTELLUNG_GETOVERVIEW", line 99

The package is build as follow:

-- DYNAMICSQL_CONTEXT specification
CREATE OR REPLACE PACKAGE REDIS_DATA.DYNAMICSQL_CONTEXT 
 AS
 PROCEDURE CONTEXT_SETPARAM(p_name  IN VARCHAR2,
                         p_value IN VARCHAR2);
END DYNAMICSQL_CONTEXT;

And the body:

CREATE OR REPLACE PACKAGE BODY REDIS_DATA.DYNAMICSQL_CONTEXT 
  IS
  PROCEDURE CONTEXT_SETPARAM(p_name  IN VARCHAR2,
                             p_value IN VARCHAR2)
    IS
    BEGIN
      DBMS_SESSION.SET_CONTEXT('parameter', p_name, p_value);
    END CONTEXT_SETPARAM;
END DYNAMICSQL_CONTEXT;

It will be called like this

  IF p_ISTADMIN = 0
  THEN
      DYNAMICSQL_CONTEXT.CONTEXT_SETPARAM('pREVISORID', p_REVISORID);
      p__wherePart := p__wherePart || 'AND ((p.ISSECURE = 1 AND p.ID IN (select PARENTOBJECT from PRUEFUNG_BETEILIGTE where PROPERTY = SYS_CONTEXT(''parameter'', ''pREVISORID''))) OR (p.ISSECURE = 0)) ';
  END IF;

the context will be used from several stored procedures. How to build this to work on both platforms????

As addition here the privilegs of the schema user:

system privilege on 10g where it works:

ALTER SESSION  
CREATE ANY CONTEXT 
CREATE CLUSTER 
CREATE INDEXTYPE 
CREATE OPERATOR 
CREATE PROCEDURE 
CREATE SEQUENCE 
CREATE SESSION 
CREATE SYNONYM 
CREATE TABLE 
CREATE TRIGGER N 
CREATE TYPE N 
CREATE VIEW N 
DEBUG ANY PROCEDURE N 
DEBUG CONNECT SESSION

I tried this privilegs on 11g but it did not work. So I gave the schema user the role DBA. But this did not work also.

I solved the problem. The privilegs of the user are not the problem. I had to initialize the context in each of the calling Stores Procedures. EXECUTE IMMEDIATE 'CREATE OR REPLACE context parameter using DYNAMICSQL_CONTEXT'; — , May 12 '15 at 12:42

score 2 · Answer 1 · answered Mar 28 '16 at 22:07

I too faced the same few days back but solve it bit differently. In my case, calling DB user had every privilege required including EXECUTE and DBMS_SESSION. You don't need to create or initialise every time if you create CONTEXT like following. Pls see sample statement below:

CREATE OR REPLACE CONTEXT parameter USING DYNAMICSQL_CONTEXT **ACCESSED GLOBALLY**;

ORA-01031: insufficient privileges at "SYS.DBMS_SESSION" when using a package on 11g

1 Answers1