Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Oracle sys tables access to users with dba role

Posted on 2014-11-06
6
747 Views
Last Modified: 2014-11-06
I am trying to create account for some users with the dba role. After creating the accounts the dba role users can't able to access sys objects. Is there some specific role for the sys objects so that the dba role users can have all access.? I tried to grant sysdba but it error out as we are not maintaining password file.
ANy suggestions like init.ora parameters etc?
0
Comment
Question by:Oranew
6 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40426198
Try granting SELECT ANY DICTIONARY to the user.

What objects are you wanting to access?
0
 
LVL 23

Expert Comment

by:David
ID: 40426252
I assume, but please verify, that your database setup included the steps to create the data dictionary and PL/SQL structures:

catalog.sql
catproc.sql

I also assume, but also please verify, that you granted the DBA role to the account you're using as DEFAULT.  It's possible to create and to assign a ROLE, that will require manual enabling.
0
 

Author Comment

by:Oranew
ID: 40426330
Great ! Granting "select any dictionary" worked and it solves the problem.
I have another question now. How can the dba role user be able to access without putting the sys.(dot)
example not sys.user$  but user$ only. ( We don't create sys object synonyms manually).
Is this something can be done ?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 40426345
If the synonym doesn't already exist, you would need to create them.

You 'can' try:
ALTER SESSION SET CURRENT_SCHEMA=SYS;

But I would advise against it.  It would be easy to make some potential mistakes.

Why are you going after the base objects like USER$ in the first place?

Oracle has set up views for you that make the old cryptic ones unnecessary.

Like DBA_USERS for example.
0
 

Author Closing Comment

by:Oranew
ID: 40426367
Yes, I agree with you.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40426393
Also note  SELECT ANY DICTIONARY is NOT sufficient to read every dictionary table in 12c

In particular,  SELECT ANY DICTIONARY does  NOT grant access to sys.user$
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.

790 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