Need Clarification on configuration of Oracle UCP

Question

Requirement : Create a multi-tenant Application which should insert each tenants data into their respective PDBs based on the tenant id in the request. In other words each tenant or customer will have there own PDB in a CDB, all PDBs will have the same schema and based on then tenant Id in the request a datasource will be selected and the data would be inserted into that PDB.

Stack - spring boot 2.3.0.RELEASE, Oracle 18c, Connection pool - Oracle shared universal connection pool

UCP connection :

<ucp-properties>
    <connection-pool
        connection-pool-name="pool1"
        connection-factory-class-name="oracle.jdbc.pool.OracleDataSource"
        url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(HOST=localhost)(PORT=1521)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=orcl.accounts.intern)))"
        user="C##commonuser"
        password="commonuser"
        initial-pool-size="10"
        min-pool-size="5"
        max-pool-size="20"
        connection-repurpose-threshold="13"
        sql-for-validate-connection="select 1 from dual"
        shared="true"
    >
        <connection-property name="oracle.jdbc.ReadTimeout" value="2000"/>
        <connection-property name="oracle.net.OUTBOUND_CONNECT_TIMEOUT" value="2000"/>
 
        <data-source data-source-name="pdbcust1" service="pdbcust1.accounts.intern" user="cust1" password="password"/>
        <data-source data-source-name="pdbcust2" service="pdbcust2.accounts.intern" user="cust2" password="password"/>
    </connection-pool>
</ucp-properties>

Spring datasource config class :

@Bean
    public DataSource dataSource() throws SQLException {
        System.setProperty("oracle.ucp.jdbc.xmlConfigFile", "file:/" + dbConfigProperties.getUcpConfigFile());
        
        final AbstractRoutingDataSource dataSource = new MultitenantRoutingDataSource();

        targetDataSources = new ConcurrentHashMap<>();

        final PoolDataSource tenantDataSource1 = getTenantDataSource("pdbcust1", "cust1", "password");
        final PoolDataSource tenantDataSource2 = getTenantDataSource("pdbcust2", "cust2", "password");

        
        targetDataSources.put("pdbcust1", tenantDataSource1 );
        targetDataSources.put("pdbcust2", tenantDataSource2 );

        dataSource.setDefaultTargetDataSource(lTenantDataSource2);
    

        lDataSource.setTargetDataSources(lTargetDataSources);

        lDataSource.afterPropertiesSet();

        return lDataSource;
    }

    private static PoolDataSource getTenantDataSource(final String tenantId, String username, String password) {
        try {
            PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource(tenantId);
            
            Properties prop = new Properties();
//          prop.setProperty("user", username);
//          prop.setProperty("password", password);
            
            //pds.reconfigureDataSource(prop);
            
            return pds;

        } catch (final Exception e) {
            e.printStackTrace();
        }
        
        return null;
    }

Above configuration does not work and throws the following error when I fire a request with a tenant id :

java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:550) ~[ojdbc8-19.3.0.0.jar:19.3.0.

However if I uncomment the following lines in the above class and remove the username and password from the UCP file, it works:

prop.setProperty("user", username);
prop.setProperty("password", password);
pds.reconfigureDataSource(prop);

So my questions are :

Why does this happen?
The UCP config xmls xsd has a user and password field, how do we use it?
This page describes share pooling https://docs.oracle.com/middleware/12213/wls/JDBCA/shared_pooling_ds.htm#JDBCA-GUID-4B7DA858-327E-4CEA-A68C-376792D4A466 This has line : "This common user must exist in all PDBs that are connected to the sharing data sources" What does it mean?

score 0 · Answer 1 · answered Jun 29 '20 at 09:01

In order to use ucp shared pool feature, the database user must be same for all the datasources sharing a common pool of connection. So you should not use user and password under datasource element.

<data-source data-source-name="pdbcust1" service="pdbcust1.accounts.intern" />
<data-source data-source-name="pdbcust2" service="pdbcust2.accounts.intern"/>

If you have a requirement to use a different user for each pdb then shared pool is not an option. In that case you should define two different pools in the XML, one for each PDB, that means you should not set the shared=true in connection-pool element. Also, for non shared pools there is no need of using a common user at pool level, you can directly use pdb user, password and service name under pool element.

<ucp-properties>
    <connection-pool
        connection-pool-name="pool1"
        connection-factory-class-name="oracle.jdbc.pool.OracleDataSource"
        url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(HOST=localhost)(PORT=1521)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=cust1.accounts.intern)))"
        user="cust1"
        password="cust1password"
        initial-pool-size="10"
        min-pool-size="5"
        max-pool-size="20"
        sql-for-validate-connection="select 1 from dual"
    >
        <connection-property name="oracle.jdbc.ReadTimeout" value="2000"/>
        <connection-property name="oracle.net.OUTBOUND_CONNECT_TIMEOUT" value="2000"/>
        <data-source data-source-name="pdbcust1" />
    </connection-pool>
    
    <connection-pool
        connection-pool-name="pool2"
        connection-factory-class-name="oracle.jdbc.pool.OracleDataSource"
        url="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(HOST=localhost)(PORT=1521)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=pdbcust2.accounts.intern)))"
        user="cust2"
        password="cust2password"
        initial-pool-size="10"
        min-pool-size="5"
        max-pool-size="20"
        sql-for-validate-connection="select 1 from dual"
    >
        <connection-property name="oracle.jdbc.ReadTimeout" value="2000"/>
        <connection-property name="oracle.net.OUTBOUND_CONNECT_TIMEOUT" value="2000"/>
        
        <data-source data-source-name="pdbcust2" />
    </connection-pool>
</ucp-properties>

Thanks for the reply. Do You mean I have to add C##commonuser to all the PDBs? btw I can connect to the pdb database using C##commonuser probably because of some container=ALL privilege which I thought would be sufficient. Also Whats the behavior when I set different PDB users programmatically as I have mentioned in the question? — humbleCoder, Jun 29 '20 at 14:43
A common user can access all PDBs, so that is not a problem. Shared pools work on the principle of re-usability where the connections to one PDB can be altered and reused for other PDB. When you try to set different users for each data-source on the shared pool which you should not do, then one of them will take effect and other one will be ignored. If you have a hard requirement to use a different user for different PDB then shared pool is not an option. — Saurabh Verma, Jun 30 '20 at 11:04
Thanks for the reply. Question : Common user should just have access to the PDBs or we have to create the same common user in all PDBs? — humbleCoder, Jun 30 '20 at 15:27
Common user has access to all PDBs but you need to grant few additional privileges to common user. 1. Common user should have the privileges to create session, alter session and set container. 2. Common user should have execute permission on 'dbms_service_prvt' package. 3. Any specific roles or password for common user should also be specified in the UCP XML config file. — Saurabh Verma, Jul 01 '20 at 17:30

Need Clarification on configuration of Oracle UCP

1 Answers1