Tuesday, 8 August 2017

Environment variable expansion in sqlnet.ora

By and large there are not many articles on the environment variables expansion in sqlnet.ora.
May be the main reason is that in majority of cases there is no such possibility. I run a bunch of tests and for all those few cases there was no such feature enabled - simply all the entries with $ sign as leading character were treated literally as text.

The only situation when it works is if used with a WALLET_LOCATION definition. The WALLET_LOCATION definition is more complex than all the rest sqlnet.ora parameters and is built with the help of usual Oracle syntax for .ora files with parentheses. Such definition could look as follows:
WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY=/opt/oracle/wallets/mywallet) ) )
But what I would emphasize here is the possibility to make a part of this definition to be build dynamically. I've tested only the file method. The only part which apparently allows for the environment variables expansion is the DIRECTORY value. Putting a variable to other attributes (like let's say METHOD) does not seem to work.
Thus one may put into the DIRECTORY path any variable defined in the environment. For example:
## as You see LOGNAME is a predefined variable, ORACLE_HOME a well known variable, 
## while $WALLET_METHOD is simply a custom one
WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY=$ORACLE_HOME/$WALLET_METHOD/wallets/walletOf$LOGNAME) ) )
Further this looks as follows in strace trail:
## ORACLE_HOME is /opt/oracle/app/oracle/product/12.1.0/client_1
## WALLET_METHOD was set to FILE
## and LOGNAME is seen as rems
## errors are due to the fact I had no wallet configured at all
## wanting only to see if the expansion would take place 
[..]
stat("/opt/oracle/app/oracle/product/12.1.0/client_1/FILE/home/rems/admin/network/admin/walletOfrems/ewallet.p12", 0x7ffebccd3430) = -1 ENOENT (No such file or directory)
stat("/opt/oracle/app/oracle/product/12.1.0/client_1/FILE/home/rems/admin/network/admin/walletOfrems/cwallet.sso", 0x7ffebccd3430) = -1 ENOENT (No such file or directory)
write(1, "ERROR:\n", 7)                 = 7
write(1, "ORA-12578: TNS:wallet open faile"..., 34) = 34
[..]
With such ability we can create several wallets being chosen based on the environment context - thus with one binaries installation (and one sqlnet.ora file) we may service many users with their own wallets thus separating their responsibilities.
One may also differentiate by $ORACLE_SID, which is also evaluated.

No comments: