Grant access to READ-ONLY Oracle Database

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?

SevercorrAsked: 2009-06-30

Who is Participating?

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium. Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 74

sdstuberCommented: 2009-06-30

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

SevercorrAuthor Commented: 2009-06-30

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?

LVL 36

Mark GeerlingsDatabase AdministratorCommented: 2009-06-30

"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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial