Table Privileges and access


I have two environments, test and production, both environments were created from an existing production database using export and import.
database version is oracle 8.1.7

access for the users is through Oracle Discoverer.
One of the Discoverer business areas contains folders looking at the DBA_ROLE_PRIVS, DBA_USERS and DBA_ROLES views.
From the test environment this works fine and the user is able to run queries against those views.
In the production environment, although the business area is available, there are no folders for selection.
The underlying privileges for the user appear to be exactly the same in both environments.
If I log in via TOAD as the user to the test environment and run the query 'select * from dba_users', then data is returned, however the same query in the production environment gives the 'Table or view does not exist' error.

Having checked all the dba_tab_privs, user_role_privs views etc, there doesn't appear to any anomalies between the two databases, therefore what could be preventing access to these views in the Production environment?

Who is Participating?
Pradeepgv03Connect With a Mentor Commented:
There will be difference in prvis granted to the user in both databases.
Please  see the output of this query from both the databases.

SQL> select * from session_privs;

If this returns the **SAME** values  please check the value set for

O7_DICTIONARY_ACCESSIBILITY in both the database.

Pradeep George,
Oracle Certified Master (OCM 9i).
firewordAuthor Commented:
Both databases have the same number of values and both have the 07_DICTIONARY_ACCESSIBILITY parameter set to TRUE.
Try as user SYS to issue

SQL>grant select on dba_users to my_user;
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Are the permissions granted through a role? If so, ensure that the role(s) are default roles. If not, the production user will not have the role priveledges set until the role is 'loaded' for the session.

You  can issue an 'alter user' statemnt to set the role as default (along wit all the other default roles).
firewordAuthor Commented:
Apologies to Pradeep, it turned out that the 07_DICTIONARY_ACCESSIBILITY parameter was actually set to FALSE, the dba led me a bum steer here!
As company standards maintain that this must be the case in the production environment, the SELECT_CATALOG_ROLE was issued to the restricted users and roles who required access to these dba views.

If this site allowed me , i would give 200 points to Pradeep for partially pointing in the correct direction and keep 300 for myself.
Thanks for the update. I was  quite sure about something was missing  from  your DBAs update..
I dont think the site will allow you to divide the points ... So full points to me (Joking :):)

I beleive that since Pradeepgv03 gave you the solution to your problem (bum steer by the DBA or not), he should be awarded the full amount that you were offering for the solution!!!!!  Just my opinion!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.