No more cleartext-passwords in Scripts – Oracle Secure External Password Store (SEPS)
By Martin | March 8th, 2012 | Category: 10g, 11g, 11gR2, Oracle Database, Security | No Comments »Gone are the day when cleartext passwords had to be stored in scripts for Oracle database access. The solution to this requirement is “Oracle Secure External Password Store (SEPS)”. This article will give a short introduction and a practical example of the solution.
Key facts:
-
no Advanced Security Option (License) necessary
-
every unix-account, who has access to the wallet can use it to log on to the contained databases without a password! Therefore prevent other unix-accounts from accessing your wallet! (chmod, chown)
Installation
-
Oracle Client: Unix-Account, who wishes to connect to the database without providing a password needs to have an Oracle Client installed. Actually it can be an Instant Client, however the Instant Client does not have the Oracle Wallet Manager (owm).
-
TNS_ADMIN: If the installed Oracle-Client is also used by other unix-accounts, you have to set Environment-Variable TNS_ADMIN to point to a dedicated destination for sqlnet.ora
echo "export TNS_ADMIN=/home/techuser/oracle/network/admin" >> ~/.bash_profile |
-
sqlnet.ora in $TNS_ADMIN: In this file, the path to your personal wallet is defined:
WALLET_LOCATION = ( SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /home/techuser/oracle/network/wallet) ) ) SQLNET.WALLET_OVERRIDE = TRUE SSL_CLIENT_AUTHENTICATION = FALSE |
Be aware that sqlnet.ora has very sensitive formatting! Blank at the beginning of the line means that previous line is continued. If the first character of a line is not a blank, then it is supposed to be new directive. Therefore note the identation of wallet_location parameter.
-
tnsnames.ora in $TNS_ADMIN: This file contains TNS Aliases, which are used to connect to database. If the unix-account needs to connect to one database but with 2 different database accounts, then it needs to use 2 distinct tns aliases!
-
Creation of Wallet: When the wallet is created, a wallet password is requested. This password is needed when viewing, adding, modifying and deleting wallet entries.
mkstore –wrl /home/techuser/oracle/network/wallet –create |
Management of Credentials in Wallet:
-
Adding Credentials to Wallet:
mkstore -wrl /home/techuser/oracle/network/wallet \ -createCredential |
-
Removal of Credentials from wallet:
mkstore -wrl /home/techuser/oracle/network/wallet \ -deleteCredential TNSAlias |
-
Modification of Credential in wallet
mkstore -wrl /home/techuser/oracle/network/wallet \ -modifyCredential N102 scott newpassword Enter password: Modify credential Modify 1 mkstore -wrl /home/techuser/oracle/network/wallet \ -modifyEntry oracle.security.client.password1 newpass Enter password: |
-
Viewing of Credentials in Wallet
mkstore -wrl /home/techuser/oracle/network/wallet -list Oracle Secret Store entries: oracle.security.client.connect_string1 oracle.security.client.password1 oracle.security.client.username mkstore -wrl /home/techuser/oracle/network/wallet \ -viewEntry oracle.security.client.connect_string1 Enter password: oracle.security.client.connect_string1 = N102 mkstore -wrl /home/techuser/oracle/network/wallet -viewEntry oracle.security.client.username1 Enter password: oracle.security.client.username1 = scott mkstore -wrl /home/techuser/oracle/network/wallet -viewEntry oracle.security.client.password1 Enter password: oracle.security.client.password1 = tiger |
-
Changing Wallet Password
orapki wallet change_pwd -wallet /home/techuser/oracle/network/wallet \ -oldpwd welcome1 -newpwd welcome2 |
Example:
DBs: TESTDB1 (11.2.0.3) TESTDB2 (11.2.0.3) Techuser: techuser1 - uses RDBMS Server Home als Client techuser2 - uses Oracle Instant Client Requirements: techuser1 should be able TO CONNECT TO ro_user@TESTDB2 AND rw_user@TESTDB2 techuser2 should be able TO CONNECT TO ro_user@TESTDB2 AND rw_user@TESTDB1 Preparations: AS oracle: . TESTDB2.env sqlplus "/as sysdba" SQL> GRANT CREATE SESSION TO ro_user IDENTIFIED BY ro_password; GRANT succeeded. SQL> GRANT CREATE SESSION TO rw_user IDENTIFIED BY rw_password; GRANT succeeded. . TESTDB1.env sqlplus "/as sysdba" SQL> GRANT CREATE SESSION TO rw_user IDENTIFIED BY rw_password; GRANT succeeded. AS root: useradd techuser1 useradd techuser2 su - techuser1 export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 export PATH=$ORACLE_HOME/bin:$PATH export TNS_ADMIN=/home/techuser1/oracle/network/admin mkdir -p oracle/network/admin oracle/network/wallet vi $TNS_ADMIN/sqlnet.ora: WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA= (DIRECTORY=/home/techuser1/oracle/network/wallet) ) ) SQLNET.WALLET_OVERRIDE=TRUE SSL_CLIENT_AUTHENTICATION=FALSE vi $TNS_ADMIN/tnsnames.ora: TESTDB2_ro = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oravm1.intra)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TESTDB2_SITE1) ) ) TESTDB2_rw = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oravm1.intra)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TESTDB2_SITE1) ) ) [techuser1@oravm1 admin]$ tnsping TESTDB2_ro TNS Ping Utility FOR Linux: Version 11.2.0.3.0 - Production ON 01-MAR-2012 11:13:34 Copyright (c) 1997, 2011, Oracle. ALL rights reserved. Used parameter files: /home/techuser1/oracle/network/admin/sqlnet.ora Used TNSNAMES adapter TO resolve the alias Attempting TO contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oravm1.intra) (PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TESTDB2_SITE1))) OK (0 msec) [techuser1@oravm1 admin]$ tnsping TESTDB2_rw TNS Ping Utility FOR Linux: Version 11.2.0.3.0 - Production ON 01-MAR-2012 11:13:36 Copyright (c) 1997, 2011, Oracle. ALL rights reserved. Used parameter files: /home/techuser1/oracle/network/admin/sqlnet.ora Used TNSNAMES adapter TO resolve the alias Attempting TO contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oravm1.intra) (PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TESTDB2_SITE1))) OK (10 msec) [techuser1@oravm1 admin]$ mkstore -wrl /home/techuser1/oracle/network/wallet -CREATE Oracle Secret Store Tool : Version 11.2.0.3.0 - Production Copyright (c) 2004, 2011, Oracle AND/OR its affiliates. ALL rights reserved. Enter password: Enter password again: mkstore -wrl /home/techuser1/oracle/network/wallet \ -createCredential TESTDB2_rw rw_user rw_password mkstore -wrl /home/techuser1/oracle/network/wallet \ -createCredential TESTDB2_ro ro_user ro_password [techuser1@oravm1 admin]$ mkstore -wrl /home/techuser1/oracle/network/wallet \ -createCredential TESTDB2_rw rw_user rw_password Oracle Secret Store Tool : Version 11.2.0.3.0 - Production Copyright (c) 2004, 2011, Oracle AND/OR its affiliates. ALL rights reserved. Enter wallet password: CREATE credential oracle.security.client.connect_string1 [techuser1@oravm1 admin]$ mkstore -wrl /home/techuser1/oracle/network/wallet \ -createCredential TESTDB2_ro ro_user ro_password Oracle Secret Store Tool : Version 11.2.0.3.0 - Production Copyright (c) 2004, 2011, Oracle AND/OR its affiliates. ALL rights reserved. Enter wallet password: CREATE credential oracle.security.client.connect_string2 [techuser1@oravm1 admin]$ sqlplus /@TESTDB2_rw SQL*Plus: Release 11.2.0.3.0 Production ON Thu Mar 1 11:19:40 2012 Copyright (c) 1982, 2011, Oracle. ALL rights reserved. Connected TO: Oracle DATABASE 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options SQL> SHOW USER USER IS "RW_USER" SQL> quit Disconnected FROM Oracle DATABASE 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options [techuser1@oravm1 admin]$ sqlplus /@TESTDB2_ro SQL*Plus: Release 11.2.0.3.0 Production ON Thu Mar 1 11:19:45 2012 Copyright (c) 1982, 2011, Oracle. ALL rights reserved. Connected TO: Oracle DATABASE 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options SQL> SHOW USER USER IS "RO_USER" [techuser1@oravm1 wallet]$ ls -la /home/techuser1/oracle/network/wallet/ total 24 drwxrwxr-x 2 techuser1 techuser1 4096 Mar 1 11:15 . drwxrwxr-x 4 techuser1 techuser1 4096 Mar 1 11:01 .. -rw------- 1 techuser1 techuser1 4341 Mar 1 11:17 cwallet.sso -rw------- 1 techuser1 techuser1 4264 Mar 1 11:17 ewallet.p12 [techuser1@oravm1 wallet]$ techuser2 WITH instant client: unzip instantclient-basic-linux.x64-11.2.0.3.0.zip unzip instantclient-sqlplus-linux.x64-11.2.0.3.0.zip export ORACLE_HOME=/home/techuser2/instantclient_11_2 export PATH=$ORACLE_HOME:$PATH export TNS_ADMIN=/home/techuser2/oracle/network/admin export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH [techuser2@oravm1 ~]$ mkdir -p /home/techuser2/oracle/network/admin [techuser2@oravm1 ~]$ mkdir -p /home/techuser2/oracle/network/wallet vi $TNS_ADMIN/sqlnet.ora WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA= (DIRECTORY=/home/techuser2/oracle/network/wallet) ) ) SQLNET.WALLET_OVERRIDE=TRUE SSL_CLIENT_AUTHENTICATION=FALSE vi $TNS_ADMIN/tnsnames.ora TESTDB2_ro = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oravm1.intra)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TESTDB2_SITE1) ) ) TESTDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oravm1.intra)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TESTDB1) ) ) Test interactive Password: [techuser2@oravm1 instantclient_11_2]$ sqlplus ro_user@TESTDB2_ro SQL*Plus: Release 11.2.0.3.0 Production ON Thu Mar 1 11:28:53 2012 Copyright (c) 1982, 2011, Oracle. ALL rights reserved. Enter password: Connected TO: Oracle DATABASE 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production WITH the Partitioning, OLAP, DATA Mining AND REAL Application Testing options SQL> quit Problem: no mkstore Tool IN Instantclient Workaround: CREATE wallet WITH oracle binaries owner AND copy afterwards [oracle@oravm1 ~]$ mkdir /tmp/wallet [oracle@oravm1 ~]$ /u01/app/oracle/product/11.2.0/dbhome_1/bin/mkstore \ -wrl /tmp/wallet -CREATE Oracle Secret Store Tool : Version 11.2.0.3.0 - Production Copyright (c) 2004, 2011, Oracle AND/OR its affiliates. ALL rights reserved. Enter password: Enter password again: [oracle@oravm1 ~]$ [oracle@oravm1 ~]$ [oracle@oravm1 ~]$ [oracle@oravm1 ~]$ [oracle@oravm1 ~]$ /u01/app/oracle/product/11.2.0/dbhome_1/bin/mkstore \ -wrl /tmp/wallet -createCredential TESTDB2_ro ro_user ro_password Oracle Secret Store Tool : Version 11.2.0.3.0 - Production Copyright (c) 2004, 2011, Oracle AND/OR its affiliates. ALL rights reserved. Enter wallet password: CREATE credential oracle.security.client.connect_string1 [oracle@oravm1 ~]$ /u01/app/oracle/product/11.2.0/dbhome_1/bin/mkstore \ -wrl /tmp/wallet -createCredential TESTDB1 rw_user rw_password Oracle Secret Store Tool : Version 11.2.0.3.0 - Production Copyright (c) 2004, 2011, Oracle AND/OR its affiliates. ALL rights reserved. Enter wallet password: CREATE credential oracle.security.client.connect_string2 [techuser2@oravm1 wallet]$ ls -al total 28 drwxr-xr-x 2 oracle oinstall 4096 Mar 1 11:32 . drwxrwxrwt 31 root root 4096 Mar 1 11:32 .. -rw------- 1 oracle oinstall 4333 Mar 1 11:33 cwallet.sso -rw------- 1 oracle oinstall 4256 Mar 1 11:33 ewallet.p12 [root@oravm1 techuser2]# chown -R techuser2:techuser2 /tmp/wallet [root@oravm1 techuser2]# ls -la /tmp/wallet/ total 28 drwxr-xr-x 2 techuser2 techuser2 4096 Mar 1 11:32 . drwxrwxrwt 31 root root 4096 Mar 1 11:35 .. -rw------- 1 techuser2 techuser2 4333 Mar 1 11:33 cwallet.sso -rw------- 1 techuser2 techuser2 4256 Mar 1 11:33 ewallet.p12 [techuser2@oravm1 wallet]$ pwd /home/techuser2/oracle/network/wallet [techuser2@oravm1 wallet]$ mv /tmp/wallet/* . [techuser2@oravm1 wallet]$ ls -la total 24 drwxrwxr-x 2 techuser2 techuser2 4096 Mar 1 11:36 . drwxrwxr-x 4 techuser2 techuser2 4096 Mar 1 11:25 .. -rw------- 1 techuser2 techuser2 4333 Mar 1 11:33 cwallet.sso -rw------- 1 techuser2 techuser2 4256 Mar 1 11:33 ewallet.p12 [techuser2@oravm1 wallet]$ sqlplus /@TESTDB2_ro SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 1 11:36:58 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show user USER is "RO_USER" SQL> quit [techuser2@oravm1 wallet]$ sqlplus /@TESTDB1 SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 1 11:38:06 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show user USER is "RW_USER" |
Referenz:
-
Using The Secure External Password Store [ID 340559.1]
-
How To Change The Wallet Password For A Secure External Password Store? [ID 557382.1]