Learn how to a build a cloud-first strategyRegister Now


Table Privileges and access

Posted on 2006-05-17
Medium Priority
Last Modified: 2013-12-11

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?

Question by:fireword
  • 2
  • 2
  • 2
  • +1

Accepted Solution

Pradeepgv03 earned 2000 total points
ID: 16699621
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).

Author Comment

ID: 16706472
Both databases have the same number of values and both have the 07_DICTIONARY_ACCESSIBILITY parameter set to TRUE.
LVL 48

Expert Comment

ID: 16733393
Try as user SYS to issue

SQL>grant select on dba_users to my_user;
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Expert Comment

ID: 16751898
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).

Author Comment

ID: 16767247
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.

Expert Comment

ID: 16767969
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 :):)


Expert Comment

ID: 16985963
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!

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question