Oracle Enterprise Stage Configuration

[elavenil]

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

[chulett]

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.

[elavenil]

Thanks Craig.

I had explained the same thing to DBA team over here but they were insisting us to provide the valid reason to have this privilege.

Let me talk to them again to get the privileges.

Regards
Saravanan

[chulett]

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.

[elavenil]

Yes Craig. They require specific info regarding those privileges and we requested the vendor to provide those info.

Thanks & Regards
Elavenil

[ray.wurlod]

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.

[elavenil]

Thanks Ray for your response.

Based on the objects i could guess that it reads available indices, partitions, data segments etc. before loading data into Oracle DB, and i explained this to the team and team is still requesting the info from vendor.

Regards
Elavenil

[chulett]

I would think that would be pretty obvious, based on what select privileges are being requested. Especially to a DBA. Just really curious what havoc they think you can wreak with all this new found SELECT power when granted?

[elavenil]

Yes. We have convinced the DBA team to provide the mentioned privileges in my earlier email.

Thanks for your responses.

Regards
Elavenil

[sajarman]

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.