Re: Users relationship to a schema...or Why can't I "see" things?

When you grant privileges on objects to another "user" in Oracle the user must qualify the object name with the user name in order to perform a query unless you create public synonyms for those objects. The granting of privileges, whether through a role or direct grants, will allow the user to perform that operation on the object but they will still need to qualify with the object owner's userid. The only way around this is to create a public synonym. That way the user does not need to qualify the object with the owner name.

Hope that helps

"David Stidham" <DStidham_at_msn.com> wrote in message news:O36V8Eq6AHA.259_at_cpmsnbbsa09...
> Hello All,
>
> Up front, allow me to point out that I realize this question might be
 better
> suited being directed to Quest Software, publishers of TOAD...but, I'll
 pose
> it here first.
>
> Our application connections to an Oracle database (good, otherwise, this
 is
> in the wrong newsgroup...grin). The Schema owner is used to create all
 the
> tables, procedures, functions, etc. In the cause of security, we would
 like
> to create a user that has access to those opjects in the schema, but not
 to
> the degree that the schema owner does. I have created a user while
> connected to the database as the shema owner. I've created two distict
> roles and BASIC and a FULL access role for this new user. Once connected,
 I
> can set the FULL role. I can alter session to set the Schema equal to the
> one I need to work with, so that I do not need to use fully qualified
 names
> (and not rewrite all of the existing PL/SQL and plain SQL). However, when
> using the TOAD application I can not "see" any of the objects in the
 schema
> I want to work with. I can successfully perform selects on those tables,
> and obtain desc of those tables. But, in the schema browser mode of TOAD,
 I
> do not see anything under my application user.
>
> The technical features of TOAD aside, am I missing a fundamental aspect of
> Oracle? Can only the schema owner "see" those objects, ever? Any users
> created can only "see" those objects they create, and those objects are
 part
> of a schema named like that user. Is this correct?
>
> I wish I could word what I'm trying to ask/clarify better. I'll try the
> following example:
>
> DATA_OWNER is used to create the database. This user can "see" everything
> in the schema.
> APPLICATION_USER is created for our external application to connect to the
> database. This user is granted roles ROLE_BASIC and ROLE_FULL with the
> BASIC being default and the FULL being password protected. Once
 connected,
> and the FULL role set, this user can not "see" the objects in the
 DATA_OWNER
> schema. Is this the way it will always be? I can issue the alter session
> set current_schema=DATA_OWNER and then not need to issue fully qualified
> object names, but can not "see" those objects in a list. Or can it?
>
> If you've not become confused by question, or my attempt to clarify my
> question, or my example, and can offer some insight or help, I would
 greatly
> appreciate it. Our goal is to provide our client base with the ability to
> use the application user in it's basic role for any adhoc reporting that
> they would like to perform on their data outside of the application.
>
> Thank you again for any help any of you can provide.
>
> David Stidham
>
>
Received on Sat Jul 21 2001 - 16:09:32 CDT