Here's a start. I didn't check the "disable user" syntax.
#!/usr/bin/perl -w
use strict;
use warnings;
use DBI;
my $s_user = q{TEST};
#----------------------------------------------------------------------
# Get a list of data sources that match string X
#----------------------------------------------------------------------
my @a_dbs = grep /X/i, DBI->data_sources("Oracle");
#----------------------------------------------------------------------
# Loop thru each source
#----------------------------------------------------------------------
DB:
foreach my $s_db (@a_dbs) {
#----------------------------------------------------------------------
# Connect to database, if possible.
#----------------------------------------------------------------------
my $dbh = DBI->connect( $s_db, "user", "password", {RaiseError => 1});
#----------------------------------------------------------------------
# Only warn and skip to next if unable to connect.
#----------------------------------------------------------------------
unless (defined $dbh) {
warn "Unable to connect: $s_db $DBI::errstr\n";
next DB;
}
#----------------------------------------------------------------------
# Does the user exist?
#----------------------------------------------------------------------
my $l_user = $dbh->selectrow_array(
q{select user_id from all_users where username = ?}, undef,
$s_user );
#----------------------------------------------------------------------
# Again, warn if not found and skip
#----------------------------------------------------------------------
unless (defined $l_user) {
warn "User $s_user not found\n";
next DB;
}
#----------------------------------------------------------------------
# Disable user id -- I didn't check if this is the correct Oracle
# syntax.
#----------------------------------------------------------------------
$dbh->do(q{alter user $s_user disable});
$dbh->disconnect;
print "Completed: $s_user on $s_db\n";
}
exit;
On Thu, Aug 14, 2003 at 02:07:26PM -0500, Grimes, Greg wrote:
> All,
>
> I'm trying my first script in perl. I want to have a script that reads the database
> instances from the TNSNAMES.ora file. Each database of interest contains a three
> letter abbreviation that I would be searching. Then for each database, I want to
> connect and see if a particular account is defined. If so, disable that account.
> For an experienced perl'er I sure that is an easy one.
>
> I have installed perl, DBI and DBD::Oracle.
>
> Here is my pseudo code:
>
> 1. Define a list containing my database abbreviations
> 2. Get username to be disabled.
> 3. Open the tnsnames.ora file
> 4. Search for the abbreviation.
> 5. For each instance, connect to the database and see if account exists. Not
> secure, but my account has the same password
> 6. If user exists in that database, disable account.
>
> If someone has done similiar, please share.
