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})))
)

score 1 · Answer 1 · answered Apr 02 '20 at 18:45

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.

Thanks for the insightful answer Ed. Doesn't sound like I will be able to solve this problem with a TNS connection string then. Can you think of another way to setup my client side connection so that I can enable failover between DBs, while also being able to supply different credentials for each DB? — mc-lean, Apr 03 '20 at 12:36

Jean de Lavarene · Answer 2 · 2020-04-03T17:47:20.733

0

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.

edited Apr 03 '20 at 17:47

answered Apr 03 '20 at 07:54

Jean de Lavarene

3,461
1
20
28

Is there a way to specify multiple addresses in a failover configuration using that style of connection string? See what our current connection string looks like in my original post. – mc-lean Apr 03 '20 at 12:34
@Jean de Lavarene - as far as that goes, you can specify it on a sqlplus connection request -- 'sqlplus scott/tiger@orcl' - but is still just going along for the ride, to be used _after_ the request has been received by and processed by the listener. – EdStevens Apr 03 '20 at 13:26
I've modified my answer to take into account your comments. – Jean de Lavarene Apr 03 '20 at 17:47
Thanks for the input, however I don't think this is a solution to the problem I outlined. I need to be able to have failover between database AND be able to specify different credentials for each DB. – mc-lean Apr 08 '20 at 17:38
Sorry I misunderstood your question. I didn't realize you wanted to set up different credentials between primary and secondary. This isn't possible. The assumption is that both contain the same database model, metadata, schemas and users. – Jean de Lavarene Apr 09 '20 at 08:13
Thanks for clarifying @JeandeLavarene – mc-lean Apr 21 '20 at 15:56

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

2 Answers2