10.4 Configuring New User Schema

This section describes how to create a new user schema in the sys user.

To create the new user, follow these steps:

1. Run the following SQL statements in simulation as sys user:

   CREATE USER <New Workspace Schema> IDENTIFIED BY <password> DEFAULT TABLESPACE
   AIF_USER_TS TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON AIF_USER_TS;
   grant create SESSION to <New Workspace Schema>;
   grant create PROCEDURE to <New Workspace Schema>;
   grant create SEQUENCE to <New Workspace Schema>;
   grant create TABLE to <New Workspace Schema>;
   grant create TRIGGER to <New Workspace Schema>;
   grant create VIEW to <New Workspace Schema>;
   grant create MATERIALIZED VIEW to <New Workspace Schema>;
   grant select on SYS.V_$PARAMETER to <New Workspace Schema>;
   grant create SYNONYM to <New Workspace Schema>;
   grant select on sys.v_$parameter to <New Workspace Schema>;
   grant select on sys.dba_free_space to <New Workspace Schema>;
   grant select on sys.dba_tables to <New Workspace Schema>;
   grant select on sys.Dba_tab_columns to <New Workspace Schema>;
   grant create RULE to <New Workspace Schema>;
   grant create any trigger to <New Workspace Schema>;
   grant drop any trigger to <New Workspace Schema>;
   grant select on SYS.DBA_RECYCLEBIN to <New Workspace Schema>;
   grant connect, resource, dba to <new workspace schema>;

