Background

In the previous article, I covered the basics of how to remove database passwords (credentials) from Oracle monitoring or backup scripts and how to instead secure them using a “Secure External Password Store” (SEPS) and Oracle Wallet.

While this mechanism is far better than putting a plain text credential in a script file, one of the more advanced options, specifically tying the files to the local host with the “-auto_login_local” introduces bugs with Oracle 12cR1 software not present with other versions.

This article goes deeper into how to harden the approach, lock-down script access to the local server, and workaround Oracle Wallet limitations and bugs.

Issues with the “-auto_login_local” Option

Oracle suggests using the “-auto_login_local” option to secure an Oracle Wallet and only allow it to be used on the server on which it was created and by the user that created it. See MOS document: “How To Prevent The Secure Password Store Wallet From Being Moved to Another Host (Doc ID 1114599.1)”

This is supposed to protect from a bad actor obtaining a copy of the file, say from a backup, and being able to use it (and the credentials contained within it) from another machine. Unfortunately, there’s a number of issues and problems with this option:

1. There are ways to work around the protection it provides.
2. The option fundamentally doesn’t work with 12.1.0.2 (while it does with 11.2.0.4 and 12.1.0.1). This is clearly an Oracle bug.

By-passing the “-auto_login_local” Parameter”

The “-auto_login_local” parameter is supposed to protect the Wallet from being used on another server. However testing proves that this is really easy to workaround.

The basics of SEPS and Oracle Wallets I covered in my previous article. To enable the -auto_login_local option, we simply modify the existing Wallet file using the orapki utility:

$ orapki wallet create -wallet "/u01/app/oracle/wallet" -auto_login_local
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:

$ mkstore -wrl "/u01/app/oracle/wallet" -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)
1: ORCL scott

$

Testing on the local machine shows that the connection using the Wallet works as expected:

$ sqlplus /@ORCL

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jan 13 15:27:54 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select SYS_CONTEXT('userenv','IP_ADDRESS') IP_ADDRESS,
  2         SYS_CONTEXT('userenv','DB_NAME') DB_NAME,
  3         SYS_CONTEXT('userenv','CURRENT_USER') CURRENT_USER
  4  from dual;

IP_ADDRESS      DB_NAME      CURRENT_USER
--------------- ------------ ------------
192.168.1.123   ORCL         SCOTT

SQL>

It’s not overly simple for a bad actor already inside the network to obtain all of the information they’d need to access the database remotely, but it is possible. Say, for the sake of an example, that a bad actor obtained access to a backup of the OS. From that they could see the DBA scripts and how they connect, obtain the network files such as the sqlnet.ora and tnsnames.ora files, and obtain the Oracle Wallet files.

If a SEPS and Oracle Wallet was not being used, they’d presumably also be able to work out the database credentials as they’d either be hard-coded in the DBA script files or obfuscated in some other plain text file (not hard to reverse engineer).

Copying the cwallet.sso and ewallet.p12 (and maybe the tnsnames.ora) files to a secondary server simulates the actions of the “bad actor”.

But trying to make the same connection from the secondary server (which the “bad actor” controls) shows the “ORA-12578: TNS:wallet open failed” error:

$ sqlplus /@ORCL

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 13 15:38:50 2016

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

ERROR:
ORA-12578: TNS:wallet open failed


Enter user-name:

This is the expected error when the “-auto_login_local” option is used. However it’s simple to work-around.

MOS Note 1114599.1 suggests that the /etc/hosts file may cause this error. So the first thing to try is changing the name in the hosts file to that of the legitimate DB server:

# cp /etc/hosts /etc/hosts.backup
# cat /etc/hosts.backup | sed -e "s/HACKED_OS/DBSERVER/ig" > /etc/hosts
# su - oracle -c "sqlplus /@ORCL"

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 13 15:59:53 2016

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

ERROR:
ORA-12578: TNS:wallet open failed


Enter user-name:

Clearly that didn’t help the situation at all. Undoing that and instead trying to rename the compromised server (separately as root) gives a different error:

