Teradata Forums / Teradata / Teradata Tools and Utilities / Can BTEQ Prompt User for Password in Scripts / Latest Posts

RE: Can BTEQ Prompt User for Password in Scripts

TeraJaguar — Thu, 04 Jan 2007 13:52:08 GMT

Just wanted to update this thread with the final script. This script allows for versioning the output files and an alternate directory to place the output in. Meets our need of not storing passwords in a file on the system.

if [ $# -lt 1 ];then
echo usage: `basename $0` yourjob.sql + optional directory
echo For output in current directory: tdsql yourjob.sql
echo For output in specified location: tdsql yourjob.sql /camsrvr/largefile/userid/
exit 9
fi

dir=""
if [ $# -gt 1 ];then
dir=$2
fi

# File name for output
tt=$1
ex1="out"
out1="$dir$tt.$ex1"
i=1
while [ -f $out1 ]
do
out1="$dir$tt.$ex1.$i"
(( i = $i + 1 ))
done
echo >$out1

#File name for nohup output
tl=$1
ex3="log"
log="$1.$ex3"
i=1
while [ -f $log ]
do
log="$tl.$ex3.$i"
(( i = $i + 1 ))
done

echo >$log

#prompt for uname and password

echo "Enter Teradata User Name: "
read usr

echo "Enter Password: "
stty -echo
read pass
stty echo

nohup bteq <<_END__ >$log 2>&1&
.set session transaction BTET
.logon edwprod/$usr,$pass
.export report file=$out1 ;
.run file=$1;
.export reset;
.quit;
_END_
stty echo

RE: Can BTEQ Prompt User for Password in Scripts

TeraJaguar — Mon, 23 Oct 2006 14:53:32 GMT

That did it. Works great and no passwords to be found.

Thanks!

RE: Can BTEQ Prompt User for Password in Scripts

Fred Pluebell — Fri, 20 Oct 2006 17:13:30 GMT

Why not just use a "here document"?

#!/bin/ksh

#prompt for username and password
echo "enter username:"
read usr
echo "enter password:"
read pass

bteq <<_END_
.logon edw/$usr,$pass
.set session transaction BTET
.export report file=$1.out;
.run file=$1;
.export reset;
.quit;
_END_

RE: Can BTEQ Prompt User for Password in Scripts

TeraJaguar — Thu, 19 Oct 2006 15:30:06 GMT

Well it worked up to a point. Since we pass the userid and password on the same line as the BTEQ command when you do a ps -ef you get the eniter command line back! That's right with the userid and password!

Ooops. On Solaris we got around this by creating a link that points to the bteq file and executing the link. The link is about 50 bytes long and it overflows the ps CMD buffer on Solaris.

On AIX the CMD buffer is huge. It just keeps wrapping so we aren't able to overflow it with the "whack it with a hammer" approach above. Sigh....

Anyone know how to shorten up the CMD buffer that the ps command access on AIX?

Thanks again for all the great suggestions.

TeraJag

RE: Can BTEQ Prompt User for Password in Scripts

TeraJaguar — Fri, 06 Oct 2006 14:03:35 GMT

That idea worked great. I added a stty -echo and a stty echo before and after the read of the password to blank out the pasword on the screen. Thanks again!

RE: Can BTEQ Prompt User for Password in Scripts

TeraJaguar — Thu, 05 Oct 2006 11:46:21 GMT

Nicolas,

I'll definitely give that a shot. Gets around my password issue very nicely. Thanks also to the person who sent the other suggestion! You will become plan B if I have trouble getting nicolas' idea to work.

Thanks,

I'll post the results soon!

RE: Can BTEQ Prompt User for Password in Scripts

nicolas — Thu, 05 Oct 2006 07:32:38 GMT

bteq cannot prompt the user for anything but korn shell can.
your script becomes:

#!/bin/ksh
#prepare your script
fil=$1;
scmd=s/\$infil/$fil/g
sed $scmd ~/bin/cmds.scr > tmp

#prompt for username and password
echo "enter username:"
read usr
echo "enter password:"
read pass

#launch bteq passing username and password in the command line
bteq < tmp .logon edw/$usr,$pass
#end of script

As for your cmds.scr script, the only thing you have to do is remove the .logon line:
.set session transaction BTET
.export report file=$infil.out;
.run file= $infil;
.export reset;
.quit;

RE: Can BTEQ Prompt User for Password in Scripts

SarathyG — Thu, 05 Oct 2006 02:36:56 GMT

alternatively you can use the 'ftppwd' utility in UNIX. where in, you could store your personalized pairs of DBServername and Passwd and retrive them, dynamically from the BTEQ script. hope dis would help.

RE: Can BTEQ Prompt User for Password in Scripts

TeraJaguar — Tue, 03 Oct 2006 09:06:59 GMT

Well....... I guess by the nuber of views that this is something of interest to a great many of us..... but based on all the feedback (or lack thereof) from the Teradata team working with us for a UDB to Teradata conversion I'm guessing this just isn't possible right now.

Unfortuantely Teradata is pushing more people to SQL Assistant at the same time our company is trying to eliminate all client installed apps....

Thanks for looking!

Can BTEQ Prompt User for Password in Scripts

TeraJaguar — Mon, 18 Sep 2006 13:43:58 GMT

I'm using a BTEQ script called from a Korn Shell Script toi automate the process of running SQL via BTEQ on Unix. The user simply types the shell script name followed by their SQL input file name and the script seds the file name into the BTEQ script. No problems there I've gotten it to work just fine.

Here is the issue. we can't have user passwords in a file. It can't even been in a very temporary file that lasts the life of the BTEQ job and then gets deleted.....

Is there any way to get BTEQ to prompt the end user for the password?
I've tried just leaving out the password and putting everything else in the .logon command but I only get an error message

Here is my current Shell and BTEQ scripts:

Shell:

#!/bin/ksh
fil=$1;
scmd=s/\$infil/$fil/g
sed $scmd ~/bin/cmds.scr > tmp
bteq < tmp

Bteq cmds.scr file:

.set session transaction BTET
.logon edw/uid,passwd;
.export report file=$infil.out;
.run file= $infil;
.export reset;
.quit;

Thanks for all your help!