Introduction

Oracle ADW is a symmetrical multiprocessing database. This database is provisioned as part of Fusion Data Intelligence (FDI) instance creation. By default, you get these two users:

ADMIN – Access to create, modify, and lock custom users, reset the password for OAX_USER, and query the data dictionary and various administrator functionalities including creating Oracle Machine Learning users.

OAX_USER – Query all the data warehouse tables created. All pf these tables have a prefix DW_ and are accessible as public synonyms.

This article provides an approach for a scenario where custom schema needs to be created specifically for Finance users in a multipillar subscription.

Approach

You need to execute the following steps:

1. Create a custom schema.
2. Analyze the filtering criteria to identify tables related Finance Analytics modules.
3. Grant access to the objects in the OAX_USER schema based upon the filtered criteria identified.
4. Create private synonyms for the custom schema for the filtered criteria identified.
5. Validate the access.

1.  Create a custom schema. 

     This is a three step process:

1. Create a user with a password.
2. Grant access to the custom user to create objects and connect to the database.
3. Grant user quota to consume storage. You need to create and execute the SQL query for each of these corresponding steps. The SQL depitcted below creates the CUSTOM_DW user with a custom password and provides the needed grants. You must execute this as an ADMIN user.

2. Analyze the filtering criteria to identify tables related to the Finance Analytics modules.

You can create a report such as the one shown below:

 Users must run the above report and not just reference the queries provided, as the FDI content is dynamic and may change with each release.​

From this report you can pick up the list of modules which are a part for example:  Application Name selected as Fusion ERP Analytics and Functional Area Name as Financials. Using the result from the report, you can create an indicative query such as the one shown below:

Validate the query in the database.

3. Grant access to the objects in the OAX_USER schema based on the filtered criteria identified.

The following PL SQL block code when run as OAX_USER schema generates a Grant statement for all the database objects for which OAX_USER has access.

SET SERVEROUTPUT ON;
DECLARE
    CURSOR c_tab_names IS
    SELECT
        f.table_name
    FROM
        dw_wh_table_details        t,
        dw_dmd_func_area_table_map f,
        user_synonyms              us
    WHERE
            f.datasource_code = 'FUSION'
        AND t.table_name = f.table_name
        AND us.synonym_name = t.table_name
        AND t.tdm_display_version = (
            SELECT
                MAX(tdm_display_version)
            FROM
                dw_wh_table_details
            WHERE
                tdm_display_version <> 99.99
        )
        AND functional_area_code IN ( 'FA_AP', 'FA_AP_PREDICTION', 'FA_AR', 'FA_AR_AGING', 'FA_AR_PAYMENT_DATE_PREDICTION',
                                      'FA_AR_PREDICTION', 'FA_AR_UNACCT_TRANSACTIONS', 'FA_ASSETS', 'FA_DUPLICATE_INVOICE_DETECTION',
                                      'FA_EMPLOYEE_EXPENSE_ANOMALY',
                                      'FA_EXM', 'FA_GL' );

    grant_stmt VARCHAR2(2000) := NULL;
    elem       VARCHAR2(2000) := 'CUSTOM_DW';
BEGIN
    FOR c1 IN c_tab_names LOOP
        grant_stmt := 'grant select on '
                      || c1.table_name
                      || ' to '
                      || elem;
        dbms_output.put_line(grant_stmt);
        EXECUTE IMMEDIATE grant_stmt;
    END LOOP;
END;

When you execute this statement in Oracle client tools such as SQL Developer, the grants are executed granting access to all the objects, as well as due to dynamic SQL grant statements.

The script also prints the grants executed as shown in the screenshot above. 

4. Create private synonyms for the custom schema for the filtered criteria identified.

After you complete step 2, you’re able to access the objects using a schema name qualifier, for example, OAX_USER.DW_ABS_CATEGORY_D instead of directly using DW_ABS_CATEGORY_D. This can be inconvenient for majority of users. You can mitigate this by creating private synonyms for each of the objects.

SET SERVEROUTPUT ON;
DECLARE
    synonym_stmt VARCHAR2(2000) := NULL;
BEGIN
    FOR c1 IN (
        SELECT
            us.synonym_name
        FROM
            dw_wh_table_details        t,
            dw_dmd_func_area_table_map f,
            user_synonyms              us
        WHERE
                f.datasource_code = 'FUSION'
            AND t.table_name = f.table_name
            AND us.synonym_name = t.table_name
            AND t.tdm_display_version = (
                SELECT
                    MAX(tdm_display_version)
                FROM
                    dw_wh_table_details
                WHERE
                    tdm_display_version <> 99.99
            )
            AND functional_area_code IN ( 'FA_AP', 'FA_AP_PREDICTION', 'FA_AR', 'FA_AR_AGING', 'FA_AR_PAYMENT_DATE_PREDICTION',
                                          'FA_AR_PREDICTION', 'FA_AR_UNACCT_TRANSACTIONS', 'FA_ASSETS', 'FA_DUPLICATE_INVOICE_DETECTION'
                                          , 'FA_EMPLOYEE_EXPENSE_ANOMALY',
                                          'FA_EXM', 'FA_GL' )
    ) LOOP
        synonym_stmt := 'create  or replace  synonym  '
                        || c1.synonym_name
                        || ' for  oax_user.'
                        || c1.synonym_name;
        dbms_output.put_line('Start => ' || synonym_stmt);
        EXECUTE IMMEDIATE synonym_stmt;
    END LOOP;
END;

You need to log in as the custom schema and then execute the above shown block. The block on execution generates private synonyms thereby enabling users to seamlessly query artifacts with a schema name qualifier.

The script also prints the synonyms created as depicted in the previous image. You can modify these scripts to meet your requirements.

5. Validate access.

Data security is a critical area for designing your own validations. In example shown below, one access to one of the HCM tables is validated for this custom schema.

Call to Action

This article presents an approach to customize access specific to Finance ERP Analytics reporting users based on the security requirements dictated by compliance and business needs. To learn more, view the product documentation, view additional Analytics blogs at blogs.oracle.com/analytics, and follow us on Twitter@OracleAnalytics.