oracle dbms_session.set_context,ORA-06512: at "SYS.DBMS_SESSION", line 122

2655270 wrote:HIwhile exec below command it is failed it showbegin dbms_session.set_context('ECGC_USERROLES_'||xx_x_preferanse.pref_verdi('OPERATION'), 'ROLELIST', null, null, 'sysadmin');end;ORA-01031: insufficient privilegesORA-06512: at "SYS.DBMS_SESSION", line 122ORA-06512: at line 2PRIVILEGEADMIN_OPTIONCOMMONCREATE ANY CONTEXTNONOCREATE PROCEDURENONOCREATE PUBLIC DATABASE LINKNONODEBUG CONNECT SESSIONNONOCREATE VIEWNONOCREATE SYNONYMNONOCREATE SESSIONNONOCREATE TRIGGERNONOCREATE SEQUENCENONODROP ANY MATERIALIZED VIEWNONODEBUG ANY PROCEDURENONOCREATE TYPENONOCREATE MATERIALIZED VIEWNONOEXECUTE ANY PROCEDURENONOCREATE TABLENONO

Is your namespace for the context really some dynamic string? That's a little weird, isn't it?

Reviewing the docs:

This procedure must be invoked directly or indirectly by the trusted package.

The caller of SET_CONTEXT must be in the calling stack of a procedure that has been associated to the context namespace through a CREATE CONTEXT statement. The checking of the calling stack does not cross a DBMS boundary.

Add the code to the package that is trusted to set a context for this namespace. It can't be done in an anonymous block like you've done:

[email protected]>exec dbms_session.set_context('ANDY_NAMESPACE','CONEXT_ATTR','VALUE_KEY',null,'client_info_string')

BEGIN dbms_session.set_context('ANDY_NAMESPACE','CONEXT_ATTR','VALUE_KEY',null,'client_info_string'); END;

*

ERROR at line 1:

ORA-01031: insufficient privileges

ORA-06512: at "SYS.DBMS_SESSION", line 130

ORA-06512: at line 1

[email protected]>create context andy_namespace using andy_secure_pkg;

Context created.

[email protected]>create or replace package andy_secure_pkg

2  is

3    procedure setStuff;

4  end andy_secure_pkg;

5  /

Package created.

[email protected]>ed

Wrote file afiedt.buf

1  create or replace package body andy_secure_pkg

2  is

3    procedure setStuff

4    is

5    begin

6      dbms_session.set_context('ANDY_NAMESPACE','CONEXT_ATTR','VALUE_KEY',null,'client_info_string');

7    end;

8* end andy_secure_pkg;

[email protected]>/

Package body created.

[email protected]>exec andy_secure_pkg.setStuff

PL/SQL procedure successfully completed.

[email protected]>select sys_context('ANDY_NAMESPACE','CONEXT_ATTR') from dual;

SYS_CONTEXT('ANDY_NAMESPACE','CONEXT_ATTR')

-----------------------------------------------------------------------------------------------------------------------------

VALUE_KEY