Categories
How to access Autonomous Data Warehouse (ADW FDI) via SQL Developer via a named user
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
-
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, andDBA_ROLE_PRIVSRegards,
Arjun
1
Answers
-
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
0 -
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
0