Oracle connections via Secure External Password Store don't appear to work properly.

JeffCote00

I'm a seasoned DBA working with a large number of developers. Developers do not have elevated privilege access to our QA or production environments. I'm wanting to set up a client-side wallet so this tool can access those environments without requiring login/password information. I've setup sqlnet.ora and ldap.ora (we don't use tnsnames.ora) and can connect via the wallet entry using SQL*Plus on my local machine without issue. However, your product (Schema Compare) fires off an ORA-01017 (invalid login/password). Yet when I use normal credentialed login procedures via Schema Compare, the test works just fine. I don't know where the disconnect is located, but any feedback is very welcome.

Alex B

Hi @JeffCote00,

As far as I know our tools are not designed to work in that manner.  Are you using the "Manual Connection" tab to specify something that is included in one of those files?  And how are the files connecting to each other to present something that you would enter in the Schema Compare for Oracle UI to connect?  I'd like to try and reproduce what you're trying to do here to see if it should be possible the way the tool works.

Kind regards,
Alex

nikjohn1538

Hi @JeffCote00
Here you will get an answer

Secure External Password Store

It is often necessary to make connections to the database from shell scripts held on the filesystem. This can be a major security issue if these scripts contain the database connection details. One solution is to use OS Authentication, but Oracle 10g Release 2 gives us the option of using a secure external password store where the Oracle login credentials are stored in a client-side Oracle wallet. This allows scripts to contain connections using the "/@db_alias" syntax

.Configure Secure External Password Store

First, decide on the location of the Oracle wallet and your local TNS configuration. In this example, I will use an OS user called "myuser", so my directories will we as follows.

export TNS_ADMIN=/home/myuser/tns</code>mkdir -p /home/myuser/wallet</pre><pre>mkdir -p /home/myuser/tns</pre><br>Use the TNS_ADMIN environment variable to point to your local TNS configuration.<br><pre class="CodeBlock"><code>

Add the following entries into the client "/home/myuser/tns/sqlnet.ora" file, with your preferred wallet location.

WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /home/myuser/wallet)
     )
   )

SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0

The SQLNET.WALLET_OVERRIDE entry allows this method to override any existing OS authentication configuration.

Create an Oracle wallet in the previously specified location using the mkstore utility with the -create option. The wallet is password protected but is defined with the "Auto Login" property enabled so connection attempts by the user who created the wallet do not require a password.

$ orapki wallet create -wallet "/home/myuser/wallet" -pwd "MyPassword1" -auto_login_local</code>$ mkstore -wrl "/home/myuser/wallet" -create</pre><pre>Enter password:

Enter password again:


$</pre>Wallets can be copied to different machines, which can represent a security risk. In 11g Release 2, you can prevent the auto login functionality of the wallet from working if it is copied to another machine by creating a local wallet using the "orapki" command, instead of the "mkstore" command.<br><pre class="CodeBlock"><code>

The wallet is created with only read/write permissions for the current user, so it can't be read by any other user.

Once the wallet is created, it can be modified using the "mkstore" command described below.

Add the password credentials to the wallet using the -createCredential option.

$ sqlplus /@db10g</code>$ mkstore -wrl "/home/myuser/wallet" -createCredential db10g scott tiger</pre><pre>Enter password:

Create credential oracle.security.client.connect_string1

$</pre><span>With the wallet created and the password credentials in place, connect to the database without specifying the username and password, as shown below.<br></span><pre class="CodeBlock"><code>

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 19 08:15:09 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

SQL> show user
USER is "SCOTT"
SQL>

Now connect to the "test" user as shown below.

$ sqlplus /@db10g_test

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 19 10:17:47 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

SQL> show user
USER is "TEST"
SQL>

Now you are cleared right!
Please go through this full information

Thanks,
Nikhil John
Senior Oracle SCM Database System Engineer