dba:oracle:howtos:audit_mini-howto
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revision | |||
dba:oracle:howtos:audit_mini-howto [2023/01/31 08:28] – removed - external edit (Unknown date) 127.0.0.1 | dba:oracle:howtos:audit_mini-howto [2023/01/31 08:28] (current) – ↷ Page moved from dba:oracle:audit_mini-howto to dba:oracle:howtos:audit_mini-howto dodger | ||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ====== [HOWTO] Oracle AUDIT management ====== | ||
+ | |||
+ | ====== Description ====== | ||
+ | Little document on how to audit oracle things. | ||
+ | |||
+ | ====== Enable audit ====== | ||
+ | To enable audit we must set the init parameter, normally that is: | ||
+ | <code sql> | ||
+ | ALTER SYSTEM SET AUDIT_TRAIL=' | ||
+ | ALTER SYSTEM SET audit_file_dest='/ | ||
+ | </ | ||
+ | You must restart Oracle to make the change effective. | ||
+ | Ensure that you have a purged process and enough diskspace on // | ||
+ | |||
+ | |||
+ | AUDIT_TRAIL can be: | ||
+ | * OS | ||
+ | * DB | ||
+ | * DB,EXTENDED | ||
+ | * XML | ||
+ | * XML, | ||
+ | [[http:// | ||
+ | |||
+ | You can check if audit is enabled with the following query: | ||
+ | <code sql>COL NAME FORMAT A50 | ||
+ | COL VALUE FORMAT A50 | ||
+ | SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME LIKE ' | ||
+ | </ | ||
+ | |||
+ | The kind of audit methods are described [[http:// | ||
+ | |||
+ | ===== Change tablespace ===== | ||
+ | |||
+ | Create the tablespace: | ||
+ | <code sql> | ||
+ | create tablespace SYSAUD DATAFILE | ||
+ | '/ | ||
+ | '/ | ||
+ | </ | ||
+ | |||
+ | |||
+ | Query actual config: | ||
+ | <code sql> | ||
+ | col parameter_name format a50 | ||
+ | col parameter_value format a50 | ||
+ | | ||
+ | ; | ||
+ | </ | ||
+ | |||
+ | Change the tablespace: | ||
+ | <code sql> | ||
+ | BEGIN | ||
+ | SYS.DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION( | ||
+ | audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, | ||
+ | audit_trail_location_value => ' | ||
+ | END; | ||
+ | / | ||
+ | |||
+ | BEGIN | ||
+ | SYS.DBMS_AUDIT_MGMT.set_audit_trail_location( | ||
+ | audit_trail_type | ||
+ | audit_trail_location_value => ' | ||
+ | END; | ||
+ | / | ||
+ | |||
+ | BEGIN | ||
+ | SYS.DBMS_AUDIT_MGMT.set_audit_trail_location( | ||
+ | audit_trail_type | ||
+ | audit_trail_location_value => ' | ||
+ | END; | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | Check config again. | ||
+ | |||
+ | ====== Query actual config ====== | ||
+ | General configuration: | ||
+ | <code sql> | ||
+ | | ||
+ | | ||
+ | select * from DBA_AUDIT_MGMT_CONFIG_PARAMS; | ||
+ | </ | ||
+ | |||
+ | AUDIT parameters: | ||
+ | <code sql> | ||
+ | COL OWNER FORMAT A30 | ||
+ | COL OBJECT_NAME FORMAT A30 | ||
+ | COL OBJECT_TYPE FORMAT A23 | ||
+ | COL ALT FORMAT A3 | ||
+ | COL AUD FORMAT A3 | ||
+ | COL COM FORMAT A3 | ||
+ | COL DEL FORMAT A3 | ||
+ | COL GRA FORMAT A3 | ||
+ | COL IND FORMAT A3 | ||
+ | COL INS FORMAT A3 | ||
+ | COL LOC FORMAT A3 | ||
+ | COL REN FORMAT A3 | ||
+ | COL SEL FORMAT A3 | ||
+ | COL UPD FORMAT A3 | ||
+ | COL EXE FORMAT A3 | ||
+ | COL CRE FORMAT A3 | ||
+ | COL REA FORMAT A3 | ||
+ | COL WRI FORMAT A3 | ||
+ | COL FBK FORMAT A3 | ||
+ | SELECT * FROM DBA_OBJ_AUDIT_OPTS ; | ||
+ | </ | ||
+ | <code sql> | ||
+ | COL USER_NAME FORMAT COL30; | ||
+ | COL USER_NAME FORMAT A30; | ||
+ | COL PROXY_NAME FORMAT A30 ; | ||
+ | COL AUDIT_OPTION FORMAT A40; | ||
+ | COL SUCCESS FORMAT A10; | ||
+ | COL FAILURE FORMAT A10; | ||
+ | SELECT | ||
+ | </ | ||
+ | |||
+ | same but: | ||
+ | <code sql> | ||
+ | | ||
+ | </ | ||
+ | |||
+ | Priviledges audited: | ||
+ | <code sql> | ||
+ | select * from DBA_PRIV_AUDIT_OPTS ; | ||
+ | </ | ||
+ | |||
+ | ====== Disable default config (if you want) ====== | ||
+ | <code sql> | ||
+ | NOAUDIT ALTER SYSTEM ; | ||
+ | NOAUDIT SYSTEM AUDIT ; | ||
+ | NOAUDIT CREATE SESSION ; | ||
+ | NOAUDIT CREATE USER ; | ||
+ | NOAUDIT ALTER USER ; | ||
+ | NOAUDIT DROP USER ; | ||
+ | NOAUDIT PUBLIC SYNONYM ; | ||
+ | NOAUDIT DATABASE LINK ; | ||
+ | NOAUDIT ROLE ; | ||
+ | NOAUDIT PROFILE ; | ||
+ | NOAUDIT CREATE ANY TABLE ; | ||
+ | NOAUDIT ALTER ANY TABLE ; | ||
+ | NOAUDIT DROP ANY TABLE ; | ||
+ | NOAUDIT CREATE PUBLIC DATABASE LINK ; | ||
+ | NOAUDIT GRANT ANY ROLE ; | ||
+ | NOAUDIT SYSTEM GRANT ; | ||
+ | NOAUDIT ALTER DATABASE ; | ||
+ | NOAUDIT CREATE ANY PROCEDURE ; | ||
+ | NOAUDIT ALTER ANY PROCEDURE ; | ||
+ | NOAUDIT DROP ANY PROCEDURE ; | ||
+ | NOAUDIT ALTER PROFILE ; | ||
+ | NOAUDIT DROP PROFILE ; | ||
+ | NOAUDIT GRANT ANY PRIVILEGE ; | ||
+ | NOAUDIT CREATE ANY LIBRARY ; | ||
+ | NOAUDIT EXEMPT ACCESS POLICY ; | ||
+ | NOAUDIT GRANT ANY OBJECT PRIVILEGE ; | ||
+ | NOAUDIT CREATE ANY JOB ; | ||
+ | NOAUDIT CREATE EXTERNAL JOB ; | ||
+ | </ | ||
+ | |||
+ | ====== AUDIT ANYTHING to the main table ====== | ||
+ | <code sql> | ||
+ | AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS; | ||
+ | </ | ||
+ | ======Useful Tables====== | ||
+ | |||
+ | * AUDIT CONFIGURATION TABLES | ||
+ | |||
+ | ^ Name ^ Description ^ | ||
+ | | DBA_PRIV_AUDIT_OPTS | Describes current system privileges being audited across the system and by user | | ||
+ | | DBA_STMT_AUDIT_OPTS | Describes current statement auditing options across the system and by user | | ||
+ | | DBA_AUDIT_POLICIES | Lists all the fine-grained auditing policies on the system | | ||
+ | | DBA_AUDIT_MGMT_CONFIG_PARAMS | Displays the currently configured audit trail properties that are used by the DBMS_AUDIT_MGMT PL/SQL package | | ||
+ | | DBA_AUDIT_POLICY_COLUMNS | Describes the fine-grained auditing policy columns on the tables and views throughout the database. | | ||
+ | | DBA_OBJ_AUDIT_OPTS | Displays the objects on which auditing options have been enabled | | ||
+ | | ALL_AUDIT_POLICIES | Describes the fine-grained auditing policies on the tables and views accessible to the current user | | ||
+ | | ALL_AUDIT_POLICY_COLUMNS | Describes the fine-grained auditing policy columns on the tables and views accessible to the current user. | | ||
+ | | ALL_DEF_AUDIT_OPTS | Lists default object-auditing options that are to be applied when objects are created | | ||
+ | |||
+ | * TRAIL TABLES | ||
+ | |||
+ | ^ Name ^ Description ^ | ||
+ | | DBA_AUDIT_OBJECT | Lists audit trail records for all objects in the system | | ||
+ | | DBA_AUDIT_STATEMENT | Lists audit trail records concerning GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM statements throughout the database | | ||
+ | | DBA_AUDIT_SESSION | Lists all audit trail records concerning CONNECT and DISCONNECT | | ||
+ | | DBA_AUDIT_TRAIL | Lists all standard audit trail entries in the AUD$ table | | ||
+ | | DBA_COMMON_AUDIT_TRAIL | Combines standard and fine-grained audit log records, and includes SYS and mandatory audit records written in XML format | | ||
+ | | DBA_FGA_AUDIT_TRAIL | Lists audit trail records for fine-grained auditing. | | ||
+ | |||
+ | * Less important: | ||
+ | |||
+ | ^ Name ^ Description ^ | ||
+ | | DBA_AUDIT_EXISTS | Lists audit trail entries produced BY AUDIT NOT EXISTS | | ||
+ | | DBA_AUDIT_MGMT_CLEAN_EVENTS | " | ||
+ | | DBA_AUDIT_MGMT_CLEANUP_JOBS | Displays the currently configured audit trail purge jobs | | ||
+ | | DBA_AUDIT_MGMT_LAST_ARCH_TS | Displays the last archive timestamps that have set for audit trail purges. | | ||
+ | | V$LOGMNR_CONTENTS | Contains log history information. To query this view, you must have the SELECT ANY TRANSACTION privilege. | | ||
+ | | V$XML_AUDIT_TRAIL | Shows standard, fine-grained, | ||
+ | |||
+ | |||
+ | ======Querys====== | ||
+ | The audit must be [[http:// | ||
+ | |||
+ | ===== Format ===== | ||
+ | Its a good practice to format the columns before quering them: | ||
+ | <code sql> | ||
+ | COLUMN USERNAME FORMAT A40 ; | ||
+ | COLUMN USERHOST FORMAT A40 ; | ||
+ | COLUMN OWNER FORMAT A30 ; | ||
+ | COLUMN OBJ_NAME FORMAT A30 ; | ||
+ | COLUMN ACTION_NAME FORMAT A18 ; | ||
+ | COLUMN NEW_OWNER FORMAT A30 ; | ||
+ | COLUMN NEW_NAME FORMAT A30 ; | ||
+ | COLUMN SES_ACTIONS FORMAT A10 ; | ||
+ | COLUMN SESSIONID FORMAT 99999999 ; | ||
+ | COLUMN ENTRYID FORMAT 9999 ; | ||
+ | COLUMN STATEMENTID FORMAT 9999 ; | ||
+ | COLUMN RETURNCODE FORMAT 9999 ; | ||
+ | COLUMN INSTANCE_NUMBER FORMAT 9999 ; | ||
+ | COLUMN SCN FORMAT 99999999999 ; | ||
+ | COLUMN SQL_BIND FORMAT A50 ; | ||
+ | COLUMN SQL_TEXT FORMAT A100 ; | ||
+ | COLUMN OBJ_EDITION_NAME FORMAT A10 ; | ||
+ | </ | ||
+ | =====Query FAILED User logins===== | ||
+ | <code sql> | ||
+ | COL ETIMESTAMP FORMAT A22 | ||
+ | COL DB_USER FORMAT | ||
+ | COL OS_USER FORMAT | ||
+ | COL USERHOST FORMAT | ||
+ | COL OBJECT_SCHEMA FORMAT | ||
+ | COL STATEMENT_TYPE FORMAT | ||
+ | |||
+ | SELECT TO_CHAR(EXTENDED_TIMESTAMP, | ||
+ | DB_USER , | ||
+ | OS_USER , | ||
+ | USERHOST , | ||
+ | OBJECT_SCHEMA , | ||
+ | | ||
+ | STATEMENT_TYPE | ||
+ | FROM DBA_COMMON_AUDIT_TRAIL | ||
+ | WHERE RETURNCODE <>0 | ||
+ | ORDER BY EXTENDED_TIMESTAMP | ||
+ | ; | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | =====Query User logins===== | ||
+ | <code sql> | ||
+ | COL ETIMESTAMP FORMAT A22 | ||
+ | COL DB_USER FORMAT | ||
+ | COL OS_USER FORMAT | ||
+ | COL USERHOST FORMAT | ||
+ | COL OBJECT_SCHEMA FORMAT | ||
+ | COL STATEMENT_TYPE FORMAT | ||
+ | |||
+ | SELECT TO_CHAR(EXTENDED_TIMESTAMP, | ||
+ | DB_USER , | ||
+ | OS_USER , | ||
+ | USERHOST , | ||
+ | OBJECT_SCHEMA , | ||
+ | STATEMENT_TYPE | ||
+ | FROM DBA_COMMON_AUDIT_TRAIL | ||
+ | WHERE STATEMENT_TYPE IN (' | ||
+ | ORDER BY EXTENDED_TIMESTAMP | ||
+ | ; | ||
+ | </ | ||
+ | |||
+ | |||
+ | =====From where a user connects===== | ||
+ | <code sql> | ||
+ | COL ETIMESTAMP FORMAT A22 | ||
+ | COL DB_USER FORMAT | ||
+ | COL OS_USER FORMAT | ||
+ | COL USERHOST FORMAT | ||
+ | COL OBJECT_SCHEMA FORMAT | ||
+ | |||
+ | SELECT DB_USER , | ||
+ | OS_USER , | ||
+ | USERHOST , | ||
+ | | ||
+ | OBJECT_SCHEMA | ||
+ | FROM DBA_COMMON_AUDIT_TRAIL | ||
+ | WHERE STATEMENT_TYPE = ' | ||
+ | ORDER BY EXTENDED_TIMESTAMP | ||
+ | ; | ||
+ | </ | ||
+ | |||
+ | ===== Query everything EXCEPT LOGIN events ===== | ||
+ | With SQL: | ||
+ | <code sql> | ||
+ | SET PAGES 0 | ||
+ | COL ETIMESTAMP FORMAT A22 | ||
+ | COL DB_USER FORMAT | ||
+ | COL OS_USER FORMAT | ||
+ | COL USERHOST FORMAT | ||
+ | COL OBJECT_SCHEMA FORMAT | ||
+ | COL OBJECT_NAME FORMAT | ||
+ | COL STATEMENT_TYPE FORMAT | ||
+ | COL SQL_TEXT FORMAT | ||
+ | |||
+ | SELECT TO_CHAR(EXTENDED_TIMESTAMP, | ||
+ | DB_USER , | ||
+ | OS_USER , | ||
+ | USERHOST , | ||
+ | OBJECT_SCHEMA , | ||
+ | OBJECT_NAME , | ||
+ | STATEMENT_TYPE , | ||
+ | SQL_TEXT | ||
+ | FROM DBA_COMMON_AUDIT_TRAIL | ||
+ | WHERE STATEMENT_TYPE NOT IN (' | ||
+ | ORDER BY EXTENDED_TIMESTAMP | ||
+ | ; | ||
+ | </ | ||
+ | |||
+ | Without SQL: | ||
+ | <code sql> | ||
+ | SET PAGES 0 | ||
+ | COL ETIMESTAMP FORMAT A22 | ||
+ | COL DB_USER FORMAT | ||
+ | COL OS_USER FORMAT | ||
+ | COL USERHOST FORMAT | ||
+ | COL OBJECT_SCHEMA FORMAT | ||
+ | COL OBJECT_NAME FORMAT | ||
+ | COL STATEMENT_TYPE FORMAT | ||
+ | |||
+ | SELECT TO_CHAR(EXTENDED_TIMESTAMP, | ||
+ | DB_USER , | ||
+ | OS_USER , | ||
+ | USERHOST , | ||
+ | OBJECT_SCHEMA , | ||
+ | OBJECT_NAME , | ||
+ | STATEMENT_TYPE | ||
+ | FROM DBA_COMMON_AUDIT_TRAIL | ||
+ | WHERE STATEMENT_TYPE NOT IN (' | ||
+ | ORDER BY EXTENDED_TIMESTAMP | ||
+ | ; | ||
+ | </ | ||
+ | |||
+ | ===== Searching the SQL of an audit record ===== | ||
+ | This query will display ressults on 2 rows each one. | ||
+ | First, the SELECT query to obtain the desired SQL and just bellow the information of that query: | ||
+ | <code sql> | ||
+ | SET PAGES 0 | ||
+ | SET LINESIZE 150 | ||
+ | SET COLSEP | | ||
+ | COL SELECT_TEXT FORMAT | ||
+ | COL ETIMESTAMP FORMAT | ||
+ | COL DB_USER FORMAT | ||
+ | COL OS_USER FORMAT | ||
+ | COL OBJECT_SCHEMA FORMAT | ||
+ | COL OBJECT_NAME FORMAT | ||
+ | COL STATEMENT_TYPE FORMAT | ||
+ | |||
+ | SELECT ' | ||
+ | ' AND STATEMENTID = ' || STATEMENTID || ' AND ENTRYID = ' || ENTRYID || ' ; ' SELECT_TEXT, | ||
+ | DB_USER , | ||
+ | OS_USER , | ||
+ | OBJECT_SCHEMA , | ||
+ | OBJECT_NAME , | ||
+ | STATEMENT_TYPE | ||
+ | FROM DBA_COMMON_AUDIT_TRAIL | ||
+ | WHERE STATEMENT_TYPE NOT IN (' | ||
+ | ORDER BY EXTENDED_TIMESTAMP | ||
+ | ; | ||
+ | </ | ||
+ | ===== Policies ===== | ||
+ | <code sql> | ||
+ | COL OWNER FORMAT A30 | ||
+ | COL OBJECT_NAME FORMAT A30 | ||
+ | COL OBJECT_TYPE FORMAT A23 | ||
+ | COL ALT FORMAT A3 | ||
+ | COL AUD FORMAT A3 | ||
+ | COL COM FORMAT A3 | ||
+ | COL DEL FORMAT A3 | ||
+ | COL GRA FORMAT A3 | ||
+ | COL IND FORMAT A3 | ||
+ | COL INS FORMAT A3 | ||
+ | COL LOC FORMAT A3 | ||
+ | COL REN FORMAT A3 | ||
+ | COL SEL FORMAT A3 | ||
+ | COL UPD FORMAT A3 | ||
+ | COL EXE FORMAT A3 | ||
+ | COL CRE FORMAT A3 | ||
+ | COL REA FORMAT A3 | ||
+ | COL WRI FORMAT A3 | ||
+ | COL FBK FORMAT A3 | ||
+ | COL REF FORMAT A3 | ||
+ | SELECT * FROM DBA_OBJ_AUDIT_OPTS ; | ||
+ | </ | ||
+ | & | ||
+ | <code sql> | ||
+ | COLUMN USER_NAME FORMAT A33 ; | ||
+ | COLUMN PROXY_NAME FORMAT A33 ; | ||
+ | COLUMN AUDIT_OPTION FORMAT A40 ; | ||
+ | COLUMN SUCCESS FORMAT A20 ; | ||
+ | COLUMN FAILURE FORMAT A20 ; | ||
+ | SELECT | ||
+ | | ||
+ | | ||
+ | | ||
+ | from DBA_STMT_AUDIT_OPTS ORDER BY USER_NAME; | ||
+ | </ | ||
+ | |||
+ | |||
+ | ===== Failed querys by day ===== | ||
+ | <code sql> | ||
+ | SELECT THEDAY, COUNT(*) | ||
+ | FROM ( | ||
+ | SELECT TO_CHAR(EXTENDED_TIMESTAMP, | ||
+ | FROM DBA_COMMON_AUDIT_TRAIL | ||
+ | WHERE STATEMENT_TYPE NOT IN (' | ||
+ | AND NOT RETURNCODE=0 | ||
+ | AND NOT (SUBSTR(SQL_TEXT, | ||
+ | ORDER BY EXTENDED_TIMESTAMP | ||
+ | ) | ||
+ | GROUP BY THEDAY | ||
+ | order by THEDAY | ||
+ | ; | ||
+ | </ | ||
+ | |||
+ | ===== DBA_AUDIT_TRAIL Maintenance ===== | ||
+ | You must be careful about DBA_AUDIT_TRAIL records so you don't exhaust the SYSAUX tablespace space. | ||
+ | Erasing records by time is as simple as : | ||
+ | <code sql> | ||
+ | DELETE FROM SYS.AUD$ WHERE NTIMESTAMP# < (SYSDATE-10) ; | ||
+ | </ | ||
+ | |||
+ | ====== Examples ====== | ||
+ | ===== Auditing login failures ===== | ||
+ | Creating the rule: | ||
+ | <code sql> | ||
+ | audit create session whenever not successful; | ||
+ | </ | ||
+ | And as usual, the query: | ||
+ | <code sql> | ||
+ | COL OS_USERNAME FORMAT A20 | ||
+ | COL USERNAME FORMAT A20 | ||
+ | COL TERMINAL FORMAT A30 | ||
+ | COL LOGONTIME FORMAT A25 | ||
+ | select | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | from | ||
+ | | ||
+ | WHERE ROWNUM <30; | ||
+ | </ | ||
+ | |||
+ | ===== Auditing Objects ===== | ||
+ | Copy/paste of oracle documentation: | ||
+ | |||
+ | //The object must be a table, view, sequence, stored procedure, function, package, materialized view, mining model, or library.// | ||
+ | |||
+ | For example, I want to audit all the executions of the procedures by a user: | ||
+ | <code sql> | ||
+ | AUDIT EXECUTE PROCEDURE BY ORAUSER BY ACCESS ; | ||
+ | </ | ||
+ | |||
+ | Query that rule: | ||
+ | <code sql> | ||
+ | COLUMN PROXY_NAME FORMAT A33 ; | ||
+ | COLUMN AUDIT_OPTION FORMAT A40 ; | ||
+ | COLUMN SUCCESS FORMAT A20 ; | ||
+ | COLUMN FAILURE FORMAT A20 ; | ||
+ | SELECT | ||
+ | | ||
+ | | ||
+ | | ||
+ | FROM DBA_STMT_AUDIT_OPTS ORDER BY USER_NAME; | ||
+ | </ | ||
+ | |||
+ | |||
+ | Now you can query the DBA_AUDIT_OBJECT table for results, for example: | ||
+ | <code sql> | ||
+ | COLUMN USERNAME FORMAT A10 ; | ||
+ | COLUMN USERHOST FORMAT A20 ; | ||
+ | COLUMN OWNER FORMAT A10 ; | ||
+ | COLUMN OBJ_NAME FORMAT A20 ; | ||
+ | COLUMN ACTION_NAME FORMAT A18 ; | ||
+ | COLUMN NEW_OWNER FORMAT A10 ; | ||
+ | COLUMN NEW_NAME FORMAT A10 ; | ||
+ | COLUMN SES_ACTIONS FORMAT A10 ; | ||
+ | COLUMN SESSIONID FORMAT 99999999 ; | ||
+ | COLUMN ENTRYID FORMAT 9999 ; | ||
+ | COLUMN STATEMENTID FORMAT 9999 ; | ||
+ | COLUMN RETURNCODE FORMAT 9999 ; | ||
+ | COLUMN INSTANCE_NUMBER FORMAT 9999 ; | ||
+ | COLUMN SCN FORMAT 99999999999 ; | ||
+ | COLUMN SQL_BIND FORMAT A10 ; | ||
+ | COLUMN SQL_TEXT FORMAT A10 ; | ||
+ | COLUMN OBJ_EDITION_NAME FORMAT A10 ; | ||
+ | |||
+ | |||
+ | SELECT USERNAME, | ||
+ | USERHOST, | ||
+ | OWNER, | ||
+ | OBJ_NAME, | ||
+ | ACTION_NAME, | ||
+ | NEW_OWNER, | ||
+ | NEW_NAME, | ||
+ | SES_ACTIONS, | ||
+ | SESSIONID, | ||
+ | ENTRYID, | ||
+ | STATEMENTID, | ||
+ | RETURNCODE, | ||
+ | INSTANCE_NUMBER, | ||
+ | SCN, | ||
+ | SQL_BIND, | ||
+ | SQL_TEXT, | ||
+ | OBJ_EDITION_NAME, | ||
+ | TIMESTAMP | ||
+ | from DBA_AUDIT_OBJECT ; | ||
+ | </ | ||
+ | |||
+ | |||
+ | ===== Auditing ALL USER actions ===== | ||
+ | |||
+ | '' | ||
+ | <code sql> | ||
+ | alter system set audit_trail=db, | ||
+ | </ | ||
+ | |||
+ | Restart is needed for this change to be applied (oracle rules...). | ||
+ | |||
+ | Then: | ||
+ | <code sql> | ||
+ | AUDIT ALL BY THEUSERNAME BY ACCESS; | ||
+ | AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY THEUSERNAME BY ACCESS; | ||
+ | AUDIT EXECUTE PROCEDURE BY THEUSERNAME BY ACCESS; | ||
+ | </ | ||
+ | |||
+ | Take care, this will insert a huge number of rows in '' | ||
+ | ====== Disable audit ====== | ||
+ | To simply disable AUDITING on statements: | ||
+ | <code sql> | ||
+ | NOAUDIT ALL STATEMENTS; | ||
+ | </ | ||
+ | |||
+ | You must use the same syntax as the AUDIT, for example: | ||
+ | <code sql> | ||
+ | NOAUDIT EXECUTE PROCEDURE BY ORAUSER; | ||
+ | </ | ||
+ | |||
+ | To completely disable audit: | ||
+ | |||
+ | <code sql> | ||
+ | </ | ||
+ | |||
+ | And restart the instance of DDBB | ||
+ | |||
+ | ====== Links ====== | ||
+ | * [[http:// | ||
+ | * [[http:// | ||
+ | * Master Note For Oracle Database Auditing (Doc ID 1299033.1) | ||
+ | |||
+ | |||
+ | ====== FGA ====== | ||
+ | [[http:// | ||
+ | |||
+ | |||
+ | |||