billing dla bazy danych oracle IU2SDADJDTZD5Q32NUMWMCQROUJ4XRJJRDQVKPI

"Billing" dla bazy danych Oracle

-DURVáDZàDJRZVNL

iRM Polska

e–mail: jarek.lagowski@irm.at

Abstrakt.

%UDPD]DNáDGXSUDF\6HWNLSUDFRZQLNyZSU]HFKRG]W G\FRG]LHQQLHZRE\GZLHVWURQ\-DNVSUDZG]LüF]\

.RZDOVNL VS G]D Z SUDF\ JRG]LQ G]LHQQLH" 3URV] - RWR OLVWD ZHMü L Z\Mü Z\ND] F]DVX SUDF\ GOD

GRZROQHJR SUDFRZQLND .D*G\ ] QLFK PD RERZL]HN ]DUHMHVWURZDü VZRMH SU]HMFLH SU]H] EUDP SRSU]H]

SURVWHXU]G]HQLHSRPLDUXF]DVX

)DNWXUD]DWHOHIRQ:\VRNRüNZRW\]QDF]QLHSU]HNUDF]DQDV]HQDMPLHOV]HRF]HNLZDQLD-DNVSUDZG]LüNWR

]DZLQLá*RQDF]\P*G]ZRQLFGR]QDMRP\FKF]\VXUIXMFSRLQWHUQHFLH´"3URV] -RWRZ\ND]SRáF]H

ND*GDUR]PRZD]QDV]HJRDSDUDWXMHVWUHMHVWURZDQDZFHQWUDOLLPR*HE\üVSUDZG]RQD

$GPLQLVWUDWRUED]\GDQ\FKPR*HVWDQüSU]HGNRQLHF]QRFLRGSRZLHG]LQDSRGREQHS\WDQLD,OHF]DVXGDQ\

X*\WNRZQLN SUDFRZDá ] ED] GDQ\FK" : MDNLFK JRG]LQDFK" &R URELá" &]\ L Z MDNL VSRVyE PRG\ILNRZDá

LVWRWQH GDQH" 2GSRZLHG]L V W\P EDUG]LHM ZD*QH MH*HOL PDP\ GR F]\QLHQLD ] GRVW SHP SU]H] ,QWHUnet lub
coraz popularniejszym "outsourcing'iem" aplikacji.

5HIHUDWSRND*HMDNSU]\Z\NRU]\VWDQLXVWDQGDUGRZ\FKGDUPRZ\FKPHFKDQL]PyZSU]\JRWRZDüELOOLQJ

GODED]\GDQ\FK2UDFOH:\NRU]\VWDQHE GWUDG\F\MQHURGNLSROHFHQLH$8',7RUD]Z\]ZDODF]HG]LDáDMFH

QD SROHFHQLDFK '0/ MDN UyZQLH* ZáDFLZRFL 2UDFOH i Ä/RJ 0LQHU´ L Z\]ZDODF]H G]LDáDMFH QD
zdarzeniach bazodanowych.

1. Rejestracja czasu pracy

=DF]QLMP\ RG U]HF]\ VWRVXQNRZR QDMSURVWV]HM UHMHVWUX SRGáF]H GR QDV]HM ED]\ GDQ\FK

&KFLHOLE\P\ZLHG]LHüNWRNLHG\LMDNGáXJRSUDFRZDáZQDV]\PV\VWHPLH%LH*FHVHVMHPR*QD

OHG]LüNRU]\VWDMF]WDEOLF96(66,21WDWDEOLFDSU]\GDVL QDP]UHV]WWURFK Sy(QLHM$OHMDN

]DSDPL WDüFDáKLVWRUL SRáF]H"-DNXFKZ\FLüPRPHQW]DNRF]HQLDVHVML"

1.1. Polecenie AUDIT

7UDG\F\MQ\PVSRVREHPUHMHVWUDFMLF]DVXSUDF\ZED]LHGDQ\FK2UDFOHG]LDáDMF\PGREU]HMX*Z

ZHUVMLMHVWSROHFHQLH$8',7']L NLQLHPXPR*HP\UHMHVWURZDüDQDVW SQLHUDSRUWRZDüUy*QH

URG]DMHDNW\ZQRFLX*\WNRZQLNyZED]\GDQ\FK

1.1.1. Zakresy obserwacji wykonywanej poleceniem AUDIT

•

Obserwacja polecenia (statement auditing) –

]ELHUDLQIRUPDFMHRZ\EUDQ\FKW\SDFKSROHFH

QSPRG\ILNXMF\FKGDQHQLH]DOH*QLHRGRELHNWXQDNWyU\PSROHFHQLHG]LDáDáRREVHUZDFM

PR*QDSURZDG]LüGODZV]\VWNLFKEG(GODZ\EUDQ\FKX*\WNRZQLNyZED]\GDQ\FK

•

Obserwacja przywileju (privilege auditing) – zbiera informacje o wykorzystaniu wybranych

SU]\ZLOHMyZQSSU]\ZLOHMXGRWZRU]HQLDQRZ\FKWDEOLFQLH]DOH*QLHRGRELHNWXQDNWyU\P

SROHFHQLH G]LDáDáR REVHUZDFM PR*QD SURZDG]Lü GOD ZV]\VWNLFK EG( GOD Z\EUDQ\FK

X*\WNRZQLNyZED]\GDQ\FK

•

Obserwacja obiektu (schema object auditing) – zbiera informacje o wykonaniu konkretnego
polecenia na konkretnym obiekcie (np. zapytania na wskazanej tablicy), obserwacja jest
prowad

]RQDGODZV]\VWNLFKX*\WNRZQLNyZED]\GDQ\FK

5HMHVWUDFMD SRáF]H ] ED] GDQ\FK PLHFL VL ]DUyZQR Z ]DNUHVLH REVHUZDFML SROHFHQLD MDN L
przywileju.

1.1.2. Poziom obserwacji wykonywanej poleceniem AUDIT

2EVHUZDFMDED]\GDQ\FKPR*HJURPDG]LüLQIRUPDFMHPQLHMOXEEDUG]LHMV]F]HJyáRZR

ELRUFSRGXZDJ

•

SROHFHQLDZ\NRQDQHW\ONRSUDZLGáRZREG(W\ONRQLHSUDZLGáRZREG(ZV]\VWNLH

•

W\ONR SLHUZV]H Z\VWSLHQLH SROHFHQLD EG( W\ONR SLHUZV]H Z\VWSLHQLH SROHFHQLD Z VHVML

X*\WNRZQLNDEG(ZV]\VWNLHZ\VWSLHQLDpolecenia

•

SROHFHQLD Z\NRQDQH W\ONR SU]H] Z\EUDQ\FK X*\WNRZQLNyZ EG( ZV]\VWNLH Z\NRQDQH
polecenia

5HMHVWUDFMDSRáF]HSRZLQQDREHMPRZDüZV]\VWNLHSUyE\UyZQLH*WHQLHXGDQHZ\NRQ\ZDQH

SU]H]ZV]\VWNLFKX*\WNRZQLNyZ

1.1.3. Aktywacja obserwacji wykonywanej poleceniem AUDIT

Aktywacja obserwacji wykonywanej poleceniem AUDIT wymaga:

•

ustawienia odpowiedniego parametru pliku startowego bazy danych,

•

ustawienia zakresu i poziomu obserwacji poleceniem AUDIT

3DUDPHWUSOLNXVWDUWRZHJRNWyU\PXVLP\XVWDZLüWR$8',7_TRAIL.

'RSXV]F]DOQHZDUWRFL

parametru to:

•

NONE –

REVHUZDFMDZ\áF]RQDZDUWRüGRP\OQD

•

TRUE – zapis obserwacji do bazy danych,

•

OS – zapis obserwacji do pliku (tylko na wybranych platformach)

3DUDPHWUQLHPR*HE\ü]PLHQLDQ\G\QDPLF]QLHWR]QDF]\*HQRZDZDUWRüSDUDPHWUX]DG]LDáD

dopiero po kolejnym stracie bazy danych. Ustawienie zapisu obserwacji do bazy danych jest

QDMZ\JRGQLHMV]H ]H Z]JO GX QD PR*OLZRü Sy(QLHMV]HJR UDSRUWRZDQLD ]D SRPRF SROHFHQLD
SELECT. Zatem, wybrana linijka z pliku initX.or

DSRZLQQDZ\JOGDüQDVW SXMFR

3U]\NáDG$NW\ZDFMDREVHUZDFML

audit_trail = true

'H]DNW\ZDFMD REVHUZDFML Z\PDJD XVWDZLHQLD SDUDPHWUX QD ZDUWRü )$/6( L SRQRZQHJR VWDUWX

ED]\ GDQ\FK 3R UHVWDUFLH ED]\ GDQ\FK ] SDUDPHWUHP XVWDZLRQ\P MDN Z\*HM SR]RVWDáR nam

ZáF]\ü*GDQRSFM REVHUZDFML3ROHFHQLH$8',7ZSRVWDFLSRWU]HEQHMGRUHMHVWUDFMLZV]\VWNLFK

SRGáF]HMHVWQDSUDZG SURVWH

3U]\NáDG8VWDZLHQLHRSFMLREVHUZDFML

SQL> audit session;

2EVHUZRZDQLH]RVWDáRZáF]RQH

, MX* – RG WHJR PRPHQWX ND*GH SU]HMFLH UyZQLH* QLHXGDQH SU]H] QDV] ÄEUDP GR ED]\

