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

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" <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:11:14 CEST