auditing
Differences
This shows you the differences between two versions of the page.
| Both sides previous revisionPrevious revision | |||
| auditing [2018/12/08 12:49] – created 0.0.0.0 | auditing [2025/02/20 11:41] (current) – external edit 127.0.0.1 | ||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ====== Auditing ====== | + | * [[https:// |
| * [[https:// | * [[https:// | ||
| * [[https:// | * [[https:// | ||
| * [[https:// | * [[https:// | ||
| * [[https:// | * [[https:// | ||
| - | =====See the latest activity on the database===== | ||
| - | < | ||
| - | =====Has your database migrated to Unified Auditing? | ||
| - | < | ||
| - | =====Show the audit trail (12c)===== | + | < |
| - | <code> | + | TRADITIONAL AUDITING |
| - | =====Disable unified auditing===== | + | Depends on db init parameter like audit_trail, |
| - | < | + | Writes audit records to different trails depending upon audit type All audit trails are writeen to single trail |
| - | < | + | sys records are written to os .aud files sys records are written to unified_audit_trail |
| - | < | + | Auditing not possible for rman/ |
| - | < | + | Each audit record was written to disk immediately, |
| + | and later will be flushed to disk which improves performance | ||
| + | Auditing need to enabled for each action/ | ||
| + | which can enabled or disables easily | ||
| + | </ | ||
| - | =====12c auditing is protected===== | + | ==== Relink Oracle binaries to enable Unified Auditing ==== |
| + | After having shutdown any instances and listeners, ... | ||
| + | < | ||
| + | cd $ORACLE_HOME/ | ||
| + | make -f ins_rdbms.mk uniaud_on ioracle | ||
| + | </ | ||
| + | then restart evertything that was shutdown. | ||
| + | |||
| + | ==== Audit records for unsuccessful login attempts ==== | ||
| + | If a user fails to logon and auditing of failed logons is enabled with | ||
| + | < | ||
| + | audit session whenever not successful; | ||
| + | </ | ||
| + | then | ||
| + | < | ||
| + | col ntimestamp# for a30 heading " | ||
| + | col userid for a20 heading " | ||
| + | col userhost for a15 heading " | ||
| + | col spare1 for a15 heading "OS User" | ||
| + | col comment$text for a80 heading " | ||
| + | |||
| + | select ntimestamp# | ||
| + | , | ||
| + | , | ||
| + | , | ||
| + | , | ||
| + | from sys.aud$ | ||
| + | order by 1; | ||
| + | </ | ||
| + | |||
| + | ==== Check if auditing is enabled (or what is being audited?) ==== | ||
| + | === where unified auditing is not used === | ||
| + | < | ||
| + | select * from dba_obj_audit_opts; | ||
| + | </ | ||
| + | === where unified auditing is used === | ||
| + | < | ||
| + | set lines 1000 pages 100 | ||
| + | col user_name | ||
| + | col policy_name for a30 | ||
| + | col enabled_opt for a25 | ||
| + | select * | ||
| + | from | ||
| + | / | ||
| + | </ | ||
| + | < | ||
| + | USER_NAME | ||
| + | -------------------- ------------------------------ ------------------------- --- --- | ||
| + | ALL USERS ORA_SECURECONFIG | ||
| + | ALL USERS ORA_LOGON_FAILURES | ||
| + | |||
| + | 2 rows selected. | ||
| + | </ | ||
| + | |||
| + | ==== See the latest activity on the database (non-unified) ==== | ||
| + | < | ||
| + | set lines 1000 pages 100 | ||
| + | col username for a20 | ||
| + | col userhost for a20 | ||
| + | select username | ||
| + | , userhost | ||
| + | , count(*) num_connections | ||
| + | , max(timestamp) last_time | ||
| + | from | ||
| + | group by username | ||
| + | , userhost | ||
| + | / | ||
| + | set lines 80 | ||
| + | </ | ||
| + | ==== Has your database migrated to (Pure) Unified Auditing? ==== | ||
| + | < | ||
| + | select value from v$option where parameter = ' | ||
| + | </ | ||
| + | |||
| + | ==== Show the unified audit trail (12c+) ==== | ||
| + | < | ||
| + | col unified_audit_policies for a30 | ||
| + | col action_name for a20 | ||
| + | select unified_audit_policies | ||
| + | , action_name | ||
| + | , count(*) | ||
| + | from | ||
| + | group by unified_audit_policies | ||
| + | , action_name | ||
| + | / | ||
| + | </ | ||
| + | < | ||
| + | set lines 2000 | ||
| + | col dbusername | ||
| + | col event_timestamp for a23 | ||
| + | col sql_text | ||
| + | |||
| + | select dbusername | ||
| + | , event_timestamp | ||
| + | , sql_text | ||
| + | from | ||
| + | order by event_timestamp | ||
| + | / | ||
| + | </ | ||
| + | ==== Disable unified auditing ==== | ||
| + | < | ||
| + | noaudit policy ORA_SECURECONFIG; | ||
| + | </ | ||
| + | < | ||
| + | Noaudit succeeded. | ||
| + | </ | ||
| + | |||
| + | ==== 12c auditing is protected ==== | ||
| The Unified Audit Trail is well protected. Not even SYS can mess with it. | The Unified Audit Trail is well protected. Not even SYS can mess with it. | ||
| - | < | + | < |
| - | =====Manage the audit trail (12c)===== | + | select table_name from dba_tables where owner=' |
| - | The only way to work with the audit trail is with the DBMS_AUDIT_MGMT | + | |
| + | TABLE_NAME | ||
| + | -------------------------------------------------------------------------------------------------------------------------------- | ||
| + | AUD$UNIFIED | ||
| + | |||
| + | |||
| + | SQL> truncate table audsys." | ||
| + | truncate table audsys." | ||
| + | * | ||
| + | ERROR at line 1: | ||
| + | ORA-55941: DML and DDL operations are not allowed on table " | ||
| + | |||
| + | SQL> drop user audsys cascade; | ||
| + | drop user audsys cascade | ||
| + | * | ||
| + | ERROR at line 1: | ||
| + | ORA-28050: specified user or role cannot be dropped | ||
| + | </ | ||
| + | === Manage the audit trail (12c+) === | ||
| + | ==== Check whether audit trail cleanup has been initialised ==== | ||
| + | < | ||
| + | set serveroutput on | ||
| + | begin | ||
| + | if dbms_audit_mgmt.is_cleanup_initialized (dbms_audit_mgmt.audit_trail_aud_std) then | ||
| + | dbms_output.put_line(' | ||
| + | else | ||
| + | dbms_output.put_line(' | ||
| + | dbms_audit_mgmt.init_cleanup( | ||
| + | audit_trail_type | ||
| + | default_cleanup_interval => 24 /* hours */); | ||
| + | dbms_output.put_line(' | ||
| + | end if; | ||
| + | end; | ||
| + | / | ||
| + | </ | ||
| + | The only way to work with the audit trail is with the dbms_audit_mgmt | ||
| Before the audit trail can be purged, you must initialise it. This is a one-off execution of the init_cleanup procedure. | Before the audit trail can be purged, you must initialise it. This is a one-off execution of the init_cleanup procedure. | ||
| - | < | + | ==== Initialise the cleanup ==== |
| + | < | ||
| + | begin | ||
| + | dbms_audit_mgmt.init_cleanup ( | ||
| + | audit_trail_type | ||
| + | default_cleanup_interval => 24 /* hours */ | ||
| + | ); | ||
| + | end; | ||
| + | / | ||
| - | =====Set auditing properties===== | + | PL/SQL procedure successfully completed. |
| - | < | + | </ |
| - | =====Check whether audit trail cleanup has been initialised===== | + | |
| - | < | + | |
| - | ====Check audit settings==== | + | |
| - | < | + | |
| - | ====When was audit trail last purged?==== | + | |
| - | < | + | |
| - | ====Set a date to purge to==== | + | ==== Set auditing properties ==== |
| + | < | ||
| + | exec dbms_audit_mgmt.set_audit_trail_property ( audit_trail_type | ||
| + | , audit_trail_property | ||
| + | , audit_trail_property_value => 1000 | ||
| + | ); | ||
| + | </ | ||
| + | === Check audit settings === | ||
| + | < | ||
| + | col parameter_name | ||
| + | col parameter_value for a35 | ||
| + | select * from dba_audit_mgmt_config_params order by 3; | ||
| + | </ | ||
| + | === When was audit trail last purged? === | ||
| + | < | ||
| + | set lines 1000 pages 100 | ||
| + | col audit_trail | ||
| + | col last_archive_ts for a40 | ||
| + | |||
| + | select * | ||
| + | from | ||
| + | order by 1,3 | ||
| + | / | ||
| + | </ | ||
| + | < | ||
| + | AUDIT_TRAIL | ||
| + | -------------------- ------------ ---------------------------------------- ----------- --------------------------------- | ||
| + | OS AUDIT TRAIL 1 17-JUL-20 07.00.00.000000 AM +01: | ||
| + | OS AUDIT TRAIL 1 12-MAR-21 07.00.03.000000 AM +01: | ||
| + | STANDARD AUDIT TRAIL 0 17-JUL-20 07.00.01.000000 AM +00: | ||
| + | STANDARD AUDIT TRAIL 0 12-MAR-21 07.00.03.000000 AM +00: | ||
| + | XML AUDIT TRAIL 1 17-JUL-20 07.00.01.000000 AM +01: | ||
| + | XML AUDIT TRAIL 1 12-MAR-21 07.00.03.000000 AM +01: | ||
| + | |||
| + | 6 rows selected. | ||
| + | </ | ||
| + | |||
| + | === Set a date to purge to === | ||
| Setup to purge anything older than 90 days before today | Setup to purge anything older than 90 days before today | ||
| - | < | + | < |
| - | ====Recheck==== | + | begin |
| - | < | + | dbms_audit_mgmt.set_last_archive_timestamp( |
| - | ====Run the audit cleanup==== | + | audit_trail_type |
| - | < | + | |
| + | end; | ||
| + | / | ||
| + | </ | ||
| + | === Run the audit cleanup === | ||
| + | audit_trail_aud_std represents the standard old style auditing in aud$ | ||
| + | < | ||
| + | begin | ||
| + | dbms_audit_mgmt.clean_audit_trail ( audit_trail_type | ||
| + | , use_last_arch_timestamp => true | ||
| + | ); | ||
| + | end; | ||
| + | / | ||
| + | </ | ||
| or | or | ||
| - | < | ||
| - | =====Audit Inserts on a particular table===== | + | audit_trail_fga_std represents the fine_grained auditing where records are held in fga_log$ |
| - | <code> | + | < |
| - | ====Which tablespace is the standard auditing table (AUD$) going to?==== | + | begin |
| - | < | + | dbms_audit_mgmt.clean_audit_trail |
| + | , use_last_arch_timestamp | ||
| + | ); | ||
| + | end; | ||
| + | / | ||
| + | </ | ||
| or | or | ||
| - | < | ||
| - | ====Move the standard audit trail (AUD$) to a different tablespace==== | + | audit_trail_db_std combines both aud and fga auditing where records are held in aud$ and fga_log$ |
| + | < | ||
| + | begin | ||
| + | dbms_audit_mgmt.clean_audit_trail ( audit_trail_type | ||
| + | , use_last_arch_timestamp => true | ||
| + | ); | ||
| + | end; | ||
| + | / | ||
| + | </ | ||
| + | or | ||
| + | |||
| + | audit_trail_db_unified represents the unified auditing where records are held in the audsys schema (use UNIFIED_AUDIT_TRAIL view) | ||
| + | < | ||
| + | begin | ||
| + | dbms_audit_mgmt.clean_audit_trail ( audit_trail_type | ||
| + | , use_last_arch_timestamp => true | ||
| + | ); | ||
| + | end; | ||
| + | / | ||
| + | </ | ||
| + | ==== Ignore the timestamp and remove all records (will probably be a truncate instead of a delete) ==== | ||
| + | < | ||
| + | begin | ||
| + | dbms_audit_mgmt.clean_audit_trail ( audit_trail_type | ||
| + | , use_last_arch_timestamp => false | ||
| + | ); | ||
| + | end; | ||
| + | / | ||
| + | </ | ||
| + | |||
| + | For a full list of auditing types search for " | ||
| + | ==== Audit Inserts on a particular table ==== | ||
| + | < | ||
| + | select owner | ||
| + | , segment_name | ||
| + | , segment_type | ||
| + | , bytes/ | ||
| + | from | ||
| + | where 1=1 | ||
| + | and segment_name = ' | ||
| + | / | ||
| + | |||
| + | OWNER SEGMENT_NAME | ||
| + | ------------------------------ --------------------------------------------------------------------------------- ------------------ -------------------- | ||
| + | ENDUR_PRD1 | ||
| + | |||
| + | |||
| + | select count(' | ||
| + | from | ||
| + | / | ||
| + | |||
| + | COUNT(1) | ||
| + | ---------- | ||
| + | 26413733 | ||
| + | |||
| + | 1 row selected. | ||
| + | |||
| + | |||
| + | OWNER TABLE_NAME | ||
| + | ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------------------ -------------------- | ||
| + | ENDUR_PRD1 | ||
| + | |||
| + | |||
| + | |||
| + | SELECT table_name | ||
| + | , tablespace_name | ||
| + | FROM | ||
| + | WHERE table_name IN (' | ||
| + | ORDER BY table_name | ||
| + | / | ||
| + | |||
| + | TABLE_NAME | ||
| + | ------------------------------ ------------------------------ | ||
| + | AUD$ | ||
| + | FGA_LOG$ | ||
| + | |||
| + | |||
| + | BEGIN | ||
| + | DBMS_AUDIT_MGMT.set_audit_trail_location( | ||
| + | audit_trail_type | ||
| + | audit_trail_location_value => ' | ||
| + | END; | ||
| + | / | ||
| + | |||
| + | dbms_fga.add_policy ( object_schema | ||
| + | , object_name | ||
| + | , policy_name | ||
| + | , audit_condition | ||
| + | , audit_column_opts => DBMS_FGA.ALL_COLUMNS | ||
| + | , audit_trail | ||
| + | , statement_types | ||
| + | ); | ||
| + | |||
| + | select DB_USER | ||
| + | , OBJECT_SCHEMA | ||
| + | , OBJECT_NAME | ||
| + | , POLICY_NAME | ||
| + | , SQL_TEXT | ||
| + | from | ||
| + | / | ||
| + | </ | ||
| + | === Which tablespace is the standard auditing table (AUD$) going to? === | ||
| + | < | ||
| + | select string_value from dam_config_param$ where audit_trail_type# | ||
| + | </ | ||
| + | or | ||
| + | < | ||
| + | select table_name, tablespace_name from dba_tables where table_name IN (' | ||
| + | </ | ||
| + | |||
| + | === Move the standard audit trail (AUD$) to a different tablespace === | ||
| Can move both the standard and the fine-grained access (fga) audit trail in one go by specifying " | Can move both the standard and the fine-grained access (fga) audit trail in one go by specifying " | ||
| - | < | + | < |
| + | create tablespace audit_dt datafile '/ | ||
| + | / | ||
| - | ====Move the unified audit trail to a different tablespace==== | + | begin |
| - | <code>21@@</ | + | dbms_audit_mgmt.set_audit_trail_location ( audit_trail_type |
| + | , audit_trail_location_value => ' | ||
| + | ); | ||
| + | end; | ||
| + | / | ||
| + | </ | ||
| - | =====AUD$ has not been cleaned up and is now too big to work with===== | + | === Move the unified audit trail to a different tablespace === |
| + | < | ||
| + | begin | ||
| + | dbms_audit_mgmt.set_audit_trail_location ( audit_trail_type | ||
| + | , audit_trail_location_value => ' | ||
| + | ); | ||
| + | end; | ||
| + | / | ||
| + | </ | ||
| + | |||
| + | ==== AUD$ has not been cleaned up and is now too big to work with ==== | ||
| Trim down the audit table the old-fashioned way... | Trim down the audit table the old-fashioned way... | ||
| - | < | + | < |
| + | - !/ | ||
| + | |||
| + | |||
| + | |||
| + | sqlplus / as sysdba <<' | ||
| + | |||
| + | whenever sqlerror exit failure | ||
| + | |||
| + | create tablespace audit_dt_temp | ||
| + | datafile '/ | ||
| + | size 500M | ||
| + | autoextend on | ||
| + | maxsize 30G | ||
| + | / | ||
| + | |||
| + | create table aud2$ | ||
| + | tablespace audit_dt_temp | ||
| + | parallel (degree 4) | ||
| + | nologging | ||
| + | as | ||
| + | select * | ||
| + | from | ||
| + | where ntimestamp# > sysdate - 90 | ||
| + | / | ||
| + | |||
| + | |||
| + | rename aud$ to aud_old | ||
| + | / | ||
| + | |||
| + | rename aud2$ to aud$ | ||
| + | / | ||
| + | |||
| + | |||
| + | drop tablespace audit_dt including contents and datafiles | ||
| + | / | ||
| + | |||
| + | create tablespace audit_dt | ||
| + | datafile '/ | ||
| + | size 500M | ||
| + | autoextend on | ||
| + | maxsize 30G | ||
| + | / | ||
| + | |||
| + | |||
| + | alter table aud$ enable row movement | ||
| + | / | ||
| + | |||
| + | alter table aud$ move tablespace audit_dt | ||
| + | / | ||
| + | |||
| + | create index i_aud | ||
| + | on aud$ (sessionid, ses$tid) | ||
| + | | ||
| + | / | ||
| + | |||
| + | alter table aud$ move tablespace audit_dt | ||
| + | lob (sqltext) store as lobsegment1 (tablespace audit_dt) | ||
| + | lob (sqlbind) store as lobsegment2 (tablespace audit_dt) | ||
| + | / | ||
| + | |||
| + | |||
| + | drop tablespace audit_dt_temp including contents and datafiles | ||
| + | / | ||
| + | |||
| + | EOSQL | ||
| + | </ | ||
auditing.1544273360.txt.gz · Last modified: 2018/12/08 12:49 by 0.0.0.0