It's all about the answers!

Ask a question

Oracle DBA permission for Data warehouse

Yohko Tanaka (10523239) | asked Sep 24 '13, 8:59 p.m.
Is is possible to reduce permission for the user which is used for creation datawarehouse?(Let's say "dw_user")
In this page, it describes the way of creating "new user" for etl.

Do we need to create new user and dw_user still has dba role?
Or could we reduce the permission for dw_user and can use dw_user for etl purpose?

Accepted answer

permanent link
Antoinette Iacobo (650712) | answered Sep 25 '13, 4:09 p.m.
 Hi Yohko,
Those commands will work with the same user; you don't have to create a new one.  
What I've done and others is:  grant the DBA role to the Oracle DW user; run createWarehouse command to create the data warehouse; revoke the DBA role; run the those Oracle commands in your IC link to grant the specific permissions.  
Yohko Tanaka selected this answer as the correct answer

3 other answers

permanent link
Sandy Grewal (1.6k1223) | answered Sep 25 '13, 8:57 a.m.
edited Sep 25 '13, 9:09 a.m.
Hi Yohko,
You need DBA permissions to create the DW.
Alternatively you can get the DW creation scripts starting 403(link below) and the DBA can create the DW by running the scripts.

The user connecting to the DW to insert data from ETL's can have reduced privileges, in the link you posted it is the "etl db user"

Hope that helps.

Yohko Tanaka commented Sep 30 '13, 6:40 a.m.

Thank you. I didn't know those scripts. It's useful information.

permanent link
Dave Marsico (4511014) | answered Sep 25 '13, 3:19 p.m.
The scripts available with 4.0.3 are nice, its great to see what is going on and be able to share that with our Oracle Admins.   They (Oracle Admins) are curious about this one question...  Do you actually check if user has DBA role or do you  check if has specific permissions that are required?   They are wondering if they could make a role that would allow "just what we need".   So I'd be interested if you could share comments on this subject. 

Sandy Grewal commented Sep 25 '13, 3:38 p.m.

Hi Dave,
As long as the User has the permissions needed to execute the sql, it is fine.

permanent link
Dave Marsico (4511014) | answered Sep 25 '13, 4:01 p.m.
ok excellent!,  thanks for the quick reply.

Your answer

Register or to post your answer.

Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.