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

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Thu, 02 Oct 2003 05:56:54 +1000
Message-Id: <3f7b3243$0$28122$afc38c87@news.optusnet.com.au>

Jack Wang wrote:

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

That may or may not be true (though since the 'resource' built-in role has unlimited tablespace, I wouldn't be too sure). But it's not what caused our OP's *first* problem.

I thought I'd just answer that bit and see how he gets on before getting too worried about the second part of his question!

When I have database to hand, I'll get back to you.

Regards
HJR  

> "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 - 14:56:54 CDT