We are building a system where:- Application users connect using different DB users
- They call DBMS_SCHEDULER.CREATE_JOB to create jobs
- Jobs are always expected to execute in the application user(APPUSER) schema
- We call external C++ functions via LIBRARY objects
- We use VPD policies and also log changes based on SESSION_USER
However, we observe that jobs run with SESSION_USER = job_creator, which is not always the same as application user.
Question:How can we ensure that DBMS_SCHEDULER jobs always run with SESSION_USER = application user, regardless of who initiates the creation?Reproducible test case:Tested on Oracle 19.25 (Non-CDB installation) on RHEL 9.5.
- APPUSER: Application schema and owner of all used objects
- DBUSER: One of the application/database users (there are many such users with different DB names)
Steps to reproduce:
1. Execute as SYS:create user appuser identified by ***;
grant create session to appuser;
grant create library to appuser;
grant create procedure to appuser;
GRANT CREATE ANY TABLE TO appuser;
grant create sequence to appuser;
alter user appuser quota unlimited on USERS;
create user dbuser identified by ***;
grant create session to dbuser;
grant create job to dbuser;
grant create any job to dbuser;
2. Execute as appuser:CREATE OR REPLACE LIBRARY extproc_userinfo_lib AS '/app/bin/libgetuser.so';
drop function loguserinfo;
CREATE OR REPLACE FUNCTION appuser.LogUserInfo
RETURN BINARY_INTEGER
AS
LANGUAGE C
LIBRARY extproc_userinfo_lib
NAME "LogUserInfo"
WITH CONTEXT
PARAMETERS (
CONTEXT,
RETURN int
);
grant execute on appuser.LogUserInfo to public;
drop table appuser.applog;
create table appuser.applog (
id number generated always as identity primary key
,message varchar2(300)--,
,log_time timestamp default systimestamp
);
select * from appuser.applog order by log_time desc;
3. Login as OS user oracle to Linux(I use RHEL 9.5) and create a new file /app/bin/getuser.cpp with the following contents.
It insert both SESSION_USER and CURRENT_USER to table APPLOG:
#include <oci.h>
#include <cstring>
#include <cstdio>
typedef struct OCIExtProcContext OCIExtProcContext;
#define OCIEXTPROC_SUCCESS 0
extern "C" int LogUserInfo(OCIExtProcContext* ctx)
{
OCIEnv* envhp = nullptr;
OCIError* errhp = nullptr;
OCISvcCtx* svchp = nullptr;
OCIStmt* stmthp = nullptr;
const char* query = "insert into appuser.applog(message) "
"SELECT 'SESSION_USER:' || SYS_CONTEXT('USERENV','SESSION_USER') || "
"'; CURRENT_USER:' || SYS_CONTEXT('USERENV','CURRENT_USER') "
"FROM dual";
if (OCIExtProcGetEnv(ctx, &envhp, &svchp, &errhp) != OCIEXTPROC_SUCCESS)
return -1;
if (OCIHandleAlloc(envhp, (void**)&stmthp, OCI_HTYPE_STMT, 0, NULL) != OCI_SUCCESS)
return -2;
if (OCIStmtPrepare(stmthp, errhp, (const OraText*)query, (ub4)strlen(query), OCI_NTV_SYNTAX, OCI_DEFAULT) != OCI_SUCCESS)
return -3;
if (OCIStmtExecute(svchp, stmthp, errhp, 1, 0, NULL, NULL, OCI_DEFAULT) != OCI_SUCCESS)
{
char errbuf[512];
sb4 errcode = 0;
OCIErrorGet(errhp, 1, NULL, &errcode, (OraText*)errbuf, sizeof(errbuf), OCI_HTYPE_ERROR);
return errcode;
}
OCIHandleFree(stmthp, OCI_HTYPE_STMT);
return 0;
}
4. Compile in /app/bin/ with:g++ -fPIC -shared -o libgetuser.so getuser.cpp -I$ORACLE_HOME/rdbms/public
5. Ensure that execution of libraries is permitted by adding the following to $ORACLE_HOME/hs/admin/extproc.ora:SET EXTPROC_DLLS=ANY
6. Login to DB as DBUSER:begin
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'APPUSER.log_user_job',
job_type => 'PLSQL_BLOCK',
job_action => 'DECLARE res number; BEGIN res := APPUSER.LogUserInfo; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY; BYHOUR=1',
enabled => false
);
end;
/
begin
DBMS_SCHEDULER.run_job('APPUSER.log_user_job',false);
end;
/
7. Login to DB as APPUSER:set pages 32000 lines 200
col id for 999
col message for a60
select * from appuser.applog order by log_time desc;
Output:
ID MESSAGE LOG_TIME
---- ------------------------------------------------------------ --------------------------------------------------
1 SESSION_USER:DBUSER; CURRENT_USER:APPUSER 2026/01/06 11:02:18.857935
NOTE: Expected is "SESSION_USER:APPUSER; CURRENT_USER:APPUSER"
This applies even if you're running a regular PL/SQL block (without external C functions), as the docs say about SESSION_USER:
The name of the session user (the user who logged on). This may change during the duration of a database session as Real Application Security sessions are attached or detached. If a Real Application Security session is currently attached to the database session, returns user XS$NULL.So you're always going to get whoever is connected to the database.
If you absolutely need SESSION_USER to be APPUSER, there is a workaround: run it as a local external job.
This enables you to run scripts on the database server. So you could call a script that connects to the database as appuser to do what you want. As you're running an external program, you could change this to connect as appuser rather than running it as a library object.
There are several steps to getting external jobs working, Connor has a troubleshooting guide at
https://connor-mcdonald.com/2025/09/01/troubleshooting-external-scheduler-jobs/