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

From: David Stidham <DStidham_at_msn.com>
Date: Sat, 21 Jul 2001 21:09:23 GMT
Message-ID: <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 - 23:09:23 CEST

Original text of this message