Facilidades para a Auditoria - Informix
A auditoria no Informix não é um ponto forte quando utilizado sua ferramenta nativa, pois não gera muitas informações úteis ou detalhadas suficiente para rastreamento e sim apenas informações do que ocorreu (Referencia). Para facilitar pra mim a leitura da auditoria no Informix criei duas maneiras, a primeira é um script em shell que lê os arquivos de auditoria e me mostra dos mais variados jeitos (por data, por tabela, por usuário, por banco), a segunda maneira (que não terminei) dá para criar um banco no informix e você carrega os arquivos da auditoria nele, com um simples select você busca o que você quer;
Primeira facilidade da Auditoria
O script que criei dá para fazer algumas buscas interessantes na auditoria - Download;
-------------------------------------------------------------------------------------------------
25/11/2013 ***** INFORMIX BASIC SERVICES ***** 13:24:23
-------------------------------------------------------------------------------------------------
informix
Code Function
---- -------------------------------------------
1 Lista as Datas dos Arquivos de Auditoria
2 Informix Audit Mnemonic Groupings
3 Lista Auditoria por Data
4 Lista Auditoria por Banco
5 Lista Auditoria por Tabela
6 Lista Auditoria por Usuario
7 Display dos Parametros da Audit
0 Voltar ao Menu Inicial
---- -------------------------------------------
Digite um Codigo - -------------------------------------------------------------------------------------------------
25/11/2013 ***** INFORMIX BASIC SERVICES ***** 13:24:41
-------------------------------------------------------------------------------------------------
informix
-rw-rw---- 1 informix informix 16393 May 23 2013 morfeu.0
-rw-rw---- 1 informix informix 145 May 23 2013 morfeu.1
-rw-rw---- 1 informix informix 49952 May 23 2013 morfeu.10
-rw-rw---- 1 informix informix 49975 May 23 2013 morfeu.11
-rw-rw---- 1 informix informix 49975 May 23 2013 morfeu.12
-rw-rw---- 1 informix informix 49977 May 23 2013 morfeu.13
-rw-rw---- 1 informix informix 49977 May 23 2013 morfeu.14
-rw-rw---- 1 informix informix 49977 May 23 2013 morfeu.15
-rw-rw---- 1 informix informix 49969 May 23 2013 morfeu.16
-rw-rw---- 1 informix informix 49977 May 23 2013 morfeu.17
-rw-rw---- 1 informix informix 49968 May 23 2013 morfeu.18
-rw-rw---- 1 informix informix 49977 May 23 2013 morfeu.19
-rw-rw---- 1 informix informix 364 May 23 2013 morfeu.2
-rw-rw---- 1 informix informix 49977 May 23 2013 morfeu.20
-rw-rw---- 1 informix informix 49977 May 23 2013 morfeu.21
-rw-rw---- 1 informix informix 49968 May 23 2013 morfeu.22
-rw-rw---- 1 informix informix 49905 May 23 2013 morfeu.23
-rw-rw---- 1 informix informix 49898 May 23 2013 morfeu.24
-rw-rw---- 1 informix informix 49961 May 23 2013 morfeu.25
-rw-rw---- 1 informix informix 49950 May 23 2013 morfeu.26
-rw-rw---- 1 informix informix 49993 May 23 2013 morfeu.27
-rw-rw---- 1 informix informix 49951 May 23 2013 morfeu.28
-rw-rw---- 1 informix informix 49912 May 23 2013 morfeu.29
-rw-rw---- 1 informix informix 49925 May 23 2013 morfeu.296
-rw-rw---- 1 informix informix 49998 May 23 2013 morfeu.297
-rw-rw---- 1 informix informix 49997 May 23 2013 morfeu.298
-rw-rw---- 1 informix informix 49937 May 23 2013 morfeu.299
---- corte -----------------------------------------------------------------------------------------------------
25/11/2013 ***** INFORMIX BASIC SERVICES ***** 13:25:27
-------------------------------------------------------------------------------------------------
informix
+---------+---------------+-----------------------------------------------+
| AUDIT | COMMAND | OBS |
+---------+---------------+-----------------------------------------------+
| BGTX | :ddl: | BEGIN WORK |
| CMTX | :ddl: | COMMIT WORK |
| RLTX | :ddl: | ROLLBACK WORK |
| ACTB | :ddl: | Access Table |
| CLDB | :ddl: | CLOSE DATABASE |
| OPDB | :ddl: | DATABASE |
| ULTB | :ddl: | Unlock Table |
| LKTB | :ddl: | Lock Table |
| STSN | :ddl: | Start Session |
+---------+---------------+-----------------------------------------------+
| ADCK | :dbsa: | Add chunk |
| ADLG | :dbsa: | Add log |
+---------+---------------+-----------------------------------------------+
---- corte -----------------------------------------------------------------------------------------------------
25/11/2013 ***** INFORMIX BASIC SERVICES ***** 13:28:34
-------------------------------------------------------------------------------------------------
informix
Digite o ANO .: 2013
Digite o MES .: 05-------------------------------------------------------------------------------------------------
25/11/2013 ***** INFORMIX BASIC SERVICES ***** 13:26:01
-------------------------------------------------------------------------------------------------
informix
ONLN|2013-05-18 04:00:04.262|talos.domain|32373|morfeu|informix|0:ONMO:-F
ONLN|2013-05-18 04:25:02.908|talos.domain|520|morfeu|informix|0:ONMO:-F
ONLN|2013-05-18 04:50:03.548|talos.domain|602|morfeu|informix|0:ONMO:-F
ONLN|2013-05-18 05:00:03.205|talos.domain|629|morfeu|informix|0:ONMO:-F
ONLN|2013-05-18 05:25:02.854|talos.domain|744|morfeu|informix|0:ONMO:-F
ONLN|2013-05-18 05:50:03.549|talos.domain|833|morfeu|informix|0:ONMO:-F
ONLN|2013-05-18 06:00:04.187|talos.domain|864|morfeu|informix|0:ONMO:-F
ONLN|2013-05-18 06:25:03.861|talos.domain|993|morfeu|informix|0:ONMO:-F
ONLN|2013-05-18 06:50:03.504|talos.domain|1074|morfeu|informix|0:ONMO:-F
ONLN|2013-05-18 07:00:05.179|talos.domain|1117|morfeu|informix|0:ONMO:-F
ONLN|2013-05-18 07:57:31.025|talos.domain|1413|morfeu|informix|0:ONMO:-z 3419
ONLN|2013-05-18 08:00:56.233|talos.domain|1538|morfeu|informix|0:ONMO:-z 4264
ONLN|2013-05-18 08:01:01.239|talos.domain|1543|morfeu|informix|0:ONMO:-z 4263
ONLN|2013-05-18 08:01:05.243|talos.domain|1548|morfeu|informix|0:ONMO:-z 4262
ONLN|2013-05-18 08:01:08.245|talos.domain|1549|morfeu|informix|0:ONMO:-z 4261
ONLN|2013-05-18 08:01:44.281|talos.domain|1587|morfeu|informix|0:ONMO:-z 4256
ONLN|2013-05-18 08:01:46.385|talos.domain|1586|morfeu|informix|-1:ONBR:-b -l
ONLN|2013-05-18 08:01:53.291|talos.domain|1600|morfeu|informix|0:ONMO:-z 4258
ONLN|2013-05-18 08:02:05.304|talos.domain|1619|morfeu|informix|0:ONMO:-z 4261
ONLN|2013-05-18 08:02:11.312|talos.domain|1620|morfeu|informix|0:ONMO:-z 4258
ONLN|2013-05-18 08:02:16.316|talos.domain|1621|morfeu|informix|0:ONMO:-z 4256
ONLN|2013-05-18 08:05:27.545|talos.domain|29987|morfeu|informix|0:ONBR:-b -w
ONLN|2013-05-18 08:05:28.508|talos.domain|1699|morfeu|informix|0:ONMO:-z 4261
ONLN|2013-05-18 08:05:35.516|talos.domain|1706|morfeu|informix|0:ONMO:-z 4258
ONLN|2013-05-18 08:05:41.521|talos.domain|1707|morfeu|informix|0:ONMO:-z 4256
ONLN|2013-05-18 08:06:39.605|talos.domain|1720|morfeu|informix|0:ONMO:-z 4258
ONLN|2013-05-18 08:06:51.629|talos.domain|1721|morfeu|informix|0:ONMO:-z 4256-------------------------------------------------------------------------------------------------
26/11/2013 ***** INFORMIX BASIC SERVICES ***** 11:03:10
-------------------------------------------------------------------------------------------------
informix
Digite o USER ID do usuario ....: webgrb-------------------------------------------------------------------------------------------------
26/11/2013 ***** INFORMIX BASIC SERVICES ***** 11:03:10
-------------------------------------------------------------------------------------------------
informix
ONLN|2012-09-05 00:11:48.969|perses.01.domain01|380308|morfeu|webgrb|-1:STSN:perses.01.domain01
ONLN|2012-09-05 00:17:00.655|perses.01.domain01|379536|morfeu|webgrb|-1:STSN:perses.01.domain01
ONLN|2012-09-06 00:11:53.918|perses.01.domain01|420380|morfeu|webgrb|-1:STSN:perses.01.domain01
ONLN|2012-09-06 00:16:57.732|perses.01.domain01|417812|morfeu|webgrb|-1:STSN:perses.01.domain01
ONLN|2012-09-06 09:33:30.862|simeonte.01.domain01|13494|morfeu|webgrb|-1:STSN
ONLN|2012-09-06 10:07:57.297|simeonte.01.domain01|13925|morfeu|webgrb|0:CRVW:dw_sig_desenv:2689:webgrb:vdw_pdevento_ageprev_2002Esta segunda opção não terminei mais é bem legal, gravo os arquivos de auditoria em um banco no informix, primeiro vamos criar um banco no dbspace data, o banco vai ser no log;
echo "create database aafai240 in data;" | dbaccess sysmaster
Database selected.
Database closed.
Database created.
Database closed.SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit
Run the current SQL statements.
----------------------- aafai240@desenv ----------- Press CTRL-W for Help --------
CREATE TABLE dba.audit_db (
audit_system CHAR(4) NOT NULL,
audit_dt_ocorrido CHAR(23) NOT NULL,
audit_origem CHAR(50) NOT NULL,
audit_pd CHAR(10) NOT NULL,
audit_instancia CHAR(20) NOT NULL,
audit_user_id CHAR(30) NOT NULL,
audit_detail CHAR(110) NOT NULL,
ENABLED
)echo "LOAD FROM '/export/home/informix/logs/audit/primary.1' insert into audit_db;" | dbaccess aafai240
echo "LOAD FROM '/export/home/informix/logs/audit/primary.2' insert into audit_db;" | dbaccess aafai240
echo "LOAD FROM '/export/home/informix/logs/audit/primary.3' insert into audit_db;" | dbaccess aafai240
echo "LOAD FROM '/export/home/informix/logs/audit/primary.4' insert into audit_db;" | dbaccess aafai240
echo "LOAD FROM '/export/home/informix/logs/audit/primary.5' insert into audit_db;" | dbaccess aafai240
echo "LOAD FROM '/export/home/informix/logs/audit/primary.6' insert into audit_db;" | dbaccess aafai240
echo "LOAD FROM '/export/home/informix/logs/audit/primary.7' insert into audit_db;" | dbaccess aafai240
echo "LOAD FROM '/export/home/informix/logs/audit/primary.8' insert into audit_db;" | dbaccess aafai240
echo "LOAD FROM '/export/home/informix/logs/audit/primary.9' insert into audit_db;" | dbaccess aafai240SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit
Run the current SQL statements.
----------------------- aafai240@desenv -------- Press CTRL-W for Help --------
select * from 'dba'.audit_db DISPLAY: Next Restart Exit
Display next page of results.
----------------------- aafai240@desenv -------- Press CTRL-W for Help --------
audit_system ONLN
audit_dt_ocorrido 2011-10-04 10:01:45.320
audit_origem cratos.domain
audit_pd 29382
audit_instancia primary
audit_user_id dba
audit_detail 0:GRTB:web_quaker:263:2:dba:public:
audit_system ONLN
audit_dt_ocorrido 2011-10-04 10:01:45.350
audit_origem cratos.domain
audit_pd 29382
audit_instancia primary
audit_user_id dba
audit_detail 0:RVTB:web_quaker:263:2:dba:public:0
audit_system ONLN
audit_dt_ocorrido 2011-10-04 10:01:45.354
audit_origem cratos.domain
audit_pd 29382
audit_instancia primary
audit_user_id dba
audit_detail 0:GRTB:web_quaker:263:2:dba:silva,lregra:O comando onshowaudit no Informix dá para fazer algumas algumas consultas nos logs de auditoria;
[s343:informix]$/export/home/informix/claudemar> onshowaudit ?
ONSHOWAUDIT Secure Audit Utility
INFORMIX-SQL Version 11.70.FC2
USAGE: onshowaudit [-I | -O] [-f (input file)] [-u (user name)] [-s (server name)]
onshowaudit [-n (server number)] [-l [(loadfile)]]
-I : read from the Informix audit trail
-O : read from the OS audit trail
-f : read a single audit trail file (Informix mode only)
-u : extract only records concerning
-s : extract only records concerning
-n : extract audit records from ADTPATH in ADTCFG.
-l [(loadfile)] : print audit records with delimiters [s343:informix]$/export/home/informix/claudemar> onshowaudit -I -f /work/aaodir/ol_lx_rama.7
ONSHOWAUDIT Secure Audit Utility
INFORMIX-SQL Version 11.70.FC4
ONLN|2011-10-06 15:28:25.000|fido|11647|abc1170tcp|informix|0:RDRW:stores_demo:106:2097230:309::
Program Over. [s343:informix]$/export/home/informix/claudemar> onshowaudit -I -f /work/aaodir/ol_lx_rama.7 -u usr1
ONLN|2009-05-29 09:09:02.000|s132.TX|-1|abc1170tcp|informix|0:DRTB:stores_demo:4294967295:dbmd3:usr1:0:1050535
ONLN|2009-05-29 09:09:07.000|s132.TX|-1|abc1170tcp|informix|0:CRTB:stores_demo:0:dbmd1:usr1:0:-
ONLN|2009-05-29 09:09:07.000|s132.TX|-1|abc1170tcp|informix|0:DRTB:stores_demo:4294967295:dbmd1:usr1:0:1050535
ONLN|2009-05-29 09:09:07.000|s132.TX|-1|abc1170tcp|informix|0:CRTB:stores_demo:0:dbmd2:usr1:0:-
ONLN|2009-05-29 09:09:07.000|s132.TX|-1|abc1170tcp|informix|0:DRTB:stores_demo:4294967295:dbmd2:usr1:0:1050535
ONLN|2009-05-29 09:09:07.000|s132.TX|-1|abc1170tcp|informix|0:CRTB:stores_demo:0:dbmd3:usr1:0:-
ONLN|2009-05-29 09:09:07.000|s132.TX|-1|abc1170tcp|informix|0:DRTB:stores_demo:4294967295:dbmd3:usr1:0:1050535A aqueles clientes que estão sujeitas a leis e a regulamentos de uma forma ou de outra, muitos se perguntam sobre quais operações devem ser auditadas. Não há uma resposta curta para isso, com a ajuda desse link (Clique Aqui) encontrei um script (Clique Aqui) dá para ter uma ideia sobre o que auditar;
Rodei o script e ele me mostrou algumas mascaras com as permissões a serem auditadas como exemplo;
onaudit -a -u _audit -e +CRAM,DRAM,LSAM,UPAM
onaudit -a -u _crud -e +DLRW,INRW,RDRW,UPRW
onaudit -a -u _dba -e +CRDB,DRDB,RNDB,STRS
onaudit -a -u _dbsa -e +ADCK,ADLG,CRBS,CRDS,DNCK,DNDM,DRBS,DRCK,DRDS,DRLG,LGDB,MDLG,RMCK,RNDS,STSC,UPCK,UPDM
onaudit -a -u _ddl -e +ACTB,ALFR,ALIX,ALSQ,ALTB,BGTX,CLDB,CMTX,CRIX,CRSN,CRSP,CRSQ,CRTB,CRTR,CRVW,DRIX,DRSN,DRSP,DRSQ,DRTB,DRTR,DRVW,LKTB,LSDB,OPDB,RLTX,RNIX,RNSQ,RNTC,STCN,STOM,STOP,STRT,STSN,SVXD,TCTB,ULTB,USSP,USTB
onaudit -a -u _dml -e +CRPT,EXSP,SCSP,STCO,STDF,STDS,STEP,STEV,STEX,STIL,STLM,STNC,STPR,STTX
onaudit -a -u _domain -e +CRDM,DRDM
onaudit -a -u _ius -e +ALME,ALOC,CRAG,CRBT,CRCT,CRDT,CRME,CROC,CRRT,CRXD,CRXT,DRAG,DRCT,DRME,DROC,DRRT,DRTY,DRXD,DRXT
onaudit -a -u _lbac -e +ALLC,CRLB,CRLC,CRPL,DRLB,DRLC,DRPL,GRLB,GRSA,GRSS,GRXM,RNLB,RNLC,RNPL,RVLB,RVSA,RVSS,RVXM
onaudit -a -u _onutils -e +ONAU,ONBR,ONCH,ONIN,ONLG,ONLO,ONMN,ONMO,ONPA,ONPL,ONSP,ONST,ONTP,ONUL
onaudit -a -u _optical -e +ALOP,CROP,DROP,RLOP,RSOP,TMOP
onaudit -a -u _perms -e +CRRL,DRRL,GRDB,GRDR,GRFR,GRRL,GRTB,RVDB,RVDR,RVFR,RVRL,RVTB,STDP,STRL,STSA
0 comentários:
Enviar um comentário