Oracle Enterprise Stage Configuration
Moderators: chulett, rschirm, roy
Oracle Enterprise Stage Configuration
We are trying to configure the Oracle databases from ETL server. We are following the steps mentioned in the documentation, which is install & upgrade guide, to configure Oracle databases and it is being mentioned that the select privileges required some of the DBA objects like DBA_EXTENTS, DBA_DATA_FILES, DBA_TAB_PARTITIONS etc, which are available in SYS schema hence the Database administration team is not ready to grant the above mentioned privileges unless until it is explained why those permission required from an application.
Could you please enlighten me in the following questions.
1. Why those permissions required?
2. We would be connecting to multiple Oracle databases, then we need to have the above mentioned privileges in all Oracle databases, which we are connecting to do LOAD/INSERT, DELETE, UPDATE operations.
Thanks in advance.
Regards
Elavenil
Could you please enlighten me in the following questions.
1. Why those permissions required?
2. We would be connecting to multiple Oracle databases, then we need to have the above mentioned privileges in all Oracle databases, which we are connecting to do LOAD/INSERT, DELETE, UPDATE operations.
Thanks in advance.
Regards
Elavenil
1. Because they are required by the application. What other answer were you looking for? Without them it will not function. We're only talking SELECT privs here, nothing scary and I'm guessing most places would assign those grants to a specific role and then grant your Oracle user that role.
2. Yes.
2. Yes.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
If they need specifics - meaning what information the application uses those select grants to get and how the Enterrprise stage uses that information - you'll need to contact Support for the gory details. Unless someone here knows or it's documented somewhere.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Basically, SELECT privilege from the system table is required so that the Oracle Enterprise stage can verify that the partitioning/parallelism information is in accord with what exists in Oracle, and can check that the executing user has appropriate privileges (INSERT, UPDATE, etc.) on the target tables.
Precisely what queries are executed you probably do not need to know, and will - I predict - be informed thus by the vendor. You (well, the DBAs) could always trace what queries are being sent to the database server.
Precisely what queries are executed you probably do not need to know, and will - I predict - be informed thus by the vendor. You (well, the DBAs) could always trace what queries are being sent to the database server.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Required Select Privileges on...
Hi,
I just found that SELECT privileges are required on the following sys tables. Thought this will help those guys like me, who find this post using search option
Please find below the list of privileges that are required for Select in DataStage:
SYS.DBA_EXTENTS
SYS.DBA_DATA_FILES
SYS.DBA_TAB_PARTITIONS
SYS.DBA_TAB_SUBPARTITIONS
SYS.DBA_OBJECTS
SYS.ALL_PART_INDEXES
SYS.ALL_PART_TABLES
SYS.ALL_INDEXES
SYS.GV_$INSTANCE
These privileges are required for accessing the partition information, indexes etc. The DataStage guide suggests to create a role for these privileges as below and grant that role to the user id for DataStage jobs.
CREATE ROLE DSXE;
GRANT SELECT on sys.dba_extents to DSXE;
GRANT SELECT on sys.dba_data_files to DSXE;
GRANT SELECT on sys.dba_tab_partitions to DSXE;
GRANT SELECT on sys.dba_objects to DSXE;
GRANT SELECT on sys.all_part_indexes to DSXE;
GRANT SELECT on sys.all_part_tables to DSXE;
GRANT SELECT on sys.all_indexes to DSXE;
Once the role is created, grant it to users who will run DataStage jobs, as
follows:
GRANT DSXE to <oracle user id>;
Thanks,
Sajarman.
I just found that SELECT privileges are required on the following sys tables. Thought this will help those guys like me, who find this post using search option
Please find below the list of privileges that are required for Select in DataStage:
SYS.DBA_EXTENTS
SYS.DBA_DATA_FILES
SYS.DBA_TAB_PARTITIONS
SYS.DBA_TAB_SUBPARTITIONS
SYS.DBA_OBJECTS
SYS.ALL_PART_INDEXES
SYS.ALL_PART_TABLES
SYS.ALL_INDEXES
SYS.GV_$INSTANCE
These privileges are required for accessing the partition information, indexes etc. The DataStage guide suggests to create a role for these privileges as below and grant that role to the user id for DataStage jobs.
CREATE ROLE DSXE;
GRANT SELECT on sys.dba_extents to DSXE;
GRANT SELECT on sys.dba_data_files to DSXE;
GRANT SELECT on sys.dba_tab_partitions to DSXE;
GRANT SELECT on sys.dba_objects to DSXE;
GRANT SELECT on sys.all_part_indexes to DSXE;
GRANT SELECT on sys.all_part_tables to DSXE;
GRANT SELECT on sys.all_indexes to DSXE;
Once the role is created, grant it to users who will run DataStage jobs, as
follows:
GRANT DSXE to <oracle user id>;
Thanks,
Sajarman.