Can I specify username and password for different addresses in oracle tns connection string?

Question

Background: Working with an Oracle 12cR2 database and in this particular solution we need maximum availability. We are using Active-Active replication with Golden Gate. Local users are setup for authentication. We setup our clients to be able to failover seamlessly and to do be able to authenticate we've manually sync'd up passwords for the same logical user across DB instances.

Problem Statement: Is there a way to have the kind of client side failure (as shown below), but be able to specify user credentials for primary vs secondary DBs?

Our credentials are currently via setter on the PoolDataSource and I have yet to find a TNS string property that we can set for username/password.

    PoolDataSource poolDataSource = (PoolDataSourceImpl) PoolDataSourceFactory.getPoolDataSource();
    poolDataSource.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
    poolDataSource.setURL(url);
    poolDataSource.setUser(username);
    poolDataSource.setPassword(password);
    ...

On the client side we using the ojdbc8, ucp and ons jar:

    <dependency>
      <groupId>com.oracle.database</groupId>
      <artifactId>ojdbc8</artifactId>
      <version>12.2.0.1</version>
    </dependency>
    <dependency>
      <groupId>com.oracle</groupId>
      <artifactId>ucp</artifactId>
      <version>12.1.2-0-0</version>
    </dependency>
    <dependency>
      <groupId>com.oracle.weblogic</groupId>
      <artifactId>ons</artifactId>
      <version>12.1.2-0-0</version>
    </dependency>

This is what our connection string looks like:

dbc:oracle:thin:@(
  DESCRIPTION_LIST=(LOAD_BALANCE=off)(FAILOVER=on)

  (DESCRIPTION=(CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
  (ADDRESS_LIST=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=${primaryHostname)(PORT=1521)))
  (CONNECT_DATA=(SERVICE_NAME=${primaryServiceName)))

  (DESCRIPTION=
  (ADDRESS_LIST=(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=${secondaryHostname })(PORT=1521)))
  (CONNECT_DATA=(SERVICE_NAME=${secondaryServiceName})))
)

Answer 1

With the Oracle JDBC driver, the JDBC URL can be configured to include the username and password:

jdbc:oracle:thin:<user>/<password>@<database>

Where "database" can be your TNS address.

To be more explicit your JDBC URL could be like this:

jdbc:oracle:thin:<user>/<password>@(DESCRIPTION_LIST=(LOAD_BALANCE=off)(FAILOVER=on).....)

And to what @EdStevens wrote, this pair of user/password is used by the client (JDBC-thin but this works in sqlplus as well) after the Net connection has been established, in other words, after your client is connected to the database foreground process.

Answer 2

tns (and I mean the entire Transparent Network Substrate, not just 'tnsnames.ora) is only concerned with delivering messages across the network, to an oracle database. It does not know or care about the user's database credentials. In fact, by the time credentials are presented to the database, TNS has already done its job and is out of the picture. TNS delivers the request to the listener. Depending on what is requested, the listener either (a) spawns a dedicated server process and tells the client what port to use to communicate directly with that server, or (b) finds an available dispatcher and tells the client what port to use to communicate directly with that dispatcher. Once that is done, the listener is out of the picture, and it is either the dedicated server or dispatcher that presents credentials to the database.