Data Dictionary rights

[alison]

Hi there,

I have a reporting database which pulls information from 22 other databases across the world, I have a series of database links in place to allow me to do this, up till now they have been linking to my own schema in each database (which has full DBA rights), however I have now changed this so that they link to a generic user called REPORT.

I don't want REPORT to have DBA rights, but it does need full select rights to all the data dictionary tables, also it needs to be able to create temporary view's (which will be deleted at the end of the process).

I understand that there is a DBMS package that I can use to allow this, but I'm not sure which one.

Any help would be great.

Kind regards

Allie

[Raminder]

if you are using 9i, there is a system privilege: Select any dictionary which can be granted to the user. It will give access to any object in the sys schema without giving him DBA privileges
In earlier releases, select any table privilege can be given.

[Shestakov]

Another decigion:

HS_ADMIN_ROLE

Script named : CATHS.SQL in $ORACLE_HOME/rdbms/admin

Used to protect access to the HS (Heterogeneous Services) data dictionary tables (grants SELECT) and packages (grants EXECUTE). It is granted to SELECT_CATALOG_ROLE and EXECUTE_CATALOG_ROLE such that users with generic data dictionary access also can access the HS data dictionary.