2. Once the user is created, run the following SQL statements in new workspace schema user created:

   1. CREATE OR REPLACE EDITIONABLE SYNONYM PR2_FIRERUN_FILTER FOR
      <<config_schema>>.PR2_FIRERUN_FILTER;
      CREATE OR REPLACE EDITIONABLE SYNONYM PR2_RUN_EXECUTION_FILTER FOR
      <<config_schema>>.PR2_RUN_EXECUTION_FILTER;
      CREATE OR REPLACE EDITIONABLE SYNONYM AAI_WF_FILTER_EXEC_MAP FOR
      <<config_schema>>.AAI_WF_FILTER_EXEC_MAP;
      CREATE OR REPLACE EDITIONABLE SYNONYM CONFIGURATION FOR
      <<config_schema>>.CONFIGURATION;
      CREATE OR REPLACE EDITIONABLE SYNONYM PR2_RUN_EXECUTION_B FOR
      <<config_schema>>.PR2_RUN_EXECUTION_B;
      CREATE OR REPLACE EDITIONABLE SYNONYM METADATA_ELEMENT_MASTER FOR
      <<config_schema>>.METADATA_ELEMENT_MASTER;
      CREATE OR REPLACE EDITIONABLE SYNONYM RTI_EVALUATION FOR
      <<config_schema>>.RTI_EVALUATION;
      CREATE OR REPLACE SYNONYM checkenvfordataredaction FOR
      <<config_schema>>.checkenvfordataredaction;
      CREATE OR REPLACE SYNONYM cssms_role_mast FOR
      <<config_schema>>.cssms_role_mast;
      CREATE OR REPLACE SYNONYM cssms_group_role_map FOR
      <<config_schema>>.cssms_group_role_map;
      CREATE OR REPLACE SYNONYM cssms_usr_group_map_view FOR
      <<config_schema>>.cssms_usr_group_map_view;
      CREATE OR REPLACE SYNONYM AAI_DB_PROPERTY FOR
      <<config_schema>>.AAI_DB_PROPERTY;
      CREATE OR REPLACE SYNONYM CONFIGURATION FOR
      <<config_schema>>.CONFIGURATION;
      CREATE OR REPLACE SYNONYM AAI_DB_DETAIL FOR
      <<config_schema>>.AAI_DB_DETAIL;
      CREATE OR REPLACE SYNONYM DSNMASTER FOR <<config_schema>>.DSNMASTER;
      CREATE OR REPLACE EDITIONABLE SYNONYM CSSMS_USR_PROFILE FOR
      <<config_schema>>."CSSMS_USR_PROFILE";

   2. Execute the following from SYSDB:

      grant select on <<config_schema>>.PR2_RUN_MAP to <SCHEMA_NAME>;
      grant insert on <<config_schema>>.PR2_RUN_MAP to <SCHEMA_NAME>;
      grant insert on <<config_schema>>.PR2_RUN_OBJECT_MEMBER to <SCHEMA_NAME>;
      grant select on <<config_schema>>.PR2_FIRERUN_FILTER to <SCHEMA_NAME>;
      grant select on <<config_schema>>.AAI_DB_PROPERTY to <SCHEMA_NAME>;
      grant select on <<config_schema>>.CONFIGURATION to <SCHEMA_NAME>;
      grant select on <<config_schema>>.DSNMASTER to <SCHEMA_NAME>;
      grant select on <<config_schema>>.AAI_DB_DETAIL to <SCHEMA_NAME>;
      grant select on <<config_schema>>.CSSMS_HOLIDAY_LIST to <SCHEMA_NAME>;
      grant select on <<config_schema>>.rti_evaluation to <SCHEMA_NAME>;
      grant select on <<config_schema>>.pr2_run_object_member to <SCHEMA_NAME>;
      grant select on <<config_schema>>.pr2_run_map to <SCHEMA_NAME>;
      grant select on <<config_schema>>.PR2_FILTERS to <SCHEMA_NAME>;
      grant select on <<config_schema>>.pr2_run_object to <SCHEMA_NAME>;
      grant select on <<config_schema>>.PR2_RUN_EXECUTION_FILTER to <SCHEMA_NAME>;
      grant select on <<config_schema>>.CONFIGURATION to <SCHEMA_NAME>;
      grant select on <<config_schema>>.AAI_WF_FILTER_EXEC_MAP to <SCHEMA_NAME>;
      grant select on <<config_schema>>.PR2_RUN_EXECUTION_B to <SCHEMA_NAME>;
      grant select on <<config_schema>>.metadata_element_master to <SCHEMA_NAME>;
      grant execute on <<config_schema>>.checkenvfordataredaction to <SCHEMA_NAME>;

   3. CREATE OR REPLACE FORCE EDITIONABLE VIEW PR2_RUN_MAP ("V_RUN_ID",
      "V_INFODOM_NAME", "V_TASK_REF_UNIQUE_NAME", "V_OBJECT_UNIQUE_NAME",
      "V_MEMBER_UNIQUE_NAME") AS
      SELECT V_RUN_ID,
      V_INFODOM_NAME,
      V_TASK_REF_UNIQUE_NAME,
      V_OBJECT_UNIQUE_NAME,
      V_MEMBER_UNIQUE_NAME
      FROM <<config_schema>>.PR2_RUN_MAP PRM
      WHERE PRM.V_INFODOM_NAME = '<<WORKSPACE_INFODOM>>';

   4. CREATE OR REPLACE FORCE EDITIONABLE VIEW PR2_RUN_OBJECT ("V_RUN_ID",
      "V_INFODOM_NAME", "V_OBJECT_UNIQUE_NAME", "V_OBJECT_TYPE_CODE",
      "V_OBJECT_LOCATION_CODE", "N_OBJECT_ORDER", "V_TASK_REF_UNIQUE_NAME",
      "V_TASK_TYPE_CODE", "V_TASK_SUB_TYPE_CODE", "V_TASK_REF_1_NAME",
      "V_TASK_REF_1_VALUE", "V_TASK_REF_2_NAME", "V_TASK_REF_2_VALUE",
      "V_TASK_REF_3_NAME", "V_TASK_REF_3_VALUE", "V_TASK_REF_4_NAME",
      "V_TASK_REF_4_VALUE") AS
      SELECT V_RUN_ID,
      V_INFODOM_NAME,
      V_OBJECT_UNIQUE_NAME,
      V_OBJECT_TYPE_CODE,
      V_OBJECT_LOCATION_CODE,
      N_OBJECT_ORDER,
      V_TASK_REF_UNIQUE_NAME,
      V_TASK_TYPE_CODE,
      V_TASK_SUB_TYPE_CODE,
      V_TASK_REF_1_NAME,
      V_TASK_REF_1_VALUE,
      V_TASK_REF_2_NAME,
      V_TASK_REF_2_VALUE,
      V_TASK_REF_3_NAME,
      V_TASK_REF_3_VALUE,
      V_TASK_REF_4_NAME,
      V_TASK_REF_4_VALUE
      FROM <<config_schema>>.PR2_RUN_OBJECT PRO
      WHERE PRO.V_INFODOM_NAME = '<<WORKSPACE_INFODOM>>';

   5. CREATE OR REPLACE FORCE EDITIONABLE VIEW PR2_RUN_OBJECT_MEMBER
      ("V_RUN_ID", "V_INFODOM_NAME", "V_OBJECT_UNIQUE_NAME",
      "V_MEMBER_UNIQUE_NAME", "V_MEMBER_TYPE_CODE", "N_MEMBER_ORDER") AS
      SELECT V_RUN_ID,
      V_INFODOM_NAME,
      V_OBJECT_UNIQUE_NAME,
      V_MEMBER_UNIQUE_NAME,
      V_MEMBER_TYPE_CODE,
      N_MEMBER_ORDER
      FROM <<config_schema>>.PR2_RUN_OBJECT_MEMBER PROM
      WHERE PROM.V_INFODOM_NAME = '<<WORKSPACE_INFODOM>>';
      

   6. CREATE OR REPLACE FORCE EDITIONABLE VIEW PR2_FILTERS ("F_IS_RRF",
      "V_DSN_NAME", "V_EXECUTION_ID", "V_TASK_ID", "V_COMPONENT_CODE",
      "N_RUN_SKEY", "V_RUN_CODE", "V_RULE_CODE", "V_FILTER") AS
      SELECT FILTERS.F_IS_RRF,
      FILTERS.V_DSN_NAME,
      FILTERS.V_EXECUTION_ID,
      FILTERS.V_TASK_ID,
      FILTERS.V_COMPONENT_CODE,
      FILTERS.N_RUN_SKEY,
      FILTERS.V_RUN_CODE,
      FILTERS.V_RULE_CODE,
      FILTERS.V_FILTER
      FROM (SELECT 'RRF' AS F_IS_RRF,
      PREF.V_INFODOM_NAME AS V_DSN_NAME,
      PREF.V_RUN_EXECUTION_ID AS V_EXECUTION_ID,
      PREF.V_TASK_ID AS V_TASK_ID,
      PREF.V_PROCESS_ID AS V_COMPONENT_CODE,
      PREF.N_RUN_SKEY AS N_RUN_SKEY,
      PREB.V_RUN_ID AS V_RUN_CODE,
      PREF.V_RULE_ID AS V_RULE_CODE,
      '(' || CASE
      WHEN PREF.V_PROCESS_FILTER IS NULL THEN
      '7=7'
      ELSE
      PREF.V_PROCESS_FILTER
      END || ')' || ' AND ' || '(' || CASE
      WHEN PREF.V_RUN_FILTER IS NULL THEN
      '8=8'
      ELSE
      PREF.V_RUN_FILTER
      END || ')' || ' AND ' || '(' || CASE
      WHEN PFF.V_RUN_FILTER IS NULL THEN
      '9=9'
      ELSE
      PFF.V_RUN_FILTER
      END || ')' AS V_FILTER
      FROM <<config_schema>>.PR2_RUN_EXECUTION_B PREB
      LEFT OUTER JOIN <<config_schema>>.PR2_FIRERUN_FILTER PFF ON
      PFF.V_RUN_EXECUTION_ID =
      PREB.V_RUN_EXECUTION_ID
      AND PFF.V_INFODOM_NAME =
      PREB.V_INFODOM_NAME
      JOIN <<config_schema>>.PR2_RUN_EXECUTION_FILTER PREF ON
      PREF.V_RUN_EXECUTION_ID =
      PREB.V_RUN_EXECUTION_ID
      AND PREF.V_INFODOM_NAME =
      PREB.V_INFODOM_NAME
      WHERE PREB.V_INFODOM_NAME = '<<WORKSPACE_INFODOM>>'
      UNION ALL
      SELECT 'PR2' AS F_IS_RRF,
      V_DSN_NAME,
      V_EXECUTION_ID,
      NULL AS V_TASK_ID,
      V_COMPONENT_CODE,
      N_RUN_SKEY,
      V_RUN_CODE,
      V_RULE_CODE,
      V_FILTER
      FROM <<config_schema>>.PR2_FILTERS PF
      WHERE PF.V_DSN_NAME = '<<WORKSPACE_INFODOM>>') FILTERS
      JOIN <<config_schema>>.CONFIGURATION ON UPPER(PARAMVALUE) =
      UPPER(F_IS_RRF)
      WHERE PARAMNAME = 'F_IS_RRF'
      UNION ALL
      SELECT AWFEM.F_IS_RRF,
      AWFEM.V_DSN_NAME,
      AWFEM.V_EXECUTION_ID,
      AWFEM.V_TASK_ID,
      AWFEM.V_COMPONENT_CODE,
      AWFEM.N_RUN_SKEY,
      AWFEM.V_RUN_CODE,
      AWFEM.V_RULE_CODE,
      AWFEM.V_FILTER
      FROM <<config_schema>>.AAI_WF_FILTER_EXEC_MAP AWFEM;

