Introduction

Oracle ADW is an symmetrical multiprocessing database. This database is provisioned as part of Fusion Data Intelligence 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 of these tables have a prefix DW_ and are accesible as public synonyms.

Sharing paswords for these system defined accounts isn’t a recommended practice. This article describes how you can set up a custom schema with equivalent level of access as that of the system provided OAX_USER.

Approach

You need to execute the following steps:

1. Create a custom schema.
2. Grant access to the objects in the OAX_USER schema.
3. Create private synonyms for the custom schema.

1.  Create a custom schema 

     This is a 3-step process:

• Create a user with a password.

• Grant access to the custom user to create objects, and connect to the database.

• Grant user quota to consume storage. You need to create and execute the SQL query for each of these corresponding steps. The SQL below creates the CUSTOM_DW user with a custom password and provides the needed grants. You must execute this as an ADMIN user.

2.  Grant access to all DW objects in OAX_USER

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.

DECLARE
    CURSOR c_tab_names IS
    SELECT
        owner,
        object_name
    FROM
        all_objects
    WHERE
        ( ( object_name LIKE 'DW_%'
            AND object_type = 'VIEW' ) )
        AND owner = 'OAX$DW'
     /*<-- This is the FAW internal DW user. DO NOT CHANGE THIS*/
    ORDER BY
        1;
    grant_stmt VARCHAR2(2000) := NULL;
    elem       VARCHAR2(2000) := 'CUSTOM_DW';
BEGIN
    FOR c1 IN c_tab_names LOOP
        grant_stmt := 'grant select on '
                      || c1.object_name
                      || ' to '
                      || elem;
        dbms_output.put_line('Start => ' || 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 depicted in the screenshot above. 

3. Create private synonyms for the custom schema

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 incovenient for majority of users. You can mitigate this by creating private synonyms for each of the objects.

DECLARE
    synonym_stmt VARCHAR2(2000) := NULL;
BEGIN
    FOR c1 IN (
        SELECT DISTINCT
            synonym_name
        FROM
            all_synonyms
        WHERE
            synonym_name LIKE 'DW_%'
            AND owner = 'OAX_USER'
    ) 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 block. The block on execution will generate private synonyms thereby enabling users to seamlessly query arttifacts with a schema name qualifier.

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

Summary

This article presents an approach to customize access for 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.