| 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 |
| all_def_audit_opts |
dba_audit_trail |
| dba_audit_exists |
dba_common_audit_trail |
| dba_audit_objects |
dba_obj_audit_opts |
| dba_audit_policies |
dba_priv_audit_opts |
| dba_audit_policy_columns |
dba_stmt_audit_opts |
| dba_audit_session |
gv$xml_audit_trail |
| dba_audit_statement |
|
|
DCL Statement Options |
| GRANT DIRECTORY |
REVOKE ON DIRECTORY |
| GRANT PROCEDURE |
REVOKE
ON PROCEDURE |
| GRANT SEQUENCE |
REVOKE
ON SEQUENCE |
| GRANT TABLE |
REVOKE
ON TABLE |
| GRANT TYPE |
REVOKE
ON TYPE |
|
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
MATERIALIZED VIEW
ALTER MATERIALIZED VIEW
DROP MATERIALIZED VIEW |
| NOT EXISTS |
All SQL statements
that fail because the object does not exist |
| PROCEDURE |
CREATE FUNCTION
CREATE LIBRARY
CREATE PACKAGE
CREATE PACKAGE BODY
CREATE PROCEDURE
DROP FUNCTION
DROP LIBRARY
DROP PACKAGE
DROP PROCEDURE |
| PROFILE |
CREATE PROFILE
ALTER PROFILE
DROP PROFILE |
| PUBLIC DATABASE
LINK |
CREATE PUBLIC DATABASE
LINK
DROP PUBLIC DATABASE
LINK |
| PUBLIC SYNONYM |
CREATE PUBLIC
SYNONYM
DROP PUBLIC SYNONYM |
| ROLE |
CREATE ROLE
ALTER ROLE
DROP ROLE
SET ROLE |
| ROLLBACK SEGMENT |
CREATE ROLLBACK
SEGMENT
ALTER ROLLBACK SEGMENT
DROP ROLLBACK SEGMENT |
| SEQUENCE |
ALTER SEQUENCE
CREATE SEQUENCE
DROP SEQUENCE |
| SESSION |
LOGONS |
| SYNONYM |
CREATE SYNONYM
DROP SYNONYM |
| SYSTEM AUDIT |
AUDIT
NOAUDIT |
| SYSTEM GRANT |
GRANT
REVOKE |
| TABLE |
ALTER TABLE
COMMENT 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 USER
ALTER USER
DROP USER |
| VIEW |
CREATE VIEW
DROP VIEW |
|
DML Statement Options |
| PL/SQL |
EXECUTE PROCEDURE |
| TABLE |
DELETE TABLE
INSERT TABLE
LOCK TABLE
SELECT TABLE
UPDATE TABLE |
|
| |
| Syntax Options |
| Audit Operations |
AUDIT ALL |
| Audit Operations |
|
| Audit Schema Object |
AUDIT <ALL | sql_operation> ON schema_name.object_name; |
| Audit On Directory |
AUDIT <sql_operation> ON DIRECTORY <directory_name>; |
| Audit Mining Model |
AUDIT <sql_operation> ON MINING MODEL <schema_name.model>; |
| Audit Default |
AUDIT <sql_operation> ON DEFAULT; |
| Audit Network |
AUDIT NETWORK BY <SESSION | ACCESS> WHENEVER [NOT] SUCCESSFUL; |
| Audit Changes to the
Audit Trail |
AUDIT SYSTEM AUDIT; |
| |
| 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$; |
