#!/nms/site/bin/perl

use strict;
use DBI;
use ebenx_dba_app;
use Getopt::Std;
use vars qw( $dbhProduction $dbhCurrent $app );
use vars qw($opt_F $opt_L $opt_P);
 

###Begin subs

main();
exit();

sub main(){

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

my $prdserver; 
$params->put("Server.Name", "MSP2PRDDB00");
$prdserver = $params->get("Server.Name");

prd_connect($prdserver);
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(\$dbhProduction, $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;
print "$CurrServer\n";
my $CURR_SERVER_PARAM_FILE = "/nms/system/etc/sybase/servers/$CurrServ
+er.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;
        &ebenx::nm_UnHide($passwd, $params->get("Server.HiddenPW"));
        $params->put("Current.Password", $passwd );
}

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

sub prd_connect{

my $prdserver = shift;
my $PROD_SERVER_PARAM_FILE = "/nms/system/etc/sybase/servers/$prdserve
+r.conf";

$params->loadFile($PROD_SERVER_PARAM_FILE)
    if ( -r $PROD_SERVER_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("Server.HiddenPW") ) {
    my $passwd;
    &ebenx::nm_UnHide($passwd, $params->get("Server.HiddenPW"));
    $params->put("PRODUCTION.Password", $passwd );
}

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

sub get_server{

my ($dbh) = @_;
my $sql = qq{select distinct d.DBSERVER
          from dbainfo..ASEINSTALL a, APPMASTER..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 = $$dbh->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;

}

sub get_idvalues{

my ($dbh) = @_;

my $sql = qq{select l.suid, a.SYSUSR_ID, a.USR_ID, i.APPSYSIDVALUE
          from APPMASTER..APPUSR a, syslogins l, APPMASTER..APPSYSID i
          where 1=1
          and l.suid = a.SYSUSR_ID
          and i.APPSYSIDNAME = 'USR_ID'
          group by a.SYSUSR_ID, l.suid, a.USR_ID, i.APPSYSIDVALUE
          having max(a.SYSUSR_ID) = (select max(suid) from master..sys
+logins)};

my $sth = $$dbh->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;
}

sub add_login{

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

my $sql = "sp_addlogin \'$usrname\', \'$passwd\', \'APPMASTER\'";
print "sql\n";

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


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

$sql = "exec AW_GET_APPSYSID USR_ID";
print "sql\n";

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

$sql = qq{declare \@usrid int, \@sysusrid int
       select \@sysusrid = suser_id('$usrname')
       select \@usrid = APPSYSIDVALUE from APPSYSID
       where APPSYSIDNAME = 'USR_ID'
       insert into APPUSR (APP_CD, USR_ID, USRNAME, USRFNAME, USRLNAME
+, FNCGRP_CD, SYSUSR_ID)
       values ('A', \@usrid, '$usrname', '$first', '$full_last', $fncg
+rp, \@sysusrid)};

print "sql\n";
#$$dbh->do($sql)
#        or $app->log($FATAL,"Can't do SQL statement [ $sql ] :: $DBI:
+:errstr");
}

sub get_database{

my $dbh = shift;
my @databases;

my $sql = qq{select distinct name, DBSERVER from master..sysdatabases,
+ APPMASTER..DB 
    where name like '[A-Z]%' 
    and name not like '%EBENX%'
    and name not like '%SHPS%'
    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};

print "sql\n";
my $sth = $$dbh->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 ($dbh, $CurrDB, $usrname, $fncgrp) = @_;
my $group = "";

if ( ($CurrDB eq "APPMASTER") || ($CurrDB eq "ENRMASTER") || ($CurrDB 
+eq "METADATA") || ($CurrDB eq "NMMASTER") ) {
   $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($dbhCurrent, $CurrDB);
my $sql = "sp_adduser \'$usrname\', \'$usrname\', \'$group\'";
print "sql\n";

#$$dbh->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 ($dbh, $usrname) = @_;

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

my $sql = qq{declare \@usrid int
    select \@usrid = USR_ID from APPUSR where USRNAME = '$usrname' 
    insert into APPDBUSR
    select 'A', DB_ID, \@usrid, 0, 1 from DB where ENVIRON_CD = 3
    and DATAMODEL_CD = 1 and REMOVE_FG = 0
    and DBNAME not in ('EBENX','T_EBENX')};

print "$sql\n";
#$$dbh->do($sql)
#        or $app->log($WARN,"Can't do SQL statement [ $sql ] :: $DBI::
+errstr");
}

sub is_existed_usr{

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

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

my $sth = $$dbh->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($$dbh);

return $flag;
}

sub display_fncgrp{

my ($dbh) = shift; 

my $result = "";

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

my $sth = $$dbh->prepare($sql)
    or error($ERROR, "Can't prepare 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($$dbh);

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]