Re: [newbie] 2 questions about granting a role to a user

Howard,

As far as I know, the only system privilege that can not be granted to a role is 'unlimited tablespace'. Please confirm.

• Jack

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:3f7a7f37$0$22463$afc38c87_at_news.optusnet.com.au...
> Franz wrote:
>
> > hi all dba!
> > I'd like to grant a role (tat I have called basic) to a user...here are
my
> > steps...
> >
> > SQL>connect / as sysdba;
> > SQL>create role basic;
> > SQL> grant create session,create table,insert any table,select any
> > table,update any table,delete any table to basic;
> >
> > SQL>alter user franz default role basic
> >
> > alter user franz default role basic
> > *
> > ERRORE alla riga 1:
> > ORA-01955: DEFAULT ROLE 'BASIC' not granted to user
> >
> > Why I'm not allowed to rant "basic" role to the user ?
>
>
> Er, why don't you look a little harder at what SQL you are actually typing
> versus what SQL you wish you were typing? You've just asked for Franz to
> have the BASIC rolw as his default role. A default role is one that is
> automatically switched on when a user logs in. You can't have a default
> role unless it has been granted to the user. You haven't actually granted
> the role to Franz.
>
> See the missing step?
>
> Before running ahead and trying to make a role a default one, try granting
> it to the user first:
>
> grant role basic to Franz;
> alter user Franz default role basic;
>
> Regards
> HJR
>
>
>
> >
> > Also: how can I obtain that a role as unlimited quota on a tablespace?
(I
> > don't want every time
> > that I create a user to specify his/her quota on the tablespace)
> >
> > grant unlimited tablespace to basic
> > *
> > ERRORE alla riga 1:
> > ORA-01931: Impossible to grant UNLIMITED TABLESPACE to a role
> > Thanks a lot
> > Franz
>
Received on Wed Oct 01 2003 - 11:32:36 CDT