GDQ\FK´E G]LHUHMHVWURZDQH:\áF]HQLHREVHUZDFMLRGE\ZDVL ]DSRPRFSROHFHQLD12$8',7

1.1.4. Raportowanie

2SFMHREVHUZDFMLSDPL WDQHVZWDEOLF\6<6$8',7:\QLNLREVHUZDFMLJURPDG]RQHVZ

tab

OLF\6<6$8'SUREOHP\]ZL]DQH]SLHO JQDFMWHMWDEOLF\E GRPyZLRQHZSXQNFLH

&L NWyU]\ OXEL ÄWHNVW\ (UyGáRZH´ PRJ F]Xü VL XV]F] OLZLHQL PR*OLZRFL Z\NRQ\ZDQLD

]DS\WD EH]SRUHGQLR QD W\FK WDEOLFDFK 0QLHM GRFLHNOLZL PRJ ]DGRZROLü VL zestawem

SHUVSHNW\Z'%$BNWyUHSUH]HQWXMGDQH]ZZWDEOLFZVSRVyEEDUG]LHMSU]\VW SQ\

8VWDZLRQHRSFMHREVHUZDFMLPR*QDRGF]\WDüZWDEOLFDFKVáRZQLNRZ\FK

•

DBA_ STMT _AUDIT_OPTS – opcje obserwacji polecenia

•

DBA_PRIV_AUDIT_OPTS – opcje obserwacji przywileju

•

DBA_OBJ_AUDIT_OPTS – opcje obserwacji obiektu

6SUDZG(P\HIHNWSROHFHQLD]SU]\NáDGX

3U]\NáDG6SUDZG]HQLHRSFMLREVHUZDFML

SQL> select *

2 from DBA_STMT_AUDIT_OPTS;

USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE

------------------------------ ------------------------------ ---------------------------------------- ---------- ----------

CREATE SESSION BY ACCESS BY ACCESS

PHQWDU]GRSU]\NáDGX.ROXPQ\86(5B1$0(L352;<B1$0(ZLDGF]RW\P*HPR*QD]DZ ]Lü

REVHUZDFM SRáF]H)DNW*HZNROXPQLH$8',7B237,21ZLG]LP\W\ONRÄ&5($7(6(66,21´QLH
SU]HV]NDG]DZUHMHVWUDFML]DUyZQRSRáF]HMDNLRGáF]H:DUWRFLÄ%<$&&(66´ZQDVW SQ\FKGZyFK
NROXPQDFKPyZLRW\P*HUHMHVWURZDQHE G]LHND*GH]GDU]HQLH'ODUHMHVWUDFMLSRGáF]H$8',7
6(66,21MHVWWRMHG\QDPR*OLZRü'ODLQQ\FKRSFMLREVHUZDFMLPR*QDZ\EUDüUHMHVWUDFM Ä%<6(66,21´

–

W\ONRSLHUZV]H]GDU]HQLHZVHVMLE G]LH

notowane.

:\QLNLREVHUZDFMLPR*QDRGQDOH(üZQDVW SXMF\FKWDEOLFDFKVáRZQLNRZ\FK

•

DBA_AUDIT_TRAIL -

SHáQDLQIRUPDFMDEH]SRG]LDáXQDRSFMHREVHUZDFML

•

DBA_AUDIT_SESSION –

UHMHVWUSRGáF]H

•

DBA_AUDIT_STATEMENT –

UHMHVWU X*\FLD SROHFH *5$17 5(92.( $8'IT,

NOAUDIT oraz ALTER SYSTEM

•

DBA_AUDIT_OBJECT –

UHMHVWU X*\FLD RELHNWyZ L SU]\ZLOHMyZ ]JRGQLH ] XVWDZLRQ\PL

opcjami obserwacji

,RWR]DSRPRFSROHFHQLD6(/(&7]'%$B$8',7B6(66,21X]\VNXMHP\UHMHVWUF]DVXSUDF\
w naszej bazie danych.

3U]\NáDG:\QLNREVHUZDFMLSRGáF]H

SQL> select sessionid,
2 to_char(timestamp,'YYYY.MM.DD HH24:MI:SS') logon_time,
3 to_char(logoff_time,'YYYY.MM.DD HH24:MI:SS') logoff_time,
4 os_username,
5 username,
6 returncode
7 from dba_audit_session

order by timestamp;

SESSIONID LOGON_TIME LOGOFF_TIME OS_USERNAME USERNAME RETURNCODE
---------- ------------------- ------------------- -------------------- -------------------- ----------
2134 2000.07.25 21:01:04 2000.07.25 21:01:15 JALA IOPT 0
2135 2000.07.25 21:01:15 2000.07.25 21:03:35 JALA ALFA 0
2136 2000.07.25 21:03:35 2000.07.25 21:19:51 JALA ALFA 0
2137 2000.07.26 09:57:00 2000.07.26 14:48:51 JALA IOPT 0
2138 2000.07.26 10:21:31 2000.07.26 15:30:17 JALA IOPT 0
2139 2000.07.26 10:21:47 2000.07.26 11:24:54 JALA IOPT 0
2162 2000.07.26 15:49:09 JALA IOPT 0
2163 2000.07.26 16:07:37 2000.07.26 16:07:41 JALA IOPT 0
2164 2000.07.26 16:07:41 JALA BETA 1017
2165 2000.07.26 16:07:46 2000.07.26 16:23:56 JALA BETA 0
2166 2000.07.26 16:23:56 JALA GAMMA 0

PHQWDU]GRSU]\NáDGX6(66,21,'MHVWXQLNDOQ\PLGHQW\ILNDWRUHPVHVML%LH*FDVHVMDPR*HVSUDZG]Lü

VZyMLGHQW\ILNDWRU]DSRPRFIXQNFMLVWDQGDUGRZHM86(5(19µ6(66,21,'¶']L NLWHPXLGHQW\ILNDWRURZL
E G]LHP\PRJOLQDVW SQLHáF]\üSURVW\UHMHVWUSRGáF]H]UDSRUWHPV]F]HJyáRZ\PRW\PFRGDQDVHVMD
URELáD,GHQW\ILNDWRUSR]RVWDMHXQLNDOQ\ZUDPDFKMHGQHMED]\GDQ\FK-H*HOLNROXPQD/2*2))B7,0(MHVW
SXVWDWR]QDF]\*HVHVMDMHV]F]HG]LDáDOXESRGáF]HQLHE\áRQLHXGDQH6HVMHSU]HUZDQHSU]H]DGPLQLVWUDWRUD
OXE]SRZRGX]DPNQL FLDED]\GDQ\FKX]\VNXMZ/2*2))B7,0(F]DV]GDU]HQLDNWyUHZ\PXVLáRSU]HUZDQLH
VHVML3RP\OQHSRGáF]HQLHLEUDNSUREOHPyZZWUDNFLHG]LDáDQLDVHVMLV\JQDOL]XMHZDUWRüZNROXPQLH
5(7851&2'(,QQDZDUWRüWRNRGEá GX25$

- sygnaliz

XMF\SUREOHP:SU]\NáDG]LHGODVHVML

Z\VWSLáGRV\üSRSXODUQ\EáGÄ25$

QLHSUDZLGáRZDQD]ZDX*\WNRZQLNDKDVáRRGPRZDUHMHVWUDFML´

.ROXPQ\X*\WHZSU]\NáDG]LHQLHVZV]\VWNLPLGRVW SQ\PLZ'%$B$8',7B6(66,21

:DUWR ZLHG]LHü *H LVWQLHMH Sewna, bardzo ciekawa kolumna w tablicy DBA_AUDIT_TRAIL

]DPDVNRZDQDSRGQLH]QDF]FQD]Z&200(17B7(;72Wy*]DZDUWHZQLHMLQIRUPDFMHQLHV

GRVW SQHZ'%$B$8',7B6(66,21DPRJRND]DüVL SR*\WHF]QH'RW\F]RQHVSRVREXLDGUHVX

]NWyUHJRáF]RQRVL GRED]\GDQ\FK6SUDZG(P\NOLNDVHVML]SU]\NáDGX

3U]\NáDG'RGDWNRZHLQIRUPDFMHRVHVMDFK

SQL> select sessionid,
2 comment_text
3 from dba_audit_trail
4 where sessionid in ( 2138, 2164, 2134);

SESSIONID COMMENT_TEXT
---------- ---------------------------------------------------------------------------------------------------
2134 Authenticated by: DATABASE
2138 Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.4.81)(PORT=2615))
2164 Authenticated by: DATABASE

PHQWDU]GRSU]\NáDGX6HVMDE\áDSRP\OQLH]DNRF]RQVHVMORNDOQ6HVMDáF]\áDVL

Z\NRU]\VWXMFSURWRNyá7&3,31HWOXE64/ 1HW6HVMDE\áDVHVMORNDOQNWyUDQLHGRV]áDGR
VNXWNX]SRZRGXEá GX25$

-01017.

3LHO JQDFMDWDEO

icy SYS.AUD$

7DEOLFD$8'WZRU]RQDMHVWSRGREQLHMDNWDEOLFHVáRZQLNRZHZSU]HVWU]HQLWDEOLF6<67(0Z

VFKHPDFLH6<67\PVDP\P]QDMGXMHVL ZJUXSLHWDEOLFNWyU\FKÄGRW\NDQLH´LQDF]HMMDNW\ONR

