Solved

Oracle sys tables access to users with dba role

Posted on 2014-11-06
6
695 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 73

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.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle Finace 3 48
CREATE TABLE syntax 4 45
Oracle PL/SQL syntax 4 52
Last record chosen in Oracle Query 3 35
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup

747 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

9 Experts available now in Live!

Get 1:1 Help Now