Rem
Rem $Header: security_tables.sql 16-aug-2007.08:06:00 pchebrol Exp $
Rem
Rem security_tables.sql
Rem
Rem Copyright (c) 2002, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      security_tables.sql - Tables to store EM Security information
Rem
Rem    DESCRIPTION
Rem      Tables to store EM Security information pertaining to Agent
Rem      IPW, CA and Agent keys.
Rem
Rem    NOTES
Rem      
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    pchebrol    08/16/07 - Changes sec table loc
Rem    pchebrol    06/22/07 - Backport shianand_core_5121288 from main
Rem    pchebrol    06/21/07 - 
Rem    gsbhatia    02/08/05 - Removing header 
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    gan         08/13/04 - add monitoring 
Rem    rpinnama    12/10/03 - Fix 3310479: Increase the size of CA_PWD 
Rem    rpinnama    02/27/03 - Parameterize the ECM depot table space
Rem    izhang      01/16/03 - 
Rem    izhang      01/06/03 - add em_ipw_info
Rem    izhang      11/13/02 - changing pki from varchar2 to BLOB
Rem    izhang      10/11/02 - Increasing size of the column for pki
Rem    dmshah      10/03/02 - dmshah_security_repository_apis
Rem    dmshah      10/02/02 - code review comments
Rem    dmshah      10/01/02 - Creating the LOB col in a different tablespace
Rem    dmshah      09/26/02 - 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"


rem
rem PURPOSE
rem
rem The MGMT_SEC_INFO table stores security information related to the EM. 
rem Specifically, it stores the agent IPW which is used as a seed to generate 
rem the agent key. It also stores the CA for the EM.
rem
rem COLUMNS
rem
rem    IPW     The initial agent Install PassWord.
rem    CA_PKI  The PKI material in CA
rem    CA_PWD  The PWD material in CA
rem    CA      The actual Certificate
rem
CREATE TABLE MGMT_SEC_INFO
(
     IPW     VARCHAR2(256) ,
     CA_PKI  BLOB ,
     CA_PWD  VARCHAR2(128)  ,
     CA      BLOB
) LOB(CA_PKI, CA) STORE AS (TABLESPACE &EM_ECM_DEPOT_TABLESPACE)
MONITORING;

rem
rem PURPOSE
rem
rem The MGMT_AGENT_SEC_INFO table stores the agent key after it is generated
rem using the agent IPW.
rem
rem COLUMNS
rem
rem    AGENT_GUID    The target guid of the EMD
rem    AGENT_KEY     The agent key
rem
CREATE TABLE MGMT_AGENT_SEC_INFO
(
     TARGET_GUID  RAW(16) NOT NULL,
     AGENT_KEY    varchar2(128) NOT NULL 
)
MONITORING;

rem
rem PURPOSE
rem
rem The EM_IPW_INFO table stores all Agent's IPW information 
rem
rem COLUMNS
rem
rem    PWD_IDX       The index of IPW
rem    IPW           The EM install password
rem    DESC_STR      The description of an IPW
rem    TYPE          The use type of an IPW (multiple/once)
rem    EXPIRE_date   The expire date of an IPW
rem
CREATE TABLE EM_IPW_INFO
(
     PWD_IDX         VARCHAR2(256) NOT NULL,  
     IPW             VARCHAR2(256),
     DESC_STR        VARCHAR2(256) ,
     PWD_TYPE        VARCHAR2(64) ,
     EXPIRE_DATE     DATE      
     
)
MONITORING;
@&EM_SQL_ROOT/core/10.2.0.4/security/security_tables.sql