# cp /etc/hosts.backup /etc/hosts
# hostname
HACKED_OS

# hostname DBSERVER
# hostname
DBSERVER

# su - oracle -c "sqlplus /@ORCL"

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 13 15:53:02 2016

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

ERROR:
ORA-21561: OID generation failed


Enter user-name:

But if we do both:

# cp /etc/hosts /etc/hosts.backup
# cat /etc/hosts.backup | sed -e "s/HACKED_OS/DBSERVER/ig" > /etc/hosts
# hostname DBSERVER
# su - oracle -c "sqlplus /@ORCL"

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 13 16:05:53 2016

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

Last Successful login time: Wed Jan 13 2016 16:04:45 -07:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select SYS_CONTEXT('userenv','IP_ADDRESS') IP_ADDRESS,
  2         SYS_CONTEXT('userenv','DB_NAME') DB_NAME,
  3         SYS_CONTEXT('userenv','CURRENT_USER') CURRENT_USER
  4  from dual;

IP_ADDRESS      DB_NAME      CURRENT_USER
--------------- ------------ ------------
192.168.1.200   ORCL         SCOTT

SQL>

So if we change both the hostname via the hostname command (or in the /etc/sysconfig/network file) and update the /etc/hosts file, then the -auto_login_local security is by-passed and we can log into the database from a compromised machine using the credentials stored in the Oracle Wallet!

Important to note there that I’m connecting to a 12.1.0.2 database but using a Wallet file that was created using the 11.2.0.4 software.

ORA-12578 with Oracle Database 12.1.0.2

To make matters worse, with Oracle 12.1.0.2 the -auto_login_local option doesn’t even work at all.

Back on the database server (legitimate DBA activity – not simulating a “bad actor”), creating the Oracle Wallet file using 12.1.0.2 software seems to prevent connectivity locally:

$ orapki wallet create -wallet "/u01/app/oracle/wallet" -auto_login_local
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:

$ sqlplus /@ORCL

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 13 16:21:05 2016

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

ERROR:
ORA-12578: TNS:wallet open failed

This is unexpected behaviour and clearly shows and Oracle bug. Taking off the -auto_login_local option (by using -auto_login) shows that the Oracle Wallet does indeed work on this server:

$ orapki wallet create -wallet "/u01/app/oracle/wallet" -auto_login
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:

$ sqlplus /@ORCL

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 13 16:22:30 2016

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

Last Successful login time: Wed Jan 13 2016 16:20:38 -07:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select SYS_CONTEXT('userenv','IP_ADDRESS') IP_ADDRESS,
  2         SYS_CONTEXT('userenv','DB_NAME') DB_NAME,
  3         SYS_CONTEXT('userenv','CURRENT_USER') CURRENT_USER
  4  from dual;

IP_ADDRESS      DB_NAME      CURRENT_USER
--------------- ------------ ------------
192.168.1.123   ORCL         SCOTT

SQL>

Hence, there clearly is a bug that’s specific to the 12.1.0.2 software where as the ORA-12578 error is returned when it shouldn’t be. Repeating the same procedure using 12.1.0.1 or 11.2.0.4 software does not exhibit the same error.

And it’s important to understand that it doesn’t matter which version of the database the connection is to. The problem is specific only to which software was used to create the Wallet file. So creating the Wallet with 11.2.0.4 software just to use against a 12.1.0.2 database works without issue.

Harding Using Other Strategies

Due to the above mentioned issues, other strategies can be used to harden the connections and credential management for use by DBA scripts.

Using localhost or 127.0.0.1

The simplest way to prevent the Wallet files from being usable on another server is to change the OracleNET Service Name to an EZconnect string that uses localhost or 127.0.0.1. For example, on the DB server:

$ mkstore -wrl "/u01/app/oracle/wallet" -create -createCredential localhost:1521/ORCL scott
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter password:
Enter password again:
Your secret/Password is missing in the command line
Enter your secret/Password:
Re-enter your secret/Password:
Create credential oracle.security.client.connect_string1

