Unleash the Power of the User Home SQLNet Config

Introduction

A crucial functionality of databases is that they are accessible via a network. This also applies to Oracle databases, where network access is controlled by the Oracle Net Service components. The two files sqlnet.ora and tnsnames.ora belong to the key configuration files and can be used on both the client machines and the database server. While you may be familiar with the essential configuration files, did you know that there’s a hidden path to unlock greater flexibility in Oracle Net Service?

  • tnsnames.ora is a configuration file that contains network service names mapped to connect descriptors for the local naming method, or net service names mapped to listener protocol addresses.
  • sqlnet.ora is the SQLNet configuration file. It resides on the client machines and the database server. Among other things, sqlnet.ora is used to enable the following configurations:
    • Specify the client domain to append to unqualified names
    • Prioritize naming methods
    • Enable logging and tracing features
    • Configure parameters for external naming
    • Configure Oracle Advanced Security

By default, both files are stored in the ORACLE_HOME/network/admin directory, or in ORACLE_BASE_HOME/network/admin for a read-only Oracle Home. The environment variable TNS_ADMIN can also be used to specify an alternative directory. This is especially useful on systems where you have several Oracle Home directories, but only want to work with one central Oracle Net Service configuration.

The order in which these files are searched is documented in Oracle® Database Database Net Services Reference 19c and in the Oracle Support Document 464410.1. All right, so that brings us to the end of this blog post, doesn’t it? Of course not, there is one small detail that is not in the documentation, or at least not any more. Oracle searches for sqlnet.ora and tnsnames.ora will also include other locations. So lets discover a less known path to greater flexibility and efficiency for Oracle Net Service.

Exploring the Alternative tnsnames.ora and sqlnet.ora Location

The easiest way to figure out which path or files are being read is to use strace when calling an Oracle tool like tnsping or sqlnet. The strace utility is very powerful and provides us with all kinds of trace information about a programme or process. In our case, however, we are only interested in seeing which files are being accessed. If strace is not available on your system, you can simply install it with yum or dnf.

sudo yum install -y strace

Below you find a simple example to call tnsping using strace. Since strace is quite chatty, we limit the output to open and access events and froward the output to a file. 

strace -e trace=open,access,stat,openat -o strace_tnsping.out \
tnsping TDB01

The output of tnsping is irrelevant in our case. Let’s take a look at the file generated by strace and search for tnsnames.ora.

grep -in tnsnames.ora strace_tnsping.out
274:access("/home/oracle/.tnsnames.ora", F_OK) = -1 ENOENT (No such file or directory)
275:access("/u01/app/oracle/network/admin/tnsnames.ora", F_OK) = 0
276:stat("/u01/app/oracle/network/admin/tnsnames.ora", {st_mode=S_IFREG|0644, st_size=484, ...}) = 0
277:openat(AT_FDCWD, "/u01/app/oracle/network/admin/tnsnames.ora", O_RDONLY) = 6

Eureka there is something. We can see, that tnsping was tring to access to files. One is tnsnames.ora, which is in my TNS_ADMIN directory, and the other is .tnsnames.ora, which is in my user home directory. The same applies to sqlnet.ora. Let’s check this with a call to sqlplus.

strace -e trace=open,access -o strace_sqlplus.out  \
sqlplus /@TEASPARX_pdbadmin <<EOFSQL
    SHOW USER
EOFSQL

Again, the effective output of sqlplus is irrelevant. Let’s take a look at the file created by strace and search for sqlnet.ora.

grep -in sqlnet.ora strace_sqlplus.out
61:access("/u01/app/oracle/network/admin/sqlnet.ora", F_OK) = 0
62:open("/u01/app/oracle/network/admin/sqlnet.ora", O_RDONLY) = 6
63:access("/home/oracle/.sqlnet.ora", F_OK) = -1 ENOENT (No such file or directory)
76:access("/u01/app/oracle/network/admin/sqlnet.ora", F_OK) = 0
77:open("/u01/app/oracle/network/admin/sqlnet.ora", O_RDONLY) = 8

We see, also here a hidden sqlnet.ora is accessed in the User Home.

Configuring the Alternative Location

The configuration is rather simple. You just have to create a hidden tnsnames.ora or sqlnet.ora file in the user home directory. Files are hidden in Linux if their name begins with a dot (.).

Lets create a Oracle net service entry for the database TDB01.

cat << EOF >$HOME/.tnsnames.ora
TDB01_system.trivadislabs.com=
   (DESCRIPTION=
     (ADDRESS=
       (PROTOCOL=TCP)
       (HOST=db19)
       (PORT=1521)
     )
     (CONNECT_DATA=
       (SERVER=DEDICATED)
       (SERVICE_NAME=TDB01.trivadislabs.com)
     )
     (UR=A)
   )
EOF

