Skip to content
Advertisements

How to Configure Clients to Use External Password Stores

This is another security method, you can able to connect the particular user data from client side without supplying password using wallet.

“Password credentials for connecting to databases can now be stored in a client-side Oracle wallet, a secure software container used to store authentication and signing credentials.

This wallet usage can simplify large-scale deployments that rely on password credentials for connecting to databases. When this feature is configured, application code, batch jobs, and scripts no longer need embedded user names and passwords. Risk is reduced because such passwords are no longer exposed in the clear, and password management policies are more easily enforced without changing application code whenever user names or passwords change.”

Let See Example :

Source Host :
Step 1:

SQL> grant dba to scott;

Grant succeeded.

SQL> conn scott/tiger
Connected.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
TEST TABLE

SQL> select name from v$database;

NAME
---------
TESTDB

SQL> select host_name from v$instance;

HOST_NAME
----------------------------------------------------------------
netbackuptest

SQL>

Client Host :

Step 2:
Create a wallet on the client using following syntex

[oracle@vcdb01 ~]$ mkdir -p /u01/app/oracle/product/wallet
[oracle@vcdb01 ~]$ mkstore -wrl /u01/app/oracle/product/wallet/ -create
Oracle Secret Store Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

Enter password:

Enter password again:

Step 3:  Add source tns entries in client tnsnames.ora file

testdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =netbackuptest)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdb)
)
)

Step 4:  Create database connection credentials in the wallet by using the following syntax at the command line

[oracle@vcdb01 ~]$ mkstore -wrl /u01/app/oracle/product/wallet/ -createCredential testdb scott tiger
Oracle Secret Store Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:

Here :

testdb --> tns alias name

scott  --> user name

tiger   --> password

Step 5:

[oracle@vcdb01 ~]$ mkstore -wrl /u01/app/oracle/product/wallet/ -listCredential
Oracle Secret Store Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:

List credential (index: connect_string username)
1: testdb scott

Step 6:  Add the following entries in sqlnet.ora

WALLET_LOCATION=(SOURCE=(METHOD=FILE) (METHOD_DATA = (DIRECTORY =/u01/app/oracle/product/wallet)))

SQLNET.WALLET_OVERRIDE = TRUE

Note: For clients not using such authentication methods or wanting to override them for database authentication, a new parameter (SQLNET.WALLET_OVERRIDE) in sqlnet.ora can be set to TRUE. The default value forSQLNET.WALLET_OVERRIDE is FALSE, allowing standard use of authentication credentials as before.

Step 7:  connect scott using  without supplying password from client machine using wallet

[oracle@vcdb01 ~]$ sqlplus /@testdb

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 25 10:27:36 2012

Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$database;

NAME
---------
TESTDB

SQL> select host_name from v$instance;

HOST_NAME
----------------------------------------------------------------
netbackuptest

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
TEST TABLE

SQL>

Step 8: Modify credential

Suppose to be, If DBA changed the password of scott user, can i able to connect from client machine using same wallet credential stored? No, you need to modify using the command line.

[oracle@vcdb01 ~]$ sqlplus /@testdb

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 25 10:29:35 2012

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

ERROR:
ORA-01017: invalid username/password; logon denied

[oracle@vcdb01 ~]$ mkstore -wrl /u01/app/oracle/product/wallet/ -modifyCredential testdb scott test
Oracle Secret Store Tool : Version 11.2.0.1.0 - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:

Modify credential
Modify 1
[oracle@vcdb01 ~]$ sqlplus /@testdb

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 25 10:31:46 2012

Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

Ref :Oracle Document

Advertisements
2 Comments Post a comment
  1. S.A.
    Good job! Thanks for sharing.

    April 6, 2012
  2. bhanu #

    Hi I am getting error “ORA-12154: TNS:could not resolve the connect identifier specified” .

    when I tried to login

    February 23, 2017

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: