Users relationship to a schema...or Why can't I "see" things?
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
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