SQL & PL/SQL

875004 wrote:
Hi
What is the scope of Package level global variable? Does each session have its own instance of this variable or is the variable shared across the sessions?
I noticed the package level global variable value initialized in one session, is accessible to other sessions.
Thanks,
ST

Global is global

local is local.

What do you not understand regarding above?

It's session specific. The global means it's accessible to other packages/procedures/functions than the one it's declared in.

You may be using connection pool, so one db session is shared by many clients.

The global one is called global context.

No, they are not. Package is instantiated in each session when it is called first time in that session.

SY.

I noticed the package level global variable value initialized in one session, is accessible to other sessions.

Sorry - but that is NOT possible so you could not have 'noticed' that.

What is the scope of Package level global variable? Does each session have its own instance of this variable or is the variable shared across the sessions?

https://docs.oracle.com/database/121/LNPLS/packages.htm#LNPLS99926

Package Instantiation and Initialization

When a session references a package item, Oracle Database instantiates the package for that session. Every session that references a package has its own instantiation of that package.
When Oracle Database instantiates a package, it initializes it. Initialization includes whichever of the following are applicable:

If you need values that are 'global' across sessions use a GLOBAL CONTEXT

https://docs.oracle.com/database/121/SQLRF/statements_5003.htm#SQLRF01202

ACCESSED GLOBALLY 
This clause indicates that any application context set in namespace is accessible throughout the entire instance. This setting lets multiple sessions share application attributes.

We have a package as below

CREATE OR REPLACE PACKAGE Core_AUD AS

    current_user  VARCHAR2(255) := USER;

    PROCEDURE set_user
       (username  IN  VARCHAR2,errorCode           out   number);

END Core_AUD;
/

CREATE OR REPLACE PACKAGE BODY Core_AUD AS

   PROCEDURE set_user
       (username  IN  VARCHAR2, errorCode           out   number)
   IS
   BEGIN
      current_user := username;
   END;

END Core_AUD;
/

Session1
We are setting Username as test1 using procedure call
Core_Aud.set_user('test1', rc)
Update Attempt set column = '1' where pk = 101

Session2
We are setting Username as test2 using below procedure call
Core_Aud.set_user('test2', rc)
Update Attempt set column = '5' where pk = 107

We have a update trigger on table "Attempt", where we are reading the value of "Core_Aud.current_user" and inserting this value into another table "Attempt_Audit", along with the modified column values of Attempt table

We intermittently noticed the records of Session1 Attempt are getting inserted into Attempt_Audit table with Username as "test2", and session2 attempt with username as "test1".

Thanks,

ST

Are you using a connection pool?

Because if you are you just shouldn't be using global variables like this.

Also why are you defaulting the variable to the current sessions user if you're going to overwrite that with a value supplied by the application code?

We intermittently noticed the records of Session1 Attempt are getting inserted into Attempt_Audit table with Username as "test2", and session2 attempt with username as "test1".

Well - that isn't happening due to any sharing of the package state across sessions.

current_user  VARCHAR2(255) := USER;

So if user 'test1' is the last one to compile that code what value do you think 'current_user' is going to have for ALL users if they don't change the value?

You likely have code that is NOT setting the package variable. So the value will be the default.

If you just need the current user then use USER in your code. There is no need for a package variable at all.

Better yet you should use SYS_CONTEXT to get environment variables:

https://docs.oracle.com/database/121/SQLRF/functions199.htm#SQLRF06117

Examples 
The following statement returns the name of the user who logged onto the database:
CONNECT OE
Enter password: password
SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') 
  FROM DUAL;
SYS_CONTEXT ('USERENV', 'SESSION_USER')
---------------------------------------
OE

Hi,

i see that you plan to make custom auditing.

Here's an concept similar to what i have used. Point is that every single time application code asks connection (from pool is assumed), it must call SetSessionContext procedure from package Compliance that is isolated to locked database user called secure. After that application code calls secure.Compliance.InsertAttempt() everywhere it is required in that single service call. In other words this audit process is done for every service call from application.

Notes!

Only application database user myapp gets grant to execute package. Names are not important here, this is just POC for you.

Edit: Word attempt means audit log in my semantics. I chose bad name but ideally it is single audit procedure. Better name would be InsertAudit,

create user secure identified by qYwTeRrEtWyQ
account lock 
default tablespace users 
quota unlimited on users
;
alter session set current_schema=secure
;
create or replace
context SecureContext using Compliance
;
create sequence id
;
create table attempts(
  attempts_id
      number
      not null
      primary key
  ,
  app_code
      varchar2( 30 )
      not null
  ,
  app_user_name
      varchar2( 30 )
      not null
  ,
  app_user_role
      varchar2( 30 )
      not null
  ,
  details
      clob
      not null
  ,
  dt
      date
      not null
)
;
create or replace 
package Compliance is
  -- Set application username and role and code into session scope context
  -- AppUserName as in IAM system
  -- AppUserRole as in IAM system
  -- AppCode as in IAM system
  procedure SetSessionContext( AppUserName varchar2, AppUserRole varchar2, AppCode varchar2 ); 
  -- Insert details for an attempt
  procedure InsertAttempt( Details clob ); 
