Rem drv: <migrate type="schema_upgrade" version="10.2.0.0"  params="EM_ECM_DEPOT_TABLESPACE" pos="assoc/assoc_schema_upgrade.sql+"/>
Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/v102010/audit_schema_upgrade.sql /st_emcore_10.2.0.4.4db11.2.0.3/1 2011/04/07 18:16:45 rpinnama Exp $
Rem
Rem audit_schema_upgrade.sql
Rem
Rem Copyright (c) 2005, 2011, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      audit_schema_upgrade.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    pshishir    07/27/07 - 
Rem    chyu        07/25/05 - modifying the upgrade header 
Rem    chyu        07/18/05 - adding the <migration> header 
Rem    shianand    03/17/05 - fix bug 4245081 alter current_schema=sys and then change 
Rem                           it back to sysman
Rem    shianand    03/15/05 - fix bug 4239230 added columns to audit_object,
Rem                           added AUDIT_DIR object
Rem    eujang      03/07/05 - adding missing columns to the mgmt_audit_logs 
Rem                           and mgmt_operations_master tables 
Rem    dcawley     02/11/05 - dcawley_user_model_mig_to_10_2
Rem    dcawley     02/09/05 - 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 users 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 users machine
--terminal:          Users 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_name1name11: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;

CREATE OR REPLACE TYPE audit_obj as object
(
object_name           VARCHAR2(4000),
object_type           VARCHAR2(4000),
object_owner          VARCHAR2(4000),
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
);
/

CREATE OR REPLACE TYPE audit_array as table of audit_obj;
/