Can a non sysdba compile dbms_session?

Question

I am trying to use sys.dbms_session.set_context in one of my stored procedures, but I am getting the following error:

ORA-01031: insufficient privileges

My issue is that I cannot use it in my stored procedure, however...I am getting the same error (Insufficient privileges) when I execute it connected as a user that has execute privileges (and sysdba privileges). Here is the code:

PROCEDURE set_my_env (p_attribute IN VARCHAR2,p_value IN VARCHAR2)
  IS
   l_namespace   VARCHAR2 (30) := 'MY_ENV';
   l_attribute   VARCHAR2 (30) := NULL;
   l_value       VARCHAR2 (4000) := NULL;
  BEGIN
   l_attribute := p_attribute;
   l_value := p_value;

    DBMS_OUTPUT.PUT_LINE('NAMESPACE: ' || l_namespace);
    DBMS_OUTPUT.PUT_LINE('Attribute: ' || l_attribute);
    DBMS_OUTPUT.PUT_LINE('value: ' || l_value);

   DBMS_SESSION.set_context (l_namespace, l_attribute, l_value);

   END set_my_env;

When I execute this code as the non-sysdba user (but it has execute privs), I get the insufficient privileges error.

Thanks in advance.

Answer 1

DBMS_SESSION is a built-in package, it is installed along with the database installation, is valid and ready to use. Why would you want to compile it (especially connected as some ordinary user)?

Anyway, back to your question: how come other users can't compile it? Because it belongs to someone else. SYS. Granting execute privilege means that you can use it, not compile it.

In order to be able to compile someone else's procedures (packages, whatever), you need to be granted alter any procedure system privilege. However, that doesn't include SYS-owned objects. In order to recompile those, you have to connect as sysdba.

---

If you want to make it (dbms_session) work, here's how: connected as SYS, grant required privileges to user (scott in my example):

SQL> show user
USER is "SYS"
SQL> grant create any context, drop any context, alter session, unlimited tablespace to scott;

Grant succeeded.

SQL> grant execute on dbms_session to scott;

Grant succeeded.

SQL>

Now, connect as scott; this still won't work:

SQL> connect scott/tiger
Connected.
SQL> exec dbms_session.set_context('my_context', 'my_parameter', 'Littlefoot');
BEGIN dbms_session.set_context('my_context', 'my_parameter', 'Littlefoot'); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 101
ORA-06512: at line 1

What you have to do is to create a package you'll use for your context business, and create context which uses that package. This is a simplified version, you'd use something smarter:

SQL> create or replace package pkg_context as
  2    procedure p_set;
  3  end;
  4  /

Package created.

SQL> create or replace package body pkg_context as
  2    procedure p_set as
  3      begin
  4        dbms_session.set_context('my_context', 'my_parameter', 'Littlefoot');
  5      end;
  6  end;
  7  /

Package body created.

SQL> create context my_context using pkg_context;

Context created.

SQL>

Now we're ready!

SQL> exec pkg_context.p_set;

PL/SQL procedure successfully completed.

SQL> select sys_context('my_context', 'my_parameter') from dual;

SYS_CONTEXT('MY_CONTEXT','MY_PARAMETER')
--------------------------------------------------------------------------------
Littlefoot

SQL>

Right; now it works as expected.