Re: ORA-01031: insufficient privileges

"Grabowy, Chris" wrote:
>
> Actually, it is default, there is a Yes under that (2nd) column.
>
> I convinced the site DBA to revoke and regrant the RESOURCE role with
> ADMIN option. It is now working. So now we are REALLY scratching our
> heads...now were dreading some sort of corruption in the
> database...sigh. It's Friday, right?
>
> Sorry for bothering everyone. Many thanks to all who responded.
>
> -----Original Message-----
> Sent: Thursday, October 17, 2002 1:59 PM
> To: Multiple recipients of list ORACLE-L
>
> Notice that RESOURCE is not a "default" role. You have to set it
> explicitly via SET ROLE in your session. You can use ALTER USER xxx
> DEFAULT ROLE ... to make it default, if you wish...
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Thursday, October 17, 2002 11:28 AM
>
> 11:54:32 SQL> GRANT RESOURCE TO JNJDSS_STG_T;
> GRANT RESOURCE TO JNJDSS_STG_T
> *
> ERROR at line 1:
> ORA-01031: insufficient privileges
>
> 11:54:39 SQL> sho user
> USER is "OPS$JNJDBA"
> 11:54:45 SQL> select * from user_role_privs where granted_role =
> 'RESOURCE';
>
> USERNAME GRANTED_ROLE ADM DEF
> OS_
> ------------------------------ ------------------------------ --- ---
> ---
> OPS$JNJDBA RESOURCE YES YES NO
>
> 11:54:52 SQL> select * from v$version;
>
> BANNER
> ----------------------------------------------------------------
> Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production PL/SQL
> Release 8.1.7.3.0 - Production
> CORE 8.1.7.0.0 Production
> TNS for Solaris: Version 8.1.7.3.0 - Production
> NLSRTL Version 3.4.1.0.0 - Production
>
> 11:54:57 SQL> select username from all_users where username =
> 'JNJDSS_STG_T';
>
> USERNAME
> ------------------------------
> JNJDSS_STG_T
>
> What the heck??? (shaking my head) What am I missing here?? Help?
>
> --

Chris,

   The answer may be in what occurs below the water. Remember that RESOURCE is a bit special (here for Oracle 5 compatibility reasons), that it is one of the two (I think) 'roles' which cannot be granted to a role and that the reason I suspect it cannot be is that it grants 'unlimited tablespace' underhand. An hypothesis might be that RESOURCE was granted and then UNLIMITED TABLESPACE revoked by a DBA unwilling to see the user pollute any tablespace.

It behaves like what you witnessed :

SQL> create user demo identified by demo;

User created.

SQL> grant create session to demo;

Grant succeeded.

SQL> grant resource to demo with admin option;

Grant succeeded.

SQL> create user demo2 identified by demo2;

User created.

SQL> revoke unlimited tablespace from demo;

Revoke succeeded.

SQL> connect demo/demo
Connected.
SQL> grant resource to demo2;
grant resource to demo2
*
ERROR at line 1:
ORA-01031: insufficient privileges

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).