Procedural SQL*Plus and Password Encryption

November 18th, 2011

One  small but bothersome  issue I’ve had for 20 years is  how to drive a program like SQL*Plus with a shell script to make it procedural.  One approach is to just run single commands to SQL*Plus causing a connection and exit for every SQL statement. Connecting and disconnecting is costly. Ideally, I just want to open up a  connection and send commands to the connection and get the response. Of course languages like java, perl, python all have Oracle connection APIs but what if I just want a simple shell script and don’t have access to perl or python with the Oracle APIs or access to java? Can’t I just do it in shell?  Can’t I just connect SQL*Plus to a named pipe and echo my commands into the pipe? Well yes but there is an annoying obstacle. After echoing the first command the pipe, which SQL*Plus dutifully executes, SQL*Plus then exits. I first ran into this problem about 20 years ago and didn’t solve it at the time.  A few years later, I figured out a trick to make it work and have been using it ever since. The trick is to have a process run a “tail -f” of an empty file into the pipe. With this second process tailing, SQL*Plus doesn’t exit when reading from the pipe. Since I first started using this I’ve never looked into exactly why. It think that when SQL*Plus tries to read from the pipe after the first command has been sent, the OS says, no more data , and SQL*Plus exits. With the second process doing the “tail -f”, then the OS tells SQL*Plus, waiting for more data to send you, and SQL*Plus waits. Would love a more detailed explanation.

