How do I configure Oracle Credentials?

This guide outlines the process of creating a user, granting the necessary permissions, and adding the credentials to the ThinkData Platform to connect to an Oracle database.

Step-by-Step Guide:

Step 1: Create a User with Read-Only permissions 

To create a user in your Oracle Database instance, follow these steps:

1. Log in to the Oracle instance using your preferred SQL client or workbench (e.g., SQL*Plus, Oracle SQL Developer, DataGrip etc.).

2. Create a new user with a secure password:

   CREATE USER ro_user IDENTIFIED BY 'Password123';

    - Replace 'ro_user' with the desired user name.

     Password Recommendation:

• • Use a password that is 64 characters long, consisting of alphanumeric characters for maximum security.
  • Note: Avoid using special characters in the password, as they may cause connection issues.

Step 2: Grant Permissions to the User

After creating the user, grant only the minimum necessary permissions listed below to enable secure access for the ThinkData Platform.

Basic Permissions

The following permissions are required to connect, list, and virtualize data assets from Oracle Database in the ThinkData Platform.

1. Grant CREATE SESSION to allow the user to connect to the Oracle database:

   GRANT CREATE SESSION TO ro_user;

2. Grant SELECT permission on specific tables in a schema:

   GRANT SELECT ON schema_name.table_name TO ro_user;

   Replace 'schema_name.table_name' with the specific table you want the user to access. 
3. (Optional) Grant SELECT permission on all existing tables in a schema:

   BEGIN
   FOR t IN (SELECT table_name FROM all_tables WHERE owner = 'schema_name') LOOP
   EXECUTE IMMEDIATE 'GRANT SELECT ON schema_name.' || t.table_name || ' TO ro_user';
   END LOOP;
   END; 

   Replace 'schema_name' with the name of the schema you wish to provide access to. This grants access to all existing tables (but not future tables) in the specified schema. 

• Note: If the user needs to grant access to future tables in the schema as well, it's recommended to periodically re-run the above command. This is necessary because the command only grants access to the tables that currently exist at the time of execution. Any new tables created after the command runs will require a re-execution of the script to grant access to those future tables.

Additional Permissions for Managing Data Products

If the user needs to create or manage data products from existing data assets in the ThinkData Platform, it is recommended to create a new user and grant the following additional permissions to this new user, rather than assigning these permissions to an existing user. This approach avoids granting these broader permissions to an existing user, ensuring better access control and security.

5. Create a new user with Read-Write Access and grant the SESSION permission to allow connection to the database:

   CREATE USER rw_user IDENTIFIED BY "MyNewPassword";
   
   GRANT CREATE SESSION TO rw_user;

6. Grant the permissions for object creation within the database:

   GRANT CREATE ANY TABLE TO rw_user;
   GRANT SELECT ANY TABLE TO rw_user; 
   GRANT INSERT ANY TABLE TO rw_user;
   GRANT ALTER ANY TABLE TO rw_user;
   GRANT DROP ANY TABLE TO rw_user;

Step 3: Add Oracle Database Credentials to the ThinkData Platform

After creating the credentials in the Oracle Database instance, add them to the ThinkData Platform to enable data access by following the steps below. This process must be completed twice: once for the user with Basic Permissions (ro_user) and again for the user with additional permissions (rw_user) to create data products.

Steps to add Oracle Database credentials to the platform:

1. Open the platform and navigate to the Credentials page:
   • Data > Credentials > Create Credential
2. Enter the user name associated with these credentials (or any other identifying name) in the Credential Name field.
3. In the Type dropdown, select Oracle.
4. Enter the  Username  and Password for the Oracle users created earlier.
5. Click Save to store the credentials.

Step 4: Using the Credentials

Now, you can use the credentials for the following:

• `ro_user` with a Source Connection to virtualize a data asset from Oracle, or
• `rw_user` with a  Destination Connection to create a Data Product with Oracle as the destination, using existing datasets from the ThinkData Platform.