It's all about the answers!

Ask a question

Oracle DBA permission for Data warehouse


Yohko Tanaka (10522535) | 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.

http://pic.dhe.ibm.com/infocenter/clmhelp/v4r0m4/topic/com.ibm.jazz.install.doc/topics/t_s_server_installation_post_config.html

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 (650512) | 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.6k1123) | answered Sep 25 '13, 8:57 a.m.
JAZZ DEVELOPER
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.
https://jazz.net/wiki/bin/view/Main/GenerateDDLScriptsForTheDataWarehouseCreationAndUpgrade.

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.
Sandy


Comments
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 (451714) | 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. 

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

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


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

Your answer


Register or to post your answer.