Solved

KSH Korn Shell script loop through username text file login sql plus issue

Posted on 2008-10-30
6
3,996 Views
Last Modified: 2013-12-20
I was aksed to create a script that audits all the usernames in our oracle databases and confirms that the username and password arent the same.

So my goal is to create a KSH korn shell script that extracts the usernames from an oracle database and then feeds the list of usernames into a varaible and attempts to login one at a time using username and username as the password.

I'm new to korn shell scripting.  IM close but I can get past this part..

When I run the code snipit below I get this:

******
TESING LOGIN FOR USER:  DBSNMP
ERROR:
ORA-01017: invalid username/password; logon denied
ERROR:
ORA-01005: null password given; logon denied
ERROR:
ORA-01005: null password given; logon denied
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
******


If I put a print statement on the sqlplus command it look perfect? see below...

******
TESING LOGIN FOR USER:  DBSNMP
sqlplus -s DBSNMP/DBSNMP
******
 
 
******
TESING LOGIN FOR USER:  SSDB
sqlplus -s SSDB/SSDB
******


Is there a way I can get it to try once and go to the next one reporting success or failure...Also after I get a success I need to test for that and exit the sqlplus session as well..

any sugestions...???

Ive also attached the full script incase you wanna see it...
{ while read users;do
  
   print " "
   print "******" 
   print "TESING LOGIN FOR USER: " $users
   sqlplus -s $users/$users  
   print "******"
   print " "
   
done } < users.txt

Open in new window

usr-lst-example.ksh.txt
0
Comment
Question by:H
  • 3
  • 2
6 Comments
 
LVL 11

Expert Comment

by:jgiordano
ID: 22846595
sqlplus username@SID/password

or

sqlplus -s apps/apps<<EOF!
SELECT sysdate
from dual;
quit
EOF!
0
 
LVL 27

Accepted Solution

by:
sujith80 earned 500 total points
ID: 22847246
Use the following snippet for checking the connections.
On successful connections the variable $result will have the values "DONE", you can check the variable to know the connection status.

result=`sqlplus -s /nolog <<EOC
set heading off
set echo off
conn $users/$users
select 'DONE' from dual;
exit;
EOC`
 
print $result

Open in new window

0
 
LVL 8

Author Comment

by:H
ID: 22850525
Hi

So I'm trying what sujith80: sugetsted and the code is below.  But it seems like Im not execudeing the sqlplus command within the variable $result? Its just storing all the Text from the command?


This is the reults:
******
TESING LOGIN FOR USER:  PLDB
sqlplus -s /nolog <<EOC set heading off set echo off conn $users/$users select DONE from dual; exit; EOC
******


 
while read users;do
  
   print " "
   print "******" 
   print "TESING LOGIN FOR USER: " $users
   
   result='sqlplus -s /nolog <<EOC
	set heading off
	set echo off
	conn $users/$users
	select 'DONE' from dual;
	exit;
	EOC' 
	print $result
   
   
   print "******"
   print " "

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 8

Author Comment

by:H
ID: 22851325
I figured it out...

I used the -L in the sql plus command to attempt the logon once...

I created a CMD.TXT file that contained a 'select 'connected from dual' and an EXIT command... when the script successfully logged in it reports connected...

I surrounded the whole section in a function and sent that to a text file..:

I ended the results in a text file...If it failed it reported error and if it successfully connected it reported connected.
LOGFLE=Report.txt
 
function USR_TEST {  
 
{ while read users;do
  
   
   print " "
   print "******" 
   print "TESING LOGIN FOR USER: " $users
   #print "sqlplus -s $users/$users" 
   
	sqlplus -L -s $users/$users @cmd.txt
	
   
   print "******"
   print " "
   
done } < users.txt
 
 
 }
 
   USR_TEST >${LOGFLE} 2>&1

Open in new window

0
 
LVL 27

Expert Comment

by:sujith80
ID: 22851344
I dont understand what you are saying here.

You may just copy and paste the code I have given above. It works. Dont do too much of intendation. Shell scripts doesnt like white spaces in many cases. If i remember correctly; if there is whitespace before that EOC it will not run properly.

Try with my code first.
0
 
LVL 8

Author Closing Comment

by:H
ID: 31511870
Used a piece from this.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question