$ sqlplus /@localhost:1521/ORCL

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 13 16:33:27 2016

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

Last Successful login time: Wed Jan 13 2016 16:31:50 -07:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show user
USER is "SCOTT"
SQL>

Now if we try using the Oracle Wallet files on a compromised server (with the /etc/hosts and /etc/sysconfig/network spoofing as described previously), the connection attempt routes through the localhost back to the compromised server and not to the database server. Hence a connection attempt gives:

[[email protected]_OS ~]$ sqlplus /@localhost:1521/ORCL

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 13 16:34:27 2016

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

ERROR:
ORA-12541: TNS:no listener

Thus, by using an EZconnect connection string and localhost instead of the actual server’s hostname, FQDN, or IP address, we’ve avoided the 12.1.0.2 bug and provided more thorough protection than the -auto_login_local option provides anyway.

And of course we could have used 127.0.0.1 instead of localhost – the results are the same.

Finally, remember that the connection string forms the primary key of the 3DES protected data in the Wallet file which can’t be modified without knowing the Wallet’s password.

Connecting Through a Dedicated Listener and “Valid Node Checking”

Another way to prevent the Oracle Wallet from being used to access the database from an unauthorized server (actually any server other than the DB server) is to have the scripts connect through a dedicated listener. The dedicated listener’s port can then be restricted using either a firewall or the listener’s “valid node checking” functionality.

For example, the dedicated listener could be configured with the following in the listener.ora file:

MONITORING_LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1599)))
  )
SID_LIST_MONITORING_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
      (SID_NAME = ORCL)
    )
  )
PASSWORDS_MONITORING_LISTENER= (F251EDED29514235)

Then for added (though possibly redundant due to the use of localhost) protection, the following entries could be adding to the server’s sqlnet.ora:

TCP.VALIDNODE_CHECKING = YES
TCP.INVITED_NODES = (localhost)

As a result, local connections using localhost:1599/ORCL will work, while connections from the compromised server, connections will receive:

ERROR:
ORA-12537: TNS:connection closed

Preventing Use by Another OS User

Another challenge is to prevent another OS user on the same server from using the Oracle Wallet to connect to the database.

Of course the wallet files should be well secured using OS directory and file security. It can further be obfuscated by making the wallet directory a hidden directory (starting with a period).

If the -auto_login_local option is used then other users on the same server will not be able to use the Oracle Wallet credentials and instead will get the “ORA-12578: TNS:wallet open failed” error. Hence, creating the Oracle Wallet using a version other than 12.1.0.2 (regardless of the database version) and enabling the -auto_login_local option is still the best solution.

Beyond OS directory and file restrictions and the -auto_login_local option, the only other method for restricting access from other OS users on the same server would be a database scoped logon trigger or secured application role.

Conclusions

Using an Oracle Secure External Password Store (SEPS) and Oracle Wallet files is the best way to handle database credentials and passwords in OS scripts. However, a number of significant problems exist:

1. The -auto_login_local parameter can be bypassed on a compromised server by changing the hostname (in /etc/hosts and /etc/hosts/network).
2. The -auto_login_local parameter doesn’t work at all when created with 12.1.0.2 software.

That being said, we can still harden our script’s database access by following some additional suggestions:

• Create the Oracle Wallet using 11.2.0.4 or 12.1.0.1 software even if connecting to 12.1.0.2 databases.
• If the Oracle Wallet files were created using 11.2.0.4 or 12.1.0.1, protect from usage by other users by using the -auto_login_local parameter.
• Prevent use from other servers by not using an OracleNET Service Name in Oracle Wallets and instead using an EZconnect connection string using either localhost or 127.0.0.1 (not the proper DB server’s hostname, FQDN, or IP address).
• Another strategy is to use a dedicated listener on a dedicated port with listener “valid node checking” to only permit connections from the local server.
• As a last resort prevent non-authorized IPs or OS Users from connecting using a logon trigger or secure application role within the DB.

Discover more about our expertise in Oracle.