SRSU]H] 6(/(&7 MHVW ]DEURQLRQH 1D V]F] FLH VWDQRZL RQD MHGQDN Z\MWHN – PR*QD L QDOH*\

XVXZDü]QLHMUHNRUG\$GPLQLVWUDWRUSRZLQLHQ]DSHZQLü

•

.RQWURO UR]PLDUXWDEOLF\$8'

•

Backup tablicy AUD$

3RQLHZD*$8']QDMGXMHVL ZSU]HVWU]HQLWDEOLF6<67(0DGRNXPHQWDFMDVWDQRZF]RRGUDG]D

przenoszenie tej tablicy w

LQQHPLHMVFHQLHZROQRGRSXFLüDE\ZVSRVyEQLHNRQWURORZDQ\URVáD

]DS\FKDMFF]\WH*UR]S\FKDMFW QDMZD*QLHMV]SU]HVWU]HZED]LHGDQ\FK2UDFOH=DWHPQDOH*\

RNUHVRZRF]\FLüW WDEOLF ]HVWDU\FKUHNRUGyZ$OHFR]URELüMH*HOLE G]LHP\FKFLHOLVL JQüGR

KLVWRULL QDV]HJR G]LHQQLND ÄZHMü L Z\Mü´" 2Wy* MH*HOL ZF]HQLHM QLH ]DSHZQLP\ PHFKDQL]PX
archiwizacji tablicy AUD$ -

QLH E G]LHP\ PLHOL GRVW SX GR XVXQL W\FK ] QLHM UHNRUGyZ QDZHW

MH*HOLG\VSRQXMHP\HNVSRUWHPED]\GDQ\FKVSU]HGPRPHQWXXVXQL cia starych rekordów. Eksport

ED]\GDQ\FKQLHREHMPXMH*DGQ\FKWDEOLFX*\WNRZQLND6<6=]DáR*HQLDWHQ*HX*\WNRZQLNSRVLDGD

W\ONR WDEOLFH VáRZQLNRZH D WH ] NROHL DXWRPDW\F]QLH ]DSHáQLDM VL Z SU]\SDGNX LPSRUWX

SR]RVWDá\FKRELHNWyZED]\GDQ\FK

Skuteczny

P SURVW\P L QDMF] FLHM VWRVRZDQ\P UR]ZL]DQLHP MHVW VSRU]G]HQLH NRSLL WDEOLF\

$8' SRG LQQ\P X*\WNRZQLNLHP Z LQQHM SU]HVWU]HQL WDEOLF 2GZLH*DQLH WDNLHM NRSLL PR*H

RGE\ZDüVL QSUD]QDGRE ]DSRPRFSURVWHMS WOL

3U]\NáDG3LHO JQDFMDWDEOLF\$

UD$

begin
for i in (select sessionid
from sys.aud$
where timestamp# < trunc(sysdate)
and sessionid not in
( select audsid
from v$session ) )
loop
insert into AUDIT_ARCH
select *
from sys.aud$
where sessionid = i.sessionid;
delete from sys.aud$
where sessionid = i.sessionid;
commit;
end loop;
end;
/

PHQWDU]GRSU]\NáDGX6L JQL FLHGRWDEOLF\96(66,21SR]ZDODQDPXQLNQüXVXQL FLDUHNRUGXGOD

G]LDáDMFHMVHVML=DSURSRQRZDQ\ZDUXQHNZ\ELHUDUHNRUG\ZF]RUDMV]HLVWDUV]H$GPLQLVWUDWRUSRZLQLHQ
RNUHOLüMDNGáXJKLVWRUL PR*HSU]HFKRZ\ZDüZWDEOLF\RU\JLQDOQHM3XVWNRSL WDEOLF\$8'ZW\P
SU]\SDGNXQD]ZDQ$8',7B$5&+PR*QDSU]\JRWRZDü]DSRPRFÄFUHDWHWDEOH$8',7B$5&+
RSFMHBVNáDGRZDQLDDVVHOHFW IURP6<6$8'ZKHUH ´3RWZLHUG]DQLHWUDQVDNFMLSRND*G\P

przeniesionym wierszu pozwala nam unikn

üSUREOHPyZ]VHJPHQWDPLZ\FRIDQLD

7DNSU]\JRWRZDQDNRSLDWDEOLF\$8'QDNRQFLHX*\WNRZQLNDLQQHJRQL*6<6MHVWFKURQLRQD

SU]H]HNVSRUWED]\GDQ\FKLW\PVDP\PPR*HE\üF]\V]F]RQD]PR*OLZRFLSRZURWXGRZHUVML

]DFKRZDQHMZF]DVLHHNVSRUWX3U]HJOGDQLHLQIRUPDFML]DZDUWHMZWDEOLF\DUFKLZDOQHMMHVWPR*OLZH
na dwa sposoby:

• F]\WDQLHZSURVW]WHMWDEOLF\PR*HE\üZVSRPDJDQHSHUVSHNW\ZDPLSU]\JRWRZDQ\PL

na wzór DBA%AUDIT%)

• F]\WDQLH]6<6$8'XSU]HGQLR]DáDGRZDQHMGDQ\PL]WDEOLF\– kopii

1.2. Zdarzenia bazodanowe

,QQ\P VSRVREHP QD UHMHVWUDFM SRGáF]H GR ED]\ GDQ\FK MHVW SU]\JRWRZDQLH Z\]ZDODF]\

G]LDáDMF\FKQD]GDU]HQLDFKED]RGDQRZ\FK-HVWWRQRZDPR*OLZRüGRVW SQDSRF]ZV]\RGZHUVML
Oracle Server 8.1. Zdarzenia na poziomie bazy danych, które mog

E\üSRZRGHPGRXUXFKRPLHQLD

Z\]ZDODF]DVQDVW SXMFH

•

SERVERERROR –

Z\VWSLáEáGUHMHVWURZDQ\QDSR]LRPLHED]\GDQ\FKQLHNWyUHEá G\QLH

SRZRGXMXUXFKRPLHQLDZ\]ZDODF]DQSÄ25$-01034: Oracle not available” - )

•

LOGON, LOGOFF –

SRáF]HQLHLUR]áF]HQLH]ED]GDQ\FK

•

STARTUP, SHUTDOWN –

VWDUWL]DPNQL FLHED]\GDQ\FK

2F]\ZLFLHDE\UHMHVWURZDüF]DVSUDF\QDOH*\Z\NRU]\VWDü]GDU]HQLD/2*21L/2*2))

1.2.1. Porównanie z poleceniem AUDIT

3RGVWDZRZ ]DOHW ]DVWRVRZDQLD Z\]ZDODF]\ Z SRUyZQDQLX ] SROHFHQLem AUDIT jest

RF]\ZLFLHPR*OLZRüRSURJUDPRZDQLD]GDU]HQLD']L NLWHPXPR*QDDNW\ZQLHNRQWURORZDüQDV]

ÄEUDP ]DNáDGRZ´ D QLH W\ONR ELHUQLH UHMHVWURZDü ZHMFLD L Z\MFLD 2WR SU]\NáDG\ PR*OLZRFL

Z\]ZDODF]\QLHGRVW SQHGODSROHFHQLD$8',7

•

zebranie do

GDWNRZHMLQIRUPDFML]WDEOLFVáRZQLNRZ\FK2UDFOH¶D

•

UHMHVWUDFMD SRGáF]H ZHGáXJ GRZROQ\FK ZDUXQNyZ ORJLF]Q\FK QS W\ONR Z RNUHORQH GQL
tygodnia),

•

RGU]XFDQLH SRGáF]H QLH VSHáQLDMF\FK RNUHORQ\FK ZDUXQNyZ QS SRáF]HQLH ] PDV]\Q\

QLHE GFHMQDOLFLH autoryzowanych)

•

SU]HV\áDQLH LQIRUPDFML RVWU]H*H ]D SRPRF VWUXPLHQL '%06B3,3( OXE NROHMHN
(Advanced Queueing Option)

3RGVWDZRZ ZDG ]DVWRVRZDQLD Z\]ZDODF]\ Z SRUyZQDQLX ] SROHFHQLHP $8',7 MHVW

RF]\ZLFLHNRQLHF]QRüRSURJUDPRZDQLD]GDU]HQLD$GPLQLVWUDWRUF]\SURJUDPLVWDSU]\JRWRZXMF\

Z\]ZDODF]G]LDáDMF\QD]GDU]HQLDFKED]RGDQRZ\FKPXVLUR]ZL]DüSRGVWDZRZHSUREOHP\

•

MDNQDSLVDüSURJUDPDE\QLHZ\ZRáDüVNXWNyZXERF]Q\FK

•

JG]LHSU]HV\áDüLQIRUPDFM R]GDU]HQLX

•

MDNQLHGRSXFLüGR]DIDáV]RZDQLDLQIRrmacji

'RGDWNRZZDGUHMHVWUDFMLF]DVXSUDF\]DSRPRFZ\]ZDODF]\MHVWG]LDáDQLHZ\]ZDODF]DW\ONR

Ä$)7(5/2*21´7\PVDP\PQLHPR*QDZ\FKZ\FLüSUyEQLHXGDQ\FKSRGáF]H

1.2.2. Przygotowanie do zbierania informacji

