#!/usr/bin/ksh
# set -xv
# B. Polarski
# 06 June 2005
WK_SBIN=$SBIN/module2/s3
HOST=`hostname`
HOST=`echo $HOST | awk '{ printf ("%-+15.15s",$1)  }'`

# -------------------------------------------------------------------------------------
function help
{
   cat <<EOF

     Auditing suppose that init.ora parameter 'audit_trail'=TRUE or there is no rows kept

    Command : 

         aud -a -p -o -m -u <USER> -prm

               -a : List Active Statement Audit Options
               -p : List Active Privilege Audit Options
               -o : List Active Objects Audit Options
               -m : List last connection for user
             -prm : List audit related parameters

         aud -lp  : List policy rules from fga$
         aud -ll  : audit last statement inserted into fga_log$
         aud -t <OJBECT_NAME> [-u <OWNER>]  : Get the policy name from a given object
         aud -ll -n <OBJECT_NAME> [-u <OWNER>] : restrict search  to given arguments

         -v   : verbose         
EOF
}
# -------------------------------------------------------------------------------------
function do_execute
{
$SETXV
sqlplus -s "$CONNECT_STRING" <<EOF
ttitle skip 2 'MACHINE $HOST - ORACLE_SID : $ORACLE_SID '  right 'Page:' format 999 sql.pno skip 2
column nline newline
set pagesize 66 linesize 100 termout on pause off embedded on verify off heading off
select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER ||  '      $TTITLE (aud -h for help)' nline
from sys.dual
/
set head on
COL user_name           FORMAT A25      HEADING 'User audited'
COL audit_option        FORMAT A50      HEADING 'Auditing action'
$BREAK
set linesize 125
prompt
$SQL
EOF
}
# -------------------------------------------------------------------------------------
#                    Main
# -------------------------------------------------------------------------------------

if [ -z "$1" ];then
   help
fi
typeset -u PAR2
typeset -u FOBJECT
ROWNUM=30
while [ -n "$1" ]
do
  case "$1" in
       -a ) ACTION=AUDIT_ST ; EXECUTE=YES ; TTITLE="List Audit Statement in DB" ;;
       -t ) ACTION=GET_FGA_NAME ; FOBJECT=$2 ; shift ; EXECUTE=YES ;;
      -lp ) ACTION=LIST_FGA_POLICY ; OBJ=$2 ; EXECUTE=YES ;;
      -ls ) ACTION=LIST_FGA_LOG ; EXECUTE=YES ;;
      -ll ) ACTION=LIST_LAST_FGA_LOG ; EXECUTE=YES ;;
       -n ) FOBJECT=$2 ; shift ;;
       -m ) ACTION=LIST_CONNECT ; EXECUTE=YES ; TITTLE="List last logong for $2"  ;;
       -o ) ACTION=OBJECT ; EXECUTE=YES ; TTITLE="List active object Audit in DB" ;;
       -p ) ACTION=PRIVS ; EXECUTE=YES ; TTITLE="List Audit Privilege in DB" ;;
     -prm ) ACTION=PRM ; EXECUTE=YES ; TITTLE="List audit specific parameters" ;;
       -u ) PAR2=$2
            A_USER=" and a.username = '$PAR2'" ; U_USER=" and u.username = '$PAR2'" 
            F_USER=" and user_name = '$PAR2'"; shift ;;
       -v ) SETXV="set -xv";;
       -x ) EXECUTE=YES;;
       -h ) help ;;
      -rn ) ROWNUM=$2 ; shift ;; 
        * ) echo "What is $1 ? "; help ;;
  esac
  shift
done


. $SBIN/scripts/passwd.env
. ${GET_PASSWD} $S_USER $ORACLE_SID
if [  "x-$CONNECT_STRING" = "x-" ];then
   echo "could no get a the password of SYS"
   exit 0
fi
# ...............................................................................................
# Retrieve the name of policy rules for given object name
# ...............................................................................................
if [ "$ACTION" = "PRM" ];then

SQL="
COL name                FORMAT A20      HEADING 'Parameter'
COL value               FORMAT A40      HEADING 'Value'
COL description         FORMAT A50      HEADING 'Description'
select name, value, description from v\$parameter where name like '%audit%' ;
"
# ...............................................................................................
# Retrieve the name of policy rules for given object name
# ...............................................................................................
elif [ "$ACTION" = "GET_FGA_NAME" ];then
   TTITLE="Get  data from fga_log\$"
   SQL="prompt Wlidcard accepted (%) : aud -t TBL%
