Solved

Oracle sys tables access to users with dba role

Posted on 2014-11-06
6
821 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
[X]
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
6 Comments
 
LVL 77

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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 …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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…

691 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