如何将sys.dbms_session 的权限授给用户?

rollingpig

有权执行，并不代表你就有权SET_CONTEXT
应该是SET_CONTEXT时出错了

wqliu

最初由 rollingpig 发布
[B]有权执行，并不代表你就有权SET_CONTEXT
应该是SET_CONTEXT时出错了 [/B]

对对,有可能是这样的,那如何解决这个问题呢

l2g32003

我写了一个和你类似的

9.2.0$sys@ora9.oracle.com >create user a identified by a;

User created.


9.2.0$sys@ora9.oracle.com >grant create session,create any procedure,create any context  to a;

Grant succeeded.

9.2.0$sys@ora9.oracle.com >connect a
Enter password:
Connected.

9.2.0$sys@ora9.oracle.com >create or replace package PGGIS_SESSION as
  2  procedure prx_start_with_version(name in varchar2);
  3  end;
  4  /

Package created.

9.2.0$sys@ora9.oracle.com >create or replace package body PGGIS_SESSION as
  2  procedure prx_start_with_version(name in varchar2)
  3  is
  4  begin
  5   DBMS_SESSION.SET_CONTEXT('my_ctx','name',name);
  6  end;
  7  end;
  8  /

Package body created.

9.2.0$sys@ora9.oracle.com >exec PGGIS_SESSION.prx_start_with_version('lg');
BEGIN PGGIS_SESSION.prx_start_with_version('lg'); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 78
ORA-06512: at "A.PGGIS_SESSION", line 5
ORA-06512: at line 1



9.2.0$sys@ora9.oracle.com >create or replace context my_ctx using PGGIS_SESSION;

Context created.

9.2.0$sys@ora9.oracle.com >exec PGGIS_SESSION.prx_start_with_version('lg');

PL/SQL procedure successfully completed.

9.2.0$sys@ora9.oracle.com >select sys_context('my_ctx','name') from dual;

SYS_CONTEXT('MY_CTX','NAME')
------------------------------------------------------------------------------------------------------------------------
lg

9.2.0$sys@ora9.oracle.com >

wqliu

最初由 l2g32003 发布
[B]我写了一个和你类似的

9.2.0$sys@ora9.oracle.com >create user a identified by a;

User created.


9.2.0$sys@ora9.oracle.com >grant create session,create any procedure,create any context  to a;

Grant succeeded.

9.2.0$sys@ora9.oracle.com >connect a
Enter password:
Connected.

9.2.0$sys@ora9.oracle.com >create or replace package PGGIS_SESSION as
  2  procedure prx_start_with_version(name in varchar2);
  3  end;
  4  /

Package created.

9.2.0$sys@ora9.oracle.com >create or replace package body PGGIS_SESSION as
  2  procedure prx_start_with_version(name in varchar2)
  3  is
  4  begin
  5   DBMS_SESSION.SET_CONTEXT('my_ctx','name',name);
  6  end;
  7  end;
  8  /

Package body created.

9.2.0$sys@ora9.oracle.com >exec PGGIS_SESSION.prx_start_with_version('lg');
BEGIN PGGIS_SESSION.prx_start_with_version('lg'); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 78
ORA-06512: at "A.PGGIS_SESSION", line 5
ORA-06512: at line 1



9.2.0$sys@ora9.oracle.com >create or replace context my_ctx using PGGIS_SESSION;

Context created.

9.2.0$sys@ora9.oracle.com >exec PGGIS_SESSION.prx_start_with_version('lg');

PL/SQL procedure successfully completed.

9.2.0$sys@ora9.oracle.com >select sys_context('my_ctx','name') from dual;

SYS_CONTEXT('MY_CTX','NAME')
------------------------------------------------------------------------------------------------------------------------
lg

9.2.0$sys@ora9.oracle.com > [/B]

真的非常感谢您的热心帮助!!
我试过也可以了....非常感谢!!!