#!/bin/ksh
SID=orcl
PORT=1521
function usage
{
       echo "Usage: $(basename $0)    [sid] [port]"
       echo "  username        database username"
       echo "  username        database password"
       echo "  host            hostname or IP address"
       echo "  sid             optional database sid (default: orcl)"
       echo "  port            optional database port (default: 1521)"
       exit 2
}
[[ $# -lt 3 ]] && usage
[[ $# -gt 5 ]] && usage
[[ $# -gt 0 ]] && UN=$1
[[ $# -gt 1 ]] && PW=$2
[[ $# -gt 2 ]] && HOST=$3
[[ $# -gt 3 ]] && SID=$4
[[ $# -gt 4 ]] && PORT=$5
  MKNOD=/etc/mknod
  DEBUG=0
  OPEN=sqlplus.open
  PIPE=sqlplus.pipe
  CLEAN=sqlplus.clean
  sh ./$CLEAN > /dev/null 2>&1
  echo "" > $CLEAN
  echo "rm $OPEN" >> $CLEAN
  echo "rm $PIPE" >> $CLEAN
  rm $OPEN $PIPE > /dev/null 2>&1
  touch  $OPEN
  cmd="$MKNOD $PIPE p"
  eval $cmd
  tail -f $OPEN >> $PIPE &
  OPENID="$!"
  echo "kill -9 $OPENID" >> $CLEAN
  # run SQLPLUS silent unless DEBUG is 2 or higher
  SILENT=""
  if [ $DEBUG -lt 2 ]; then
      SILENT="-s"
  fi
  CONNECT="$UN/$PW@(DESCRIPTION= \
                     (ADDRESS_LIST=             \
                         (ADDRESS=              \
                             (PROTOCOL=TCP)     \
                             (HOST=$HOST)       \
                             (PORT=$PORT)))     \
                      (CONNECT_DATA=            \
                             (SERVER=DEDICATED) \
                             (SID=$SID)))"
   cmd="sqlplus $SILENT \"$CONNECT\" < $PIPE > /dev/null &"
   cmd="sqlplus $SILENT \"$CONNECT\" < $PIPE  &"
   echo "$cmd"
   echo "PIPE $PIPE"
   eval $cmd
   SQLID="$!"
   echo "kill -9 $SQLID" >> $CLEAN

Example execution

$ ./sqlplus_pipe.sh  scott tiger 192.168.1.2
PIPE sqlplus.pipe
$ echo 'select * from dual;' > sqlplus.pipe
D
-
X
$ echo 'exit' > sqlplus.pipe
$ sh sqlplus.clean

The above code will create a SQL*Plus connection reading it’s input from a pipe. It also creates a cleanup file to remove the pipe file and kill the tail process. If creating multiple connections then the file names will have to be pre/post-pended with some string to keep them separate. could be a timestamp. Could be info about which target.

Now what does this have to do with password encryption?
Well there are packages that handle password encryption. Oracle has as cool thing called wallets, that can be setup so that SQL*Plus can connect without a password.
Oracle’s Doc
http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/cnctslsh.htm
Here is a quick setup:
http://www.oracle-base.com/articles/10g/SecureExternalPasswordStore_10gR2.php
Unfortunately Oracle’s wallet method requires files that can’t be redistributed. These file dependencies legally have to be installed by the end user,  where as Oracle instant client can be redistributed. So what if I’m redistributing a tool that uses instant client? Then the wallet solution is out of the question, at least for easy installs.
Now what if I create my own binary to handle password encryption, like “Oracle Password Repository”
http://opr.sourceforge.net/
This doesn’t help that much for protection, because a user can actually get the text password. What I want is a way to encrypt passwords and hide  the unencrypted passwords from the user. Sure I want  them to connect, then they have access to the database,  but I want to prevent them from walking off with a file full of clear text passwords.  One solution, like the Oracle wallet with the “cwallet.sso” files, is to have a binary that creates the connections  for SQL*Plus over a pipe and then pass the pipe to the users. Bit of a hack, especially for an interactive users, but for scripts that run SQL*Plus it not only centralizes the passwords, but it encrypts and  helps prevent the user from getting access to and walking away with a set of clear text passwords.

NOTE:

Reguarding the beginning of this blog post and the problem of SQL*Plus exiting after receiving the first command via a named pipe, here is what truss looks like echoing ‘select * from dual’ into the pipe with and without having a second process tailing (nothing) into the pipe. First example has a second process doing a “tail -f ” of an empty file into the pipe while echoing ‘select * from dual’ into the pipe which SQL*Plus reads, executes and stays connected

fstat64(1, 0x08044440)                          = 0
write(1, "\n", 1)                               = 1
write(1, " D\n", 2)                             = 2
write(1, " -\n", 2)                             = 2
write(1, " X\n", 2)                             = 2
write(1, "\n", 1)                               = 1
read(0, 0x0813FAD4, 5120)       (sleeping...)

Second example, there is no “tail -f” and we just do “echo ‘select * from dual;'” into the pipe which SQL*Plus executes then exits:

write(1, "\n", 1)                               = 1
write(1, " D\n", 2)                             = 2
write(1, " -\n", 2)                             = 2
write(1, " X\n", 2)                             = 2
write(1, "\n", 1)                               = 1
write(1, " S Q L >  ", 5)                       = 5
read(0, 0x0813F714, 5120)                       = 0
write(4, "\0\r\0\006\0\0\0\0\003\t".., 13)      = 13
read(4, "\011\0\006\0\0\0\0\0\t01".., 2064)     = 17
write(1, " D i s c o n n e c t e d".., 95)      = 95
write(1, " W i t h   t h e   P a r".., 78)      = 78
write(4, "\0\n\0\006\0\0\0\0 @", 10)            = 10
close(4)

Here is a good explanation http://linux.die.net/man/7/pipe

If all file descriptors referring to the write end of a pipe have been closed, then an attempt to read(2) 
from the pipe will see end-of-file (read(2) will return 0).

The part that isn’t explained, for me, is that a reader will wait until at the write end has been opened.  So the EOF doesn’t happened until there is an open and a close and all open write file descriptors have to be closed, thus adding a never finishing write will keep the reader from reading an EOF.

Here is the code for OpenSolaris (thanks to Erik Schrock for this links)

 


Uncategorized

  1. Trackbacks

  2. No trackbacks yet.
  1. Comments

  2. AndyP
    November 29th, 2011 at 20:24 | #1

    As the script is using ksh then a co-process may be a better option than a named pipe – also has the advantage of not spamming the user/pass in the process list or OS audit records. Although processing of failures with co-processes isn’t trivial. After saying that it wouldn’t address the core issue around encrypted password.

    The percieved advantage of wallets is an interesting question – if I have a file with a plain text password in it how much worse is that than having a file where the password is not stored in plain text but I don’t need to be able to decrypt the wallet content to perform a connection (and as we know there must be some level of trust between the Oracle software and the wallet, for an auto-login wallet, there must be a way using a signed(?) Java class to get at the password?

    Wallets also pose the question of where the password for the wallet is retained – and how password rotation of the password stored within the wallet is accomplished.

    Perhaps pktool is as good an option as an Oracle wallet on a Solaris like OS? The implementation for encrypted zfs filesystems within smf could also be a decent model. An option if the concern is around password stored on backup media would be to use an encrypted zfs filesystem to persist the password in plain-text…..

    The opr code is quite clear about what is being gained by the encryption/obfuscation of the password it employs……

  3. wski
    December 31st, 2011 at 18:10 | #2

    There’s a trick to cheat SQL*Plus into being procedural. See github.com/wski/blackbox

  4. January 2nd, 2012 at 19:06 | #3

    @wski Thanks for stopping by and sharing your script. As far as I can tell your script is mainly a SQL script store with the pivotal part being:

    
    SQL> set buf y
    SQL> i
      1  select 'y' from dual
      2  .
    SQL> l
      1* select 'y' from dual
    SQL> set buffer x
    SQL> i
      1  select 'x' from dual;
      2  .
    SQL> l
      1* select 'x' from dual;
    SQL> set buffer y
    SQL> l
      1* select 'y' from dual
    
    SQL> set buffer x
    SQL> set buffer y
    SQL> save n replace
    Wrote file n.sql
    SQL> r
      1* select * from dual
    D
    -
    X
    
    SQL> set buffer x
    SQL> save n replace
    Wrote file n.sql
    SQL> r
      1* select * from dual
    D
    -
    X
    

    which is a cool trick and allows one to store all their sql script in one file but doesn’t really make the script procedural, ie if/then/else, while/do/done, etc

    One can do this in PLSQL but then then there is no output until the the command is finished.

  5. Vivek
    March 8th, 2013 at 14:00 | #4

    Give me Guidance about SQL *PLUS with Oracle.how to use and configure these stuff, i m student if u don’t mind waiting for reply

You must be logged in to post a comment.