пятница, 25 января 2013 г.

Настройка аудита Oracle



ALTER system SET audit_sys_operations=TRUE scope=spfile;
ALTER system SET audit_trail=db_extended scope=spfile;


shutdown immediate;
startup


BEGIN
  DBMS_AUDIT_MGMT.INIT_CLEANUP(
    AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    DEFAULT_CLEANUP_INTERVAL => 24
  );
END;
/
commit;



CREATE TABLESPACE "AUDIT_DATA" DATAFILE SIZE 314572800 AUTOEXTEND ON NEXT 104857600 MAXSIZE 29696M;


BEGIN
  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
    AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    AUDIT_TRAIL_LOCATION_VALUE => 'AUDIT_DATA'
  );
END;
/
commit;



BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name   => 'DAILY_AUDIT_ARCHIVE_TIMESTAMP',
    job_type   => 'PLSQL_BLOCK',
    job_action => 'BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
                                                                    LAST_ARCHIVE_TIME => sysdate-1); END;',
    start_date => sysdate,
    repeat_interval => 'FREQ=HOURLY;INTERVAL=24',
    enabled    =>  TRUE,
    comments   => 'Create an archive timestamp'
  );
END;
/

Create daily purge job:
BEGIN
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
    AUDIT_TRAIL_TYPE           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    AUDIT_TRAIL_PURGE_INTERVAL => 24,
    AUDIT_TRAIL_PURGE_NAME     => 'Daily_Audit_Purge_Job',
    USE_LAST_ARCH_TIMESTAMP    => TRUE
  );
END;
/
commit;

Руками аудит можно удалить так:

BEGIN
  DBMS_AUDIT_MGMT.clean_audit_trail(
   audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   use_last_arch_timestamp => TRUE);
END;
/

Просмотр настроек  аудита:

SELECT PARAMETER_NAME, PARAMETER_VALUE, AUDIT_TRAIL
 FROM DBA_AUDIT_MGMT_CONFIG_PARAMS
 WHERE audit_trail = 'STANDARD AUDIT TRAIL';

SELECT owner,segment_name,tablespace_name
 FROM dba_segments
 WHERE segment_name ='AUD$';


SELECT  * FROM DBA_STMT_AUDIT_OPTS;

SELECT * FROM DBA_PRIV_AUDIT_OPTS;

SELECT * FROM DBA_OBJ_AUDIT_OPTS;

SELECT JOB_NAME,JOB_STATUS,AUDIT_TRAIL,JOB_FREQUENCY FROM DBA_AUDIT_MGMT_CLEANUP_JOBS;

SELECT job_name, next_run_date, state, enabled FROM dba_scheduler_jobs WHERE job_name LIKE '%AUDIT%';


Просмотр данных  аудита:

select * from  sys.aud$        
select * from  DBA_AUDIT_TRAIL


select * from  DBA_AUDIT_EXISTS  

select * from  DBA_AUDIT_OBJECT  

select * from  DBA_AUDIT_SESSION 

select * from  DBA_AUDIT_STATEMENT

Активность пользователей

select username, to_char(logoff_time,'mm/dd') ts,
sum(logoff_lread) lread,
sum(logoff_pread) pread,
sum(logoff_lwrite) lwrite,
sum(session_cpu) scpu
from dba_audit_trail
where logoff_time between TO_DATE('01.01.2013:00:00:00','DD.MM.YYYY:HH24:MI:SS')
                      and TO_DATE('30.01.2013:23:59:59','DD.MM.YYYY:HH24:MI:SS')
group by username, to_char(logoff_time,'mm/dd')
order by username, to_char(logoff_time,'mm/dd')


CPU Usage

select username, to_char(logoff_time,'mm/dd') ts,
count(1) cnt,
sum(session_cpu) sum_cpu, avg(session_cpu) avg_cpu,
min(session_cpu) min_cpu, max(session_cpu) max_cpu
from dba_audit_trail
where logoff_time between TO_DATE('01.01.2013:00:00:00','DD.MM.YYYY:HH24:MI:SS')
                      and TO_DATE('30.01.2013:23:59:59','DD.MM.YYYY:HH24:MI:SS')
