#!/bin/bash
 #==========================================
 #
 #  iq_compare_grants.sh
 #
 #==========================================
 
 server1=$1
 server2=$2
 
 if test $# -ne 2; then
      echo "Parms:  ServerA  ServerB"
      exit
 fi
 
 tfile1=/opt/dba/util/temp/${server1}.dat
 tfile2=/opt/dba/util/temp/${server2}.dat
 tfile3=/opt/dba/util/temp/${server2}_needs.dat
 
 cd /opt/dba/util/scripts
 
 echo "Examining $server1 ...."
 ./iq_perms_listing.sh $server1 > $tfile1
 sleep 2
 
 echo "Examining $server2 ...."
 ./iq_perms_listing.sh $server2 > $tfile2
 sleep 2
 
 echo "Detecting differences .."
 sleep 2
 
 ./file_diff.pl $tfile1 $tfile2 > $tfile3
 
 ct=`grep -i grant $tfile3 | wc -l`
 
 if test $ct -gt 0 ; then
    echo "Warning: $ct grant differences detected."
    sleep 2
    cat $tfile3
    echo " "
    echo "File to check .... $tfile3"
 else
    echo "Compare OK."
 fi
 

 #!/bin/bash
 #==============================================================
 # iq_perms_listing.sh
 #
 # Generate all grant statements for the specified IQ server
 #
 #==============================================================
 
 iqserver=$1
 
 sdir=/opt/dba/util/scripts
 secret=julia123
 sqlfile=iq_perms_listing.sql
 
 isqlx=${sdir}/isql.sh
 
 cd $sdir
 
 echo "ISQL .."
 
 $isqlx $iqserver -Udba -P$secret -i$sqlfile > _perms1.dat
 
 echo "Perl .."
 
 ./iq_perms_listing.pl _perms1.dat _perms2.dat
 
 echo "Done .."
 sleep 1
 
 cat _perms2.dat
 

 -- iq_perms_listing.sql
 
 select substring(t4.user_name,1,25) as USER1, substring(t1.table_name,1,44) as TNAME, substring(t2.user_name,1,25) as USER2,
 t3.selectauth, t3.insertauth, t3.deleteauth, t3.updateauth
 from    SYSTABLE t1, SYSUSERPERM t2, SYSTABLEPERM t3, SYSUSERPERM t4
 where   t3.stable_id = t1.table_id and
         t2.user_id   = t3.grantee  and
         t4.user_id   = t1.creator
 order by 1,2
 go
 

 #!/usr/bin/perl
 #==============================================================
 # iq_perms_listing.pl
 #
 # Filter / generate grant SQL
 #
 #==============================================================
 
 $infile   = $ARGV[0];
 $outfile  = $ARGV[1];
 
 open (FILE1,"<$infile") or die "Error .. FILE1\n\n" ;
 open (FILE2,">$outfile") or die "Error .. FILE2\n\n" ;
 
 while ()
   {
   $line1 = $_ ;
   chomp $line1;
 
   if ($line1 =~ / PUBLIC /)
      { $line1 = ' '; }
   if ($line1 =~ / dbo /)
      { $line1 = ' '; }
 
   @line1 = split ' ', $line1 ;
 
   $owner_user  = shift @line1 ;
   $tname  = shift @line1 ;
   $user   = shift @line1 ;
   $select = shift @line1 ;
   $insert = shift @line1 ;
   $delete = shift @line1 ;
   $update = shift @line1 ;
 
   if ($select eq 'Y')
        {  print FILE2  "grant select on $owner_user.$tname to $user;\n" }
   if ($update eq 'Y')
        {  print FILE2  "grant update on $owner_user.$tname to $user;\n" }
   if ($delete eq 'Y')
        {  print FILE2  "grant delete on $owner_user.$tname to $user;\n" }
   if ($insert eq 'Y')
        {  print FILE2  "grant insert on $owner_user.$tname to $user;\n" }
 
   }
 
 close (FILE1);
 close (FILE2);