Πώς ενεργοποιούμε το auditing σε Oracle Database

Σχετικά με
τελευταίες δημοσιεύσεις

Το ταξίδι άρχισε το 2008 όπου ξεκίνησα να εργάζομαι επίσημα στον τομέα της τεχνολογίας πληροφορικής (IT). Ξεκινώντας το πρώτο εξάμηνο της σχολής κατάλαβα μια ιδιαίτερη έλξη προς τις βάσεις δεδομένων και τους αυτοματισμούς.Έχω ασχοληθεί με βάσεις δεδομένων όπως Microsoft SQL Server / Oracle Database, ανάλυση δεδομένων και αυτοματισμούς με τη χρήση γραμμής εντολών (CLI), Visual Basic for Applications και Python. Μέσα από τα χρόνια εμπειρίας εξέλιξα αυτές τις δυνατότητες ώστε να μπορώ να κάνω τη ζωή μου πιο εύκολη. Για μένα ο σκοπός του κάθε IT guy αλλά και κάθε υπαλλήλου γραφείου είναι το να έχει τις γνώσεις ώστε μέσω εργαλείων να μπορεί να δουλεύει λίγο αλλά να παράγει πολύ. Μέσα από αυτό τον ιστότοπο του DataPlatform.gr προσπαθώ να προσφέρω γνώσεις και να προτείνω λύσεις σε καθημερινά προβλήματα.

Πιστοποιήσεις:

certs

Τελευταίες δημοσιεύσεις από Stratos Matzouranis (Προβολή όλων)

Σε αυτό το άρθρο θα δούμε τα βήματα και τις επιλογές που έχουμε για να ενεργοποιήσουμε το auditing σε μία Oracle Database. Μέσα από το auditing μπορούμε να καταγράφουμε ποιος και με τι τρόπο κάνει προσπέλαση ή αλλαγές στα δεδομένα της.

Το auditing μπορούμε να το ενεργοποιήσουμε πάνω σε συγκεκριμένους χρήστες, σε συγκεκριμένα objects ή και σε όλη τη βάση δεδομένων.

Πώς ενεργοποιείται

Για αρχή πρέπει να δούμε άπο ένα command window στη βάση δεδομένων, αν είναι ενεργοποιημένη η παράμετρος audit_trail:

SQL>  show parameter audit_trail

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB, EXTENDED

Αν δεν είναι, την ενεργοποιούμε με τις επιλογές db,extended. To extended χρειάζεται ώστε να κρατάμε εκτός από το ποιος προσπέλασε τα δεδομένα αλλά και το sql statement μαζί με τις παραμέτρους του.

Για να πάρει την αλλαγή, χρειάζεται να γίνει restart το instance:

alter system set audit_trail = db,extended scope=spfile;

shutdown immediate;

startup;

Audit σε συγκεκριμένο object

Αν θέλουμε να κάνουμε audit σε κάποιον συγκεκριμένο πίνακα τρέχουμε το παρακάτω:

audit select on SCOTT.DEPT by access;

--noaudit select on SCOTT.DEPT;

*Τo by access ορίζει ότι κάθε φορά που ο ίδιος χρήστης από το ίδιο session πάει να προσπελάσει τον πίνακα να το κρατήσει σαν ξεχωριστή εγγραφή

**Με την ίδια εντολή ξεκινώντας με το noaudit χωρίς το by access το κάνουμε disable.

Για να δούμε ότι ενεργοποιήθηκε, τρέχουμε το παρακάτω query στο view dba_obj_audit_opts και θα δούμε ότι γράφει Α/Α στην στήλη του select:

select * from DBA_OBJ_AUDIT_OPTS where object_name ='DEPT';

*θα μπορούσαμε να προσθέσουμε και άλλα actions όπως insert,update,delete,alter κ.τ.λ

Πώς ενεργοποιούμε το auditing σε Oracle Database — 01

Που βρίσκουμε τα αποτελέσματα του auditing

Αφού τρέξουμε ένα select στο object που ενεργοποιήσαμε το audit προηγουμένως, π.χ.:

select * from scott.dept;

Θα δούμε την πληροφορία του auditing στο dba_audit_trail, που είναι view του πίνακα sys.aud$.

Κάνοντας το select στο view θα δούμε πληροφορίες όπως από ποιο username έγινε, από ποιο τερματικό, τι ώρα, σε ποιο object και τι ακριβώς sql query έτρεξε:

--view
select username,os_username,userhost,terminal,timestamp,owner,obj_name,action_name,SQL_TEXT,SQL_BIND from dba_audit_trail; 

--table select * from sys.aud$;

Audit σε συγκεκριμένο χρήστη

Μπορούμε αντίστοιχα με ενεργοποιήσουμε το auditing μόνο σε συγκεκριμένο χρήστη και σε συγκεκριμένα actions όπως select, insert, update, delete:

audit select table,update table,insert table, delete table,execute procedure BY stratos by access;

--noaudit select table,update table,insert table, delete table,execute procedure BY stratos;

