Setting Oracle DB v$session properties for Keycloak Quarkus jdbc connection #19277

hill-daniel · 2023-03-23T14:58:18Z

hill-daniel
Mar 23, 2023

Hello, I am unable to configure additional properties for an Oracle Database connection.
I.e. in Java, you can configure V$SESSION properties upon connecting to a Oracle DB using the JDBC Thin driver the following way:

Properties p = new java.util.Properties();
p.put("v$session.osuser", "ca_user");         
p.put("v$session.machine", "deployment_dev123");         
p.put("v$session.program", "token_service");

// get connection with properties...

Or configure a DataSource with Spring | Boot (w/ Hikari):

spring:
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    url: jdbc:oracle:thin:@localhost:1521:XE
    driver-class-name: oracle.jdbc.OracleDriver
    username: username
    password: password
    data-source-properties: |
      v$session.program: token_service
      v$session.osuser: ca_user
      v$session.machine: deployment_dev123

This sets fields of the V$SESSION table.

I've been trying to achieve the same thing with Keycloak and Quarkus, but to no avail.
I tried:

adding quarkus.properties to /opt/keycloak/conf with content: quarkus.datasource.jdbc.additional-jdbc-properties.connectionProperties="v$session.OSUSER=MY_USER"
using db-url-properties in keycloak config
adapting the db-url to contain the clientId like so: jdbc:oracle:thin:@localhostL1521:XE?clientId={OSUSER=myuser,PROGRAM=myprogram,MACHINE=mymachine} (results in keycloak not even starting)
setting system property with -Doracle.jdbc.v$session.osuser="ca_user" with JAVA_OPTS_APPEND env variable

Is this even possible? If so, how?

Answered by hill-daniel

Mar 23, 2023

nvm, solved it after experimenting with quarkus.properties. This did it:
quarkus.datasource.jdbc.additional-jdbc-properties.connectionProperties=v$session.osuser=ca_user;v$session.machine=deployment_dev123

View full answer

hill-daniel · 2023-03-23T16:52:58Z

8000

hill-daniel
Mar 23, 2023
Author

nvm, solved it after experimenting with quarkus.properties. This did it:
quarkus.datasource.jdbc.additional-jdbc-properties.connectionProperties=v$session.osuser=ca_user;v$session.machine=deployment_dev123

0 replies

Navigation Menu

Repository navigation

Setting Oracle DB v$session properties for Keycloak Quarkus jdbc connection #19277

Uh oh!

{{title}}

Uh oh!

Replies: 1 comment

Uh oh!

{{title}}

Uh oh!

Select a reply

Uh oh!

Setting Oracle DB v$session properties for Keycloak Quarkus jdbc connection #19277

Uh oh!

hill-daniel Mar 23, 2023

Replies: 1 comment

Uh oh!

hill-daniel Mar 23, 2023 Author

hill-daniel
Mar 23, 2023

hill-daniel
Mar 23, 2023
Author