Technical Blog Post
Abstract
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.
UID
ibm11142128