:\]ZDODF]H G]LDáDMFH QD ]GDU]HQLDFK ED]RGDQRZ\FK PRJ NRU]\VWDü ]H VSHFMDOQ\FK IXQNFML

]ZUDFDMF\FK DWU\EXW\ ]GDU]HQLD , WDN ]GDU]HQLH $)7(5 /2*21 XGRVW SQLD QDVW SXMFH
atrybuty:

•

ora_sysevent -

GRNáDGQLHµ/2*21¶

•

ora_login_user –

QD]ZDX*\WNRZQLNDED]\GDQ\FK

•

ora_instance_num – numer in

VWDQFMLSU]H]NWyUX*\WNRZQLNáF]\VL ]ED]GDQ\FK

•

ora_database_name – nazwa bazy danych,

•

ora_client_ip_address –

DGUHV]NWyUHJRPDPPLHMVFHSRáF]HQLHMH*HOLZ\NRU]\VW\ZDQ\MHVW

SURWRNyá7&3,3

Zdarzenie BEFORE LOGOFF oferuje o jeden atrybut mniej –

QLH XGRVW SQLD

ÄRUDBFOLHQWBLSBDGGUHVV´ -DN ZLGDü LQIRUPDFMD GRVWDUF]DQD SU]H] DWU\EXW\ ]GDU]HQLD QLH MHVW ]E\W

REILWD0XVLP\]DWHP]DGEDüDE\Z\]ZDODF]PyJá]DUHMHVWURZDüQLHFRZL FHMGDQ\FK

%DUG]RERJDW\P(UyGáHPZLHG]\RZáDFLZRFLDFKVHVMLSRáF]enia) jest tablica V$SESSION.

&y*NLHG\MHVWRQDGRVW SQDW\ONRGODDGPLQLVWUDWRUyZ$SU]HFLH*SDPL WDüQDOH*\*HZ\]ZDODF]

Z\NRQ\ZDQ\ MHVW ] SUDZDPL VHVML SRZRGXMFHM ]GDU]HQLH 'RGDWNRZD WUXGQRü QDZHW GOD

DGPLQLVWUDWRUDPR*HSROHJDüQDXVWDOHQLXNWyUD]VHVMLZLG]LDQ\FKZ96(66,21MHVWPRMVHVM"

3RQL*V]\SU]\NáDGMHVWMHGQ]PR*OLZRFLUR]ZL]DQLDWHJRSUREOHPX

3U]\NáDG8GRVW SQLHQLHLQIRUPDFMLRZáDVQHMVHVML

SQL> connect sys
3RGDMKDVáR

3RáF]RQ\
SQL> create or replace view session_info
2 as
3 select *
4 from v$session
5 where sid = (select distinct
6 sid
7 from sys.v$mystat);
3HUVSHNW\ZD]RVWDáDXWZRU]RQD
SQL> create public synonym session_info for session_info;
6\QRQLP]RVWDáutworzony.
SQL> grant select on session_info to public;
3U]\]QDQLHXSUDZQLH]DNRF]RQHSRZRG]HQLHP

.RPHQWDU]GRSU]\NáDGX3HUVSHNW\ZD9P\VWDWSRND]XMHVWDW\VW\NLGODELH*FHMVHVML1LHVWHW\VWDQGDUGRZR
GRVW SQDMHVWW\ONRGODX*\WNRZQLND6<6'ODWHJRWH*SRZ\*V]HSROHFHQLDZ\NRQ\ZDQHVMDNR6<6
.RU]\VWDMF]FXG]HJRRELHNWXVNáDGRZDQHJRQSSHUVSHNW\ZDOXESDNLHWX*\WNRZQLNSRWU]HEXMHXSUDZQLHQLD
W\ONRQDWHQ*HRELHNW:V]HONLHZHZQ WU]QHRGZRáDQLDUHDOL]RZDQHVGRP\OQLH]SUDZDPLZáDFLFLHOD

RELHNWXVNáDGRZDQHJRZZHUVMLSRMDZLáDVL PR*OLZRüXUXFKDPLDQLDRELHNWyZVNáDGRZDQ\FK]SUDZDPL
ZRáDMFHJR'ODWHJRWH*Z\VWDUF]\QDGDüXSUDZQLHQLDQDSHUVSHNW\Z NRFRZ

– SESSION_INFO.

.RU]\VWDMF]SHUVSHNW\Z\6(66,21B,1)2SU]\JRWRZDQHMZSU]\NáDG]LHND*GDVHVMDPR*H

Ä]REDF]\ü´VZRMHLW\ONRVZRMHSDUDPHWU\WDNMDNVSRND]DQHZ96(66,21.ROHMQ\PNURNLHP

E G]LHSU]\JRWRZDQLHPLHMVFDVNáDGRZDQLDLQIRUPDFML

3U]\NáDG3U]\JRWRZDQLHWDEOLF\JURPDG]FHMKLVWRUL SRáF]H

SQL> connect system
P

RGDMKDVáR

3RáF]RQ\
SQL> create table session_hist
2 ( sid number,
3 serial# number,
4 audsid number,
5 username varchar2(30),
6 osuser varchar2(30),
7 machine varchar2(64),
8 terminal varchar2(16),
9 program varchar2(64),
10 logon_time date,
11 logoff_time date,
12 ip_address varchar2(64) )
13 tablespace tools;
7DEHOD]RVWDáDXWZRU]RQD
SQL> create public synonym session_hist for session_hist;
6\QRQLP]RVWDáXWZRU]RQ\
SQL> grant select,insert,update(logoff_time) on session_hist to public;
3U]\]QDQLHXSUDZQLH]DNRF]RQHSRZRG]HQLHP

.RPHQWDU]GRSU]\NáDGX7ZRU]FWDEOLFHQLHSRZLQQLP\NRU]\VWDü]NRQWD6<6SRUyZQDMSXQNW

Tworzenie t

DEOLF\QDNRQFLH6<67(0WH*QLHMHVWZVND]DQHDOHSU]\NáDGMHVWUHDOL]RZDQ\QDWHVWRZHMED]LH

danych

--DN]DEH]SLHF]\ü6(66,21B+,67SU]HGIDáV]RZDQLHP"=RVWDZP\WRMDNRSUDF GRPRZGOD

bardziej dociekliwych.

A oto i zestaw dwóch, prostych wyzwalaczy re

MHVWUXMF\FK SRGáF]HQLD L UR]áF]HQLD ] ED]

danych:

3U]\NáDG:\]ZDODF]HUHMHVWUXMFHÄZHMFLDLZ\MFLD´

SQL> create or replace trigger after_logon
2 after logon on database
3 declare
4 ip_ varchar2(30);
5 begin
6 ip_ := ORA_CLIENT_IP_ADDRESS;
7 insert into session_hist
8 ( SID, SERIAL#,
9 AUDSID, USERNAME, OSUSER,
10 MACHINE, TERMINAL, PROGRAM,
11 LOGON_TIME, LOGOFF_TIME, IP_ADDRESS )
12 select SID,
13 SERIAL#,
14 AUDSID,
15 USERNAME,
16 OSUSER,
17 MACHINE,
18 TERMINAL,
19 PROGRAM,
20 LOGON_TIME,
21 NULL,
22 ip_
23 from session_info;
24 if user = 'ALFA'
25 then
26 raise_application_error( -20001, 'Ciebie nie lubimy!');
27 end if;
28 end;
29 /
:\]ZDODF]]RVWDáXWZRU]RQ\
SQL>
SQL> create or replace trigger before_logoff
2 before logoff on database
3 begin
4 update session_hist
5 set logoff_time = sysdate
6 where (sid, serial#, audsid) =
7 (select sid, serial#, audsid
8 from session_info );
9 end;
10 /
:\]ZDODF]]RVWDáXWZRU]RQ\

.RPHQWDU]GRSU]\NáDGX

Atrybut ORA_CLIENT_IP_ADDRE

66X*\W\EH]SRUHGQLRZSROHFHQLX,16(57

]ZUDFD18//:\]ZDODF]$)7(5B/2*21ÄQLHZSXFL´GRED]\GDQ\FKX*\WNRZQLND$/)$6HVMDRWZRU]RQD

z „AS SYSDBA” (lub „INTERNAL”) otrzymuje AUDSID = 0 (i nie jest rejestrowana przez AUDIT).

Jednoznaczna identyfikacja

VHVMLMHVW]DWHPPR*OLZDGRSLHURSRX*\FLXWU]HFKNROXPQ6,'6(5,$/L

AUDSID.

Wygenerujmy teraz kilka wpisów do tablicy SESSION_HIST

3U]\NáDG3RáF]HQLDOHG]RQHSU]H]Z\]ZDODF]H

SQL> connect iopt
3RGDMKDVáR

3RáF]RQ\
SQL> connect beta/beta@iopt
3RáF]RQ\
SQL> connect sys as sysdba
3RGDMKDVáR

3RáF]RQ\
SQL> connect alfa/alfa
ERROR:
ORA-

Z\VWSLáEáGQDSR]LRPLHUHNXUHQF\MQHJR64/

ORA-20001: Ciebie nie lubimy!
ORA-06512: przy linia 24
2VWU]H*HQLH1LHPDMX*SRáF]HQLD]25$&/(
SQL> connect gamma/gamma@iopt
3RáF]RQ\

6SUDZG(P\]DSLV\ZWDEOLF\UHMHVWUXMFHM

3U]\NáDG:\QLNUHMHVWUDFMLSURZDG]RQHMSU]H]Z\]ZDODF]H

