"Billing" dla bazy danych Oracle
-DURVáDZàDJRZVNL
iRM Polska
e–mail: jarek.lagowski@irm.at
Abstrakt.
%UDPD]DNáDGXSUDF\6HWNLSUDFRZQLNyZSU]HFKRG]WG\FRG]LHQQLHZRE\GZLHVWURQ\-DNVSUDZG]LüF]\
.RZDOVNL VSG]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 ] GRVWSHP SU]H] ,QWHUnet lub
coraz popularniejszym "outsourcing'iem" aplikacji.
5HIHUDWSRND*HMDNSU]\Z\NRU]\VWDQLXVWDQGDUGRZ\FKGDUPRZ\FKPHFKDQL]PyZSU]\JRWRZDüELOOLQJ
GODED]\GDQ\FK2UDFOH:\NRU]\VWDQHEGWUDG\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]\GDVLQDP]UHV]WWURFKSy(QLHM$OHMDN
]DSDPLWDüFDáKLVWRULSRá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']LNLQLHPXPR*HP\UHMHVWURZDüDQDVWSQLHUDSRUWRZDü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]JOGX QD PR*OLZRü Sy(QLHMV]HJR UDSRUWRZDQLD ]D SRPRF SROHFHQLD
SELECT. Zatem, wybrana linijka z pliku initX.or
DSRZLQQDZ\JOGDüQDVWSXMFR
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]\ü*GDQRSFMREVHUZDFML3ROHFHQLH$8',7ZSRVWDFLSRWU]HEQHMGRUHMHVWUDFMLZV]\VWNLFK
SRGáF]HMHVWQDSUDZGSURVWH
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´EG]LHUHMHVWURZDQH:\áF]HQLHREVHUZDFMLRGE\ZDVL]DSRPRFSROHFHQLD12$8',7
1.1.4. Raportowanie
2SFMHREVHUZDFMLSDPLWDQHVZWDEOLF\6<6$8',7:\QLNLREVHUZDFMLJURPDG]RQHVZ
tab
OLF\6<6$8'SUREOHP\]ZL]DQH]SLHOJQDFMWHMWDEOLF\EGRPyZLRQHZSXQNFLH
&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]\VWSQ\
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
Ko
PHQWDU]GRSU]\NáDGX.ROXPQ\86(5B1$0(L352;<B1$0(ZLDGF]RW\P*HPR*QD]DZ]Lü
REVHUZDFMSRá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´ZQDVWSQ\FKGZyFK
NROXPQDFKPyZLRW\P*HUHMHVWURZDQHEG]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]HQLHZVHVMLEG]LH
notowane.
:\QLNLREVHUZDFMLPR*QDRGQDOH(üZQDVWSXMF\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
8
order by timestamp;
9
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
Ko
PHQWDU]GRSU]\NáDGX6(66,21,'MHVWXQLNDOQ\PLGHQW\ILNDWRUHPVHVML%LH*FDVHVMDPR*HVSUDZG]Lü
VZyMLGHQW\ILNDWRU]DSRPRFIXQNFMLVWDQGDUGRZHM86(5(19µ6(66,21,'¶']LNLWHPXLGHQW\ILNDWRURZL
EG]LHP\PRJOLQDVWSQLHá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]DPNQLFLDED]\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]\VWNLPLGRVWSQ\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
GRVWSQHZ'%$B$8',7B6(66,21DPRJRND]DüVLSR*\WHF]QH'RW\F]RQHVSRVREXLDGUHVX
]NWyUHJRáF]RQRVLGRED]\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
Ko
PHQWDU]GRSU]\NáDGX6HVMDE\áDSRP\OQLH]DNRF]RQVHVMORNDOQ6HVMDáF]\áDVL
Z\NRU]\VWXMFSURWRNyá7&3,31HWOXE64/1HW6HVMDE\áDVHVMORNDOQNWyUDQLHGRV]áDGR
VNXWNX]SRZRGXEáGX25$
-01017.
3LHOJQDFMDWDEO
icy SYS.AUD$
7DEOLFD$8'WZRU]RQDMHVWSRGREQLHMDNWDEOLFHVáRZQLNRZHZSU]HVWU]HQLWDEOLF6<67(0Z
VFKHPDFLH6<67\PVDP\P]QDMGXMHVLZJUXSLHWDEOLFNWyU\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ü
•
.RQWUROUR]PLDUXWDEOLF\$8'
•
Backup tablicy AUD$
3RQLHZD*$8']QDMGXMHVLZSU]HVWU]HQLWDEOLF6<67(0DGRNXPHQWDFMDVWDQRZF]RRGUDG]D
przenoszenie tej tablicy w
LQQHPLHMVFHQLHZROQRGRSXFLüDE\ZVSRVyEQLHNRQWURORZDQ\URVáD
]DS\FKDMFF]\WH*UR]S\FKDMFWQDMZD*QLHMV]SU]HVWU]HZED]LHGDQ\FK2UDFOH=DWHPQDOH*\
RNUHVRZRF]\FLüWWDEOLF]HVWDU\FKUHNRUGyZ$OHFR]URELüMH*HOLEG]LHP\FKFLHOLVLJQüGR
KLVWRULL QDV]HJR G]LHQQLND ÄZHMü L Z\Mü´" 2Wy* MH*HOL ZF]HQLHM QLH ]DSHZQLP\ PHFKDQL]PX
archiwizacji tablicy AUD$ -
QLH EG]LHP\ PLHOL GRVWSX GR XVXQLW\FK ] QLHM UHNRUGyZ QDZHW
MH*HOLG\VSRQXMHP\HNVSRUWHPED]\GDQ\FKVSU]HGPRPHQWXXVXQLcia 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üVLQSUD]QDGRE]DSRPRFSURVWHMSWOL
3U]\NáDG3LHOJQDFMDWDEOLF\$
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;
/
Ko
PHQWDU]GRSU]\NáDGX6LJQLFLHGRWDEOLF\96(66,21SR]ZDODQDPXQLNQüXVXQLFLDUHNRUGXGOD
G]LDáDMFHMVHVML=DSURSRQRZDQ\ZDUXQHNZ\ELHUDUHNRUG\ZF]RUDMV]HLVWDUV]H$GPLQLVWUDWRUSRZLQLHQ
RNUHOLüMDNGáXJKLVWRULPR*HSU]HFKRZ\ZDüZWDEOLF\RU\JLQDOQHM3XVWNRSLWDEOLF\$8'ZW\P
SU]\SDGNXQD]ZDQ$8',7B$5&+PR*QDSU]\JRWRZDü]DSRPRFÄFUHDWHWDEOH$8',7B$5&+
RSFMHBVNáDGRZDQLDDVVHOHFWIURP6<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üGRVWSQDSRF]ZV]\RGZHUVML
Oracle Server 8.1. Zdarzenia na poziomie bazy danych, które mog
E\üSRZRGHPGRXUXFKRPLHQLD
Z\]ZDODF]DVQDVWSXMFH
•
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]DPNQLFLHED]\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']LNLWHPXPR*QDDNW\ZQLHNRQWURORZDüQDV]
ÄEUDP ]DNáDGRZ´ D QLH W\ONR ELHUQLH UHMHVWURZDü ZHMFLD L Z\MFLD 2WR SU]\NáDG\ PR*OLZRFL
Z\]ZDODF]\QLHGRVWSQHGODSROHFHQLD$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\
QLHEGFHMQDOLFLH 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üLQIRUPDFMR]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 XGRVWSQLD QDVWSXMFH
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 XGRVWSQLD
ÄRUDBFOLHQWBLSBDGGUHVV´ -DN ZLGDü LQIRUPDFMD GRVWDUF]DQD SU]H] DWU\EXW\ ]GDU]HQLD QLH MHVW ]E\W
REILWD0XVLP\]DWHP]DGEDüDE\Z\]ZDODF]PyJá]DUHMHVWURZDüQLHFRZLFHMGDQ\FK
%DUG]RERJDW\P(UyGáHPZLHG]\RZáDFLZRFLDFKVHVMLSRáF]enia) jest tablica V$SESSION.
&y*NLHG\MHVWRQDGRVWSQDW\ONRGODDGPLQLVWUDWRUyZ$SU]HFLH*SDPLWDü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áDG8GRVWSQLHQLHLQIRUPDFMLRZá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
GRVWSQDMHVWW\ONRGODX*\WNRZQLND6<6'ODWHJRWH*SRZ\*V]HSROHFHQLDZ\NRQ\ZDQHVMDNR6<6
.RU]\VWDMF]FXG]HJRRELHNWXVNáDGRZDQHJRQSSHUVSHNW\ZDOXESDNLHWX*\WNRZQLNSRWU]HEXMHXSUDZQLHQLD
W\ONRQDWHQ*HRELHNW:V]HONLHZHZQWU]QHRGZRáDQLDUHDOL]RZDQHVGRP\OQLH]SUDZDPLZáDFLFLHOD
RELHNWXVNáDGRZDQHJRZZHUVMLSRMDZLáDVLPR*OLZRüXUXFKDPLDQLDRELHNWyZVNáDGRZDQ\FK]SUDZDPL
ZRáDMFHJR'ODWHJRWH*Z\VWDUF]\QDGDüXSUDZQLHQLDQDSHUVSHNW\ZNRFRZ
– 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
EG]LHSU]\JRWRZDQLHPLHMVFDVNáDGRZDQLDLQIRUPDFML
3U]\NáDG3U]\JRWRZDQLHWDEOLF\JURPDG]FHMKLVWRULSRá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\WRMDNRSUDFGRPRZGOD
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]DPNQLFLHED]\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
SDPLWDü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]QLFKQLHEG]LHPR*OLZHHIHNW\ZQHUDSRUWRZDQLH]
WDEOLF NWyUH SRWHQFMDOQLH PRJ SU]HFKRZ\ZDü ZLHOH W\VLF\ 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(üVLZSOLNDFKG]LHQQLNDSRZWyU]HZ\MWNLHPVRSHUDFMH]RSFM12/2**,1*–
NWR X*\ZD WHM RSFML MHVW VDP VRELH ZLQLHQ Z SU]\SDGNX DZDULL $* GR ZHUVML 2UDFOH QLH
XGRVWSQLDá *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 GRVWSQD GR UDSRUWRZDQLD ]D SRPRF 6(/(&7 =DWHP F]\ SRWU]HED F]HJR
jeszcze? Tak. Potrzeba selektywnej rejestracji zmian. Selektywna rejestracja zmian pozwala
QDVWSQLH á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 GRVWSQ\P
RGZHUVMLMHVWX*\FLHZ\]ZDODF]\G]LDáDMF\FKQDSROHFHQLDFK'0/QDZ\EUDQ\FKWDEOLFDFK
:\PDJDWRWURFKSUDF\DOHMDNEGVWDUDáVLSRND]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]DVLGRVWZLHUG]HQLD*HRZV]HPGDQ\X*\WNRZQLNQDGDQHMWDEOLF\GRNRQDá]PLDQ
GDQ\FK1LHZLDGRPRQDWRPLDVWMDNLHGDQH]RVWDá\ZVWDZLRQHXVXQLWHOXE]PLHQLRQH-H*HOLWHQ
SR]LRP LQIRUPDFML MHVW Z\VWDUF]DMF\ WR ]DFKFDP 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ü
•
WDEOLFNWyUFKFHP\OHG]Lü
•
RSHUDFMNWyUFKFHP\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 SDPLWDMFD 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+,678GRVWSQLWRSHáQLQIRUPDFMRVHVMLPRG\ILNXMFHMGDQHDWDN*HSR]ZROLZUD]LHSRWU]HE
y,
SRáF]\üWDEOLFHUHMHVWUXMFHVHVMHL]PLDQ\.ROXPQD$&7,21EG]LHSU]HFKRZ\ZDüNRGDNFML,
– INSERT, U
– UPDATE, D –
'(/(7(6HNZHQFMD'$7$B+,67B6(4EG]LHJHQHURZDüXQLNDOQ\NOXF]GODWDEOLF
UHMHVWUXMF\FK]PLDQ\2F]\ZLFLHQDOH*\SDPLWDü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']LNLQRZHMZá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*\SDPLWDü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áXVXQLW\
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 SDPLWDü ZDUWRFL NROXPQ QLH
zmienionych.
•
:SU]\SDGNXUHMHVWUDFMLSROHFH,16(57L'(/(7(QLHZDUWRSDPLWDü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]QLFKQLHEG]LHPR*OLZHHIHNW\ZQH
raportowanie z tablic, które p
RWHQFMDOQLHPRJSU]HFKRZ\ZDüZLHOHW\VLF\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=DWHPRSLHUDMFVLW\ONRQD
G]LHQQLNDFKELH*F\FKQLHMHVWHP\ZVWDQLHZRJyOQ\PSU]\SDGNX]DSHZQLüUDSRUWRZDQLD]PLDQ
NWyUHPLDá\PLHMVFHQDZHWQLHGDOHMMDNQSJRG]LQWHPX
=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]HNLZDQLD1LHEGSU]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 QDVWSnym 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]\PSUG]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 EG]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üMHGQRNURWQJHQHUDFMSOLNXVá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 VLJDQLD GR
WDEOLF\/RJ0LQHUDEGSXVWH3LHUZV]\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\NDQyZMDNQDVWSXMH
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]DMPXMHVWRVXQNRZRQDMZLFHMF]DVX]DWHPMH*HOLVWUXNWXUD
GDQ\FKQLH]PLHQLáDVL
–
QLHZDUWRJHQHURZDüQRZHJRVáRZQLND]DND*G\PUD]HP'ODFKWQ\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]DFKFDPGRSU]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]DSDPLWDQHZG]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]\SLFLXG]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