For a user to execute a shell script, the user has to be able to read the shell script. Storing the name of a file (that can be read by your script) in a script that can be read by the user means that the user can read the file containing the DB username and DB username's password, so there isn't much security added by putting them in a different file. Some systems allow you to create set-UID shell scripts that could be used to run as the DB user and read a file that is only readable by that user, but there aren't many systems that still support set-UID shell scripts (and if you're using a shell that does allow that, you need to be VERY careful to avoid letting someone running your script get access to all of DB user's private files and changing DB user's password).
What operating system and shell are you using?
Can you use C or C++ instead of shell to write your script?
Thanks.
I knew there is no security indeed.
BUT, it is our silly company policy that does not want the user name and password put in the same place in the same script.
#!/bin/ksh
DATAFILE=/path/to/file/containing/user+password # both on one line separated by a <space>.
JOBLOG="what ever this is"
SQLLDR_CTL="what ever this is"
INFILE="what ever this is"
#=================================================
# Get private data from data file readable by everyone.
#=================================================
read -r loginName password < "$DATAFILE"
#=================================================
# Run SQL Loader
#=================================================
sqlplus -S "$loginName@UAT/$password" <<EOF
sqlldr "$loginName@UAT/$password" log="$JOBLOG" control="$SQLLDR_CTL" data="$INFILE" errors=10000 direct=true rows=10000
EOF
My original post had several typos as pointed out by RudiC in a private message. (The read had loginName twice instead of loginName and password and the sqlplus command had unquoted literal strings instead of quoted variable expansions.) I apologize for anyone who read this message before the corrections were made.
should set DATAFILE to the pathname of a file that contains the user's name and the user's password on the first line in that file with the values separated by a space. The read statement does not grab values from the string assigned to DATAFILE ; it grabs values from the contents of the file named by that string!
I apologize for my original post (which contained several typos) that you mostly fixed. Please look at the updated suggestion in post #4 (which was updated 2 minutes before your last reply).
To make it slightly less obvious to people looking for passwords, I would suggest that the last component of the absolute path named by DATAFILE should have a period as the first character (such as .secret ) so it won't show up in an ls command unless the -a option is included.
Note also that there is no reason to export any of the variables used in this script. You only need to export variables that you want to be available in the environment of processes invoked by your script. Your script depends on the variables INFILE_PATH , JOBNAME , LOG_PATH , DATE , and SQLLDR_CTL_PATH to be exported by whatever process invokes your script.
My DB days are gone for quite long time, and my sql has corroded away, but as far as I recall, sqlldr is an independent command of its own and thus should not occur in the input stream of sqlplus , i.e. it should disappear from the "here document"?
Not entirely silly, it's halfway to a real solution, but only halfway. The password file can be placed at a remove via ownerships and file permissions. Then you can use sudo to run the script, so that only the script and nothing else can read the password file.
Hi Don, what do you mean by the follows ?
Do you mean to name the credential file with loginName and password as "xxxx.secret" ?
If so, I tried to name it as "login.secret", but it can still show up with "ls" command.
" To make it slightly less obvious to people looking for passwords, I would suggest that the last component of the absolute path named by DATAFILE should have a period as the first character (such as .secret) so it won't show up in an ls command unless the -a option is included. "
That is not what I said. Look more closely at the red text in your quote from my earlier message above.
The first character of xxx.secret and login.secret is not a period so they will not be hidden when listed by ls . If you name the file .secret (with a period as the first character as I suggested), it will not show up in ls output unless you include the -a option on the ls command.
You can also create a password file under your $HOME/.ssh directory and have a separate script read that file and store the passwords as local environment variables. Meaning to add the word export. If someone can log on as the Oracle user on a Linux/Unix system they can authenticate using the us user.
sqlplus '/as sysdba'
Thus you already need to keep the $HOME/.ssh locked down, so that is the best place to keep passwords.