Re: Question on user schema

Hi GM, Tom and all,

Although this is a little "left field" and not what I would necessarily recommend in this example, there is the option to 'ALTER SESSION SET CURRENT_SCHEMA=John;' This will result in my current user (say Bowie) still being connected as Bowie but accessing John's schema objects by default (i.e.. select * from ziggy would be John's ziggy table, not Bowie's).

This can be extended by creating all the necessary objects in John's schema then revoking all privileges from John (including create session). That way, you can only access John's data indirectly and not via the user John. John is effectively a schema and not a user as such.

Then again, there are various limitations to all this (such as restricting which schemas you can make connections to) but it might avoid having to create synonyms and it's just another little something to chew on.

Cheers

Richard

"Tom Best" <oracle_person_at_yahoo.com> wrote in message news:ahpceq$jns$1_at_news.bentley.com...
> GM:
>
> From a previous similar post...
>
> From: "Tom Best" <oracle_person_at_yahoo.com>
> Subject: Re: create user and grant
> Date: Thursday, July 25, 2002 10:59 AM
>
> Gabor:
>
> The quick answer is, by default, each user sees only the data in his
> "schema" i.e. the tables under his username. john can have a table called
> "tab1" and so can bill, and they are totally separate tables. If you want
> to share data from a schema to another user, you GRANT access to it for
that
> user. You may also create SYNONYMs to allow the other user to refer to
the
> table without prefixing it with the owner's userid.
>
> So, yes, bill and john are different users, with different data. They
can't
> see each others data (by default). And if you create "ryan", again, he
will
> have access only to HIS objects. The SQL statement "GRANT" will allow
other
> users to see his data.
>
> Take a look at Part VIII of this:
>
>

http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/
> a76965/toc.htm
>
> HTH,
> Tom Best
>
>
>
> "gulyas gabor" <gabor.gulyas_at_sysdata.siemens.hu> wrote in message
> news:ahp0vu$497$1_at_news.siemens.at...
> > Hi all,
> >
> > I am new to oracle, so I would like to ask a little help.
> > I use oracle 8.1.7 on unix platform with SQLplus.
> > There is a database called kurbie and if I use the 'sqlplus
> > john/john_at_kurbie' command I can administrate some tables, views, etc.
> > But if I use 'sqlplus bill/bill_at_kurbie' as command then different
tables,
> > views, etc. are available.
> >
> > I think that bill and john are different users, they use the same
> database,
> > but they have different rights, so both of them can reach their own
> things,
> > and the others are unavailable for them. Is it right ?
> >
> > So it is the present situation, and here is my question:
> > I would like to use this kurbie database as mentioned above, but I need
a
> > third case to the existing two.
> > with 'sqlplus ryan/ryan_at_kurbie' I want to see nothing from the data of
> john
> > and bill, but I want to create ryan's data.
> >
> > If you know how to do this, please answer me. (Ithink I should create a
> new
> > user and then grant rights, but I don't know how to do this exactly.)
> >
> > thanks a lot,
> > Gabor
> >
> >
>
>
> "G.M." <gi_at_o@tiscalinet.it> wrote in message
> news:HWW%8.1951$n04.34084_at_twister2.libero.it...
> > Hello,
> > I created some tables , now I can acces to them by the users called for
> > example xxx on server manager by:
> > connect xxx/xxx_password. Can I have a different user, for example yyy
to
> > access to same schema after a connect like: connect yyy/yyy_password?
> > Thanks
> >
> >
>
>
Received on Thu Jul 25 2002 - 20:00:45 CDT