How to hide login data of sql-scripts on Windows

One of the often given advices on hardening a database is to run scripts without broadcasting your login data at the same time. According to Arup Nanda in his famous articles on “Project Lockdown” you have three options to run your scripts without letting everybody in on your password secrets:

1. Start your scripts under /nolog and add your login to the SQL-script your running
2. Start SQL*Plus under /nolog and add the login at the beginning of your shell script
3. Don’t use a login in your command and sql-scripts but a central, highly secured password file

The last option is quite often described for *nix systems, but examples for Windows are quite hard to find. And that’s what this blog is about, an implementation of Arup Nanda’s third option for Windows, in the form of a simple Windows command script which gets passwords from a highly secured file and uses them to run SQL-scripts.

First of all, choose a directory where to “hide” your password file on the database server. Advisably, it should not be located under an Oracle Home, because intruders would search there first, and it really does not matter where you put it as long as you a) make it readable for the Oracle Software owner and b) make the directory not inheritable with the access rights from C: and c) if possible, don’t call the owning user “oracle”. And maybe you should not call the file “passwords.txt” or anything else that directly indicates its content.

My chosen location is an old AMIS favorite, a directory structure called “oradmin”. It is created under ORACLE_BASE and contains several subdirectories like \bin, \sql, \par, \log and/or \exp.

Figure 1 – The oradmin-directory tree

Here, all administrative (ad-hoc) and batch scripts can be stalled.

I called my password file “input.txt” and placed it in $OB\oradmin\par. The “run_script_as.cmd” which I will describe in this blog is located in the \bin-directory and the sql-scripts to be executed are situated in the \sql-directory. Below you find an example of the (fictional) content of my “input.txt” :

# SID:USERNAME:password:"path to Oracle Home (planned future use)";
EMMA:SYS:pitfall1:C:\oracle\db\11.2.0_SE;
ANNA:MOODLE:MoOd13:C:\oracle\db\11.2.0_EE;
ANNA:SYS:pitfall2:C:\oracle\db\11.2.0_EE;

Figure 2 – Security settings of the input.txt-file

In Figure 2 you can see that the security on this file is very, very strict. The Windows group, which has also been granted access (read/execute access only!) is the renamed “ora_dba”-group and user “moodle” is the alternative “oracle”-user on this Windows machine. Here, even the administrators are “officially” excluded from all access to everything under ORACLE_BASE. This means that the only user able to manage this file and its content is the local user “moodle”.

But now comes the somehow tricky part, creating a Windows command script which uses this “input.txt” and runs SQL-scripts (for batches) with the correct username-password combination on the correct database (my machine for example has 3 different Oracle Homes and 4 databases).

At first I wanted the script to loop through each row in order to find the correct password, but this is not necessary because you know the SID of the database your want the script to be run on, AND you know the username which should run the script (no real secrets per se). You have to be a little more specific and also tell the command script as well which script should be run. These three chunks of information can identify which row in “input.txt” is needed and what your command has to look for. Finally I came up with a script which I called “run_script_as.cmd“:

REM #/******************************************************************************
REM Script Name : run_script_as.cmd
REM Creator : K. Kriebisch, AMIS Services b.v., Nieuwegein (NL)
REM Creation date : 30-07-2014
REM Purpose : used in conjunction with an input-file for passwords
REM to run SQL-scripts on local databases of Windows servers
REM without providing a (visible) password
REM Usage : run_script_as <SID> <USERNAME> <scriptname>
REM Remarks : used to obfuscate the used logins for the scripts
REM The input.txt must be highly secured and is only
REM accessible to the Oracle software owner
REM SQL-script should be located in $OB\oradmin\sql
REM Versions : usable on all Windows versions like XP and higher
REM
REM -------------------------------------------------------------------------------
REM
REM Revision record
REM Date Version Author Modification
REM ---------- ------ ------ -------------------------------------------------
REM 30-07-2014 1.0 KK Created
REM
REM ******************************************************************************/

setlocal
set echo off
REM Here should come some tests whether the usage is correct or not ...

REM ... and the logging is also not yet implemented

REM Setting Defaults, so all variables are filled with a default value
set ORACLE_SID=%1
set USERNAME=%2
set SQL_SCRIPT=%3
set PWD=geen
set SCRIPT_BASE=C:\oracle\oradmin
set SEARCHSTRING=%1:%2

REM Does the search string exist in the input file? If not, stop the script execution
IF not exist ('findstr /L/B /C:%SEARCHSTRING% %SCRIPT_BASE%\par\input.txt') do (
echo %SEARCHSTRING% not found!”
goto end
)

