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

From: Howard J. Rogers <howardjr_at_www.com>
Date: Sat, 21 Jul 2001 21:10:51 GMT
Message-ID: <3b1b7ec4_at_news.iprimus.com.au>


I don't know TOAD that well, but it sounds to me that not very much is amiss.

You say the user you create can select from the objects, but just can't see the opjects in TOAD. Sounds like a limitation in TOAD.

Any User to whom a privilege to another User's object is granted will see that object in the ALL_TABLES (or equivalent) view. They won't see it in the USER_TABLES view, though. USER_TABLES shows you what tables you actually own. ALL_TABLES shows you what objects you own AND what objects you have been granted privileges to. If your application user has been granted privileges to the schema tables, I would expect you to be able to log in as that User and do 'select * from all_tables' and see all those tables. If TOAD then refuses to show you those tables, it's a TOAD problem.

On the other hand, you claim that with the BASIC role set, all is visible, but with the FULL role set, *not* all is visible. Given that setting one role disables all other roles, the immediate question is exactly what set of privileges is granted in BASIC, and what is in FULL. Because there is clearly something in BASIC, which is disabled by enabling FULL, which FULL doesn't have. I can't think what it might be, so your best bet is to post the entire set of privileges in each role here.

Regards
HJR

--
=============================!!=============================
The views expressed are my own only, and definitely NOT those of Oracle
Corporation
=============================!!=============================


"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 - 23:10:51 CEST

Original text of this message