2

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.

jonesy19
  • 139
  • 1
  • 16

1 Answers1

3

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.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • Thanks for the response, that does help...I was trying to compile it as a simpler way to determine if I can use it. With the knowledge you just gave me, I now know I need to execute it to see if I can use it...So this helps... I have updated my question based on your helpful comments above. Do you know how I would be able to use DBMS_SESSION.set_context in a stored procedure being run from the non-sysdba user? – jonesy19 May 26 '20 at 19:36
  • 1
    You're welcome. I just posted an example which shows how to make it work. Have a look, please. – Littlefoot May 26 '20 at 19:37
  • Thanks, I am looking at it as we speak and will update this thread once I figure it out. Again, I appreciate the time you took for the detailed response. – jonesy19 May 26 '20 at 19:40
  • 1
    No problem, take your time :) – Littlefoot May 26 '20 at 19:42
  • 1
    You are....THE MAN! Again, thank you for taking the time to give me that detailed explanation. With all of the googling I have done, I now see that I had the information I needed out there, but I didn't have the knowledge to piece it all together. Thank you so much for writing the descriptive answer above,as it took me under two hours to figure it out (I am new to oracle here). Thanks again. – jonesy19 May 26 '20 at 20:55