SQL> select to_char(LOGON_TIME,'YYYY.MM.DD HH24:MI:SS') LOGON_TIME,
2 to_char(LOGOFF_TIME,'YYYY.MM.DD HH24:MI:SS') LOGOFF_TIME,
3 USERNAME,
4 OSUSER,
5 MACHINE,
6 IP_ADDRESS,
7 PROGRAM
8 from session_hist
9 order by logon_time;

LOGON_TIME LOGOFF_TIME USERNAME OSUSER MACHINE IP_ADDRESS PROGRAM
------------------- ------------------- -------- ------ ----------- ------------ -----------
2000.07.28 16:37:28 2000.07.28 16:37:37 IOPT JALA IRM\JAREK1 SQLPLUS.EXE
2000.07.28 16:37:38 2000.07.28 16:37:51 BETA JALA IRM\JAREK1 192.168.4.81 SQLPLUS.EXE
2000.07.28 16:37:51 2000.07.28 16:38:01 SYS JALA IRM\JAREK1 SQLPLUS.EXE
2000.07.28 16:38:12 GAMMA JALA IRM\JAREK1 192.168.4.81 SQLPLUS.EXE

.RPHQWDU]GRSU]\NáDGX

Odrzucona s

HVMDX*\WNRZQLND$/)$QLHSR]RVWDZLáDSRVRELHODGX6HVMH

NRU]\VWDMFH]SRáF]HQLD1HW]DSLVDá\VZyMDGUHV6HVMDX*\WNRZQLND*$00$MHV]F]HWUZDOXEVHVMD
]RVWDáDSU]HUZDQDZVSRVyEJZDáWRZQ\QS]DPNQL FLHED]\GDQ\FK]RSFM,00(',$7(OXE$%257

1.3. Podsumowanie

'ODFHOyZUHMHVWUDFMLLW\ONRUHMHVWUDFMLSRGáF]HGRQDV]HMED]\GDQ\FK]GHF\GRZDQLHSROHFDP

SROHFHQLH $8',7 -HVW SURVWV]H Z X*\FLX L GRVWDUF]D SHáQHM LQIRUPDFML UyZQLH* R SRáF]HQLDFK

QLHXGDQ\FK :\]ZDODF]H G]LDáDMFH QD ]GDU]HQLDFK ED]RGDQRZ\FK QDOH*\ WUDNWRZDü UDF]HM MDNR

HOHPHQW ZVSRPDJDMF\ QS GR ILOWURZDQLD SRGáF]H 1LH]DOH*QLH RG ]DVWRVRZDQHJR VSRVREX

SDPL WDüWU]HEDRREVáXG]HELH*FHMLEDFNXSLHWDEOLF]KLVWRULSRáF]H-HVWWRSU]HFLH*ÄELOOLQJ´
naszej bazy danych

1LH EH] ]QDF]HQLD SR]RVWDMH UyZQLH* IDNW SU]\JRWRZDQLD RGSRZLHGQLFK

LQGHNVyZGODQDV]\FKWDEOLFKLVWRU\F]Q\FK%H]QLFKQLHE G]LHPR*OLZHHIHNW\ZQHUDSRUWRZDQLH]

WDEOLF NWyUH SRWHQFMDOQLH PRJ SU]HFKRZ\ZDü ZLHOH W\VL F\ UHNRUGyZ DOH WR MX* WHPDW QD inny
referat).

2. Rejestracja zmian danych

5HMHVWUDFMD ]PLDQ Z ED]LH GDQ\FK 2UDFOH MHVW ]DZV]H ZáF]RQD .D*GD SRWZLHUG]RQD ]PLDQD

PXVL]QDOH(üVL ZSOLNDFKG]LHQQLNDSRZWyU]HZ\MWNLHPVRSHUDFMH]RSFM12/2**,1*–

NWR X*\ZD WHM RSFML MHVW VDP VRELH ZLQLHQ Z SU]\SDGNX DZDULL $* GR ZHUVML 2UDFOH QLH

XGRVW SQLDá *DGQHJR QDU] G]LD GR RJOGDQLD ]DZDUWRFL G]LHQQLNyZ Z IRUPLH ]UR]XPLDáHM GOD

F]áRZLHND3RF]ZV]\RGWHMZHUVMLDGPLQLVWUDWRUPDGRG\VSR]\FMLQDU] G]LHRQD]ZLH/RJ0LQHU

=D SRPRF WHJ R* QDU] G]LD ]DZDUWRü G]LHQQLNyZ SRZWyU]H ]DUyZQR ÄRQ-line” jak i

DUFKLZDOQ\FK MHVW GRVW SQD GR UDSRUWRZDQLD ]D SRPRF 6(/(&7 =DWHP F]\ SRWU]HED F]HJR
jeszcze? Tak. Potrzeba selektywnej rejestracji zmian. Selektywna rejestracja zmian pozwala

QDVW SQLH áDWZLHM L V]\EFLHM UDSRUWRZDü =GDU]D VL *H Z DSOLNDFM ZEXGRZDQH V PHFKDQL]P\

Z\SHáQLDMFH SU]\JRWRZDQH WDEOLFH ]PLDQ -DN QDWRPLDVW ]RUJDQL]RZDü VHOHNW\ZQ UHMHVWUDFM

]PLDQ Z VSRVyE ]DXWRPDW\]RZDQ\ QLH]DOH*Q\ RG DSOLNDFML" 7UDG\F\MQ\P VSRVREHP GRVW SQ\P

RGZHUVMLMHVWX*\FLHZ\]ZDODF]\G]LDáDMF\FKQDSROHFHQLDFK'0/QDZ\EUDQ\FKWDEOLFDFK

:\PDJDWRWURFK SUDF\DOHMDNE G VWDUDáVL SRND]DüQLHMHVWWDNLHWUXGQH

2.1. Polecenie AUDIT

: UHMHVWUDFML ]PLDQ PR*H QDP SRPyF ]QDQH MX* SROHFHQLH $8',7 3R]ZDOD RQ XVWDZLü

OHG]HQLH SROHFH QD Z\EUDQ\P RELHNFLH =DWHP PR*QD VRELH ]D*\F]\ü UHMHVWUDFML ND*GHJR
INSERT, UPDATE lub DELETE na wybranych tablicach. Niestety, informacja zebrana w ten

VSRVyERJUDQLF]DVL GRVWZLHUG]HQLD*HRZV]HPGDQ\X*\WNRZQLNQDGDQHMWDEOLF\GRNRQDá]PLDQ

GDQ\FK1LHZLDGRPRQDWRPLDVWMDNLHGDQH]RVWDá\ZVWDZLRQHXVXQL WHOXE]PLHQLRQH-H*HOLWHQ

SR]LRP LQIRUPDFML MHVW Z\VWDUF]DMF\ WR ]DFK FDP GR ]Já ELHQLD VNáDGQL SROHFHQLD $8',7 L
wybrania odpowiednich opcji.

$NW\ZDFMDVWDUWLUDSRUWRZDQLHOHG]HQLDSR]RVWDMHWDNLHVDPRMDNZ

SU]\SDGNXOHG]HQLDSRGáF]H:V]\VF\]DLQWHUHVRZDQLUHMHVWUDFMZDUWRFL]PLHQLDQ\FKGDQ\FK

PXV]X*\üLQQ\FKVSRVREyZ

2.2. Wyzwalacze na poleceniach DML

1DWXUD Z\]ZDODF]D ]RVWDáD SU]HGVWDZLRQD SU]\ RND]ML OHG]HQLD SRGáF]H GR ED]\ GDQ\FK

-H*HOLFKFHP\UHMHVWURZDüZDUWRü]PLHQLDQ\FKGDQ\FKPR*HP\X*\üZ\]ZDODF]\G]LDáDMF\FKQD
poleceniach DML (Data Manipulation Language

: WUHFL SURJUDPLVWD PR*H X*\ü VSHF\ILNDFML

:NEW.nazwa_kolumny i :OLD.nazwa_kolumny

, WR ZáDQLH F]\QL X*\FLH Z\]ZDODF]\ '0/

QLH]DVWSLRQ\PLZUHMHVWUDFML]PLDQGDQ\FK&RPXVLP\Z\VSHF\ILNRZDü

•

WDEOLF NWyUFKFHP\OHG]Lü

•

RSHUDFM NWyUFKFHP\OHG]Lü– INSERT, UPDATE, DELETE

•

NLHG\FKFHP\XUXFKDPLDüZ\]Zalacz – BEFORE lub AFTER

•

F]\Z\]ZDODF]PDG]LDáDüRJyOQLHQDSR]LRPLHRSHUDFMLF]\WH*GODND*GHJR]PLHQLDQHJR
wiersza z osobna (FOR EACH ROW)

'RGDWNRZR RF]\ZLFLH QDOH*\ SU]\JRWRZDü WDEOLF UHMHVWUXMF ]PLDQ\ 7DN QDSUDZG WR

SRZLQQ\ E\ü WR SU]\QDMPQLHM GZLH WDEOLFH 3LHUZV]D UHMHVWUXMFD RJyOQH ZáDVQRFL VHVML

PRG\ILNXMFHM GDQH L GUXJD SDPL WDMFD ZDUWRFL ]PLHQLDQ\FK NROXPQ 3RQL*HM ]QDMG]LHP\

SU]\NáDGWDNLFKWDEOLF

3U]\NáDG7DEOLFHGRUHMHVWUDFML]PLDQGDQ\FK