Τρέχοντας select στο view dba_stmt_audit_opts θα δούμε ακριβώς τι ορίσαμε στο auditing να καταγράφει:

select * from dba_stmt_audit_opts where user_name='STRATOS';

Κάνοντας ένα update με αυτόν τον χρήστη…:

update scott.dept set LOC='FLORIDA' where LOC='BOSTON';
commit;

Θα δούμε ότι το κατέγραψε και αυτό στο dba_audit_trail view.

Audit σε συγκεκριμένα actions

Αντίστοιχα μπορούμε να ορίσουμε να ενεργοποιηθεί το auditing σε συγκεκριμένα actions όπως select,insert,update,delete:

audit select table,update table,insert table, delete table,execute procedure by access;

--noaudit select table,update table,insert table, delete table,execute procedure;

Τρέχοντας select στο view dba_stmt_audit_opts θα δούμε τι ορίσαμε να καταγράφει με το username αυτή τη φορά κενό:

Audit σε ότι object φτιαχτεί μελλοντικά

Αν θέλουμε να ενεργοποιήσουμε το auditing σε συγκεκριμένα actions σε ότι object καινούργιο δημιουργήσουμε προσθέτουμε την παράμετρο on default, όπως παρακάτω:

audit select,insert,update,delete,alter,execute on default by access;

--noaudit select,insert,update,delete,alter,execute on default;

Οπότε, μόλις φτιάξουμε έναν πίνακα και προσθέσουμε μια εγγραφή όπως εδώ:

create table stratos.stratos (id int);
insert into stratos.stratos values (1);
commit;

Θα προστεθεί στο view dba_obj_audit_opts:

select * from dba_stmt_audit_opts;

Και αντίστοιχα θα καταγραφεί στο view dba_audit_trail:

select username,os_username,userhost,terminal,timestamp,owner,obj_name,action_name,SQL_TEXT,SQL_BIND from dba_audit_trail;

Audit παντού

Αν θέλουμε να έχουμε ενεργοποιημένο το audit στα πάντα, τρέχουμε το παρακάτω:

audit all by access;

--noaudit all;

Αν τότε τρέξουμε select στο view dba_stmt_audit_opts, θα δούμε ότι θα περιέχει όλα τα actions που υπάρχουν:

select * from dba_stmt_audit_opts;

Πώς συντηρούμε το auditing

Επειδή αυτές οι εγγραφές θα καταλαμβάνουν μεγάλο χώρο σε συστημικά tablespaces θα πρέπει ανά διαστήματα να διαγράφουμε παλιές εγγραφές από τον πίνακα sys.AUD$ που βλέπει το view dba_audit_trail.

Πρώτα απο όλα πρέπει να έχουμε κάνει ένα init cleanup το οποίο ορίζουμε να μεταφέρονται τα audits records από το schema SYSTEM στο SYSAUX:

BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP(
              audit_trail_type   => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
      default_cleanup_interval   => 12 /* hours */);
END;

Στη συνέχεια ελέγχουμε οτι έχει ενεργοποιηθεί:

SET SERVEROUTPUT ON
BEGIN
IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
DBMS_OUTPUT.put_line('YES');
ELSE
DBMS_OUTPUT.put_line('NO');
END IF;
END;
/

Για να σβήσουμε τις παλιές εγγραφές manual τρέχουμε το παρακάτω:

alter table sys.AUD$ nologging;
delete from sys.aud$ 
where TIMESTAMP# <= sysdate-30;
commit;

Αν θέλουμε να φτιαχτεί ένα job που θα σβήνει τις εγγραφές άνω των 30 ημερών αυτόματα, τρέχουμε το παρακάτω:

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'JOB_PURGE_AUDIT_RECORDS',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, TRUNC(SYSTIMESTAMP)-30); END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
end_date => NULL,
enabled => TRUE,
comments => 'Update last_archive_timestamp');
END;
/

Για να δούμε πότε θα έτρεξε τελευταία φορά το job και πότε θα ξανατρέξει τρέχουμε το παρακάτω:

select LAST_START_DATE,LAST_RUN_DURATION,NEXT_RUN_DATE from dba_scheduler_jobs where job_name= 'JOB_PURGE_AUDIT_RECORDS';

Πώς αποθηκεύουμαι τα audit trail files σε ξεχωριστό tablespace

Για να μην γεμίσουμε SYS / SYSTEM / SYSAUX tablespace έχουμε την δυνατότητα να φτιάξουμε ένα καινούργιο tablespace και να ορίσουμε σε αυτό να αποθηκεύεται το auditing.

Για να γίνει αυτό πρώτα φτιάχνουμε το νεό αυτό tablespace:

create tablespace AUDIT_TS datafile '/oracle/oradata/orcl/audit_ts_001.dbf' size 100m autoextend on next 64m maxsize 32767m;

Και έπειτα τρέχουμε την παρακάτω εντολή βάζοντας το όνομα του tablespace που μόλις φτιάξαμε:

BEGIN DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, audit_trail_location_value => 'AUDIT_TS');
END;
/

