saps19
January 23, 2012, 11:00pm
1
Hi All,
I want to validate the Production Database password at the time of login through script. If incorrect password entererd by the user, the script will ask again for the password.
Below is the sample of my script...
#########################
# Unix Code Starts here #
#########################
##------ Pre-Database Connection Starts here ------##
echo 'Please Enter Database Password: '
stty -echo
read db_pass
stty echo
###############################
# Database connection started #
###############################
sqlplus -s user/$db_pass@prd3 <<ENDOFSQL
WHENEVER SQLERROR EXIT 1
set serveroutput on size 1000000
set echo off
set feedback off
set heading off
set pages 0
set pagesize 1000
set linesize 300
spool $spool_file
/* PL/SQL block starts from here */
...
How do I put a check here for database password?
You could try examining the return code from sqlplus:
sqlplus -s user/$db_pass@prd3 2> /tmp/sql.err <<ENDOFSQL
...
ENDOFSQL
return_code=$?
if [ $return_code -ne 0 ]
then
echo "sqlplus failed: error code $return_code"
echo "Error string is:"
cat /tmp/sql.err
fi
rm -f /tmp/sql.err
Use an invalid username/password and see what you get back.
If the return code isn't unique for incorrect password (some programs just return 1 for any error found). Try a search for a unique error string in the sql.err file relating to username/password.
saps19
January 24, 2012, 4:06am
3
chubler_xl:
You could try examining the return code from sqlplus:
sqlplus -s user/$db_pass@prd3 2> /tmp/sql.err <<ENDOFSQL
...
ENDOFSQL
return_code=$?
if [ $return_code -ne 0 ]
then
echo "sqlplus failed: error code $return_code"
echo "Error string is:"
cat /tmp/sql.err
fi
rm -f /tmp/sql.err
Use an invalid username/password and see what you get back.
If the return code isn't unique for incorrect password (some programs just return 1 for any error found). Try a search for a unique error string in the sql.err file relating to username/password.
Please check the code and output:
#! /bin/ksh
#
#########################
# Unix Code Starts here #
#########################
#
LOG_DIR=/app/work/saps/logs
varDate=`date +%d%h%y`
echo 'Please Enter Database Password: '
stty -echo
read db_pass
stty echo
sqlplus -s user/$db_pass@prd3 2> /tmp/sql_today.err <<ENDOFSQL
ENDOFSQL
return_code=$?
echo $return_code
if [ $return_code -ne 0 ]
then
echo 'sqlplus failed: error code '$return_code
cat /tmp/sql_today.err >$LOG_DIR/sql_err_${varDate}.txt
else
echo '\n\033[32mConnected !\033[0m'
fi
#rm -f /tmp/apac_sql_today.err
find $LOG_DIR -name "sql_err_*.txt" -mtime +0 -type f;
The output with correct DB Pass:
$ksh:/work/saps
ksh:0$ ./test_db_conn
Please Enter Database Password:
0
Connected !
with wrong DB Pass:
ksh:0$ ./test_db_conn
Please Enter Database Password:
ERROR:
ORA-01017: invalid username/password; logon denied
0
Connected !
Still it's going to the else section, output of the $return_code is 0 and it's not creating any log files in LOG Directory.
Please explain.. :S
OK looks like sqlplus is returning 0 regardless. We will need to search for "logon denied" on stdout.
I'm piping output to tee so it still appears on the tty (usefull if the sqlplus session is interactive, and also for debugging). You can change to redirect directly to the file if you don't need the output displayed.
Try this:
#! /bin/ksh
#
#########################
# Unix Code Starts here #
#########################
#
LOG_DIR=/app/work/saps/logs
varDate=`date +%d%h%y`
echo 'Please Enter Database Password: '
stty -echo
read db_pass
stty echo
( sqlplus -s user/$db_pass@prd3 2>&1 <<ENDOFSQL
ENDOFSQL ) | tee /tmp/sql_today.err
if grep -q "logon denied" /tmp/sql_today.err
then
echo 'sqlplus failed!'
cat /tmp/sql_today.err >$LOG_DIR/sql_err_${varDate}.txt
else
echo '\n\033[32mConnected !\033[0m'
fi
#rm -f /tmp/apac_sql_today.err
find $LOG_DIR -name "sql_err_*.txt" -mtime +0 -type f;
saps19
January 30, 2012, 12:12am
5
Hi Chubler,
I'm getting the following err:
./test_db_conn.ksh[13]: syntax error at line 13 : `<<' unmatched
...
chubler_xl:
OK looks like sqlplus is returning 0 regardless. We will need to search for "logon denied" on stdout.
I'm piping output to tee so it still appears on the tty (usefull if the sqlplus session is interactive, and also for debugging). You can change to redirect directly to the file if you don't need the output displayed.
Try this:
#! /bin/ksh
#
#########################
# Unix Code Starts here #
#########################
#
LOG_DIR=/app/work/saps/logs
varDate=`date +%d%h%y`
echo 'Please Enter Database Password: '
stty -echo
read db_pass
stty echo
( sqlplus -s user/$db_pass@prd3 2>&1 <<ENDOFSQL
ENDOFSQL ) | tee /tmp/sql_today.err
if grep -q "logon denied" /tmp/sql_today.err
then
echo 'sqlplus failed!'
cat /tmp/sql_today.err >$LOG_DIR/sql_err_${varDate}.txt
else
echo '\n\033[32mConnected !\033[0m'
fi
#rm -f /tmp/apac_sql_today.err
find $LOG_DIR -name "sql_err_*.txt" -mtime +0 -type f;
Try this:
#! /bin/ksh
#
#########################
# Unix Code Starts here #
#########################
#
LOG_DIR=/app/work/saps/logs
varDate=`date +%d%h%y`
echo 'Please Enter Database Password: '
stty -echo
read db_pass
stty echo
sqlplus -s user/$db_pass@prd3 2>&1 1> /tmp/sql_today.err <<ENDOFSQL
ENDOFSQL
if grep -q "logon denied" /tmp/sql_today.err
then
echo 'sqlplus failed!'
cat /tmp/sql_today.err >$LOG_DIR/sql_err_${varDate}.txt
else
echo '\n\033[32mConnected !\033[0m'
fi
#rm -f /tmp/apac_sql_today.err
find $LOG_DIR -name "sql_err_*.txt" -mtime +0 -type f;
---------- Post updated at 03:33 PM ---------- Previous update was at 03:29 PM ----------
saps19:
Hi Chubler,
( sqlplus -s user/$db_pass@prd3 2>&1 <<ENDOFSQL
ENDOFSQL ) | tee /tmp/sql_today.err
I'm getting the following err:
./test_db_conn.ksh[13]: syntax error at line 13 : `<<' unmatched
...
Or try this:
(
sqlplus -s user/$db_pass@prd3 2>&1 <<ENDOFSQL
ENDOFSQL
) | tee /tmp/sql_today.err
Make sure the paranthesis after ENDOFSQL {ENDOFSQL )} should be in new line.
saps19
January 30, 2012, 7:04am
7
knight_eon:
Try this:
I have modified my code as below:
1 #! /bin/ksh
2 #
3 #########################
4 # Unix Code Starts here #
5 #########################
6 #
7 LOG_DIR=/app/work/saps/logs
8 varDate=`date +%d%h%y`
9 echo 'Please Enter Database Password: '
10 stty -echo
11 read db_pass
12 stty echo
13 result=`sqlplus -s user/$db_pass@uat3 2>&1 <<ENDOFSQL
14 ENDOFSQL`
15
16 #echo $result
17
18 if [ "$result" == "" ]
19 then
20 echo '\n\033[32mConnected !\033[0m'
21 else
22 echo 'sqlplus failed! Invalid username/password. For more details, check the LOG File in ' $LOG_DIR
23 echo $result >$LOG_DIR/sql_err_${varDate}.txt
24 fi
25 #rm -f /tmp/sql_today.err
26 find $LOG_DIR -name "sql_err_*.txt" -mtime +0 -type f;
27
It's working fine now...!
Still you guys can review my code and suggest me if anything needs to be added/modified or deleted [Remembering "the Useless use of cat" :)].