SQL> create table data_hist
2 ( entry_id number,
3 timestamp date,
4 sid number,
5 serial# number,
6 audsid number,
7 username varchar2(30),
8 osuser varchar2(30),
9 machine varchar2(64),
10 terminal varchar2(16),
11 program varchar2(64),
12 table_owner varchar2(30),
13 table_name varchar2(30),
14 action varchar2(1) )
15 tablespace tools;
7DEHOD]RVWDáDXWZRU]RQD
SQL> create table data_hist_det
2 ( entry_id number,
3 column_name varchar2(30),
4 old_value varchar2(2000),
5 new_value varchar2(2000) )
6 tablespace tools;
7DEHOD]RVWDáDXWZRU]RQD
SQL> create sequence data_hist_seq;
6HNZHQFMD]RVWDáDXWZRU]RQD

.RPHQWDU]GRSU]\NáDGX=HVWDZSRF]WNRZ\FKNROXPQWDEOLF\'$7$B+,67MHVWWDNLVDPMDNZWDEOLF\
6(66,21B+,678GRVW SQLWRSHáQLQIRUPDFM RVHVMLPRG\ILNXMFHMGDQHDWDN*HSR]ZROLZUD]LHSRWU]HE

SRáF]\üWDEOLFHUHMHVWUXMFHVHVMHL]PLDQ\.ROXPQD$&7,21E G]LHSU]HFKRZ\ZDüNRGDNFML,

– INSERT, U

– UPDATE, D –

'(/(7(6HNZHQFMD'$7$B+,67B6(4E G]LHJHQHURZDüXQLNDOQ\NOXF]GODWDEOLF

UHMHVWUXMF\FK]PLDQ\2F]\ZLFLHQDOH*\SDPL WDüRQDGDQLXRGSRZLHGQLFKXSUDZQLHSU]\JRWRZDQLX
V\QRQLPyZL]DEH]SLHF]HQLXSU]HGIDáV]RZDQLHP

6WZRU]HQLH Z\]ZDODF]D OHG]FHJR ]PLDQ\ PR*H E\ü Z ]QDF]Q\P VWRSQLX ]DXWRPDW\]RZDQH

0R*H RGE\ZDü VL WR QD SR]LRPLH DSOLNDFML NWyUD Z\JHQHUXMH RGSRZLHGQL VNU\SW GR WZRrzenia

Z\]ZDODF]D']L NLQRZHMZáDFLZRFL2UDFOH– wbudowanego, dynamicznego SQL (polecenie

(;(&87( ,00(',$7( MHVW WR QDZHW SURVWV]H 3RQL*HM SUH]HQWRZDQ\ MHVW SU]\NáDG

UHMHVWURZDQLDSROHFHQLD83'$7(GODGREU]H]QDQHMWDEOLF\X*\WNRZQLND6&277'(3T.

3U]\NáDG:\]ZDODF]UHMHVWUXMF\83'$7(QDWDEOLF\6&277'(37

SQL> create or replace trigger SCOTT_DEPT_U
2 before UPDATE on SCOTT.DEPT for each row
3 declare
4 info_ session_info%rowtype;
5 entry_id_ number;
6 begin
7 select *
8 into info_
9 from session_info;
10 select data_hist_seq.nextval
11 into entry_id_
12 from dual;
13
14 insert into data_hist
15 ( ENTRY_ID, TIMESTAMP,
16 SID, SERIAL#, AUDSID,
17 USERNAME, OSUSER,
18 MACHINE, TERMINAL, PROGRAM,
19 TABLE_NAME, TABLE_OWNER, ACTION )
20 values
21 ( entry_id_, sysdate,
22 info_.sid, info_.serial#, info_.audsid,
23 info_.username, info_.osuser,
24 info_.machine, info_.terminal, info_.program,
25 'DEPT', 'SCOTT', 'U');
26
27 insert into data_hist_det
28 ( ENTRY_ID, COLUMN_NAME,
29 OLD_VALUE, NEW_VALUE )
30 values
31 ( entry_id_, 'DEPTNO',
32 substr(to_char(:old.DEPTNO),1,2000),
33 substr(to_char(:new.DEPTNO),1,2000) );
34
35 insert into data_hist_det
36 ( ENTRY_ID, COLUMN_NAME,
37 OLD_VALUE, NEW_VALUE )
38 values
39 ( entry_id_, 'DNAME',
40 substr(:old.DNAME,1,2000),
41 substr(:new.DNAME,1,2000) );
42
43 insert into data_hist_det
44 ( ENTRY_ID, COLUMN_NAME,
45 OLD_VALUE, NEW_VALUE )
46 values
47 ( entry_id_, 'LOC',
48 substr(:old.LOC,1,2000),
49 substr(:new.LOC,1,2000) );
50
51 end;
52 /

:\]ZDODF]]RVWDáXWZRU]RQ\.

.RPHQWDU]GRSU]\NáDGX3R]RVWDáH]GDU]HQLDPRJE\üUHMHVWURZDQHSU]H]DQDORJLF]QHZ\]ZDODF]H

BEFORE INSERT i BEFORE DELETE. Wykorzystywana jest perspektywa SESSION_INFO przygotowana w

SU]\NáDG]LH:VWDZLDMFQRZHLVWDUHZDUWRFLNROXPQGRWDEOLF\UHMHVWUXMFHMQDOH*\SDPL WDüRNRQZHUVML
GDQ\FKQDWHNVWRUD]RJUDQLF]HQLXQDGáXJRüWHJRWHNVWX

7HUD]SR]RVWDáRVSUDZG]LüG]LDáDQLHUHMHVWUDWRUD

3U]\NáDG=PLDQ\GDQ\FK

SQL> connect gamma/gamma@iopt
3RáF]RQ\
SQL> update dept
2 set loc = 'WARSZAWA'
3 where deptno = 10;
ZLHUV]]RVWDá]PRG\ILNRZDQ\
SQL> commit;
=DWZLHUG]DQLH]RVWDáRXNRF]RQH
SQL> connect beta/beta
3RáF]RQ\

SQL> insert into dept
2 values ( 50, 'NEW', 'ZAKOPANE');
ZLHUV]]RVWDáXWZRU]RQ\
SQL> commit;
Zatwierdzanie

]RVWDáRXNRF]RQH

SQL> connect scott/tiger@iopt
3RáF]RQ\
SQL> delete from dept
2 where deptno = 50;
ZLHUV]]RVWDáXVXQL W\
SQL> commit;
=DWZLHUG]DQLH]RVWDáRXNRF]RQH

3U]\NáDG5DSRUW]H]PLDQGDQ\FK

SQL> select to_char(A.TIMESTAMP,'YYYY.MM.DD HH24:MI:DD') TIMESTAMP,
2 A.USERNAME,
3 decode(A.ACTION,'I','INSERT','D','DELETE','U','UPDATE') ACTION,
4 A.TABLE_OWNER||'.'||A.TABLE_NAME CHANGED_TABLE,
5 B.COLUMN_NAME,
6 B.OLD_VALUE,
7 B.NEW_VALUE
8 from data_hist A,
9 data_hist_det B
10 where A.entry_id = B.entry_id
11 order by
12 A.timestamp;

TIMESTAMP USERNAME ACTION CHANGED_TABLE COLUMN_NAME OLD_VALUE NEW_VALUE
------------------- ---------- ------ ------------- -------------------- -------------------- ------------
2000.07.28 19:23:28 GAMMA UPDATE SCOTT.DEPT DEPTNO 10 10
DNAME ACCOUNTING ACCOUNTING
LOC NEW YORK WARSZAWA
2000.07.28 19:24:28 BETA INSERT SCOTT.DEPT DEPTNO 50
DNAME NEW
LOC ZAKOPANE
2000.07.28 19:25:28 SCOTT DELETE SCOTT.DEPT DEPTNO 50
DNAME NEW
LOC ZAKOPANE

2.2.1 Kilka uwag

Nie

]PLHUQLH ZD*Q VSUDZ MHVW RJUDQLF]HQLH UHMHVWURZDQ\FK ]PLDQ *URPDG]HQLH ]E\W GX*HM

LORFLLQIRUPDFMLSRZRGXMHRGF]XZDOQHREFL*HQLHV\VWHPXLGDOV]HSUREOHP\]UDSRUWRZDQLHP2WR

NLONDZVND]yZHNMDNRJUDQLF]\üLORüGDQ\FKZWDEOLFDFKUHMHVWUXMF\FK]PLDny.

•

*URPDG]HQLH LQIRUPDFML R ]PLDQDFK GDQ\FK SRZLQQR E\ü RJUDQLF]RQH W\ONR GR

QDMZD*QLHMV]\FKWDEOLF

•

: SU]\SDGNX UHMHVWUDFML SROHFHQLD 83'$7( QLH ZDUWR SDPL WDü ZDUWRFL NROXPQ QLH
zmienionych.

•

:SU]\SDGNXUHMHVWUDFMLSROHFH,16(57L'(/(7(QLHZDUWRSDPL WDüNROXPQ]ZDUWRFL
NULL.

•

:\]ZDODF] QD SROHFHQLX 83'$7( PR*H E\ü XUXFKDPLDQ\ W\ONR SU]\ ]PLDQDFK

Z\]QDF]RQ\FKNROXPQSDWU]VNáDGDQLD&5($7(75,**(5

•

.D*G\Z\]ZDODF]PR*HPLHü]GHILQLRZDQ\ZDUXQHNORJLF]Q\NWyUHJRVSHáQLHQLHZDUXQNXMH
uruchomienie

