Set Up Access Rights for an Oracle Database

Before you can use an Oracle database in Transformation Designer, you need to set up access rights. This configuration requires some up-front planing to ensure that the Oracle database is available and that there are adequate Oracle user accounts.

This preparation is required because the default behavior of Oracle allows only the object owner to perform any operations on a table or a view. That means that if the Kofax TotalAgility user and the Oracle database owner are not the same, the Oracle database is accessible only if special security privileges are configured and the necessary configuration steps are performed.

If these configuration steps are not performed, the Oracle database is not visible when adding an Oracle database in Kofax TotalAgility 7.4.1 - Transformation Designer .

You can prepare an Oracle Database for use by following these steps:

  1. Define the minimum user and role privileges in Oracle.
    1. The ORACLE_USER or any other user account that creates the Oracle database must have the following rights assigned to be able to create the database table:
      RESOURCE
      CREATE TABLE
      CREATE SESSION
    2. In addition, to make the database visible and configurable in Transformation Designer these rights are needed:
      CREATE ROLE
      CREATE SYNONYM
  2. The KTM_USER or any other user account that accesses the Oracle database from Kofax TotalAgility must have the following rights:
    CONNECT
    RESOURCE
    EXP_FULL_DATABASE (SELECT ANY TABLE)
  3. Optionally, in case you have various users you can define a user role such as KTM_ROLE and assign the above listed rights to the role. A role is a predefined collection of privileges that can be easily assigned to users.
  4. Create the Oracle table. For example, KTM_VENDORS.
    CREATE TABLE KTM_VENDORS 
    ( 
        NUM NUMBER NOT NULL ,
        Firma VARCHAR2(20) NULL , 
        STREET VARCHAR2(30) NULL , 
        CITY VARCHAR2(30) NULL ,
        POSTAL_CODE VARCHAR2(20) NULL , 
        CONSTRAINT PK_VENDOR PRIMARY KEY (NUM)
    )
  5. Give the KTM_USER or KTM_ROLE access to the new Oracle table.
    GRANT CREATE ANY INDEX, SELECT ANY TABLE TO KTM_USER;
    GRANT CREATE ANY INDEX, SELECT ANY TABLE TO KTM_ROLE;
  6. Define a public synonym to the Oracle database table.
    create public synonym KTM_VENDORS for ORACLE_USER.KTM_VENDORS;

    Now the KTM_USER or users that have KTM_ROLE assigned can view the database in Oracle.

  7. Create a view for the Oracle database and assign it to KTM_USER or KTM_ROLE so that this view can be selected and configured in Kofax TotalAgility 7.4.1 - Transformation Designer or Kofax Search and Matching Server Administration.
    CREATE VIEW KTM_VENDORS_VIEW AS SELECT * FROM KTM_VENDORS;
  8. Add an Oracle database in Kofax TotalAgility 7.4.1 - Transformation Designer .