end;
/
create or replace
package body Compliance is
  -- Context constants
  Ctx       constant varchar2( 30 ) := 'SecureContext';
  CtxUid    constant varchar2( 30 ) := 'AppUserName';
  CtxRole   constant varchar2( 30 ) := 'AppUserRole';
  CtxCode   constant varchar2( 30 ) := 'AppCode';
  -- Application username from context
  function AppUserName return varchar2; 
  -- Application user role from context
  function AppUserRole return varchar2; 
  -- Application code from context
  function AppCode return varchar2; 
  
  -- Set current application information into session context
  -- Application must call this procedure before any other database activity
  procedure SetSessionContext( AppUserName varchar2, AppUserRole varchar2, AppCode varchar2 )
  is
    -- Session identifier for debugging and tracing purposes
    procedure SetSessionIdentifier 
    is 
      IdentifierTemplate varchar2( 64 ) default '"user" : "{0}", "role" : "{1}", "app" : "{2}"';
    begin
      dbms_session.set_identifier( 
        replace( replace( replace( IdentifierTemplate
          ,'{0}', Compliance.AppUserName() )
          ,'{1}', Compliance.AppUserRole() )
          ,'{2}', Compliance.AppCode() )
      );
    end;
  begin
    dbms_session.set_context( Ctx, CtxUid, AppUserName );
    dbms_session.set_context( Ctx, CtxRole, AppUserRole );
    dbms_session.set_context( Ctx, CtxCode, AppCode );
    --
    SetSessionIdentifier; 
  end;
  --
  procedure InsertAttempt( Details clob )
  is
    A attempts%rowtype;
  begin
    A.attempts_id   := id.nextval;
    A.app_code      := Compliance.AppCode();
    A.app_user_name := Compliance.AppUserName();
    A.app_user_role := Compliance.AppUserRole();
    A.details       := Details;
    A.dt            := sysdate;
    insert into attempts values A; 
  end;
  --
  function AppUserName return varchar2 
  is
  begin
    return sys_context( Ctx, CtxUid );
  end;
  --
  function AppUserRole return varchar2 
  is
  begin
    return sys_context( Ctx, CtxRole );
  end;
  --
  function AppCode return varchar2 
  is
  begin
    return sys_context( Ctx, CtxCode );
  end;
end;
/
-- application session user
create user myapp identified by Guess
;
grant create session to myapp
;
grant execute on secure.compliance to myapp
;
alter package secure.compliance compile
;
-- test
--sqlplus /nolog
--connect myapp/Guess
exec secure.compliance.setsessioncontext( 'jarkko.turpeinen', 'user', 'MicroserviceX' );
exec secure.compliance.insertattempt( 'Details about' );
set lines 200
column attempts_id format a20
column app_code format a20
column app_user_name format a20
column app_user_role format a20
column details format a30
select * from secure.attempts
;
drop user myapp cascade
;
drop user secure cascade
;
drop context SecureContext
;
User SECURE created.
Session altered.
Context SECURECONTEXT created.
Sequence ID created.
Table ATTEMPTS created.
Package COMPLIANCE compiled
Package body COMPLIANCE compiled
User MYAPP created.
Grant succeeded.
Grant succeeded.
Package SECURE.COMPLIANCE altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
         ATTEMPTS_ID APP_CODE             APP_USER_NAME        APP_USER_ROLE        DETAILS                        DT                 
-------------------- -------------------- -------------------- -------------------- ------------------------------ --------------------
                   1 MicroserviceX        jarkko.turpeinen     user                 Details about 20-MAR-17           
User MYAPP dropped.
User SECURE dropped.
Context SECURECONTEXT dropped.

That can be integrated easily for FGA (handler module) nor FDA (values point in time) collaboration.

Package variables are scoped at session level.  The only way I can think you would see the same package variable would be if you were using session pooling (and the session pool mechanism hasn't reset the package state) or if you haven't called set_user manually.

Personally, I don't think you should be exposing global variables in your package spec, it's breaks good design paradigms.  If you are allowing callers to override the user, a better approach would be to make current_user an access function.   You can also add a function to check if set_user has been called, to allow you to check if someone's forgotten to call it.  Something like this (untested)....

create or replace package Core_AUD as

  function current_user return varchar2;

  procedure set_user(username in varchar2, errorCode out number);

  function SetUserCalled return boolean;

end Core_AUD;

/

create or replace package body Core_AUD AS

  gCurrent_User VARCHAR2(255);

  gSetUserCalled boolean := False;  -- Only set to True if manually changed

  function Current_user return varchar2 is

  begin

    if gCurrent_User is null then

      gCurrent_User := USER;

    end if;

    return gCurrent_User;

  end;

  procedure set_user(username in varchar2, errorCode out number) IS

  begin

    gCurrent_User := username;

    gSetUserCalled := True;

  end;

  function SetUserCalled return boolean is

  begin

    return gSetUserCalled;

  end;

end Core_AUD;

/

rp0428 wrote:
We intermittently noticed the records of Session1 Attempt are getting inserted into Attempt_Audit table with Username as "test2", and session2 attempt with username as "test1".
Well - that isn't happening due to any sharing of the package state across sessions.
current_user VARCHAR2(255) := USER;
So if user 'test1' is the last one to compile that code what value do you think 'current_user' is going to have for ALL users if they don't change the value?

I would expect current_user to show the name of the schema running the procedure since user is not resolved at compile time.

SQL> create package test_p as
  2     current_user  varchar2(255) := user;
  3     procedure set_user (username  in  varchar2,
  4                         errorCode out number);
  5     function get_user return varchar2;
  6  end;
  7  /
Package created.
SQL> create package body test_p as
  2  procedure set_user (username  in  varchar2,
  3                      errorcode out number) is
  4  begin
  5     current_user := username;
  6  end;
  7
  8  function get_user return varchar2 is
  9  begin
 10    return current_user;
 11  end;
 12  end;
 13  /
Package body created.
SQL> grant create session to a identified by a;
Grant succeeded.
SQL> grant execute on test_p to a;
Grant succeeded.
SQL> select test_p.get_user from dual;
GET_USER
--------------------------------------------------------------------------------
OPS$ORACLE
SQL> conn a/a
Connected.
SQL> select ops$oracle.test_p.get_user from dual;
GET_USER
--------------------------------------------------------------------------------
A

John