Έτσι τρέχοντας το παρακάτω query μπορούμε να επιβεβαιώσουμε οτι ο πίνακας AUD$ ανήκει στο καινούργιο tablespace:

select * from dba_tables where table_name ='AUD$';

Ξαναρυθμίζουμε το init cleanup ώστε να είμαστε σίγουροι ότι οι εγγραφές θα μεταφέρονται στο καινούργιο tablespace:

BEGIN
DBMS_AUDIT_MGMT.DEINIT_CLEANUP(
  AUDIT_TRAIL_TYPE  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD);
END;

BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP(
              audit_trail_type   => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
      default_cleanup_interval   => 12 /* hours */);
END;

SET SERVEROUTPUT ON
BEGIN
IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
DBMS_OUTPUT.put_line('YES');
ELSE
DBMS_OUTPUT.put_line('NO');
END IF;
END;
/

Σε περίπτωση ORA-01403 error

Σε περίπτωση που έχουμε κάνει αναβάθμιση από παλαιότερη έκδοση ενδέχεται η εντολή αλλαγής tablespace να μας εμφανίσει το παρακάτω σφάλμα:

ORA-01403: no data found

Σε αυτή την περίπτωση ελέγχουμε σε ποιό schema είναι ο πίνακας AUD$:

select * from dba_tables where table_name ='AUD$';

Αν όντως είναι σε διαφορετικό π.χ. στο SYSTEM αντί για το SYS τότε θα πρέπει να κρατήσουμε backup το DDL που πίνακα:

select dbms_metadata.get_ddl('TABLE', 'AUD$','SYSTEM') from dual;

Έπειτα σβήνουμε το synonym και τον πίνακα και τα ξαναφτιάχνουμε με το DDL που κρατήσαμε στο schema SYS:

drop table system.aud$
drop synonym sys.aud$;
 
  CREATE TABLE "SYS"."AUD$" 
   (  "SESSIONID" NUMBER NOT NULL ENABLE, 
  "ENTRYID" NUMBER NOT NULL ENABLE, 
  "STATEMENT" NUMBER NOT NULL ENABLE, 
  "TIMESTAMP#" DATE, 
  "USERID" VARCHAR2(128), 
  "USERHOST" VARCHAR2(128), 
  "TERMINAL" VARCHAR2(255), 
  "ACTION#" NUMBER NOT NULL ENABLE, 
  "RETURNCODE" NUMBER NOT NULL ENABLE, 
  "OBJ$CREATOR" VARCHAR2(128), 
  "OBJ$NAME" VARCHAR2(128), 
  "AUTH$PRIVILEGES" VARCHAR2(16), 
  "AUTH$GRANTEE" VARCHAR2(128), 
  "NEW$OWNER" VARCHAR2(128), 
  "NEW$NAME" VARCHAR2(128), 
  "SES$ACTIONS" VARCHAR2(19), 
  "SES$TID" NUMBER, 
  "LOGOFF$LREAD" NUMBER, 
  "LOGOFF$PREAD" NUMBER, 
  "LOGOFF$LWRITE" NUMBER, 
  "LOGOFF$DEAD" NUMBER, 
  "LOGOFF$TIME" DATE, 
  "COMMENT$TEXT" VARCHAR2(4000), 
  "CLIENTID" VARCHAR2(128), 
  "SPARE1" VARCHAR2(255), 
  "SPARE2" NUMBER, 
  "OBJ$LABEL" RAW(255), 
  "SES$LABEL" RAW(255), 
  "PRIV$USED" NUMBER, 
  "SESSIONCPU" NUMBER, 
  "NTIMESTAMP#" TIMESTAMP (6), 
  "PROXY$SID" NUMBER, 
  "USER$GUID" VARCHAR2(32), 
  "INSTANCE#" NUMBER, 
  "PROCESS#" VARCHAR2(16), 
  "XID" RAW(8), 
  "AUDITID" VARCHAR2(64), 
  "SCN" NUMBER, 
  "DBID" NUMBER, 
  "SQLBIND" CLOB, 
  "SQLTEXT" CLOB, 
  "OBJ$EDITION" VARCHAR2(128), 
  "RLS$INFO" CLOB, 
  "CURRENT_USER" VARCHAR2(128)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" 
LOB ("SQLBIND") STORE AS BASICFILE (
  TABLESPACE "SYSTEM" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
  NOCACHE LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) 
LOB ("SQLTEXT") STORE AS BASICFILE (
  TABLESPACE "SYSTEM" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
  NOCACHE LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) 
LOB ("RLS$INFO") STORE AS BASICFILE (
  TABLESPACE "SYSTEM" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION 
  NOCACHE LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))

Δίνουμε το δικαίωμα του delete στο delete_catalogue_role και ξαναδοκιμάζουμε την εντολή αλλαγής tablespace:

GRANT DELETE ON AUD$ TO DELETE_CATALOG_ROLE;

BEGIN DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, audit_trail_location_value => 'AUDIT_TS');
END;
/