How to setup Oracle Wallet

An Oracle Wallet is nothing more than a protected logical container (a single file named ewallet.p12)
that is used for the secure external password store. Multiple wallets may be created on a machine; however,
each wallet should be contained in its own directory. Using a wallet for the secure external password store is
not the only use of an Oracle wallet. The wallet can also be used to store encrypted keys needed by the Oracle database in order to access
SSL sites as well as many of the more advanced security options in Oracle like Transparent Data Encryption (TDE) or PKI Credentials.
These advanced options are part of Oracle's Advanced Security Option (ASO) and are only available when using Oracle Enterprise Edition.

Note that creating an Oracle wallet for the secure external password store (and importing keys to access SSL sites) can be done using Oracle Standard Edition.
 It is only when using the advanced features like TDE or PKI credentials that require the Advanced Security Option and Enterprise Edition.


Create  wallet Location where you want to store the wallet creds

mkdir -p /u01/app/oracle/product/11.2.0.4.0/db_1/network/admin/wallets

Give the permission to wallet directory
chown oracle:dba /u01/app/oracle/product/11.2.0.4.0/db_1/network/admin/wallets

Now create a wallet and give the password
mkstore -wrl "/u01/app/oracle/product/11.2.0.4.0/db_1/network/admin/wallets" -create

wallets pass
passwd oracle123

Now give the RWX permision to wallets
chmod -Rf 777  /u01/app/oracle/product/11.2.0.4.0/db_1/network/admin/wallets

Now setup the sqlnet.ora tnsname.ora listener.ora file

===
[root@dba admin]# cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.40)(PORT = 1521))
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = oradb )
     (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4.0/db_1)
     (SID_NAME = oradb )
    )
   )

ADR_BASE_LISTENER = /u01/app/oracle

[root@dba admin]# cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4.0/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /u01/app/oracle/product/11.2.0.4.0/db_1/network/admin/wallets)
     )
   )

SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE

ADR_BASE = /u01/app/oracle
NAMES.DIRECTORY_PATH= (TNSNAMES)


[root@dba admin]# cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

FIN =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.40)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oradb)
    )
  )

ORADB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.40)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = oradb)
    )
  )


DBABHI=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.40)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME =ORADB)
    )
  )
[root@dba admin]#
===

NOTE Here we use fin db_alias for hr user and DBABHI db_alias use for dbabahi user
Now store the credential of user's in wallets.Make sure the user are unlock in database

mkstore -wrl <wallet_location> -createCredential <dbase_alias> <username> <password>

[oracle@dba ~]$ mkstore -wrl /u01/app/oracle/product/11.2.0.4.0/db_1/network/admin/wallets/ -createCredential dbabhi dbabhi dbabhi
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:          

Create credential oracle.security.client.connect_string2

[oracle@dba ~]$ mkstore -wrl /u01/app/oracle/product/11.2.0.4.0/db_1/network/admin/wallets/ -createCredential FIN HR hr
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:          

Create credential oracle.security.client.connect_string1

Now connet to the database

[oracle@dba ~]$ sqlplus /@dbabhi

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 5 16:13:17 2017

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show user
USER is "DBABHI"
SQL> exit
==

Set up a shell script to access the dbabhi user:

[oracle@dba ~]$ vi dbabhi.sh
#!/bin/bash

LogDirectory='/tmp/logs'
DataDirectory='/tmp/data'
sqlplus  <<EOF  > ${LogDirectory}/query_dbabhi.log
/@dbabhi
set feedback off
set heading off

spool ${DataDirectory}/query_output_dbabhi.txt


select * from test;

show user;

spool off

EOF
==

vi query_output_dbabhi.txt

select * from test;
NAME          MARKS
UDIT            100
SOUMYA     100
THAKUR     90

show user;

dbabhi


==
Administering the Wallet

Listing credentials stored in the wallet:

mkstore -wrl <wallet_location> -listCredential

mkstore -wrl /u01/app/oracle/product/11.2.0.4.0/db_1/network/admin/wallets -listCredential

[oracle@dba ~]$ mkstore -wrl /u01/app/oracle/product/11.2.0.4.0/db_1/network/admin/wallets -listCredential

Oracle Secret Store Tool : Version 11.2.0.4.0 - Production

Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:          
 
List credential (index: connect_string username)
2: dbabhi dbabhi
1: FIN HR
==

Modifying credentials stored in the wallet:

mkstore -wrl <wallet_location> -modifyCredential <dbase_alias> <username> <password>

Deleting credentials stored in the wallet:

mkstore -wrl <wallet_location> -deleteCredential <db_alias>
Deleting the whole wallet:

rm -rf <wallet_location>

==