#!/u01/app/oracle/oraperl/bin/perl

#    NAME
#      dbcool_audit.pl
#    DESCRIPTION
#      Perl script for performing a basic Oracle security audit
#    USAGE
#      dbcool_audit.pl userid=<u/p> [tns=<Alias>]
#    NOTES
#      Writes results to stdout, look out for lines with ! or !!
#      Requires a Perl interpreter linked with Oracle
#      If tns= not supplied, uses database identified by $ORACLE_SID
#    RETURNS 
#      N/A

   use Oraperl; # for Oracle interface
   use CGI;     # for command line processing
   $query = new CGI;
   
   # get command line parameters
   ($userid,$tns) = process_cmd_args();
   
   $use_tns='';
   if ($tns) { $userid="$userid\@$tns"; }
   if ($tns) { $use_tns="\@$tns"; }

   # get a userid handle for the session or exit program
   $session = &ora_login('', $userid, '');
   die "ERROR: login\n$ora_errstr" if $ora_errno;

   $c_db = &ora_open($session, "select g.*,user from global_name g");
   die $ora_errstr if $ora_errno;
   ($dbname,$sess_user) = &ora_fetch($c_db);
   die $ora_errstr if $ora_errno;
   &ora_close($c_db);
   print "\nUser $sess_user running audit for db=$dbname on " . `date`;

   # get a cursor for selecting the values
   $c_user = &ora_open($session, 
             "select username from dba_users order by username");
   warn $ora_errstr if $ora_errno;

   # fetch the users from the database
   while (($user) = &ora_fetch($c_user))
   {
       $password_match='n';
       $user_session =  &ora_login('', "$user/$user" . $use_tns, ''); 
   
       if (not $ora_errno) # fetch worked, ERROR
       {
          print "\nWARNING!: userid=$user password same as account name";
          $password_match='y';
          &ora_logoff($user_session);
       }
       else
       {
          print "\nPASSED: userid=$user password different from account name";
       }
       if ($user eq "SYSTEM")
       {
          $user_session =  &ora_login('',"system/manager" . $use_tns, ''); 
          if (not $ora_errno) # fetch worked, ERROR
          {
             print "\nWARNING!: SYSTEM has default password MANAGER";
             &ora_logoff($user_session);
          }
          else
          {
             print "\nPASSED: SYSTEM has non-default password";
          }
       }

       if ($user eq "SYS")
       {
          $user_session =  &ora_login('',"sys/change_on_install" . $use_tns, ''); 
          if (not $ora_errno) # fetch worked, ERROR
          {
             print "\nWARNING!: SYS has default password CHANGE_ON_INSTALL";
             &ora_logoff($user_session);
          }
          else
          {
             print "\nPASSED: SYS has non-default password";
          }
       }

       show_privs ( $session, $user);
       print "\n";
   }
       
   print "\n";
   &ora_close($c_user);
   
   &ora_logoff($session);

