Problem Description:
~~~~~~~~~~~~~~~~~~~~
You created context and are trying to add additional attributes there using
DBMS_SESSION.SET_CONTEXT procedure are are receiving:
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 55
ORA-06512: at line 1
For example in SQL*Plus you did:
SQL> CREATE CONTEXT m_context USING sec_user.p_context;
where
m_context is the context name (namespace)
p_context is the package in the sec_user schema.
Then you issued command:
SQL> EXECUTE DBMS_SESSION.SET_CONTEXT('m_context','attr1','Hello');
OR
SQL> begin
2 DBMS_SESSION.SET_CONTEXT('m_context','attr1','Hello');
3 end;
4 /
where
m_context is the context name (namespace)
attr1 is the name of an attribute you're adding
Hello is the value for this attribute
As a result you receive the above errors.
Solution Description:
~~~~~~~~~~~~~~~~~~~~~
To solve the problem create additional procedure.
(for example set_additional_attr) in the p_context package and put
DBMS_SESSION.SET_CONTEXT('m_context','attr1','Hello');
into this procedure.
Then from SQL*Plus run
SQL> EXECUTE sec_user.p_context.set_additional_attr;
Now attribute is set.
Testcase:
~~~~~~~~~
connect system/manager;
create user sec_user identified by sec_user;
grant connect, resource, dba to sec_user;
connect sec_user/sec_user;
create or replace package p_context as
procedure set_additional_attr;
end p_context;
/
create or replace package body p_context as
procedure set_additional_attr is
begin
dbms_session.set_context( 'm_context', 'attr1', 'Hello' );
end;
end p_context;
/
connect system/manager;
create context m_context using sec_user.p_context;
-- This statement will fail
execute dbms_session.set_context( 'm_context', 'attr1', 'Hello' );
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 55
ORA-06512: at line 1
-- set attribute by running this command
execute sec_user.p_context.set_additional_attr;
-- check if attribute was set
select sys_context( 'm_context', 'attr1' ) from dual;