Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Grant access to READ-ONLY Oracle Database
Posted on 2009-06-30
I am the owner of the Oracle schema. I tried to grant read only access of all my objects to other users. However, when other users login they can not see all the objects (e.g the definitions of tables or views) on their session. They are using either Toad or Oracle SQL Developer to view the database objects. How do I give them access to view the definitions of my OBJECTS?
[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
3 Comments
Active today
Some of your privileges will make the all_xxxx views work for the other users.
but some objects will require access to dba_xxxx views
by default toad doesn't use dba_xxxx views, you must configure it to check for access to those views
but some objects will require access to dba_xxxx views
by default toad doesn't use dba_xxxx views, you must configure it to check for access to those views
I am not concerning about the Dictionanray tables or views. My question is how come other users can not see the tables or views that I created in their sessions. What priveleges do I need to gave for them to see?
"how come other users can not see the tables or views that I created in their sessions".
Oracle does not support creating objects "in their sessions". If you have the "create any table" system privilege (or the DBA role which includes this system privilege) you can create objects in any schema (but not in another session).
For other users to see objects in your schema, two things are required:
1. You must "grant select on [object_name] to [other users or roles they have]"
2. They must either use fully-qualified object names: [schema].[object_name] or there must be a synonym for your objects. This could either be a public synonym (that works for all other users) or it must be a priviate synonym in each uers's schema.
Synonyms (whether public or private) do *NOT* take the place of grants. Synonyms just allow for objects to be referenced with shorter names, but if there is no grant on the actual object the synonym refers to, the other user will still get the "object does not exist" error.
Oracle does not support creating objects "in their sessions". If you have the "create any table" system privilege (or the DBA role which includes this system privilege) you can create objects in any schema (but not in another session).
For other users to see objects in your schema, two things are required:
1. You must "grant select on [object_name] to [other users or roles they have]"
2. They must either use fully-qualified object names: [schema].[object_name] or there must be a synonym for your objects. This could either be a public synonym (that works for all other users) or it must be a priviate synonym in each uers's schema.
Synonyms (whether public or private) do *NOT* take the place of grants. Synonyms just allow for objects to be referenced with shorter names, but if there is no grant on the actual object the synonym refers to, the other user will still get the "object does not exist" error.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
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…
Read about achieving the basic levels of HRIS security in the workplace.
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.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month5 days, 5 hours left to enroll
Earn Certification
Certified Information Systems Auditor (CISA)
$99.00$89.10
635 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.