sub show_privs()
{
   my($l_session,$l_user) = @_;
   my($sql_sys_priv);
   my($c_sys_priv);

   uc($l_user);

   if ($l_user eq 'SYS' or $l_user eq 'SYSTEM')
   {
      return;
   }

   $sql_sys_priv = "select privilege,  decode(ADMIN_OPTION,'YES','ADMIN OPTION',ADMIN_OPTION) \n" .
                   "from   dba_sys_privs p,sys.user\$ u\n" .
                   "where  grantee='$l_user'\n" .
                   "and    p.grantee=u.name\n" .
                   "and    u.type# =1";
   $c_sys_priv = &ora_open($l_session, $sql_sys_priv);
   if ($ora_errno)
   {
      print "\nCould not open $sql_sys_priv\n$ora_errstr";
      return;
   } 

   print "\n     System privs:";
   while (($priv,$admin_option) = &ora_fetch($c_sys_priv))
   {
       my($warning)="";
       $warning = warn_priv("$priv $admin_option");
       if ($password_match eq 'y') { $warning = "!$warning" }; # add an extra !
       print "\n        $warning $priv $admin_option";
   }
   &ora_close($c_sys_priv);



   $sql_tab_priv= "select GRANTOR, OWNER, TABLE_NAME, PRIVILEGE ,decode(GRANTABLE,'YES','WITH GRANT OPTION',GRANTABLE)\n" .
                  "from dba_tab_privs p, sys.user\$ u\n" . 
                  "where  grantee='$l_user'\n" . 
                  "and    p.grantee=u.name\n" .
                  "and    u.type# =1\n" .
                  "order  by 1,2,3";

   $c_tab_priv = &ora_open($l_session, $sql_tab_priv);
   if ($ora_errno)
   {
      print "\nCould not open $sql_tab_priv\n$ora_errstr";
      return;
   } 

   print "\n     Table privs:";
   while (($grantor,$owner,$table_name,$privilege,$grantable) = &ora_fetch($c_tab_priv))
   {
       my($warning)="";
       $warning = warn_priv("$privilege $grantable");
       print "\n        $warning $grantor $owner $table_name $privilege $grantable";
   }
   &ora_close($c_tab_priv);


   ## prompt DBA_ROLE_PRIVS
   $sql_role_priv = "select GRANTED_ROLE, decode(ADMIN_OPTION,'YES','ADMIN OPTION',ADMIN_OPTION), DEFAULT_ROLE\n" .
                    "from dba_role_privs p,sys.user\$ u\n" .
                    "where  p.grantee='$l_user'\n" .
                    "and    p.grantee=u.name\n" .
                    "and    u.type# =1\n" .
                    "order by 1";

   $c_role_priv = &ora_open($l_session, $sql_role_priv);
   if ($ora_errno)
   {
      print "\nCould not open $sql_role_priv\n$ora_errstr";
      return;
   } 

   print "\n     Role privs:";
   while (($granted_role,$admin_option,$default_role) = &ora_fetch($c_role_priv))
   {
       my($warning)="";
       $warning = warn_priv("$granted_role $admin_option");
       if ($password_match eq 'y') { $warning = "!$warning" }; # add an extra !
       print "\n        $warning $granted_role $admin_option"; 
   }
   &ora_close($c_role_priv);


}

sub warn_priv()
{
   my($priv) = @_;
   my($warning) = "";
  
   SWITCH:
   {
      if ($priv =~ m/ADMIN OPTION/) { $warning='!'; last SWITCH;}
      if ($priv =~ m/ALTER USER/) { $warning='!'; last SWITCH;}
      if ($priv =~ m/ANY /) { $warning='!'; last SWITCH;}
      if ($priv =~ m/CREATE USER/) { $warning='!'; last SWITCH;}
      if ($priv =~ m/DBA /) { $warning='!'; last SWITCH;}
      if ($priv =~ m/DROP USER/) { $warning='!'; last SWITCH;}
      if ($priv =~ m/FORCE/) { $warning='!'; last SWITCH;}
      if ($priv =~ m/PUBLIC /) { $warning='!'; last SWITCH;}
      if ($priv =~ m/RESOURCE/) { $warning='!'; last SWITCH;}
      if ($priv =~ m/RESTRICTED/) { $warning='!'; last SWITCH;}
      if ($priv =~ m/TABLESPACE/) { $warning='!'; last SWITCH;}
      if ($priv =~ m/UNLIMITED/) { $warning='!'; last SWITCH;}
   }   

   return $warning;
}


#------------------------process_cmd_args-----------------------
sub process_cmd_args
{
   use CGI;     # for command line processing
   my($query) = new CGI;
   my($arg_userid,$arg_tns,$arg_help);

   # get command line parameters
   my($usage)="usage: dbcool_audit.pl userid=u/p [tns=<Alias>] [help=y]\n";

   my @param_names = $query->param; # get command line arguments
   
   if ($#param_names == -1) # no params supplied
   {
      print "$usage";
      exit 1;
   }

   $arg_userid = $query->param('userid');
   $arg_tns = $query->param('tns');
   $arg_help = $query->param('help');

   if ($arg_help && $arg_help eq 'y')
   {
      print "$usage";
      exit 0;
   }

   return $arg_userid,$arg_tns;

} # sub process_cmd_args