Re: How to determine default tablespace and accessible tablespaces

"André Hartmann" <andrehartmann_at_hotmail.com> wrote in message news:40c09330$1_at_olaf.komtel.net...
>
> "Anurag Varma" <avdbi_at_hotmail.com> schrieb im Newsbeitrag
> news:2j0wc.167$9g6.29_at_nwrdny03.gnilink.net...
> >
> > "André Hartmann" <andrehartmann_at_hotmail.com> wrote in message
> news:40c08e0e$1_at_olaf.komtel.net...
> > > Hi there,
> > >
> > > I have an Oracle user account on a 9i instance which is granted
roles
> > > CONNECT and RESOURCE plus UNLIMITED TABLESPACE system right. Now I
> wonder if
> > > that user can determine:
> > >
> > > (1) What is his default tablespace and default temporary tablespace
> (user
> > > cannot select from sys.dba_users --> ORA-00942) ?
> > > (2) What is the set of tablespaces that there are in the instance
(user
> > > cannot select from sys.dba_tablespaces --> ORA-00942) ?
> > > (3) What is the set of tablespaces that the user is allowed to create
> schema
> > > objects in ?
> > >
> > > Thanks a lot,
> > >
> > > André
> > > :)
> > >
> > >
> >
> > Are you aware there are user_* and all_* views too?
> > Thus there exists a user_users view. and a user_tablespaces view.
> >
> > Anurag
> >
> >
>
> Oh no,
>
> I was not aware of that... cool ! never heard of or seen those before..
of
> course thats the way to do it, thanks !!!

The other way to do it is to realise that no-one, ever, should grant connect and resource to anyone. Those are roles, not privileges. Therefore they contain all sorts of privileges you might not realise you've granted to your users. Including, incidentally, unlimited tablespace throughout the database... so your direct grant of that privilege was a little redundant. The official advice from Oracle, just in case you think I'm making this up, is that those roles are there for backwards compatibility only.

You should create your own roles and grant your own selection of privileges to them.

By the way, have you had a nose around http://tahiti.oracle.com?

Regards
HJR Received on Fri Jun 04 2004 - 14:42:39 CDT