Rem drv:
Rem
Rem $Header: audit_tables.sql 13-aug-2007.17:53:49 pshishir Exp $
Rem
Rem audit_tables.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.
Rem
Rem NAME
Rem audit_tables.sql -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem pshishir 05/18/07 - Adding custom attribute support
Rem gsbhatia 06/26/05 - New repmgr header impl
Rem gsbhatia 02/13/05 - updating repmgr header
Rem gsbhatia 02/09/05 - updating repmgr header
Rem gsbhatia 02/07/05 - updating repmgr header
Rem shianand 02/03/05 - shianand_audit
Rem shianand 12/12/04 - Created
Rem
rem
rem This script requires the following arguments
rem 1. Name of the ecm_depot tablespace where all LOBs are stored
rem
DEFINE EM_ECM_DEPOT_TABLESPACE = "&1"
--TABLE MGMT_AUDIT_MASTER
--audit_level: This is a column set audit level. When it is true, the audit
-- is carried out for all the above operations and when it is
-- false, the user needs register the operation codes which
-- are to be audited.
--audit_level (all=>0/selected=>1/none=>2 DEFAULT none)
CREATE TABLE MGMT_AUDIT_MASTER
(
audit_level NUMBER(1) DEFAULT 2
) MONITORING;
--TABLE MGMT_AUDIT_DESTINATION
--audit_mode: This defines whether the audit needs to be stored in DB or
-- in a file.
--audit_destination: This defines the physical path of the audit file in the
-- file system.
--audit_mode (DB=>0/ FILE=>1 /DB and FILE both=>2)
--audit_destination (in case of file path of the file)
CREATE TABLE MGMT_AUDIT_DESTINATION
(
audit_mode NUMBER(1) DEFAULT 0,
audit_destination VARCHAR2(4000) DEFAULT NULL
) MONITORING;
--TABLE MGMT_USER_SESSION
--user_session_id: HTTP session id which gets generated when a user logs in
--em_user: EM user
--em_user_type: Type of the user e.g. Normal user, SSO user, SuperUser
--em_user_host_name: The hostname of the user
--oms_host_name: The hostname of the oms which handles the http request
-- after it is assigned the request by load balancer
--browser_type: Type of the user?s browser
--login_time: Time stamp for the user when he logs in
--logoff_time: Time stamp for the user when he logs off
--ip_address: IP Address of the user?s machine
--terminal: User?s terminal name
--session_type: If the audit happens when the user is logged in, the
-- session_type stores 1, but when user logs in and logs out
-- with audit to happen on the back ground, then the value
-- which this column stores is reset to 0.
CREATE TABLE MGMT_USER_SESSION
(
user_session_id_guid RAW(64),
user_session_id RAW(64),
em_user VARCHAR2(4000),
em_user_type VARCHAR2(4000),
em_user_host_name VARCHAR2(4000),
oms_host_name VARCHAR2(4000),
browser_type VARCHAR2(4000),
login_time DATE,
logoff_time DATE,
ip_address VARCHAR2(4000),
osuser VARCHAR2(4000),
session_status VARCHAR2(4000),
session_type VARCHAR2(4000),
time_zone VARCHAR2(4000),
CONSTRAINT user_session_id_guid PRIMARY KEY(user_session_id_guid)
) MONITORING;
--TABLE MGMT_OPERATIONS_MASTER
--operation_code: This is a predefined unique value for all operation codes.
--operation_description: Description of the operation
--operation_audit: Admin can set it to true if he needs to audit otherwise
-- he can set it up to false.
--audit_column_name1?name11:It stores the audited data names e.g. for
-- submit/modify/delete jobs audit, audit_column_name1
-- will store (job_name)? and audit_column_name2 will
-- store (job_type).
--operation_code NUMBER(1,2,3..)
--operation_description
--operation_audit (on/off) NUMBER (0,1)
--operation_code: This is a predefined unique value for all the below operations
--change_password=1,
--create_user=2,
--delete_user=3,
--logon=4,
--logoff=5,
--grant_role=6,
--grant_priviledge=7,
--revoke_role=8,
--revoke_priviledge=9,
--submit_job=10,
--modify_job=11,
--delete_job=12,
--change_pref_cred=13
CREATE TABLE MGMT_OPERATIONS_MASTER
(
op_code NUMBER(2),
operation_description VARCHAR2(4000),
operation_audit NUMBER(1) DEFAULT 1,
audit_column_name1 VARCHAR2(4000) DEFAULT NULL,
audit_column_name2 VARCHAR2(4000) DEFAULT NULL,
audit_column_name3 VARCHAR2(4000) DEFAULT NULL,
audit_column_name4 VARCHAR2(4000) DEFAULT NULL,
audit_column_name5 VARCHAR2(4000) DEFAULT NULL,
audit_column_name6 VARCHAR2(4000) DEFAULT NULL,
audit_column_name7 VARCHAR2(4000) DEFAULT NULL,
audit_column_name8 VARCHAR2(4000) DEFAULT NULL,
audit_column_name9 VARCHAR2(4000) DEFAULT NULL,
audit_column_name10 VARCHAR2(4000) DEFAULT NULL,
audit_column_name11 VARCHAR2(4000) DEFAULT NULL,
audit_column_name12 VARCHAR2(4000) DEFAULT NULL,
audit_column_name13 VARCHAR2(4000) DEFAULT NULL,
audit_column_name14 VARCHAR2(4000) DEFAULT NULL,
audit_column_name15 VARCHAR2(4000) DEFAULT NULL,
audit_clob_name1 VARCHAR2(4000) DEFAULT NULL,
CONSTRAINT op_code PRIMARY KEY(op_code)
) MONITORING;
--TABLE MGMT_AUDIT_LOGS
--audit_guid: this is the unique guid for audit
--operation_code: This is a predefined unique value for all the above operation.
--time_stamp
CREATE TABLE MGMT_AUDIT_LOGS
(
user_session_id_guid RAW(64),
audit_guid RAW(64),
op_code NUMBER,
object_name VARCHAR2(4000),
object_type VARCHAR2(4000),
object_owner VARCHAR2(4000),
time_stamp DATE,
audit_column_value1 VARCHAR2(4000),
audit_column_value2 VARCHAR2(4000),
audit_column_value3 VARCHAR2(4000),
audit_column_value4 VARCHAR2(4000),
audit_column_value5 VARCHAR2(4000),
audit_column_value6 VARCHAR2(4000),
audit_column_value7 VARCHAR2(4000),
audit_column_value8 VARCHAR2(4000),
audit_column_value9 VARCHAR2(4000),
audit_column_value10 VARCHAR2(4000),
audit_column_value11 VARCHAR2(4000),
audit_column_value12 VARCHAR2(4000),
audit_column_value13 VARCHAR2(4000),
audit_column_value14 VARCHAR2(4000),
audit_column_value15 VARCHAR2(4000),
audit_clob_value1 CLOB
) LOB(audit_clob_value1) STORE AS (TABLESPACE &EM_ECM_DEPOT_TABLESPACE)
MONITORING;
@&EM_SQL_ROOT/core/10.2.0.4/audit/audit_tables.sql