Then we test with tnsping whether the hidden tnsnames.ora is being read.

strace -e trace=open,access,stat,openat -o strace_tnsping.out \
tnsping TDB01_system
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 28-JUN-2023 09:41:39

Copyright (c) 1997, 2023, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=db19) (PORT=1521)) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=TDB01.trivadislabs.com)) (UR=A))
OK (10 msec)

We see tnsping finds the new Oracle Net Service entry and returns a correct response. What about the output of strace?

grep -in tnsnames.ora strace_tnsping.out
275:access("/home/oracle/.tnsnames.ora", F_OK) = 0
276:access("/u01/app/oracle/network/admin/tnsnames.ora", F_OK) = 0
277:stat("/home/oracle/.tnsnames.ora", {st_mode=S_IFREG|0644, st_size=245, ...}) = 0
278:openat(AT_FDCWD, "/home/oracle/.tnsnames.ora", O_RDONLY) = 6

As expected, the new Oracle Net Service entry is read from the hidden tnsnames.ora file in the user home directory. If we do another tnsping on the Net Service Name TDB01 as we did at the beginning, we see that both files are read. First the hidden file .tnsnames.ora and because TDB01 is not found, the central tnsnames.ora from the TNS_ADMIN directory.

strace -e trace=open,access,stat,openat -o strace_tnsping.out \
tnsping TDB01
grep -in tnsnames.ora strace_tnsping.out
269:access("/home/oracle/.tnsnames.ora", F_OK) = 0
270:access("/u01/app/oracle/network/admin/tnsnames.ora", F_OK) = 0
271:stat("/home/oracle/.tnsnames.ora", {st_mode=S_IFREG|0644, st_size=245, ...}) = 0
272:openat(AT_FDCWD, "/home/oracle/.tnsnames.ora", O_RDONLY) = 6
273:stat("/u01/app/oracle/network/admin/tnsnames.ora", {st_mode=S_IFREG|0644, st_size=484, ...}) = 0
274:openat(AT_FDCWD, "/u01/app/oracle/network/admin/tnsnames.ora", O_RDONLY) = 6

If we do another tnsping on the net service name TDB01, as we did at the beginning, we see that both files are read. First the hidden file .tnsnames.ora and because TDB01 is not found, the central tnsnames.ora from the $TNS_ADMIN directory.

The examples shown above were all with tnsnames.ora. But for sqlnet.ora the whole thing works analogously.

Use Cases and Advantages

With this undocumented functionality of Oracle Net Services, a wide range of use cases can be covered.

  • Personal Oracle Net service names or aliases
  • Test Adhoc SQL Net configurations. E.g. for troubleshooting, tracing or testing special functions, etc.
  • Overrule or extend central SQL Net configurations
  • Use of a local wallet for Secure External Password Store (SEPS)

Let’s take a look at the example of Secure External Password Store, where we use a wallet for authentication. First, we generate a password using pwgen and create an Oracle wallet using mkstore.

mkdir -p $HOME/.pwd $HOME/wallet
pwgen -s -1 15 >$HOME/.pwd/.wallet_password.txt
chmod 600 $HOME/.pwd/.wallet_password.txt
chmod 700 $HOME/.pwd
mkstore -wrl $HOME/wallet -create <<CREATE
$(cat $HOME/.pwd/.wallet_password.txt)
$(cat $HOME/.pwd/.wallet_password.txt)
CREATE
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2023, Oracle and/or its affiliates. All rights reserved.

Enter password:   
Enter password again:

For testing, we create a simple user SEPS_TEST in the DB TDB01. The password is generated using pwgen and temporary stored in a text file for easier deployment. The following SQL statements are packed in a here doc. Convenient way to execute SQL directly with a copy/paste. The prerequisite is, of course, that the corresponding Oracle environment is set.

pwgen -s -1 15 >$HOME/.pwd/.seps_test_password.txt
chmod 600 $HOME/.pwd/.seps_test_password.txt
${ORACLE_HOME}/bin/sqlplus -S -L /nolog <<EOFSQL 
    CONNECT / AS SYSDBA
    DROP USER seps_test;
    CREATE USER seps_test IDENTIFIED BY "$(cat $HOME/.pwd/.seps_test_password.txt)";
    GRANT create session TO seps_test;
    GRANT select_catalog_role TO seps_test;
EOFSQL

Next, we create a TNS entry TDB01_seps_test.trivadislabs.com in the hidden tnsnames.ora

cat << EOF >>$HOME/.tnsnames.ora
TDB01_seps_test.trivadislabs.com=
   (DESCRIPTION=
     (ADDRESS=
       (PROTOCOL=TCP)
       (HOST=db19)
       (PORT=1521)
     )
     (CONNECT_DATA=
       (SERVER=DEDICATED)
       (SERVICE_NAME=TDB01.trivadislabs.com)
     )
     (UR=A)
   )