REM If the search string exists, find the token which represents the password in the matching line
FOR /F "eol=; tokens=3 delims=:" %%i in ('findstr /L/B /C:%SEARCHSTRING% %SCRIPT_BASE%\par\input.txt') DO (set PWD=%%i)
REM ... and get the string for the Oracle_home path
FOR /F "eol=; tokens=4 delims=:" %%j in ('findstr /L/B /C:%SEARCHSTRING% %SCRIPT_BASE%\par\input.txt') DO (set ORACLE_HOME=%%j)

REM Combine all information to create the connectstring
set CONNECTSTRING=%USERNAME%/%PWD%@%ORACLE_SID%

REM In order to connect as user SYS don't forget to add as sysdba
if "%2" == "SYS" (set CONNECTSTRING="%USERNAME%/%PWD%@%ORACLE_SID% as sysdba")

REM ... and now we run our desired script!

sqlplus %CONNECTSTRING% @%SCRIPT_BASE%\sql\%SQL_SCRIPT%

REM Goto lable to stop the script if search string can not be found
:end

endlocal

What does this script do?
The center of this script is the row :

IF not exist (‘findstr /L/B /C:%SEARCHSTRING% %SCRIPT_BASE%\par\input.txt’) DO goto end

which should find a specific row in the input file or stop the command script when that is not the case.

‘findstr /L/B /C:%SEARCHSTRING% %SCRIPT_BASE%\par\input.txt’ means “search for the given string in each row of the input file (=> /C:[search string] [path and file name]) and try to find a literal match (=> /L) and /B meaning “start looking at the beginning of each line”.

“SEARCHSTRING” is defined as the combination of upper ORACLE_SID (“%1”), a colon and the given upper username (“%2”) which forms the begin of each row in the input file. The IF-statement checks for the string in the whole file from head to bottom. If it finds a matching entry before the end of the file, the script continues processing the following FOR-loop; otherwise it warns the user that this combination does not exist in the input file and then stops the execution of this command file.

When the script reaches the first “FOR /F” command, we already know that the ‘findstr’ command does find (at least) one matching entry, which we can use as input in the “FOR /F”–loop to find the password in that same row. What we are looking for is the third token (= entry) of the row which is the password string and put it in the variable called PWD so rest of the script can use it. And that is what the next row of the script says :

FOR /F “eol=; tokens=3 delims=:” %%i in (‘findstr /L/B /C:%SEARCHSTRING% %SCRIPT_BASE%\par\input.txt’) do (set PWD=%%i)

The /F switch of a FOR-loop command tells the Windows command engine to loop through items in a given file item (= line) by item (/D would loop through a directory listing finding file and subdirectory names etc.). Here, the items are, beter the item is, the result of the ‘findstr’-function executed on our “input.txt” file, a specific, single row.

1. eol=; means each line ends with a semi-colon and the script will stop when a line begins with it
2. tokens=3 look only for the 3^rd token (and put it in variable %%i)
3. delimiter=: the tokens are divided by colons

The next line does the same for token no.4, which is the string of Oracle_Home variable.

The password and the Oracle_home variable could be filled in one go, but I’m still fighting with the colon in the string, which I did not want to loose because of the similarity between this input file and the oratab file. But mayby, I should use a different delimiter…

BTW, the loop command would then be

FOR /F “eol=; tokens=3,4 delims=:” %%i %%j in (‘findstr /L/B /C:%SEARCHSTRING% %SCRIPT_BASE%\par\input.txt’) DO (set PWD=%%i set ORACLE_HOME=%%j)

Now we have all the information to run our sql-scripts.

The command script now sets the Oracle parameters (to make sure the correct SQL*plus version and database will be used) and forms the total connectstring. The following command, an exception, is only executed if the username is “SYS”, so it will not override the already created connectstring if the condition is not met.

The default location of the scriptname is in the script base (C:\oracle\oradmin), preferably for an SQL-script in \sql and remember, the value of the variable “SQL_SCRIPT” as been defined as the third input argument (“%3”).

When you put “C:\oracle\oradmin\bin” in the PATH variable of your machine, the script can finally call SQL*Plus(.exe) and the desired sql scripts with :

sqlplus %CONNECTSTRING% @%SCRIPT_BASE%\sql\%SQL_SCRIPT%

All the calling user has to do in order to use this script, is, call it with its three parameters from wherever he/she stands in the directory system of the database server as the script header indicates:

run_script_as EMMA SYS query1

That’s it! No passwords in sql-scripts anymore on this machine!

I know, there’s still a lot which can be beautified in this script, but it works for me ;-)).

So, have fun adopting the script for yourself!