group by username, to_char(logoff_time,'mm/dd')
order by username, to_char(logoff_time,'mm/dd')


Trace Account Locks

select to_char(timestamp,'mm/dd/yy hh24:mi') ts,
os_username, userhost, returncode
from dba_audit_trail
--where username = 'SCOTT'
order by timestamp;


0     - ok
1017  - wrong password
28000 - account locked
.....

List  audit information:

SELECT USERNAME, LOGOFF_TIME, LOGOFF_LREAD, LOGOFF_PREAD, LOGOFF_LWRITE, LOGOFF_DLOCK
    FROM DBA_AUDIT_SESSION;


SELECT username, extended_timestamp, owner, obj_name, action_name,   ses_actions,
      DECODE (ses_actions,
              '---S------------', 'DELETE',
              '------S---------', 'INSERT',
              '----------S-----', 'UPDATE',
              '------------S---', 'EXECUTE',
              '---------S------', 'SELECT',
              'S----------------','ALTER'
      ) transactions
FROM dba_audit_trail
WHERE
      TO_CHAR(extended_timestamp, 'DD.MM.YYYY:HH24:MI:SS') >= '01.01.2013:00:00:00'
  AND TO_CHAR(extended_timestamp, 'DD.MM.YYYY:HH24:MI:SS') <= '30.01.2013:23:59:59'
  AND username NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'EXFSYS')
ORDER BY extended_timestamp DESC;


