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

How to create new wallet

Here I just showed how do we create  new wallet using orapki utility or OWM ( Oracle Wallet Manager).

Step 1 : Login as oracle user, set the oracle_home path and make new directory for wallet stored.

[oracle@netbackuptest ~]$ export ORACLE_SID=testdb

[oracle@netbackuptest ~]$cd $ORACLE_HOME
[oracle@netbackuptest dbhome_2]$ mkdir -p wallets

Step 2: Create Wallete using orapki

[oracle@netbackuptest dbhome_2]$ orapki wallet create -wallet $ORACLE_HOME/wallets
Oracle PKI Tool : Version 11.2.0.2.0 - Production
Copyright (c) 2004, 2010, Oracle and/or its affiliates. All rights reserved.

Enter password:
Enter password again:

Note :  PASSWORD_POLICY : Passwords must have minimum length of eight characters and contain alphabetic 
characters combined with numbers or special characters.

[oracle@netbackuptest dbhome_2]$ ls -l wallets/
total 4
-rw------- 1 oracle oinstall 3512 Mar 20 04:07 ewallet.p12

to display the contents of a wallet

[oracle@netbackuptest dbhome_2]$ orapki wallet display -wallet $ORACLE_HOME/wallets
Oracle PKI Tool : Version 11.2.0.2.0 - Production
Copyright (c) 2004, 2010, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
Requested Certificates:
User Certificates:
Trusted Certificates:
Subject: OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject: OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US
Subject: CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US

Using OWM :

Set Oracle home path and execute owm command

Open a existing wallet

Choose open and select a directory and put it a existing wallet password and then you can see the wallet information.

If you want to create a new wallet using OWM, you can choose new panel from left side panel and start to create wallet

Listener local os authentication

Listener local os authentication parameter control the start or stop listener services through remotely.

Let see example,

Step 1:  Source Host Listener status, here local os authentication is ON.

Step 2: set the new password for listener and save configuration

Step 3: look listener.ora file whether password file is stored or not.

Step 4: Go to remote server, remote Host :

Add following entries in tnsnames.ora file in target host

/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

Step 5: reload the source listener from remote host

Set the  password at remote host and then reload listener

Step 6: Go to source host, If I OFF the local os autentication, Can I able to reload/stop/start from remote host? No

go to listener.ora file and add the following entires

LSNRCTL> reload
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=netbackuptest.ace-ins.com)(PORT=1521)))
The command completed successfully

Step 7 : Go to  remote host, reload/stop/start source listener

Step 8 : How do i remove password protection from listener.ora

remove the password listener entries from listener.ora file, though If you don’t want to protect local os authentication also, you set local_os_authentication_listenername=off

and after go to listener utility , reload the listener

Check the status of security off

PRODUCT_USER_PROFILE table, How do we disable roles using by this table?

PRODUCT_USER_PROFILE table to provide product level security that supplements the user-level security provided by the SQL GRANT andREVOKE commands and user roles.

This table in System account. We can insert the product based data here which user roles, grant and revoke command you want to restrict.

This table does not apply for SYSDBA or SYSOPER privileges.

The PUP table applies only to the local database. If accessing objects on a remote database through a database link, the PUP table for the remote database does not apply. The remote database cannot extract the username and password from the database link in order to determine that user’s profile and privileges.

More Detail from here : Oracle Document

Here I’m going to create user, roles and create table for this user.

SQL> conn / as sysdba
Connected.
SQL> create user fsmith identified by fsmith;
User created.

SQL> grant create session to fsmith;

Grant succeeded.

SQL> create role role2;

Role created.

SQL> grant create table to role2;

Grant succeeded.

SQL> grant role2 to fsmith;

Grant succeeded.

SQL> conn fsmith/fsmith
Connected.
SQL> create table emp(empno number,empname varchar2(20));

Table created.

Now I insert into product_user_profile table for product level security like roles and after create table for 
this user, It has no privileges for create table because of the roles has disabled in product_user_profile table.

SQL> conn system/Admin123
Connected.

SQL> desc product_user_profile;
Name Null? Type
----------------------------------------- -------- ----------------------------
PRODUCT NOT NULL VARCHAR2(30)
USERID VARCHAR2(30)
ATTRIBUTE VARCHAR2(240)
SCOPE VARCHAR2(240)
NUMERIC_VALUE NUMBER(15,2)
CHAR_VALUE VARCHAR2(240)
DATE_VALUE DATE
LONG_VALUE LONG

SQL> insert into product_user_profile values('SQL*Plus','FSMITH','ROLES',NULL,NULL,'ROLE2',NULL,NULL);

1 row created.

SQL> commit;

Commit complete.

SQL> conn fsmith/fsmith
Connected.

SQL> create table role(role_name varchar2(20));
create table role(role_name varchar2(20))
*
ERROR at line 1:
ORA-01031: insufficient privileges

If O7_DICTIONARY_ACCESSIBILITY is set to TRUE, What happened?

“If you’re granted select any table to a user when O7_DICTIONARY_ACCESSIBILITY  is set to TRUE, the particular user can able to see the data dictionary tables.”

O7_DICTIONARY_ACCESSIBILITY controls restrictions on SYSTEM privileges. If the parameter is set to true, access to objects in the SYS schema is allowed (Oracle7 behavior). The default setting of false ensures that system privileges that allow access to objects in “any schema” do not allow access to objects in the SYS schema.

For example, if O7_DICTIONARY_ACCESSIBILITY is set to false, then the SELECT ANY TABLE privilege allows access to views or tables in any schema except the SYS schema (data dictionary tables cannot be accessed). The system privilege EXECUTE ANY PROCEDURE allows access on the procedures in any schema except the SYS schema.

If this parameter is set to false and you need to access objects in the SYS schema, then you must be granted explicit object privileges. The following roles, which can be granted to the database administrator, also allow access to dictionary objects:

• SELECT_CATALOG_ROLE
• EXECUTE_CATALOG_ROLE
• DELETE_CATALOG_ROLE

Example :

SQL> grant select any table to scott;

Grant succeeded.

SQL> show parameter O7_DICTIONARY_ACCESSIBILITY;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE

SQL> conn scott/tiger

Connected.

SQL> select * from dba_users;

select * from dba_users               *

ERROR at line 1: ORA-00942: table or view does not exist

SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=TRUE scope=spfile;

System altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size                  2226912 bytes

Variable Size             687867168 bytes

Database Buffers          905969664 bytes

Redo Buffers                7348224 bytes

Database mounted. Database opened.

SQL> show parameter O7_DICTIONARY_ACCESSIBILITY;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

O7_DICTIONARY_ACCESSIBILITY          boolean     TRUE

SQL> conn scott/tiger
 Connected.

SQL> select count(username) from dba_users;

COUNT(USERNAME)

---------------

45

Ref : Oracle Document