Z\]ZDODF]DSDWU]VNáDGDQLD&5($7(75,**(5

=GDQLH SRZWyU]RQH ] SXQNWX 1LH EH] ]QDF]HQLD SR]RVWDMH UyZQLH* IDNW SU]\JRWRZDQLD

RGSRZLHGQLFKLQGHNVyZGODQDV]\FKWDEOLFKLVWRU\F]Q\FK%H]QLFKQLHE G]LHPR*OLZHHIHNW\ZQH
raportowanie z tablic, które p

RWHQFMDOQLHPRJSU]HFKRZ\ZDüZLHOHW\VL F\UHNRUGyZDOHWRMX*

temat na inny referat).

2.3. Log Miner

-DN MX* WR E\áR SRZLHG]LDQH /RJ 0LQHU MHVW WR VWRVXQNRZR QRZH QDU] G]LH SR]ZDODMFH

RJOGDü]DZDUWRüSOLNyZG]LHQQLNDSRZWyU]H]DSRPRFSROHFHQLD6ELECT. Dotyczy to zarówno

G]LHQQLNyZ ELH*F\FK ÄRQ-OLQH´ MDN L DUFKLZDOQ\FK MH*HOL ED]D GDQ\FK G]LDáD Z WU\ELH

DUFKLZL]DFML , WX GRFKRG]LP\ GR EDUG]R ZD*QHJR VSRVWU]H*HQLD /RJ 0LQHU MDNR QDU] G]LH GR

OHG]HQLD]PLDQGDQ\FKMHVWEH]X*\WHF]Q\MH*HOLED]DGDQ\FKQLHG]LDáDZWU\ELHDUFKLZL]DFML$WR

GODWHJR*HELH*FHG]LHQQLNL]DSLV\ZDQHVZF\NOXFLJOHQDQRZR=DWHPRSLHUDMFVL W\ONRQD

G]LHQQLNDFKELH*F\FKQLHMHVWHP\ZVWDQLHZRJyOQ\PSU]\SDGNX]DSHZQLüUDSRUWRZDQLD]PLDQ

NWyUHPLDá\PLHMVFHQDZHWQLHGDOHMMDNQSJRG]LQ WHPX

=NROHLG\VSRQXMFG]LHQQLNDPLDUFKLZDOQ\PLPR*HP\SRWU]HERZDüGX*RF]DVXDE\GRWU]Hü

GR LQIRUPDFML R LQWHUHVXMF\FK QDV ]PLDQDFK ']LHMH VL WDN GODWHJR *H PLPR X*\FLD SROHFHQLD

6(/(&7 WDN QDSUDZG G]LDáDP\ QD SOLNDFK VHNZHQF\MQ\FK 3RQLHZD* G]LHQQLNL JURPDG]

ZV]\VWNLH]PLDQ\ZED]LHGDQ\FKPR*HWRR]QDF]DüSRWU]HE ÄSU]HNRSDQLD´NLONXVHWPHJDEDMWyZ

DE\RGQDOH(üMHGQWUDQVDNFM =UHJXá\QLHZLHP\NLHG\GRNáDGQLHWUDQVDNFMDPLDáDPLHMVFHDQS
podejrzewa

P\*HZF]RUDM–/RJ0LQHUPXVLSU]HMU]HüZV]\VWNLHZF]RUDMV]HSOLNLG]LHQQLND

1DOH*\WUDNWRZDü]DWHP/RJ0LQHU¶DMDNRQDU] G]LHZVSRPDJDMFHDQLHSRGVWDZRZHZ]DGDQLX

UDSRUWRZDQLD ]PLDQ GDQ\FK /RJ 0LQHU MHVW ] NROHL QLH]DVWSLRQ\ MH*HOL FKFHP\ X]\VNDü

LQIRUPDFMH R ]PLDQDFK NWyUH QLH E\á\ OHG]RQH Z\]ZDODF]DPL : SRSU]HGQLFK ZHUVMDFK 2UDFOH

DGPLQLVWUDWRU E\á ÄEH] V]DQV´ ZREHF WDNLHJR ]DGDQLD 7HUD] NRU]\VWDMF ] ZHUVML PR*H

SRZLHG]LHüÄ7RSRWUZDDOHGDVL ]URELü´/RJ0LQHUQLHMHVWQDU] G]iem typu SQL*Plus, które
uruchamiamy i „jest”. Tutaj, mamy do czynienia z zestawem pakietów i perspektyw, które

RGSRZLHGQLRX*\WHVSHáQLQDV]HRF]HNLZDQLD1LHE G SU]HGVWDZLDáVSHF\ILNDFMLW\FK*HSDNLHWyZL
perspektyw -

FLHNDZ\FK RGV\áDP GR GRNXPHQWDFML &KFLDáE\P QDWRPLDVW SRND]Dü Z NROHMQ\FK

SXQNWDFKNURNSRNURNXSU]\JRWRZDQLHLX*\FLH/RJ0LQHUD

2.3.1 Parametr pliku startowego

$E\ Z SHáQL NRU]\VWDü ] XVáXJ /RJ 0LQHUD SRZLQQLP\ SU]\JRWRZDü VSHFMDOQ\ SOLN

VáRZQLNRZ\ &R WR WDNLHJR – R W\P Z QDVW Snym punkcie. Plik ten powstaje w katalogu
wskazywanym parametrem utl_file_dir

SOLNX LQLWRUD -H*HOL WHQ SDUDPWHU QLH MHVW XVWDZLRQ\ GOD

QDV]HMED]\GDQ\FKQDOH*\F]\PSU G]HMSRSUDZLüWRQLHGRSDWU]HQLHLSRQRZQLHZ\VWDUWRZDüED]
danych:

3U]\NáDG3DU

ametr init.ora

utl_file_dir = d:\OraDir\iOPT

3U]\JRWRZDQLHVáRZQLND

Specjalny plik –

VáRZQLNSRZLQLHQE\üSU]\JRWRZDQ\SU]HGZáDFLZ\PVNRU]\VWDQLHP]XVáXJ

/RJ 0LQHUD 6áRZQLN WHQ EXGRZDQ\ MHVW QD SRGVWDZLH DNWXDOQHJR 6áRZQLND 'anych w bazie

GDQ\FK 2UDFOH %H] WHJR ZVSRPDJDMFHJR SOLNX /RJ 0LQHU E G]LH QDP SUH]HQWRZDá QS QD]Z\

NROXPQZSRVWDFLKHNVDGHF\PDOQHMDQLHMHVWWRVSRVyEV]F]HJyOQLHPLá\GODRND6áRZQLNSRZLQLHQ

E\üRGZLH*DQ\SRND*GHM]PLDQLHZVWUXNWXU]HGDQ\FK'ODSURGXNF\MQ\FKED]GDQ\FKPR*HWR

R]QDF]DüMHGQRNURWQJHQHUDFM SOLNXVáRZQLND
%XGRZDVáRZQLNDMHVWUHDOL]RZDQDSU]H]SURFHGXU %8,/'SDNLHWX'%06B/2*015B'SDWU]

SU]\NáDG

3U]\JRWRZDQLHOLVW\SOLNyZG]LHQQLNDGRSU]HJOGDQLD

'RNG QLH SU]\JRWXMHP\ OLVW\ SOLNyZ G]LHQQLND NWyUH FKFHP\ SU]HJOGDü Z\QLNL VL JDQLD GR

WDEOLF\/RJ0LQHUDE GSXVWH3LHUZV]\HOHPHQWOLVW\MHVWMHGQRF]HQLHSRF]WNLHPQRZHMOLVW\L

MHVW GRGDZDQ\ SURFHGXU $''B/2*),/( ] SDNLHWX '%06B/2*015 ] SDUDPHWUHP 1(:
Kole

MQH SOLNL GRGDMHP\ Z\NRU]\VWXMF W VDP SURFHGXU W\P UD]HP ] SDUDPHWUHP $''),/(

SDWU]SU]\NáDG

2.3.4 Aktywacja

7HUD] MHVWHP\ JRWRZL GR DNW\ZDFML VHVML /RJ 0LQHUD 2GE\ZD VL WR ]D SRPRF SURFHGXU\

67$57B/2*015 ] SDNLHWX '%06B/2*015 3R*\WHF]Q\P DOH QLHRERZL]NRZ\P

SDUDPHWUHP SURFHGXU\ MHVW SHáQD QD]ZD áF]QLH ] NDWDORJLHP SOLNX VáRZQLNRZHJR = LQQ\FK

SDUDPHWUyZZDUWRZVSRPQLHüRÄVWDUW7LPH´LÄHQG7LPH´,FKZDUWRFLGRP\OQHVSU]\JRWRZDQH

SU]H]XURF]\FK$PHU\NDQyZMDNQDVW SXMH

startTime

IN DATE default TO_DATE('01-jan-1988','DD-MON-YYYY'),

endTime

IN DATE default TO_DATE('01-jan-2988','DD-MON-YYYY'),

=DWHPDOERQDOH*\Z\VSHF\ILNRZDüMHGRNáDGQLHDOERSU]HGZ\ZRáDQLHPSURFHGXU\]PLHQLüM ]\N
sesji na AMERICAN.

Pon

L*HM ]DSUH]HQWRZDQ\ MHVW NRPSOHWQ\ SU]\NáDG SU]\JRWRZXMF\ /RJ 0LQHUD GR SU]HOHG]HQLD

ELH*F\FKG]LHQQLNyZ

