Solved

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

Posted on 2008-10-30
6
3,984 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

760 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now