select spare1           /* OS_USERNAME */,
       userid               /* USERNAME */,
       userhost           /* USERHOST */,
       terminal            /* TERMINAL */,
       to_char(cast (          
           (from_tz(aud.ntimestamp#,'00:00') at local) as date),'mm/dd/yy hh24:mi') ts,
       obj$creator      /* OWNER */,
       obj$name         /* OBJECT_NAME */,
       aud.action#      /* ACTION */,
       act.name          /* ACTION_NAME */
from sys.aud$ aud, system_privilege_map spm, system_privilege_map spx,
     STMT_AUDIT_OPTION_MAP aom, audit_actions act
where   aud.action#     = act.action    (+)
  and - aud.logoff$dead = spm.privilege (+)
  and   aud.logoff$dead = aom.option#   (+)
  and - aud.priv$used   = spx.privilege (+)
  -- and   userid = 'SCOTT'
  -- and act.name = 'ALTER USER'
  and aud.ntimestamp# between TO_DATE('01.01.2013:00:00:00','DD.MM.YYYY:HH24:MI:SS')
                          and TO_DATE('31.07.2013:23:59:59','DD.MM.YYYY:HH24:MI:SS')
order by ts;



Создаем пользователей для системы ARCSIGHT:


-- Run from user SYSTEM:

-- :
create user arcsight identified by "*****";
grant create session to ARCSIGHT;


-- :
create user ARCSIGHT_PRIV identified by "*****";
grant create session to ARCSIGHT_PRIV;
grant create view to ARCSIGHT_PRIV;



Предоставим доступ к словарю БД пользователю  ARCSIGHT:

-- Run from user SYS:
grant select on sys.dba_common_audit_trail to ARCSIGHT_PRIV with grant option;
grant select on sys.v_$instance            to ARCSIGHT_PRIV with grant option;
grant select on sys.v_$session             to ARCSIGHT_PRIV with grant option;
grant select on sys.user$                  to ARCSIGHT_PRIV with grant option;


Создаем представление:

-- Run from user ARCSIGHT_PRIV;
create or replace view ARCSIGHT_PRIV.AUDIT_ALL as
SELECT
  dba_common_audit_trail.AUDIT_TYPE       as AUDIT_TYPE,
  dba_common_audit_trail.SESSION_ID       as SID,
  dba_common_audit_trail.PROXY_SESSIONID  as PROXY_SESSIONID,
  dba_common_audit_trail.STATEMENTID      as STATEMENTID,
  dba_common_audit_trail.ENTRYID          as ENTRYID,
  cast (dba_common_audit_trail.EXTENDED_TIMESTAMP as date) as EXTENDED_TIMESTAMP,
  dba_common_audit_trail.GLOBAL_UID       as GLOBAL_UID,
  dba_common_audit_trail.DB_USER          as USERNAME,
  dba_common_audit_trail.CLIENT_ID        as CLIENT_ID,
  dba_common_audit_trail.EXT_NAME         as EXT_NAME,
  dba_common_audit_trail.OS_USER          as OS_USERNAME,
  dba_common_audit_trail.USERHOST         as USERHOST,
  dba_common_audit_trail.OS_PROCESS       as OS_PROCESS,
  dba_common_audit_trail.TERMINAL         as TERMINAL,
  dba_common_audit_trail.INSTANCE_NUMBER  as INSTANCE_NUMBER,
  dba_common_audit_trail.OBJECT_SCHEMA    as OBJECT_SCHEMA,
  dba_common_audit_trail.OBJECT_NAME      as OBJECT_NAME,
  dba_common_audit_trail.ACTION           as ACTION,
  dba_common_audit_trail.STATEMENT_TYPE   as ACTION_NAME,
  dba_common_audit_trail.RETURNCODE       as RETURNCODE,
  dba_common_audit_trail.SCN              as SCN,
  dba_common_audit_trail.COMMENT_TEXT     as COMMENT_TEXT,
  dba_common_audit_trail.SQL_TEXT         as SQL_TEXT,
  dba_common_audit_trail.OBJ_PRIVILEGE    as OBJ_PRIVILEGE,
  dba_common_audit_trail.ADMIN_OPTION     as ADMIN_OPTION,
  dba_common_audit_trail.OS_PRIVILEGE     as OS_PRIVILEGE,
  dba_common_audit_trail.GRANTEE          as GRANTEE,
  dba_common_audit_trail.PRIV_USED        as PRIV_USED,
  dba_common_audit_trail.SES_ACTIONS      as SES_ACTIONS,
  v$session.PROGRAM                       as PROGRAM,
  V$Instance.HOST_NAME                    as HOST_NAME,
  V$Instance.VERSION                      as VERSION,
  V$Instance.INSTANCE_NAME                as INSTANCE_NAME
   FROM  dba_common_audit_trail, V$Instance, V$session
WHERE V$session.AUDSID(+)=dba_common_audit_trail.SESSION_ID
  and (
    (dba_common_audit_trail.ACTION = 100 and dba_common_audit_trail.RETURNCODE <> 0)
    OR
    (dba_common_audit_trail.ACTION <> 100 and dba_common_audit_trail.AUDIT_TYPE<>'Mandatory XML Audit')
  )
  union all
  SELECT v$session.type || ' Audit' as AUDIT_TYPE,
       v$session.AUDSID as SID, null,null,null,
               cast (v$session.LOGON_TIME as date) as EXTENDED_TIMESTAMP, null,
               v$session.USERNAME, null,null,
               v$session.OSUSER as OS_USERNAME,
               v$session.MACHINE as USERHOST,
               null,
               v$session.TERMINAL, null,
               v$session.SCHEMANAME as OBJECT_SCHEMA,
               null,100,'Succesful_Logon',0,null,
               v$session.event as COMMENT_TEXT,
               null,null,null,null,null,null,null,
               v$session.PROGRAM as PROGRAM,
               V$Instance.HOST_NAME,
               V$Instance.VERSION,
               V$Instance.INSTANCE_NAME
   FROM        v$session, v$instance
/

grant select on ARCSIGHT_PRIV.AUDIT_ALL to ARCSIGHT;



Проверяем работу представления под пользователем  ARCSIGHT:

-- Run from user ARCSIGHT:
select *
from ARCSIGHT_PRIV.AUDIT_ALL;


Можем еще создать дополнительные представления:

-- Run from user ARCSIGHT_PRIV;
create or replace view ARCSIGHT_PRIV.AUDIT_PWD as
select name, ctime, ltime, ptime, exptime from sys.user$;

grant select on ARCSIGHT_PRIV.AUDIT_PWD to ARCSIGHT;


-- Run from user ARCSIGHT:
select *
from ARCSIGHT_PRIV.AUDIT_PWD;




Очистка аудита:


# Clean system audit
06 04 * * * ${HOME}/bin/pkg/clean_sys_audit/_run.sh 1 > /dev/null 2>> /tmp/crontab_oracle.err



$more _run.sh

#!/bin/bash


#
# Package Name:
#

tmp_string="`dirname $0`"
pkg_name="`basename ${tmp_string}`"

if [ "${pkg_name}" = "." ]; then
  tmp_string="`pwd`"
  pkg_name="`basename ${tmp_string}`"
fi



# Check Parameters:
if [ $1 ]; then
  retention_period=$1
else
  echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: Error! (Parameter=2). Call `basename $0` " >&2
  exit 2
fi


#
# Setup Environment:
#

pkg_env="${HOME}/bin/_conf/.env"
if [ ! -r "${pkg_env}" ]; then
  echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Not Found Environment file: ${pkg_env}" >&2
  exit 2
fi

if [ -s "${pkg_env}" ]; then
  . ${pkg_env}
else
  echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment file is empty: ${pkg_env}" >&2
  exit 2
fi


#
# Check Environment:
#

# Check ORACLE_USER:
if [ -z "${ORA_USER}" ]; then
  echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment variable ORA_USER not defined" >&2
  exit 2
else
  ora_user=${ORA_USER}
fi


# Check ORACLE_BASE:
if [ -z "${ORACLE_BASE}" ]; then
  echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment variable ORACLE_BASE not defined" >&2
  exit 2
else
  oracle_base=${ORACLE_BASE}
fi


# Check ORACLE_HOME:
if [ -z "${ORACLE_HOME}" ]; then
  echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment variable ORACLE_HOME not defined" >&2
  exit 2
elif [ ! -d "${ORACLE_HOME}" ]; then
  echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Directory ORACLE_HOME not found (${ORACLE_HOME})" >&2
  exit 2
fi


# Check TMP_DIR:
if [ -z "${TMP_DIR}" ]; then
  echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment variable TMP_DIR not defined" >&2
  exit 2
elif [ ! -d "${TMP_DIR}" ]; then
  echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Directory TMP_DIR not found (${TMP_DIR})" >&2
  exit 2
elif [ ! -w "${TMP_DIR}" ]; then
  echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Directory TMP_DIR not writeable (${TMP_DIR})" >&2
  exit 2
fi


# Check BIN_DIR:
if [ -z "${BIN_DIR}" ]; then
  echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment variable BIN_DIR not defined" >&2
  exit 2
elif [ ! -d "${BIN_DIR}" ]; then
  echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Directory BIN_DIR not found (${BIN_DIR})" >&2
  exit 2
fi


# Check COMPANY_NAME:
if [ -z "${COMPANY_NAME}" ]; then
  echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment variable COMPANY_NAME not defined" >&2
  exit 2
fi


# Check HOST_NAME:
if [ -z "${HOST_NAME}" ]; then
  echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment variable HOST_NAME not defined" >&2
  exit 2
fi


# Check MAIL_RECIPIENTS:
if [ -z "${MAIL_DBA}" ]; then
  echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment variable MAIL_RECIPIENTS not defined" >&2
  exit 2
fi


# Check UTL_SEND:
if [ -z "${UTL_SEND}" ]; then
  echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. Environment variable UTL_SEND not defined" >&2
  exit 2
elif [ ! -f "${UTL_SEND}" ]; then
  echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. File UTL_SEND not found (${UTL_SEND})" >&2
  exit 2
elif [ ! -x "${UTL_SEND}" ]; then
  echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: ERROR!!! Execution Aborted. File UTL_SEND not executable (${UTL_SEND})" >&2
  exit 2
fi



# Initialize Package:

pkg_flag="`basename $0 .sh`"
run_in_progress=${TMP_DIR}/${pkg_name}_${oracle_sid}.lock
log_file=${TMP_DIR}/${pkg_name}_${oracle_sid}.log
prefix="${COMPANY_NAME}#${HOST_NAME} ${pkg_name}"
mess_theme="${prefix}: Error! Another process running (${run_in_progress})"

###mail_recipients="$MAIL_DBA"
echo $mail_recipients
mail_recipients="angor@vbrr.ru"
echo $mail_recipients



# Check Another process for running:
if [ -f ${run_in_progress} ]; then
  echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: Error! Another process running. Remove a file ${run_in_progress}" >&2
  echo "Remove a file ${run_in_progress}" | ${BIN_DIR}/_lib/utl_send.sh "${mess_theme}" "${mail_recipients}"
  exit 2
fi


# Prevent Parallel Execution:
touch ${run_in_progress}


# Run Implementation:
${BIN_DIR}/pkg/${pkg_name}/body.sh "${pkg_flag}" "${retention_period}" | tee ${log_file}


# Send E-Mail:
# mess_theme="${host_name}(${oracle_sid}): ${mess_header}"
# cat ${log_file} | ${BIN_DIR}/_lib/utl_send.sh "${mess_theme}" "${mail_recipients}"


# Cleanup Stop Flag:
test -r ${run_in_progress} && rm ${run_in_progress}
$




$vi body.sh

#!/bin/bash

#
# Check Parameters:
#

# Package Name:
tmp_string="`dirname $0`"
pkg_name="`basename ${tmp_string}`"

if [ "${pkg_name}" = "." ]; then
  tmp_string="`pwd`"
  pkg_name="`basename ${tmp_string}`"
fi

# Check for internal call:
if [ ! $1 = '_run' ]; then
  echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: Error! (Parameter=1) Call `basename $0` from ./_run.sh" >&2
  exit 2
fi


# Check For :
if [ $2 ]; then
  retention_period=$2
else
  echo "`date +%Y.%m.%d" "%H:%M:%S` - ${pkg_name}: Error! (Parameter=3) Call `basename $0` With 3-d parameter" >&2
  exit 2
fi


#
# Define local variables:
#

# Variables:


host_name=${HOST_NAME}
oracle_sid=${ORACLE_SID}
mail_recipients="$MAIL_DBA"

sys_audit_dir=${ORACLE_HOME}/rdbms/audit/

echo "`date +%T` Check directory for system audit: (${sys_audit_dir})"

if [ -r ${sys_audit_dir} ]; then
  echo "system audit dir done."
  ${FIND_UTL} /u01/app/oradb/product/11.2.0.3/dbhome_1/rdbms/audit/ -type f -name "*_ora\_*\_*.aud" -mtime +${retention_period} -print -exec ls -l {} \;
  ${FIND_UTL} /u01/app/oradb/product/11.2.0.3/dbhome_1/rdbms/audit/ -type f -name "*_ora\_*\_*.aud" -mtime +${retention_period} -print -exec rm {} \;
else
  loc_mess_theme="${host_name}(${oracle_sid})-${pkg_name}: Error! Not Found Directory for Clean system audit"
  loc_mess_body="Not exist directory: ${sys_audit_dir}"
  echo "${loc_mess_body}" | ${BIN_DIR}/_lib/utl_send.sh "${loc_mess_theme}" "${mail_recipients}"
  echo " - Error!"
  exit
fi
$

Комментариев нет:

Отправить комментарий