Solved

Oracle sys tables access to users with dba role

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle 12c patching 1 81
automatic email alert 1 50
Can't Access My Database 57 64
Import and exporting Oracle Data with encrypted columns 4 29
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to recover a database from a user managed backup

776 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