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" >> $CLEANExample 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.cleanThe 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)
Comments