Difference between ORACLE Enterprise and DRM (Dynamic) Stage

horserider · Post by **horserider** » Fri Apr 18, 2008 1:24 pm

Hello All,

Our DBA has refused to grant access to any SYS tables that needs SELECT acess in order to use the Oracle Enterprise Stage. We can use either Oracle Enterprise Stage or Dynamic Relational Stage to access ORACLE Data.

My question is

(1)
Why does ORACLE Enterprise Stage needs access to the SYS Tables? Is there any documentation that explains how/why does it use the SELECT grants to the SYS TABLES?

(2)
Is there huge difference between Oracle Enterprise and Dynamic Relational Stage if we are ONLY use ORACLE as SOURCE? Our target is NOT ORACLE?

kumar_s · Post by **kumar_s** » Fri Apr 18, 2008 1:46 pm

Refer to Install and Upgrade Guide for "Accessing the Oracle Parallel Server".

ray.wurlod · Post by **ray.wurlod** » Fri Apr 18, 2008 4:19 pm

Oracle Enterprise uses system tables to determine whether and, if so, how the table is partitioned.

kumar_s · Post by **kumar_s** » Fri Apr 18, 2008 4:25 pm

And to use the right Index.

chulett · Post by **chulett** » Fri Apr 18, 2008 6:11 pm

Get a new DBA.

horserider · Post by **horserider** » Mon Apr 21, 2008 7:06 am

Even though we know why SELECT access to above tables are needed, it cannot be implemented in my environment. DBA simply refused to give access to these table and I can see a valid point there! DBA says if we are given SELECT privileges, we will be able to know all information about any table on the ORACLE server and that can compromise on security!

SO, Instead of doing the below step

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_tab_subpartitions 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 userid>;

Is there a way we can still make these tables available through VIEWS ONLY and make the Oracle Enterprise Stage Work? OR any other way to make the Enterprise Stage work as well as addressing the security concern?

iDomz · Post by **iDomz** » Mon Apr 21, 2008 7:25 am

Shouldn't security be focusing more on your data than metadata?

If you have access to the data, the system or your business is already compromised.

PS : No, I dont have a solution

shamshad · Post by **shamshad** » Mon Apr 21, 2008 7:36 am

Do you have option to use SERVER JOB?

If you don't want to use Relational Database Stage to connect to ORACLE and want to use any ORACLE STAGE, try Oracle OCI Stage in Server Job.

If you wish you can find some documentation regarding ORACLE ENTERPRISE STAGE in Parallel Job and ORACLE OCI Stage in SERVER Job.