3U]\NáDG3U]\JRWRZDQLH/RJ0LQHU¶DGRSU]HJOGDQLDG]LHQQLNyZELH*F\FK

SQL> declare
2
3 logmnr_dir_ varchar2(200);
4 logmnr_file_ varchar2(30);
5 redo_log_ varchar2(200);
6 start_date_ date;
7 stop_date_ date;
8 new_list_ boolean := TRUE;
9
10 begin
11
12 -- Prepare new LogMiner Dictionary file
13 select value
14 into logmnr_dir_
15 from v$parameter
16 where name = 'utl_file_dir';
17
18 logmnr_file_ := 'lm'||to_char(sysdate,'MMDD')||'.dic';
19
20 sys.dbms_logmnr_d.build(logmnr_file_, logmnr_dir_);
21
22 -- Prepare on line redo log list
23 for i in (select group#
24 from v$log )
25 loop
26
27 select member
28 into redo_log_
29 from v$logfile
30 where nvl(status,'OK') != 'INVALID'
31 and group# = i.group#
32 and rownum = 1;
33
34 if new_list_
35 then
36 sys.dbms_logmnr.add_logfile( redo_log_, sys.dbms_logmnr.new);
37 new_list_ := FALSE;
38 else
39 sys.dbms_logmnr.add_logfile( redo_log_, sys.dbms_logmnr.addfile);
40 end if;
41
42 end loop;
43
44 -- Start LogMiner
45
46 sys.dbms_logmnr.start_logmnr(DictFileName =>
47 logmnr_dir_||'\'||logmnr_file_);
48
49 end;
50
51 /
PL/SQL procedure successfully completed.

.RPHQWDU]GRSU]\NáDGX3U]\JRWRZDQLHVáRZQLND]DMPXMHVWRVXQNRZRQDMZL FHMF]DVX]DWHPMH*HOLVWUXNWXUD
GDQ\FKQLH]PLHQLáDVL

–

QLHZDUWRJHQHURZDüQRZHJRVáRZQLND]DND*G\PUD]HP'ODFK WQ\FKSURSRQXM

SU]HNV]WDáFHQLHSRZ\*V]HJRSU]\NáDGXWDNDE\SU]\JRWRZDü/RJ0LQHUDGROHG]HQLDZF]RUDM

szych

G]LHQQLNyZDUFKLZDOQ\FK3RGSRZLHG(QDOH*\VNRU]\VWDü]WDEOLF\9$5&+,9('B/2*

2.3.4 Raportowanie

7DEOLFDSHUVSHNW\ZDNWyUDSRZ\*HM]DSUH]HQWRZDQ\FKNURNDFKMHVWJRWRZDGRX*\FLDZFHOX

UDSRUWRZDQLD ]DZDUWRFL G]LHQQLNyZ QD]\ZD VL 9/2*015B&217(176 3HáQ VSHF\ILNDFM

PR*QDRGQDOH(üZGRNXPHQWDFMLMD]DFK FDPGRSU]HOHG]HQLDNROHMQHJRSU]\NáDGX

3U]\NáDG5DSRUWRZDQLH]DSRPRF/RJ0LQHU¶D

SQL> select to_char(timestamp,'YYYY.MM.DD HH24:MI:SS') TimeStamp,
2 seg_owner,

3 seg_name,
4 seg_type,
5 table_space,
6 operation,
7 username,
8 session_info,
9 sql_redo,
10 sql_undo
11 from v$logmnr_contents
12 where operation != 'INTERNAL'
13 and seg_owner = 'SCOTT'
14 and seg_name = 'DEPT'
15 order by
timestamp;

TIMESTAMP SEG_OWNER SEG_NAME SEG_TYPE TABLE_SPACE
------------------- -------------------------------- -------------------------------- ---------- ---------------------
OPERATION USERNAME
-------------------------------- ----------
SESSION_INFO
---------------------------------------------------------------------------------------------------------------------

SQL_REDO
----------------------------------------------------------------------------------------------------------------------

SQL_UNDO
----------------------------------------------------------------------------------------------------------------------

2000.07.28 18:26:15 SCOTT DEPT 2 USERS
INSERT SCOTT
LoginUserName = SCOTT, ClientInfo = , OsUserName = JALA, MachineName = IRM\JAREK1
insert into SCOTT.DEPT(DEPTNO,DNAME,LOC) values (10,'ACCOUNTING','NEW YORK');
delete from SCOTT.DEPT where DEPTNO = 10 and DNAME = 'ACCOUNTING' and LOC = 'NEW YORK' and ROWID =
'AAADnoAACAAAAAIAAD';

2000.07.28 18:26:21 SCOTT DEPT 2 USERS
INSERT SCOTT
LoginUserName = SCOTT, ClientInfo = , OsUserName = JALA, MachineName = IRM\JAREK1
insert into SCOTT.DEPT(DEPTNO,DNAME,LOC) values (20,'RESEARCH','DALLAS');
delete from SCOTT.DEPT where DEPTNO = 20 and DNAME = 'RESEARCH' and LOC = 'DALLAS' and ROWID = 'AAADnoAACAAAAAIAAA';

2000.07.28 18:26:27 SCOTT DEPT 2 USERS
INSERT SCOTT
LoginUserName = SCOTT, ClientInfo = , OsUserName = JALA, MachineName = IRM\JAREK1
insert into SCOTT.DEPT(DEPTNO,DNAME,LOC) values (30,'SALES','CHICAGO');
delete from SCOTT.DEPT where DEPTNO = 30 and DNAME = 'SALES' and LOC = 'CHICAGO' and ROWID = 'AAADnoAACAAAAAIAAB';

2000.07.28 18:26:32 SCOTT DEPT 2 USERS
INSERT SCOTT
LoginUserName = SCOTT, ClientInfo = , OsUserName = JALA, MachineName = IRM\JAREK1
insert into SCOTT.DEPT(DEPTNO,DNAME,LOC) values (40,'OPERATIONS','BOSTON');
delete from SCOTT.DEPT where DEPTNO = 40 and DNAME = 'OPERATIONS' and LOC = 'BOSTON' and ROWID = 'AAADnoAACAAAAAIAAC';

2000.07.28 19:23:25 SCOTT DEPT 2 USERS
UPDATE GAMMA
LoginUserName = GAMMA, ClientInfo = , OsUserName = JALA, MachineName = IRM\JAREK1
update SCOTT.DEPT set LOC = 'WARSZAWA' where ROWID = 'AAADnoAACAAAAAIAAD';
update SCOTT.DEPT set LOC = 'NEW YORK' where ROWID = 'AAADnoAACAAAAAIAAD';

2000.07.28 19:24:36 SCOTT DEPT 2 USERS
INSERT SYSTEM
LoginUserName = BETA, ClientInfo = , OsUserName = JALA, MachineName = IRM\JAREK1
insert into SCOTT.DEPT(DEPTNO,DNAME,LOC) values (50,'NEW','ZAKOPANE');
delete from SCOTT.DEPT where DEPTNO = 50 and DNAME = 'NEW' and LOC = 'ZAKOPANE' and ROWID = 'AAADnoAACAAAAAIAAE';

2000.07.28 19:25:03 SCOTT DEPT 2 USERS
DELETE SYSTEM
LoginUserName = SCOTT, ClientInfo = , OsUserName = JALA, MachineName = IRM\JAREK1
delete from SCOTT.DEPT where DEPTNO = 50 and DNAME = 'NEW' and LOC = 'ZAKOPANE' and ROWID = 'AAADnoAACAAAAAIAAE';
insert into SCOTT.DEPT(DEPTNO,DNAME,LOC) values (50,'NEW','ZAKOPANE');

Koment

DU]GRSU]\NáDGX:\QLNLHPVZV]\VWNLHRSHUDFMHQDWDEOLF\'(37]DSDPL WDQHZG]LHQQLNDFK

ELH*F\FK5yZQLH*F]WHU\,16(57¶\Z\NRQDQHSU]HGZáF]HQLHPOHG]HQLD]DSRPRFZ\]ZDODF]\
3R]RVWDáHRSHUDFMHPR*QDSRUyZQDü]Z\QLNLHPZSU]\NáDG]LH&]DVUHDOL]DFMLSROHFHQLDZ\QLyVáQLHFR
SRQDGGZLHPLQXW\SU]\SL FLXG]LHQQLNDFKRUR]PLDU]H0END*G\

2.4. Podsumowanie

2ND]XMHVL *HSRGREQLHMDNSU]\UHMHVWUDFMLSRáF]HWUDG\F\MQHPHWRG\]ELHUDQLDLQIRUPDFMLR

]PLDQDFKGDQ\FKQDGDOVZDUWHX*\FLD1RZLQNDZSRVWDFL/RJ0LQHU¶DVWDQRZLEDUG]RLVWRWQ

SRPRFDOHQLHPR*HE\üWUDNWRZDQDMDNRáDWZHUR]ZL]DQLHSUREOHPXOHG]HQLD]PLDQ

Bibliografia

1. Oracle8i Administrator’s Guide, December 1999, Part No. A76956-01

2. Oracle8i Reference, December 1999, Part No. A76961-01

3. Oracle8i SQL Reference, December 1999, Part No. A76989-01

4. Oracle8i Application Developer’s Guide - Fundamentals, December 1999, Part No. A76939-01

5. Oracle8i Supplied PL/SQL Packages Reference, December 1999, Part No. A76936-01

6. Oracle8i Concepts, December 1999, Part No. A76965-01