#!/nms/site/bin/perl -w

use strict;
use dba_app;
use Getopt::Std;

use vars qw( $dbhProduction $dbhCurrent $app );
use vars qw($opt_F $opt_L $opt_P);

main();
exit 0;


###Begin subs

sub main() {

$app = new dba_app(\@ARGV, $0);

check_opts();

my $first = ucfirst(lc($opt_F));
my $last = ucfirst(lc($opt_L));
my $usrname;

print "Please enter User ID: \n";
chomp($usrname = <STDIN>);

my $fncgrp = 0;

my $flag = is_existed_usr(\$dbhProduction, $usrname);
while ($flag) {
print "User $usrname already exist.\n" .
        "Enter new login again: ";
chomp($usrname = lc(<STDIN>));
$flag = is_existed_usr($usrname);
}

my $passwd = lc($opt_P);
while ( length $passwd < 6 ) {
print "A valid password must be at least 6 charachters. \n" .
        "Enter a password again: ";
chomp($passwd = lc(<STDIN>));
}

my $result = display_fncgrp($dbhProduction);

if ($result) {
$result = "Functional group: \n" . $result . 
        "\nEnter Functional Group: [0 - 9] ";
print $result;
chomp($fncgrp = <STDIN>);
}

my ($server, $prevsuid, $prevsysusrid, $prevuserid, $prevappsysid, $pr
+evserver);
my $count = 0;
my $servers = get_server(\$dbhProduction);
my $idvalues = get_idvalues(\$dbhCurrent);

foreach $server (@$servers){
my ($suid, $sysusrid, $userid, $appsysid) = @$idvalues;


if (($suid != $sysusrid) || ($userid != $appsysid)){
        $app->log($FATAL, "IDVALUES on [ " . $server . " ] ARE NOT SYN
+CHRONIZED"); 
}

if ($count == 0){

           $prevsuid = $suid;
           $prevsysusrid = $sysusrid;
           $prevuserid = $userid;
           $prevappsysid = $appsysid;
}
else{

if (($prevsuid != $suid) || ($prevsysusrid != $sysusrid) || ($prevuser
+id != $userid) || ($prevappsysid != $appsysid)){
        $app->log($FATAL, "IDVALUES on [ " . $server . " ] ARE NOT SYN
+CHRONIZED WITH [ " . $prevserver . " ]");
}
        $prevsuid = $suid;
        $prevsysusrid = $sysusrid;
        $prevuserid = $userid;
        $prevappsysid = $appsysid;
        $prevserver = $server;
}
$count++;
}

foreach $server (@$servers){
    conn_init($servers);
    add_login(\$dbhCurrent, $usrname, $first, $last, $passwd, $fncgrp)
+; 
    my $databases = get_database(\$dbhCurrent);
    foreach my $CurrDB ( @$databases ) {
    add_usr(\$dbhCurrent, $CurrDB, $usrname, $fncgrp);
    }    
    add_access(\$dbhCurrent, $usrname);
    $app->log($INFO, "Account " . $usrname . " has been created on " .
+ $server);
    $app->disconnect($dbhCurrent);
}
}

sub conn_init{

my $CurrServer = shift;

my $CURR_SERVER_PARAM_FILE = $params->get("Global.Config.Dir") . "/ser
+vers/$CurrServer.conf";

$params->loadFile($CURR_SERVER_PARAM_FILE)
    if ( -r $CURR_SERVER_PARAM_FILE );

$params->put("Current.User", $params->get("Server.User"))
        if ( $params->exists("Server.User") );

$params->put("Current.HiddenPW", $params->get("Server.HiddenPW"))
    if ( $params->exists("Server.HiddenPW") );

if ( $params->exists("Current.HiddenPW") ) {
        my $passwd;
        &dba::nm_UnHide($passwd, $params->get("Current.HiddenPW"));
        $params->put("Current.Password", $passwd );
}

$dbhCurrent = $app->connect($CurrServer,
                        "master",
                        $params->get("Current.User"),
                        $params->get("Current.Password"));
}

sub prd_connect(){

my $server = shift;

my $PRDSERVER_PARAM_FILE = $params->get("Global.Config.Dir") .  "$serv
+er.conf";
$params->loadFile($PRDSERVER_PARAM_FILE)
    if ( -r $PRDSERVER_PARAM_FILE );

$params->put("PRODUCTION.User", $params->get("Server.User"))
    if ( $params->exists("Server.User") );

$params->put("PRODUCTION.HiddenPW", $params->get("Server.HiddenPW"))
    if ( $params->exists("Server.HiddenPW") );

if ( $params->exists("PRODUCTION.HiddenPW") ) {
    my $passwd;
    &dba::nm_UnHide($passwd, $params->get("PRODUCTION.HiddenPW"));
    $params->put("PRODUCTION.Password", $passwd );
}

my $dbhProduction = $app->connect("SERVER",
                    "master",
                    $params->get("PRODUCTION.User"),
                    $params->get("PRODUCTION.Password"));
}

get_server{

my ($dbhProduction) = @_;
my $sql = qq{select distinct d.DBSERVER
          from dbadb..infotable a, MAIN..DB d
          where  1 = 1
          and a.INSTANCE = d.DBSERVER
          and d.REMOVE_FG = 0
          and a.ACTIVE_FG = 1
          order by a.ENVIRON_CD desc};

my $sth = $$dbhProduction->prepare($sql)
    or error($ERROR, "Can't prepate SQL statement [ $sql ] :: $DBI::er
+rstr");

$sth->execute();

my $servers = $sth->fetchall_arrayref();

$sth->finish();

return $servers;

}

get_idvalues{

my $dbhProduction = shift;

my $sql = qq{select l.suid, a.SYSUSR_ID, a.USR_ID, i.SYSIDVALUE
          from MAIN..USR a, syslogins l, MAIN..SYSID i
          where 1=1
          and l.suid = a.SYSUSR_ID
          and i.SYSIDNAME = 'USR_ID'
          group by a.SYSUSR_ID, l.suid, a.USR_ID, i.SYSIDVALUE
          having max(a.SYSUSR_ID) = (select max(suid) from master..sys
+logins)};

my $sth = $$dbhProduction->prepare($sql)
    or app->error($ERROR, "Can't prepare SQL statement [ $sql ] :: $DB
+I::errstr");

$sth->execute();

my $idvalues = $sth->fetchall_arrayref(); 

$sth->finish();

return $idvalues;
}

LINE 203 sub add_login{

my ($dbhCurrent, $usrname, $first, $last, $passwd, $fncgrp) = @_;
my $full_last = $last . " SHPS";

my $sql = "sp_addlogin \'$usrname\', \'$passwd\', \'MAIN\'";

$$dbhCurrent->do($sql)
    or $app->error($WARN, "Can't do SQL statement [ $sql ] :: $DBI::er
+rstr");


$app->use_database($dbhCurrent, "MAIN");

$sql = "exec GET_SYSID USR_ID";

$$dbhCurrent->do($sql)
        or $app->log($WARN,"Can't do SQL statement [ $sql ] :: $DBI::e
+rrstr");

$sql = qq{declare \@usrid int, \@sysusrid int
       select \@sysusrid = suser_id('$usrname')
       select \@usrid = SYSIDVALUE from SYSID
       where SYSIDNAME = 'USR_ID'
       insert into USR (CD, USR_ID, USRNAME, USRFNAME, USRLNAME, FNCGR
+P_CD, SYSUSR_ID)
       values ('A', \@usrid, '$usrname', '$first', '$full_last', $fncg
+rp, \@sysusrid)};

$$dbhCurrent->do($sql)
        or $app->log($FATAL,"Can't do SQL statement [ $sql ] :: $DBI::
+errstr");
Line 230 }

sub get_database{

my $dbhProduction = shift;
my @databases;

my $sql = qq{select distinct name, DBSERVER from master..sysdatabases,
+ MAIN..DB 
    where name like '[A-Z]%' 
    and name not in ('ANLST')
    and name = DBNAME
    and DBSERVER = @@servername
    and REMOVE_FG <> 1
    and DATAMODEL_CD not in (5, 19, 20, 22, 23, 25, 26, 32, 31)
    and status & 32 = 0 
    and status & 256 = 0
    and status & 1024 = 0
    and status <> -32768
    order by name};

my $sth = $$dbhProduction->prepare($sql)
    or error($ERROR, "Can't prepate SQL statement [ $sql ] :: $DBI::er
+rstr");

$sth->execute();
while ( my $database = $sth->fetchrow_array()) {
    push @databases, $database;
}

$sth->finish();
return \@databases;
}

sub add_usr() {

my ($dbhCurrent, $CurrDB, $usrname, $fncgrp) = @_;
my $group = "";

if ( ($CurrDB eq "MAIN") || ($CurrDB eq "MASTER") || ($CurrDB eq "DATA
+") || ($CurrDB eq "NM") ) {
   $group = "readonly";
} elsif ( rindex($CurrDB, "_FIN") > 0 ) {
    if ( ($fncgrp == 2)||($fncgrp == 3) ) {
       $group = "readonly";
       }
    else {
       $group = "readwrite";  ##readwrite
    }
} else {
   $group = "readwrite"; ##readwrite
}

$app->use_database($dbh, $CurrDB);
my $sql = "sp_adduser \'$usrname\', \'$usrname\', \'$group\'";

$$dbhCurrent->do($sql)
       or $app->log($INFO,"Can't do SQL statement [ $sql ] :: $DBI::er
+rstr");

       $app->log($INFO, "User " . $usrname . " has been added to " . $
+CurrDB);
}

sub add_access() {

my ($dbhCurrent, $usrname) = @_;

$app->use_database($dbhCurrent, "MAIN");

my $sql = qq{declare @usrid int
    select @usrid = USR_ID from USR where USRNAME = '$usrname' 
    insert into DBUSR
    select 'A', DB_ID, @usrid, 0, 1 from DB where ENVIRON_CD = 3
    and DATAMODEL_CD = 1 and REMOVE_FG = 0};

$$dbhCurrent->do($sql)
        or $app->log($WARN,"Can't do SQL statement [ $sql ] :: $DBI::e
+rrstr");
}

sub is_existed_usr() {

my ($dbhProduction, $usrname) = @_;

my $sql = qq{select 1 from master..syslogins where name = '$usrname'};

my $sth = $dbhProduction->prepare($sql)
    or error($ERROR, "Can't prepate SQL statement [ $sql ] :: $DBI::er
+rstr");

$sth->execute();

my $flag = $sth->fetchrow_array();
$sth->finish();
$app->disconnect($dbhProduction);

return $flag;
}

sub display_fncgrp() {

my $dbhProduction = shift;

my $result = "";

my $sql = qq{select VALUE, DSC from MASTER..CD
             where CDDSC_ID = 110 order by VALUE};

my $sth = $dbhProduction->prepare($sql)
    or error($ERROR, "Can't prepate SQL statement [ $sql ] :: $DBI::er
+rstr");

$sth->execute();

while ( my ( @array ) = $sth->fetchrow_array() ) {
    $result .= "\t $array[0] \t $array[1] \n";
}

$sth->finish();
$app->disconnect($dbhProduction);

return $result;
}

sub check_opts() {

if ( ! getopts("F:L:P:")) {
 die "create_login.pl -F First Name -L Last Name -P Password \n";
 exit 1;
}

if (! $opt_F) {
print "Enter First Name: ";
$opt_F = <>;
chomp($opt_F);
}

if (! $opt_L) {
print "Enter Last Name: ";
$opt_L = <>;
chomp($opt_L);
}

if (! $opt_P) {
print "Enter Password: ";
$opt_P = <>;
chomp($opt_P);
}

}
[download]