AUDIT czyli "Widzę Cię". Śledzenie działań użytkowników w Oracle.

Niekiedy zachodzi potrzeba obserwowania co się dzieje z obiektem w bazie danych. Przykładowo kto wrzuca dane, zmienia kluczowe wartości - my chcemy wiedzieć kto zmieniał np. tabelę i kiedy. Możemy też chcieć obserwować poczynania konkretnego użytkownika niezależnie od tego na jakich obiektach operuje. Do tego celu służy nam AUDIT.
Opcja monitoringu tego typu jest domyślnie wyłączona w Oracle. Musisz więc ją ustawić, a następnie zrestartować bazę.
 Zaloguj się jako SYS a następnie wydaj polecenie :

alter system set audit_trail=true scope=spfile;


Ta komenda włącza opcję systemową audytu. Dodatek SCOPE = SPFILE na końcu oznacza że zmiany mają trafić do binarnego pliku parametrów bazy. Gdybyśmy tego nie dodali, system próbowałby wykonać to polecenie z domyślnym parametrem SCOPE=BOTH - czyli wrzucić do pliku parametrów oraz ustawić wartość dla sesji. Nie mógłby tego zrobić, ponieważ AUDIT_TRAIL nie jest parametrem który można zmieniać dynamicznie na włączonej bazie.
Jak już to włączysz zrestartuj bazę. Jako sys wykonaj SHUTDOWN IMMEDIATE (pamiętaj że ta opcja zerwie wszystkie sesje i wycofa transakcje. Miej to na uwadze jeśli koleżanka z działu obok wykonuje zapytanie trwające kilka godzin i lubi krzyczeć :) ).
Czas włączyć bazę. Zaloguj się jako sys do wyłączonej (!) bazy. Aby móc to zrobić, skorzystaj z SQL*Plusa i zaloguj się bez podawania "@adres_ip". Co za tym idzie-musisz zrobić to z komputera na którym stoi serwer Oracle (choćby przez shella). Jeśli wyłączałeś bazę z SQL*Plusa właśnie to nie musisz się przelogowywać.

Nie zapomnij o dodaniu "as sysdba" :).
Odpal bazę wpisując STARTUP;

Teraz masz już włączoną opcję audytu. Możesz się upewnić wywołując:

select * from v$parameter where name like '%trace_enabled%';



Śledzenie użytkownika


Dla przykładu stworzymy sobie jakiegoś użytkownika, zaczniemy go śledzić a następnie z jego poziomu wykonamy kilka czynności.

Stwórz użytkownika i nadaj mu uprawnienia do łączenia się do bazy danych i tworzenia obiektów w swoim
schemacie. Następnie włącz śledzenie użytkownika:

Kodzik:

create user przykladowy identified by przykladowy;

grant connect, resource to przykladowy;

audit all by przykladowy;

audit select table, insert table, delete table,update table by przykladowy by access;

Możesz oczywiście wybrać które działania użytkownika chcesz monitorować. W tej drugiej linijce z audit wymieniasz wtedy tylko to co Cię interesuje.

Opcja BY ACCESS powoduje zachowanie informacji o działaniu użytkownika nawet jeśli już w ramach danej sesji wykonał instrukcję tego samego typu (np. więcej niż 1 raz wykonał selekta na jakiejś tabeli). Przeciwieństwem dla tej opcji jest BY SESSION, ale wtedy dany tym działania jest rejestrowany tylko raz.

Jeśli by zdarzyła się taka sytuacja, że rozpoczynasz śledzenie jakiegoś usera który już istnieje i do tego jest podłączony, to by śledzenie zaczęło działać musi się on przelogować lub musisz wymusić jego rozłączenie.

Zaloguj się teraz na tego nowego użytkownika. Wykonaj kilka operacji, najlepiej takich które chcesz śledzić :)

Kodzik:

create table produkty(

nr number,

nazwa varchar2(500),

cena number

);

insert into produkty values (1,'komputer',2500);

insert into produkty values (2,'telewizor',1800);

insert into produkty values (3,'pralka',1230);

insert into produkty values (4,'mikrofalówka',450);

update produkty set cena=320 where nr=4;

delete from produkty where nr=3;

select * from produkty;

Aby przejrzeć wyniki śledzenia wróć do użytkownika SYS. W Oracle jest tabelka dba_audit_trail która przechowuje wyniki śledzenia. Zadając do niej odpowiednie zapytanie, jesteś w stanie prześledzić działalność konkretnego użytkownika.

Kodzik:

SELECT username,

       extended_timestamp,

       owner,

       obj_name,

       action_name

FROM   dba_audit_trail

WHERE  USERNAME LIKE 'PRZYKLADOWY'

and owner not like 'SYS'

ORDER BY timestamp;

Ten warunek and owner not like 'SYS' jest po to by nie wyświetlać zmian w tabelach systemowych które wykonują się w związku z działalnością użytkownika.

Możesz też wyświetlić informacje na temat działań związanych z obiektami których właścicielem (twórcą) jest wybrany użytkownik:

Kodzik:

SELECT username,

       extended_timestamp,

       owner,

       obj_name,

       action_name

FROM   dba_audit_trail

WHERE  owner LIKE 'PRZYKLADOWY'

ORDER BY timestamp;

Wyświetliłem tylko kilka kolumn, bo tylko te nas właściwie interesują. W kolumnie USERNAME jest informacja kto wykonał dane działanie, w kolumnie OWNER na czyim obiekcie (tutaj akurat nasz user zmieniał dane we własnym obiekcie). OBJ_NAME zawiera informacje o nazwie obiektu na którym zostały wykonane działania a ACTION_NAME trzyma info o tym jaki typ działań został przez usera wykonany.

ZaCOMMITuj zmiany w tabeli którą utworzyłeś bo zaraz będziemy z tych zmian korzystać a chcemy mieć dostęp do danych dla innych użyszkodników.

Śledzenie działań na obiekcie

W tym przykładzie będziemy śledzić kto zmienia zawartość kontretnego obiektu, niezależnie od tego który to użyszkodnik. 

Z poziomu SYSa wykonaj:

audit delete,insert,update on przykladowy.produkty by access;

Polecenie to włączy obserwację tabeli produkty w schemacie użytkownika przykładowy. Obserwowane będzie jedynie kasowanie, wrzucanie oraz zmiana danych w tej tabeli. Możesz oczywiście wymienić co chcesz, wybrać część z tych poleceń albo np. dodać select :)

Nadaj jeszcze uprawnienia użytkownikowi HR do wykonywania wszelakich działań na tabeli produkty użytkownika przykładowy:

grant all on przykladowy.produkty to hr;

Zaloguj się teraz jako user HR i dodaj jakieś dane, wykonaj selecta, zmień coś w tabeli produkty. Ja zrobiłem selecta i zmieniłem cenę jednego produktu:

select * from przykladowy.produkty;

update przykladowy.produkty set cena=1650 where nr=2;

Trzeba trochę zmodyfikować nasze wcześniejsze zapytanie. W tym przypadku zmieniłem po prostu warunek where. Wyświetlam informacje o działaniach tylko te które mająw nazwie obiektu 'PRODUKTY' a więc nazwę tabeli która mnie interesuje.Dla wszelkiej pewności możesz dodać jeszcze warunek dla kolumny OWNER by mieć pewność że chodzi  o tabelę konkretnego użytkownika. Tabela o takiej samej nazwie może występować przecież w kilku schematach.

Kodzik:

SELECT username,

       extended_timestamp,

       owner,

       obj_name,

       action_name

FROM   dba_audit_trail

WHERE  obj_name LIKE 'PRODUKTY'

ORDER BY timestamp;