Oracle Fusion Data Intelligence

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

How to access Autonomous Data Warehouse (ADW FDI) via SQL Developer via a named user

Accepted answer
183
Views
3
Comments

We want to be able to run queries against DW tables in ADW via SQL Developer using a named user (and NOT OAX_USER). When trying this, it says table or view doesn't exist. Same error even if we prefix the select with "oax$oac". Example, "select * from oax$oac.DW_PROJECT_COMMITMENT_CF;".

Providing grant for each named user to various DW tables would be onerous… 5-6 users accessing 100 DW tables would require setting 500 to 600 grants. Has anyone accessed ADW using a named user (or have they only used OAX_USER)? Please advise how you approached this?

Thanks,

Jay

Best Answer

  • Rank 8 - Analytics Strategist
    Answer ✓

    Hi @Jay Chandran

    As per design, OAX_USER is mainly designed to query your DW tables. The user having an readonly access to tables related to OAX$OAC, OAX$DW tables.

    if you are planning to create an Named users and use the same , i hope you need to provide an grants manually.

    check below tables, to get the grants given to OAX_USER .

    DBA_SYS_PRIVS, DBA_TAB_PRIVS, and DBA_ROLE_PRIVS

    Regards,

    Arjun

Answers

  • Rank 1 - Community Starter

    Hello Mallikarjuna…thanks for your prompt response.

    Now, say, if there are 5 IT users who will need to run queries in ADW against say, 50 tables, does it mean the grant script needs to be run 5 X 50 = 250 times?

    Thanks,

    Jay

  • Rank 8 - Analytics Strategist
    edited Nov 5, 2024 8:26PM

    Hi @Jay Chandran

    You can put all the entries in one script file and run it. OR you can work with your DBA to automate the script/process.

    Regards,

    Arjun

Welcome!

It looks like you're new here. Sign in or register to get started.