nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

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

Jay Chandran

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

Find more posts tagged with

Fusion AI Data Platform

Accepted answers

Mallikarjuna Kuppauru-Oracle

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

All comments

Mallikarjuna Kuppauru-Oracle

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

Jay Chandran

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

Mallikarjuna Kuppauru-Oracle

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