Select access to sys.dba_extents

sonia jacob · Post by **sonia jacob** » Thu Jul 06, 2006 8:56 pm

Hi all,

Why does the user ID under which DataStage connects to Oracle require select permissions to sys.dba_extents?

Thanks.

ray.wurlod · Post by **ray.wurlod** » Thu Jul 06, 2006 9:07 pm

Because it needs to determine/verify from this table how the partitioning of the table is organized (or, indeed, if there is any).

sonia jacob · Post by **sonia jacob** » Thu Jul 06, 2006 9:13 pm

ray.wurlod wrote:Because it needs to determine/verify from this table how the partitioning of the table is organized (or, indeed, if there is any). ...

thank you Ray. Needed to confirm that same before I go to DBA.

sonia jacob · Post by **sonia jacob** » Thu Jul 06, 2006 9:26 pm

sonia jacob wrote:
ray.wurlod wrote:Because it needs to determine/verify from this table how the partitioning of the table is organized (or, indeed, if there is any). ...

Are there other system tables that DS would need select access to?

Thanks

richdhan · Post by **richdhan** » Tue Jul 11, 2006 3:38 am

Hi,

We had a similar issue.

Follow this link. It should be resolved soon.

HTH
--Rich

MatsM · Post by **MatsM** » Tue Sep 05, 2006 1:46 am

[quote="richdhan"]Hi,

We had a similar issue.

Follow this [url=viewtopic.php?t=87409]link[/url]. It should be resolved soon.

HTH
--Rich[/quote]

Thanks m8, your post helped me out
BR
/Mats

sajarman · Post by **sajarman** » Wed Jan 24, 2007 9:46 am

Hi,

You can also create a role and Grant that role to the user ids using 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>;

Cheers,
Sajeed.

narasimha · Post by **narasimha** » Wed Jan 24, 2007 9:53 am

The DBA's are never happy to give these rights to anyone

DSguru2B · Post by **DSguru2B** » Wed Jan 24, 2007 10:28 am

narasimha wrote:The DBA's are never happy to give these rights to anyone

Especially if the table names have sys in it

Jokes apart, if they understand why the tool requires such access, they will give you access. They just want to be sure on their part that such an access will not in any way harm any thing. Some companies have not used such tools which require such vast access and hence there In House standards are such that they cannot give access. I had sucha client where they had just bought the tool and were refusing to give me anything over select and execute permission on regular tables. That was some long gory hours that i spent with the dba group and in house security folks.

narasimha · Post by **narasimha** » Wed Jan 24, 2007 10:52 am

DSguru2B wrote:
narasimha wrote:The DBA's are never happy to give these rights to anyone
Especially if the table names have sys in it
Jokes apart, if they understand why the tool requires such access, they will give you access. They just want to be sure on their part that such an access will not in any way harm any thing.

Very true, They made me take the director's approval for read only access to a few sys tables while I was working with Oracle - OMW.

DSXchange

Select access to sys.dba_extents

Select access to sys.dba_extents

Creating role for the privileges.