Hi

I have a java application that requires access to oracle database with an app_user instead of schema owner user and this is for security purposes.

- The schema owner user is the user that owns oracle objects that need to be accessed from java application.
- The app_user is the user that the application is connecting with database and having EXECUTE privilege on schema owner objects.

There are 2 approaches that can be used :

1. Synonyms Approach : create synonyms owned by app_user that are pointing to schema owner objects.

2. CURRENT_SCHEMA approach: this approach consists of setting the current_schema ( in app_user sessions ) to the schema owner

e.g : in app_user oracle sessions ; run following SQL :
alter session set current_schema= [schema_owner_user];

Knowing that i'm using a java application using JDBC for database connection, and my application is using OracleDataSource to manage connections with database app_user schema.

and my aim is to set current_schema of those ( OracleDataSource sessions with database app_user schema) with the schema_owner .

and the idea is to setup the current_schema (by running : alter session set current_schema= [schema_owner_user]) for physical connections before being added to the pool.

- this purpose of setup current_schema can be achieved by a dedicated java method in JDBC ?
knowing that i have the option to use JDBC's ( oracle-ojdbc6-11.2.0.3.0.jar / oracle-ojdbc7-12.1.0.2.jar) on my application .

- does oracle.jdbc.driver.OracleConnection.setSchema Java method , can setup current_schema for physical connections of Oracle Datasource before being added to the pool ?

- There is possibility of running SQL statement when physical connection is created and before being added to the pool ?
- Does oracle.jdbc.pool.OracleDataSource can be setup with property "initSQL" ?
- does oracle.jdbc.driver.OracleConnection.setSchema method is going over the network to oracle Database
for each physical or logical connection ?

Thanks
Oussema

grant create session to app_user identified by app_user;
create user schema_owner identified by schema_owner;

create or replace trigger app_user.logon_t 
after logon on app_user.schema  
begin
  execute immediate 'alter session set current_schema = schema_owner';
end;
/

conn app_user/app_user

select sys_context('USERENV', 'CURRENT_SCHEMA') from dual;

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')  
SCHEMA_OWNER