Shell Scripts and Oracle Passwords

Summary

In a typical Oracle environment, it is very common to have bunch of shell scripts to automate the routine tasks against the database. To make oracle connections from shell scripts, one is left with two options,

1) OS authentication

2) hardcode the password.

This document details how to avoid hard coding the oracle passwords in your scripts when the option

(1) is not available in your environment for some specific reasons. The option

(2) would be considered as a security compromise by the security folks in your organization even if you try to convince them after setting the file permissions to 700.

In some cases where application owner password might be maintained differently in the development, test and production environments. In such cases, the hard coded password in the scripts must be changed for each environment upon deployment.

It would further complicate the password maintenance task just in case if you have password expiration policy set in your environment.

There must be many ways of implementing this particular requirement and it varies from person to person who writes the scripts. The objective here is to make it as a standard in your environment.

Implementation

The implementation consists of a text file, a shell script and a C wrapper program. The text file stores the oracle user accounts and passwords in a specific format. The shell script takes user id as an argument and returns the password by reading the text file.

The C program implements setuid logic on the shell script. The location and ownership of these files could be changed according to your environment, but the source code in this document assumes the following:

· oracle software owner account -> owner of these files

· $ORACLE_HOME/bin -> location of the shell script and the wrapper

· $ORACLE_BASE -> location of the text file

Text file

The proposed name of the file is .ora_passwd and the permission on this file must be set to 600 (using chmod). Here is the sample entries of the file

|sid=ORCL|uid=scott|passwd=tiger

|sid=DEV|uid=tamilx|passwd=hello1

|sid=ORCL|uid=backup|passwd=Awrsc!3

|sid=RCAT|uid=rman|passwd=Zxd*9s

Shell script

Name the shell script like getpasswd.sh and set permission to 700 using chmod command. Here the source of the shell script

#!/bin/sh

FILE=$ORACLE_BASE/.ora_passwd

#

get_value(){

echo "$1" | sed "s/.*|$2=\([^|]*\).*/\1/"

}

#

cat $FILE | while read line ; do

[ `get_value "$line" sid` = "$1" ] && {

[ `get_value "$line" user` = "$2" ] && {

get_value "$line" passwd

}

}

done

C wrapper program

This wrapper a generic routine could be customized according to your needs. It can be compiled using any version of GCC compiler or any standard ANSI C compiler you have in your environment. Make sure you name the compiled binary same as the shell script without the .sh extension. In this case, it can be getpasswd.

The file permission should be 6750 using chmod command. To be able to invoke the wrapper program in this example, the unix user accounts who might run batch jobs need to be added to the oinstall group. Or you can set up a new unix group and assign it instead of the oinstall.

Ex.

-rwsr-x--- 1 oracle oinstall 249 Jan 26 12:38 getpasswd

/*

Desc : Generic wrapper routine that invokes a shell

script by assuming effective uid and gid of

the script.

*/

#include

#include

#include

#include

int main (int argc, char *argv[]) {

//

char *command[5];

char *basename;

char script[64];

//

// strip the forward slash

if ((basename = strrchr(argv[0],'/'))) {

basename++;

}

else {

basename=argv[0];

}

//

memset(command, 0, sizeof(command));

//

if ( argc != 3 ){

fprintf(stderr,"Usage: %s ORACLE_SID USER ..!\n",basename);

return 1;

}

//

if ( getenv("ORACLE_HOME") == NULL) {

fprintf(stderr,"ORACLE_HOME must be set..!");

return 1;

}

//

// build the script name to execute

//

command[0]=getenv("ORACLE_HOME");

strcat(command[0],"/bin/");

//

strcpy(script,basename);

strcat(script,".sh");

//

strcat(command[0],script);

//

//set uid gid and execute the command

//

setuid(geteuid());

setgid(getegid());

execl(command[0],script,argv[1],argv[2],0);

return 0;

}

Sample shell script that uses this implementation

#!/bin/sh

#

USR=scott

PASS=`getpasswd ORCL scott`

#

CHECKDB=`sqlplus –s $USR/$PASS <

Set pages 0 feedback off

select ‘Hello World !’

from dual;

EOF`

#

echo $CHECKDB

Conclusion

The centralized password maintenance is the highlight of this implementation. The text file is the only file the gets maintained for each environment. It is not recommended to include SYS and SYSTEM (or any other user with SYSDBA privilege) users in this implementation.