prompt
set lines 190
col object_schema for a18 head 'Owner'
col pname for a20 head 'Policy name'
col POLICY_TEXT for a55 head 'Policy check text' truncate
col object_name for a18

select OBJECT_SCHEMA, OBJECT_NAME, POLICY_NAME pname,  ENABLED, SEL, INS, UPD, DEL, ' '||POLICY_TEXT policy_text
       from (
        select OBJECT_SCHEMA, OBJECT_NAME, POLICY_NAME, POLICY_TEXT, ENABLED, SEL, INS, UPD, DEL
          from DBA_AUDIT_POLICIES where  object_name like '$FOBJECT' $AND_OWNER )
where rownum <= $ROWNUM ;
"
# ...............................................................................................
# list rules
# ...............................................................................................
elif [ "$ACTION" = "LIST_FGA_POLICY" ];then
   if [ -n "$FOBJECT" ];then
        AND_FOBJECT=" and object_name = '$FOBJECT' "
   fi
   if [ -n "$PAR2" ];then
        AND_OWNER=" and object_schema = '$PAR2' "
   fi
   TTITLE="List FGA rules"
   SQL="set lines 190
prompt use aud -lp -rn <nn> to see more rows
prompt
col policy_text for a60
col policy_name for a14 head 'Policy|Name'
col object_name for a26 head 'Object Name'
col object_schema for a18 head 'Owner'
select OBJECT_SCHEMA, OBJECT_NAME, POLICY_NAME,
                  ENABLED, SEL, INS, UPD, DEL , POLICY_TEXT
from
 (
select OBJECT_SCHEMA, OBJECT_NAME, POLICY_NAME, POLICY_TEXT,
                  ENABLED, SEL, INS, UPD, DEL
          from DBA_AUDIT_POLICIES where 1=1 $AND_FOBJECT $AND_OWNER
)where rownum <= $ROWNUM ;

"
# ...............................................................................................
# list last log entries
# ...............................................................................................
elif [ "$ACTION" = "LIST_LAST_FGA_LOG" ];then
 
   if [ -n "$FOBJECT" ];then
        AND_FOBJECT=" and OBJECT_NAME = '$FOBJECT' "
   fi
   if [ -n "$PAR2" ];then
        AND_OWNER=" and OBJECT_SCHEMA = '$PAR2' "
   fi

   TTITLE="Get  data from fga_log\$"
   SQL="set lines 190
col object_name for a22
col sql_text for a55 truncate
col owner for a18
col SCN for 99999999999999
col INSTANCE_number for 99 head 'In|st'

set longchunksize 60


select INSTANCE_number,  owner, object_name, fdate, scn,   statement_type type, sql_text from (
select INSTANCE_number, OBJECT_SCHEMA owner, object_name, 
        to_char(timestamp,'YYYY-DD-MM HH24:MI:SS') fdate, scn,  statement_type, sql_text 
        from dba_fga_audit_trail f  where 1=1 $AND_FOBJECT $AND_OWNER
        order by timestamp desc
) where rownum <=$ROWNUM;
"
# ...............................................................................................
# ...............................................................................................
elif [ "$ACTION" = "AUDIT_ST" ];then
   SQL="break on user
   select user_name, audit_option, success, failure from DBA_STMT_AUDIT_OPTS where 1=1 $F_USER order by user ;"

# ...............................................................................................
# ...............................................................................................
elif [ "$ACTION" = "LIST_CONNECT" ];then
   SQL="SELECT distinct (u.username), to_char(last_logoff,'DD-MM-YYYY HH24:MI:SS') last_logoff
  FROM dba_users u,
       (select max(logoff_time) last_logoff, username from dba_audit_trail group by username) b
  WHERE u.username = b.username (+) $U_USER ;
"
# ...............................................................................................
# ...............................................................................................
elif [ "$ACTION" = "PRIVS" ];then
   SQL="break on user
   select * from dba_priv_audit_opts ;"
# ...............................................................................................
# ...............................................................................................
elif [ "$ACTION" = "OBJECT" ];then
     SQL="break on owner
      SELECT * FROM DBA_OBJ_AUDIT_OPTS  order by owner, object_type;"
fi


if [ "$EXECUTE" = "YES" ];then
   do_execute
else
  echo "$SQL"
fi