Solved

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

Posted on 2008-10-30
6
4,012 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many‚Ķ
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

724 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