This post is also available in:
English
O Oracle vai sempre gerar auditoria para algumas operações de SYSDBA no audit_file_dest, mesmo que você tente evitá-la. De acordo com o Doc ID 1528170.1, "Some auditing of SYSDBA is mandatory and cannot be disabled (STARTUP, SHUTDOWN, and CONNECT)." Se o parâmetro audit_sys_operations estiver TRUE, muitos arquivos serão gerados no audit_file_dest pela atividade dos usuários SYSDBA.
Neste artigo, irei explicar e demonstrar facilmente como ler essa informação por dentro de uma tabela Oracle.
Irei dividir em 3 etapas:
- Criar um shell script para gerar arquivos aud temporários no formato de "loader".
- Criar uma tabela externa e uma view para ler dinamicamente esse conteúdo.
- Criar um job na crontab para converter a cada tantos minutos os novos audits gerados.
Então vamos começar. Primeiro de tudo, cria uma tabela no SO para manter o shell script e os arquivos de audit convertidos.
Neste exemplo, a pasta para o script de audit é a /home/oracle/audsys e eu irei manter os arquivos transformados na /home/oracle/audsys/out. O meu audit_file_dest é "/u02/admin/orcl/adump".
O script abaixo irá converter os arquivos de audit do formato de linha para o formato de coluna com separação de campos:
#! /bin/ksh
# Created by Rodrigo Jorge (www.dbarj.com.br) in Oct/2014
audfiles=/u02/admin/orcl/adump/*.aud
outfolder=/home/oracle/audsys/out
outsufix=".ext" # Sufix for temp files
procfile ()
{
cont=0
seq=1
delim="|||"
ifile=$1
ofile=$2
rm -f $ofile
while read var; do
if [[ $var == ACTION* ]]
then
cont=1
result="${linha_2ant}${delim}$(echo $linha_ant | sed 's/^[^:]*: //')"
fi
if [ $cont -ge 1 -a $cont -le 7 ]
then
if [[ $cont == 2 ]] && [[ $var != DATABASE* ]]
then
result="${result} ${var}"
else
result=${result}${delim}$(echo $var | sed 's/^[^:]*:\[[0-9]*\] //')
cont=$((cont+1))
fi
fi
if [[ $cont == 8 ]]
then
cont=0
result="${result}${delim}$(basename $ifile)${delim}${seq}"
seq=$((seq+1))
echo $result >> $ofile
fi
linha_2ant=$linha_ant
linha_ant=$var
done <$ifile
}
for f in $audfiles
do
outfile=$outfolder/$(basename $f)${outsufix}
if [ ! -f $outfile -o "$outfile" -ot "$f" ]
then
procfile $f $outfile
touch -m -r $f $outfile
fi
done
shfile=${outfolder}/catfiles.sh
[ ! -f $shfile ] && echo "$(which cat) ${outfolder}/*.aud${outsufix}" > $shfile
[ ! -x $shfile ] && chmod +x $shfile
echo > $outfolder/null.txt
Execute o script para criar os arquivos iniciais. A primeira execução pode demorar um pouco. Após a primeira, as seguintes serão diferenciais.
No próximo passo, vamos criar um diretório apontando para a nossa pasta temporária:
SQL> CREATE DIRECTORY AUDSYSDIR AS '/home/oracle/audsys/out'; Directory created. SQL>
Agora, vamos criar a tabela externa:
CREATE TABLE TB_AUDSYS
(
LOGDATE VARCHAR2(2000),
LENGTH NUMBER,
ACTION CLOB,
DATABASE_USER VARCHAR2(30),
PRIVILEGE VARCHAR2(2000),
CLIENT_USER VARCHAR2(2000),
CLIENT_TERMINAL VARCHAR2(2000),
STATUS NUMBER,
DBID NUMBER,
FILENAME VARCHAR2(2000),
SEQ NUMBER
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY AUDSYSDIR
ACCESS PARAMETERS
(
records delimited by newline
preprocessor audsysdir:'catfiles.sh'
badfile audsysdir:'query%a_%p.bad'
logfile audsysdir:'query%a_%p.log'
fields terminated by '|||'
missing field values are null
(
LOGDATE CHAR(2000),
LENGTH CHAR(2000) enclosed by X'27',
ACTION CHAR(100000),
DATABASE_USER CHAR(2000) enclosed by X'27',
PRIVILEGE CHAR(2000) enclosed by X'27',
CLIENT_USER CHAR(2000) enclosed by X'27',
CLIENT_TERMINAL CHAR(2000) enclosed by X'27',
STATUS CHAR(2000) enclosed by X'27',
DBID CHAR(2000) enclosed by X'27',
FILENAME CHAR(2000),
SEQ CHAR(2000)
)
)
LOCATION ('null.txt')
)
PARALLEL
REJECT LIMIT 0;
Note que aqui usamos como separados de campos o "fields terminated by '|||'", que é o mesmo utilizado no shell script acima. Alguns campos estão isolados pelo caractere X'27', valor Hexadecimal para "aspas simples". O "location" precisa ser um arquivo vazio (ele precisa existir) já que irei usar um script "preprocessor". Este script fará um "cat" dinamicamente de todos os arquivos existentes dentro da pasta temporária.
Vamos verificar o conteúdo da tabela:
SQL> set lines 1000 pages 1000 wrap off SQL> col logdate format a32 SQL> col length format 9999 SQL> col action format a30 SQL> col database_user format a15 SQL> col privilege format a7 SQL> col client_user format a20 SQL> col client_terminal format a5 SQL> col status format 9999 SQL> col dbid format 9999999999 SQL> col filename format a15 SQL> col seq format 999 SQL> select * from tb_audsys sample (1); LOGDATE LENGTH ACTION DATABASE_USER PRIVILE CLIENT_USER CLIEN STATUS DBID FILENAME SEQ -------------------------------- ------ ------------------------------ --------------- ------- -------------------- ----- ------ ----------- --------------- ---- Wed Oct 1 16:26:34 2014 -03:00 169 'CONNECT' / NONE emc12 Not A 1017 2044245708 sirac_ora_15997 2 Sun Oct 12 16:26:48 2014 -03:00 155 'CONNECT' / NONE emc12 1017 2044245708 sirac_ora_20302 1 Mon Sep 29 14:29:29 2014 -03:00 166 'CONNECT' MDOXDBA SYSDBA oracle pts/0 0 2044245708 sirac_ora_29181 1 Sat Oct 11 16:26:38 2014 -03:00 155 'CONNECT' / NONE emc12 1017 2044245708 sirac_ora_45362 1 Mon Sep 29 16:53:30 2014 -03:00 166 'CONNECT' MDOXDBA SYSDBA oracle pts/1 0 2044245708 sirac_ora_53002 1 Thu Sep 18 03:00:03 2014 -03:00 165 'CONNECT' RMANBACKUP SYSDBA oracle 0 2044245708 sirac_ora_57015 1 Thu Oct 2 16:26:33 2014 -03:00 169 'CONNECT' / NONE emc12 Not A 1017 2044245708 sirac_ora_62200 2 Fri Oct 10 16:26:37 2014 -03:00 155 'CONNECT' / NONE emc12 1017 2044245708 sirac_ora_63673 1 8 rows selected. SQL>
Ótimo, está tudo funcionando. Posso criar agora uma view para converter a coluna LOGDATA de varchar2 para timestamp:
CREATE VIEW VW_AUDSYS AS
SELECT TO_TIMESTAMP_TZ(SUBSTR(LOGDATE, 5), 'Mon fmDDfm hh24:mi:ss YYYY TZH:TZM') LOG_TZ,
LENGTH,
TO_CHAR(SUBSTR(ACTION,1,4000)) ACTION,
DATABASE_USER,
PRIVILEGE,
CLIENT_USER,
CLIENT_TERMINAL,
STATUS,
DBID,
FILENAME,
SEQ
FROM TB_AUDSYS;
O último passo é agendar o shell para executar a cada X minutos. Desta forma, iremos ter a tabela sempre atualizada com as últimas ações do SYSDBA:
[oracle@serveraudsys]$ crontab -l */5 * * * * /home/oracle/audsys/procaud.shGostou? Não deixe de comentar ou deixar um 👍!
