Re: Schema/Users relationship

"Tudor" <balan_tudor_at_yahoo.com> wrote in message news:b92858f4.0210130247.1454cb88_at_posting.google.com...
> Hi all,
>
> I am new to oracle and i have just read the documentation from Oracle
> 8i concepts about the fundamental concepts. However, i have some
> questions and i really hope that some one could give me some more
> clear definitions and answers to my questions:
>
> 1) I've read in the documentation that for each user of a database
> there is an associated schema(with the same name). But running the
> following sqls :
> --select username from dba_users order by username;
> --select distinct owner from dba_objects;
> i discovered that it is possible to have some users without a schema.
> Som the oracle documentation is false?? Furthermore, i read on this
> discusion group an answer to a question claiming that "there
> definitely will be users without a schema". SO, PLEASE SOMEBODY CLEAR
> ONCE FOR EVER THIS PROBLEM FOR ME.
No need to shout. And no need to get confused. A schema is a collection of objects owned by a user. Therefore you can't have a schema without an owner (ie, user), but it is perfectly possible for a user not to own any objects, so you can have a user without a schema.

>
> 2) Is is possible to allow a user A to access the tables of a user B's
> schema?
>

It is possible for user B to grant any access to any table to any other user. And if she does so with the 'with grant option', the person to whom she grants such a privilege can then make further grants of that privilege to another user.

Hence:

connect userb/password;
grant select on mytable to usera;
grant update on mytable to usera with grant option; connect usera/password;
select * from userb.mytable;
(15 rows returned)
update userb.mytable set COL1="X";
(15 rows updated)
grant select on userb.mytable to userc;
(Insufficient privileges)
grant update on userb.mytable to userc;
(grant succeeds).

Regards
HJR
> Thank you in advance
>
> Tudor
Received on Sun Oct 13 2002 - 06:04:24 CDT