| STARTUP PARAMETERS |
| AUDIT_SYS_OPERATIONS |
| FALSE |
Do not audit SYS |
| TRUE |
Audit SYS |
|
| AUDIT_TRAIL |
| db |
Enables database auditing and directs all audit records to the SYS.AUD$ table |
| db_extended |
Enables database auditing and directs all audit records to the database audit trail (the
SYS.AUD$ table) inlcuding the SQLBIND and SQLTEXT CLOB columns |
| none |
Disables database auditing |
| os |
Enables database auditing and directs all audit records to the operating system's audit trail |
| xml |
Enables database auditing and writes all audit records to XML format OS files |
| xml_extended |
Enables database auditing and prints all
audit trail columns, including SqlText and SqlBind values |
|
| Related Dictionary Views |
| dba_audit_exists |
dba_audit_session |
dba_obj_audit_opts |
| dba_audit_objects |
dba_audit_statement |
dba_priv_audit_opts |
| dba_audit_policies |
dba_audit_trail |
dba_stmt_audit_opts |
| dba_audit_policy_columns |
dba_common_audit_trail |
gv$xml_audit_trail |
|
DCL Statement Options |
| GRANT DIRECTORY |
A |
| REVOKE DIRECTORY |
C |
| GRANT SEQUENCE |
C |
| GRANT TABLE |
C |
| GRANT TYPE |
C |
|
DDL Statement Options |
| ALTER SYSTEM |
ALTER SYSTEM |
| CLUSTER |
CREATE CLUSTER
ALTER CLUSTER
DROP CLUSTER
TRUNCATE CLUSTER |
| CONTEXT |
CREATE CONTEXT
DROP CONTEXT |
| DATABASE LINK |
CREATE DATABASE
LINK
DROP DATABASE LINK |
| DIMENSION |
CREATE DIMENSION
ALTER DIMENSION
DROP DIMENSION |
| DIRECTORY |
CREATE DIRECTORY
DROP DIRECTORY |
| INDEX |
CREATE INDEX
ALTER INDEX
ANALYZE INDEX
DROP INDEX |
| MATERIALIZED VIEW |
CREATE
ALTER
DROP |
| NOT EXISTS |
All SQL statements
that fail because the object does not exist |
| PRO
CEDURE |
CREATE FUNCTION
CREATE LIBRARY
CREATE PACKAGE
CREATE PACKAGE BODY
CREATE PROCEDURE
DROP FUNCTION
DROP LIBRARY
DROP PACKAGE
DROP PROCEDURE |
| PROFILE |
CREATE
ALTER
DROP |
| PUBLIC DATABASE
LINK |
CREATE
DROP |
| PUBLIC SYNONYM |
|
| ROLE |
|
| ROLLBACK SEGMENT |
CREATE ROLLBACK
SEGMENT
ALTER ROLLBACK SEGMENT
DROP ROLLBACK SEGMENT |
| SEQUENCE |
CREATE SEQUENCE
DROP SEQUENCE |
| SESSION |
LOGONS |
| SYNONYM |
CREATE SYNONYM
DROP SYNONYM |
| SYSTEM AUDIT |
AUDIT
NOAUDIT |
| SYSTEM GRANT |
|
| TABLE |
CREATE TABLE
DROP TABLE
TRUNCATE TABLE |
| TABLESPACE |
CREATE TABLESPACE
ALTER TABLESPACE
DROP TABLESPACE |
| TRIGGER |
CREATE TRIGGER
ALTER TRIGGER (with ENABLE and DISABLE clauses) |
| TYPE |
CREATE TYPE
CREATE TYPE BODY
ALTER TYPE
DROP TYPE
DROP TYPE BODY |
| USER |
CREATE
ALTER
DROP |
| VIEW |
CREATE VIEW
DROP VIEW |
|
DML Statement Options |
| DELETE TABLE |
A |
| EXECUTE PROCEDURE |
C |
| CONTEXT |
CREATE CONTEXT
DROP CONTEXT |
| DATABASE LINK |
CREATE DATABASE
LINK
DROP DATABASE LINK |
| DIMENSION |
CREATE DIMENSION
ALTER DIMENSION
DROP DIMENSION |
|
| |
| |
| Audit Demo |
Sys Audit |
conn / as sysdba
set linesize 121
col name format a40
col value format a40
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%audit%';
NAME VALUE
-------------------- --------------------
audit_sys_operations FALSE
audit_file_dest C:\ORACLE\PRODUCT\ADMIN\ORABASE\ADUMP
audit_trail
DB
-- Note: Do not do this with audit_trail=XML
in 10gR2
-- fails
ALTER SYSTEM SET audit_sys_operations=TRUE
COMMENT='Begin auditing SYS'
SCOPE=BOTH;
-- fails
ALTER SYSTEM SET audit_sys_operations=TRUE
COMMENT='Begin auditing SYS'
SCOPE=MEMORY;
-- succeeds
ALTER SYSTEM SET audit_sys_operations=TRUE
COMMENT='Begin auditing SYS'
SCOPE=SPFILE;
-- auditing will begin after a restart
shutdown immediate
startup
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%audit%';
CREATE USER xyz
IDENTFIED BY xyz;
DROP USER xyz;
-- look in the audit_file_dest directory for a file named
-- ora_<pid>.aud where "pid" is the operating system process ID
ALTER SYSTEM SET audit_trail='XML'
COMMENT='Audit trail as XML'
SCOPE=SPFILE;
shutdown immediate
startup
SELECT name, value
FROM gv$parameter
WHERE name LIKE '%audit%';
CREATE USER xyz
IDENTFIED BY xyz;
DROP USER xyz;
-- look in the audit_file_dest directory for a file named
-- ora_<pid>.aud where "pid" is the operating system process ID
ALTER SYSTEM SET audit_trail='db'
COMMENT='Change auditing to sys.aud$'
SCOPE=BOTH;
|
| |
| Audit Demos |
Audit CREATE OBJECT |
AUDIT <sql_statement_clause>
BY <session> [WHENEVER [NOT] SUCCESSFUL]
AUDIT <sql_statement_clause> BY <access> [WHENEVER
[NOT] SUCCESSFUL]
AUDIT <schema_object_clause>
AUDIT NETWORK; |
conn / as sysdba
audit create procedure;
audit table;
audit create table;
audit create trigger;
audit create view;
audit table;
conn uwclass/uwclass
CREATE TABLE t (
newcol VARCHAR2(20));
CREATE PROCEDURE p IS
BEGIN
NULL;
END;
/
CREATE VIEW v AS
SELECT * FROM t;
conn / as sysdba
SELECT COUNT(*)
FROM aud$; |
Audit SELECT |
conn / as sysdba
audit select on scott.emp;
audit select any table whenever successful;
audit select any table whenever not successful;
conn scott/tiger
SELECT COUNT(*)
FROM emp;
SELECT *
FROM emp
WHERE sal < 3000;
conn / as sysdba
SELECT COUNT(*)
FROM aud$; |
