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

[David Stidham]

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

[Kevin Dean]

Are you granting rights on these tables to the other users or roles?
There's no reason TOAD shouldn't see the tables if you have "GRANT SELECT ON
TABLE_NAME TO ROLE_FULL".

--
Remove NOSPAM woven into e-mail address to reply directly.

[Peter Cohen]

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" <DSti...@msn.com> wrote in message
news:O36V8Eq6AHA.259@cpmsnbbsa09...

[Howard J. Rogers]

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" <DSti...@msn.com> wrote in message
news:O36V8Eq6AHA.259@cpmsnbbsa09...

[David Stidham]

Thank you all for your assistance. You've confirmed what I have been
"seeing" in my attempts to "see" those tables and other objects owned by the
DATA_OWNER user.

There is one other way to avoid using fully qualified names that no one has
mentioned;
ALTER SESSION SET SCHEMA=new_schema_name;

This allows the alternate user to then reference the tables and objects of
the new_schema_name without fully qualifying them or having to create public
synonyms for them. Thought I would share that tidbit in the event that
there were any readers that were unaware of this information.

David

[Spencer]

"SET SCHEMA" is not available prior to 8i.

you do know that in the TOAD Schema browswer window,
you can select a different "owner" from the drop down list
box near the top of the window.

if that still doesn't work, you could try enabling the use of
the DBA_ catalog views:

connected as system:

GRANT SELECT_CATALOG_ROLE TO newuser ;

in TOAD options window, select option

"check for access to dba_ views on startup"

restart TOAD.

HTH

"David Stidham" <DSti...@msn.com> wrote in message

news:#$CdFqP7AHA.274@cpmsnbbsa07...

[Chirag]

Hi David,

Let me understand your question first. In Oracle data security is
given by two way i.e one with giving priviledges on object to
user(schema) or to the ROLE. Role is collection of priviledges on
objects for better maintance of object priviledges.
Now what you need is dynamic enabling or disabling of USER ROLE in a
concurrent transaction depending upon condition. For that Oracle
provides SYSTEM CONTROL statement SET ROLE.
In a transaction if u have two ROLE like DATA_OWNER and
APPLICATION_OWNER. While crearting APPLICATION_OWNER give password or
use following statement to alter role property.

ALTER ROLE APPLICATION_USER IDENTIFIED BY david;
(Here david is password)

Then issue following statement in your program. It is assumed that
user(schema) has granted DATA_OWNER role initially. To change role
DATA_OWNER for given user, pl. use following statement ..

SET ROLE APPLICATION_OWNER IDENTIFIED BY david;

and then u can access all priviledges that u specify in
APPLICATION_OWNER role.

With regards,

Chirag Shah
Oracle Administrator

"David Stidham" <DSti...@msn.com> wrote in message news:<O36V8Eq6AHA.259@cpmsnbbsa09>...