Solved

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

Posted on 2008-10-30
6
4,004 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
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ORA-02288: invalid OPEN mode 2 87
c++  placing data into a form and an editbox 5 25
MS SQL Server Management Studio R2 4 32
Adjust the codes 3 35
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

726 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