CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle AUDITING
Version 11.1
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$;
 
Related Topics
Audit Vault
Fine Grained Auditing
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [54 users online]    © 2010 psoug.org