oracle查看directory使用权,请教关于dbms_session的使用权限问题

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;