The wallet, the password and the odd requirement

An otn question this week had the requirement to allow a client account to be able to run "sqlplus /" from a client machine and this would connect to a remote database server with no credentials being passed.

This can easily be done using ops$ accounts and setting remote_os_authent to true - however this is notoriously insecure (i.e. i could easily connect my own machine to the network, add a local user of the ops$localuser name to match the db and the database would let me login with no password)

In there case however they were already using a 'secure external password store' (a wallet) and they wanted to be able to use "sqlplus /" using this.

However they were having trouble as they seemed to be forced to always specify the tnsnames alias

i.e. sqlplus /@DB worked but sqlplus / didn't

I initially said 'just set TWO_TASK' thinking this would resolve it as it forces normal connections to go out over sqlnet to the tns alias mentioned in TWO_TASK. This apparently didn't work - i didn't believe them so i went ahead and did a test myself

So some pre-reqs - i have a dummy db called 'DB' and i add a user called demosd to it

SQL> create user demosd identified by demosd;

User created.

SQL> grant create session to demosd;

Grant succeeded.

SQL>

I then create a wallet to store these credentials

mkstore -wrl /home/oracle -create
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:

(choosing welcome1 as the password at the prompts)

I can then see it created some wallet stuff

-rw-rw-rw- 1 oracle oinstall        0 Jan 27 14:55 ewallet.p12.lck
-rw------- 1 oracle oinstall       75 Jan 27 14:55 ewallet.p12
-rw-rw-rw- 1 oracle oinstall        0 Jan 27 14:55 cwallet.sso.lck
-rw------- 1 oracle oinstall      120 Jan 27 14:55 cwallet.sso

I then add the user/password/database combo i'm interested in

mkstore -wrl /home/oracle -createCredential DB demosd demosd
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
Create credential oracle.security.client.connect_string1

So that's all setup

Now a few extra lines in the sqlnet.ora to tell oracle where the wallet is and that he should always use it

cat sqlnet.ora

# sqlnet.ora Network Configuration File: /oracle/12.0.0.1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SQLNET.ALLOWED_LOGON_VERSION=10

WALLET_LOCATION =
   (SOURCE =
      (METHOD = FILE)
      (METHOD_DATA = (DIRECTORY = /home/oracle))
)

SQLNET.WALLET_OVERRIDE = TRUE

Now i just export TWO_TASK=DB and it works right.....?

Well no it doesn't and i was quite surprised..... (sqlplus /@DB works fine just not sqlplus /)

So how to solve this?

A simple alias won't work as you can't have a space in an alias

The fix i came up with (and to be honest i don't like it and you probably shouldn't use it) is to do this:

Create a function called sqlplus in the .profile and 'export' it

sqlplus() {
    if [[ $@ == "/" ]]; then
        command sqlplus /@DB
    fi
}

export -f sqlplus

Then after executing the .profile again we have a function called sqlplus which when passed an argument of / runs sqlplus /@DB....

total bodge i know - but it does work - i just wouldn't ever want to set up a system this way.....