3. Configure the new INFODOM with the new schema details in the tnsnames.ora file and WebLogic server. To enter the new schema details in the tnsnames.ora file, see the following sample template:

   <SCHEMA_NAME> =
   (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = <IP ADDRESS>)(PORT = <PORT>)
   (CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = <SERVICE_NAME>)
   )
   )
   <SIM_NEW_INFODOM> =
   (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST =<IP ADDRESS>)(PORT = <PORT>))
   (CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = <SERVICE_NAME>)
   )
   )

4. To enter new INFODOM details in the WebLogic server and add the INFODOM in Console, follow these steps:
   1. Log into WebLogic console and go to Services.
   2. Click Data Sources.
   3. Click New and add Data Source name as Simulation_infodom and JNDI Name as JDBC/<Simulation_infodom> for the new database schema details.

      Note:

      <<Simulation Infodom>> must not have more than 11 characters.
5. Add the entry for new INFODOM in web.xml under ##Deployed Area##/##Context##/WEB- INF:

   <resource-ref>
   <description>DB Connection <<Infodom Name>></description>
   <res-ref-name>JDBC/<<Infodom name>> </res-ref-name>
   <res-type>javax.sql.DataSource</res-type>
   <res-auth>Container</res-auth>
   </resource-ref>

6. Configure the wallet with the new schema details in the Studio Setup.

   For more information, see Setup Password Stores with Oracle Wallet section in Compliance Studio Installation Guide.

7. Before running the batch, create the IPE_KYC_Source folder under/ftpshare/<<Workspace infodom>>/logs/.