Table Privileges and access

Posted on 2006-05-17
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
    LVL 6

    Accepted Solution

    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

    Both databases have the same number of values and both have the 07_DICTIONARY_ACCESSIBILITY parameter set to TRUE.
    LVL 47

    Expert Comment

    Try as user SYS to issue

    SQL>grant select on dba_users to my_user;
    LVL 1

    Expert Comment

    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

    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.
    LVL 6

    Expert Comment

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

    LVL 1

    Expert Comment

    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

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    730 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now