Configuring two users for Incorta metadata (Oracle database)
This article is mainly for those selecting "Oracle" as the database for the Incorta Analytics metadata, and wish to separate the ownership of the schema tables from the metadata.
During installation, if you select "Oracle" database for the Incorta Analytics metadata, you may choose to configure two database users as shown below:
User 1 (INCORTA_OWNER): This is the owner of the metadata performing the installation to the metadata tables, and will have DDL access.
User 2 (INCORTA_USER): This is the Incorta Analytics user, and will only have DML access to the objects.
For Fresh Installations
To configure two database users for new installations:
- Create a new Oracle user (e.g. INCORTA_OWNER).
- Grant this user ALL Privileges to their own schema.
- Create another Oracle user (e.g. INCORTA_USER).
- Run the installer, and follow the steps of the installation wizard.
- Tick the checkbox required to define the DDL user.
- Enter the username and password for the DDL user to create the Incorta Analytics metadata.
Do not create a default tenant during the installation process. That is, in the "Create a Tenant" step of the Installation Wizard, leave the "Create a default tenant" checkbox unchecked.
- If you have Incorta Analytics version 2.8 (or later), go to step 8. Grant select, insert, update, and delete to the new INCORTA_USER on all of the INCORTA tables owned by the INCORTA_OWNER in this example.
- Create a tenant using the Tenant Management Tool (TMT). For more information, refer to the TMT chapter in the Incorta Analytics Administrator Guide.
For Existing Installations
During an upgrade, there are two scenarios when configuring two database users:
- Current installation works with only one DB user while two different users are needed to handle the Incorta Analytics metadata. In this case, the following steps will have to be performed manually before the upgrade process takes place:
- Create a new Oracle user, e.g. INCORTA_USER.
- Grant select, insert, update, and delete to the new INCORTA_USER on all of the Incorta tables owned by the DDL user.
- Modify the "server.xml" file in the installation directory:<INSTALLATION_PATH>/server/conf/server.xml
and add the following properties:
a. factory = "org.apache.tomcat.jdbc.pool.DataSourceFactory"
b. initSQL = "ALTER SESSION SET CURRENT_SCHEMA = <INCORTA_OWNER_NAME>"
<Resource name="jdbc/incortaDB" auth="Container" type="javax.sql.DataSource" driverClassName="oracle.jdbc.driver.OracleDriver" testOnBorrow="true" validationQuery="select 1 from Dual" url="jdbc:oracle:thin:@<HOSTNAME>:<DATABASE_PORT>:<DATABASE_NAME>" username="<INCORTA_USER_USERNAME>" password="<INCORTA_USER_PASSWORD>" maxActive="500" factory = "org.apache.tomcat.jdbc.pool.DataSourceFactory" initSQL = "ALTER SESSION SET CURRENT_SCHEMA = <INCORTA_OWNER_USERNAME>" />
- Current installation already works with two different Oracle users. In this case, run the installer and it will automatically detect that two users are already defined. It will then ask for the DDL password to execute the upgrade process.