Oracle DBA permission for Data warehouse
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?
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
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.
3 other answers
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
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
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.
