Installing IBM InfoSphere MDMv11.6 on Oracle using different user name and schema name

Body

Oracle database automatically creates a schema when a user is created.  When a user logs in the default schema used is the one with the same name as the user.

In order for InfoSphere Master Data Management v11.6 to use a schema that is not the same the user name, the Logon Trigger given below has to be created and certain privileges have to be granted to the schema name (user).

Logon Trigger

The Oracle native driver does not provide a property to specify the schema name when the schema name is not the same as the User name.  Hence a trigger has to be executed, when the schema that has to be used is different from the user name.

CREATE OR REPLACE TRIGGER LOGON_TRIGGER

  AFTER LOGON ON DATABASE

BEGIN

    IF (USER IN ('<USER>')) THEN

      EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA = <SCHEMA>';

    END IF;

EXCEPTION

  WHEN OTHERS

    THEN NULL;

END LOGON_TRIGGER;

/

In the above trigger, the placeholders <USER> and <SCHEMA> have to be replaced with the appropriate values.

Privileges to be granted

Create schema script that comes with MDM provides the below privileges to the user:

GRANT CREATE SESSION TO <SCHEMA>;

GRANT UNLIMITED TABLESPACE TO <SCHEMA>;

GRANT CREATE SEQUENCE TO <SCHEMA>;

GRANT CREATE ANY SYNONYM TO <SCHEMA>;

GRANT CREATE TABLE TO <SCHEMA>;

GRANT CREATE TRIGGER TO <SCHEMA>;

GRANT CREATE TYPE TO <SCHEMA>;

GRANT CREATE VIEW TO <SCHEMA>;

GRANT SELECT ANY TABLE TO <SCHEMA>;

GRANT IMP_FULL_DATABASE TO <SCHEMA>;

GRANT SELECT ANY DICTIONARY TO <SCHEMA>;

GRANT RESOURCE TO <SCHEMA>;

GRANT CONNECT TO <SCHEMA>;

GRANT CREATE SNAPSHOT TO <SCHEMA>;

In addition to the above privileges and access to tablespaces, the below privileges have to be granted:

GRANT SELECT ANY SEQUENCE TO <SCHEMA>;

GRANT ANALYZE ANY TO <SCHEMA>;

GRANT LOCK ANY TABLE TO <SCHEMA>;

The purpose for each Privilege is explained below:

GRANT SELECT ANY SEQUENCE TO <SCHEMA>;

This privilege is required to access sequences that are created during RDM installation.

GRANT ANALYZE ANY TO <SCHEMA>;

This privilege is required to execute SQLs which are found in Insensitive_search_enabled.sql which executes statements similar to the below:

EXEC dbms_stats.gather_table_stats(ownname => '<SCHEMA>', tabname => 'HIERARCHY' ,method_opt => 'for all indexed columns size auto');

GRANT LOCK ANY TABLE TO <SCHEMA>;

This statement is required to obtain a lock on the SIB tables and to execute verify_install which obtains locks on certain SE tables.