EOF

Don’t forget to test if we can connect to the database using this new net service name.

tnsping TDB01_seps_test
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 28-JUN-2023 13:27:41

Copyright (c) 1997, 2023, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=db19) (PORT=1521)) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=TDB01.trivadislabs.com)) (UR=A))
OK (0 msec)

We store the credentials using mkstore in the previously created wallet.

mkstore -wrl $HOME/wallet -createCredential \
 TDB01_seps_test seps_test <<ADD
$(cat $HOME/.pwd/.seps_test_password.txt)
$(cat $HOME/.pwd/.seps_test_password.txt)
$(cat $HOME/.pwd/.wallet_password.txt)
ADD
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2023, Oracle and/or its affiliates. All rights reserved.

Your secret/Password is missing in the command line 
Enter your secret/Password:   
Re-enter your secret/Password:   
Enter wallet password:

For SEPS to work, we still need to create a WALLET entry in sqlnet.ora. We now do this in the hidden sqlnet.ora file.

cat >> $HOME/.sqlnet.ora <<CAT
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY="$HOME/wallet")))
SQLNET.WALLET_OVERRIDE=TRUE
CAT

With the help of Secure External Password Store, we can now log on directly to the database without entering the password. To do this, we use the defined TNS entry. SQLPlus then reads the corresponding information from the wallet. Lets to a simple test.

${ORACLE_HOME}/bin/sqlplus /@TDB01_seps_test <<EOFSQL 
    SHOW USER
EOFSQL
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 28 13:49:19 2023
Version 19.19.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0

SQL> USER is "SEPS_TEST"
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0

And now let’s control the whole stuff with strace and run grep to verify the output.

strace -e trace=open,access,stat,openat -o strace_sqlplus.out \
${ORACLE_HOME}/bin/sqlplus /@TDB01_seps_test <<EOFSQL 
    SHOW USER
EOFSQL
grep -in tnsnames.ora strace_sqlplus.out
69:access("/u01/app/oracle/network/admin/sqlnet.ora", F_OK) = 0
70:openat(AT_FDCWD, "/u01/app/oracle/network/admin/sqlnet.ora", O_RDONLY) = 6
71:access("/home/oracle/.sqlnet.ora", F_OK) = 0
72:openat(AT_FDCWD, "/home/oracle/.sqlnet.ora", O_RDONLY) = 6
96:access("/u01/app/oracle/network/admin/sqlnet.ora", F_OK) = 0
97:openat(AT_FDCWD, "/u01/app/oracle/network/admin/sqlnet.ora", O_RDONLY) = 8
402:access("/home/oracle/.tnsnames.ora", F_OK) = 0
403:access("/u01/app/oracle/network/admin/tnsnames.ora", F_OK) = 0
404:stat("/home/oracle/.tnsnames.ora", {st_mode=S_IFREG|0644, st_size=493, ...}) = 0
405:openat(AT_FDCWD, "/home/oracle/.tnsnames.ora", O_RDONLY) = 12
407:stat("/home/oracle/.tnsnames.ora", {st_mode=S_IFREG|0644, st_size=493, ...}) = 0
408:stat("/home/oracle/.tnsnames.ora", {st_mode=S_IFREG|0644, st_size=493, ...}) = 0

As expected, the files from TNS_ADMIN as well as the hidden files in the user home directory are being read.

Do not forget to save the password for the wallet and the test user in a password manager and remove the temporarily created files.

rm -rf $HOME/.pwd/.seps_test_password.txt $HOME/.pwd/.wallet_password.txt

Best Practices, Considerations and Common Issues

First of all, it is important to know that you can use these hidden Net Service configuration files to control the behaviour. It is a bit unfortunate when someone else creates such files without your knowledge. e.g. to direct you to another database or to switch off a configuration such as encryption. Therefore, the feature should be used wisely.

  • Avoid using it in shared user environment where multiple user login into the same OS user e.g. like oracle software owner
  • Douple check whats stored in your home folder
  • Be aware, that you can disable settings which are centrally defined in a TNS_ADMIN folder
  • If necessary you can also user INFILE to include additional tnsnames.ora or sqlnet.ora files

Conclusion

Especially for ad hoc tests and troubleshooting of Oracle SQL Net, the hidden configuration files in the User Home are extremely helpful. It is relatively easy to verify certain configurations with a local user. There are only restrictions where the Oracle server process also requires certain information, e.g. the server-side Kerberos and SSL configuration with corresponding reference to the keytab or wallet file. There is no way around working with TNS_ADMIN. For things like Secure External Password Store, however, this feature is worth its weight in gold. You can configure and use a local wallet relatively easily. Yet an other reason why you should work with personal and not shared accounts on the database server 😉.

Additional Resources

A few links and references to relevant documentation, blog posts and other external resources for further reading.