Database Logins (Perl for System Administration)

7.5. Database Logins

As mentioned before, database administrators have to deal with some of the same issues system administrators contend with, like maintaining logins and accounts. For instance, at my day job we teach database programming classes. Each student who takes a class gets a login on our Sybase server and her or his very own (albeit small) database on that server to play with. Here's a simplified version of the code we use to create these databases and logins:

use DBI;

# USAGE: syaccreate <username>

$admin = 'sa';
print "Enter passwd for $admin: ";
chomp($pw = <STDIN>);
$user=$ARGV[0];

# generate a *bogus* password based on user name reversed 
# and padded to at least 6 chars with dashes
$genpass = reverse join('',reverse split(//,$user));
$genpass .= "-" x (6-length($genpass));

# here's a list of the SQL commands we will execute in order
# we: 1) create the database on the USER_DISK device, 
#        with the log on USER_LOG
#     2) add a login to the server for the user, 
#        making the new database the default
#     3) switch to the newly created database
#     4) change its owner to be this user
@commands = ("create database $user on USER_DISK=5 log on USER_LOG=5",
             "sp_addlogin $user,\"$genpass\",$user",
             "use $user",
             "sp_changedbowner $user");

# connect to the server 
$dbh = DBI->connect('dbi:Sybase:',$admin,$pw);
die "Unable to connect: $DBI::errstr\n" 
  unless (defined $dbh);

# loop over the command array, execute each command in turn
for (@commands) {
    $dbh->do($_) or die "Unable to $_: " . $dbh->errstr . "\n";
}

$dbh->disconnect;

Because this task consists of running a set of commands that don't return data sets, we can write this as a very compact loop that just calls $dbh->do( ) repeatedly. We could use an almost identical script to delete these accounts and their databases when the class has concluded:

use DBI;

# USAGE: syacdelete <username>

$admin = 'sa';
print "Enter passwd for $admin: ";
chomp($pw = <STDIN>);
$user=$ARGV[0];

# here's a list of the SQL commands we will execute in order
# we: drop the user's database
#     drop the user's server login
@commands = ("drop database $user",
            "sp_droplogin $user");

# connect to the server
$dbh = DBI->connect('dbi:Sybase:',$admin,$pw);
die "Unable to connect: $DBI::errstr\n" 
  unless(defined $dbh);

# loop over the command array, execute each command in turn 
for (@commands) {
    $dbh->do($_) or die "Unable to $_: " . $dbh->errstr . "\n";
}

$dbh->disconnect or
  warn "Unable to disconnect: " . $dbh->errstr . "\n";

There are many login-related functions that can be coded up. Here are a few ideas:

Password checker: Connect to the server and get a listing of databases and logins. Attempt to connect using weak passwords (login names, blank passwords, default passwords).
User mapping: Generate a listing of which logins can access which databases.
Password control: Write a pseudo-password expiration system.