RE: imp issue...very confusing!

Unlimited tablespace is granted directly to the user to whom you've
granted RESOURCE. This appears to be hard-coded into the kernel.

example below, 9.2.0.1 but I've done this at least as far back as
Oracle7, probably version 6 as well but I can't remember and don;t have
a database under 6 to test on.

SQL> create user resource_test identified by test
  2  default tablespace users
  3  temporary tablespace temp
  4  /

User created.

SQL> select * from dba_sys_privs where grantee='RESOURCE_TEST';

no rows selected

SQL> select * from dba_role_privs where grantee='RESOURCE_TEST';

no rows selected

SQL> grant resource to resource_test;

Grant succeeded.

SQL> select * from dba_role_privs where grantee='RESOURCE_TEST';

GRANTEE                        GRANTED_ROLE                   ADM DEF  
        
------------------------------ ------------------------------ --- ---  
        
RESOURCE_TEST                  RESOURCE                       NO  YES  
        

SQL> select * from dba_sys_privs where grantee='RESOURCE_TEST';

GRANTEE                        PRIVILEGE                         ADM   
 
------------------------------ --------------------------------- ---   
 
RESOURCE_TEST                  UNLIMITED TABLESPACE              NO    
 


SQL> select * from dba_sys_privs where grantee='RESOURCE';

GRANTEE                        PRIVILEGE                         ADM
------------------------------ --------------------------------- ---
RESOURCE                       CREATE TYPE                       NO
RESOURCE                       CREATE TABLE                      NO
RESOURCE                       CREATE CLUSTER                    NO
RESOURCE                       CREATE TRIGGER                    NO
RESOURCE                       CREATE OPERATOR                   NO
RESOURCE                       CREATE SEQUENCE                   NO
RESOURCE                       CREATE INDEXTYPE                  NO
RESOURCE                       CREATE PROCEDURE                  NO

8 rows selected.
--- Paul Baumgartel <treegarden@xxxxxxxxx> wrote:
> But wouldn't revoking unlimited tablespace have no effect here, since
> it's granted to the role, not to the user?
> 
> --- Rachel Carmichael <wisernet100@xxxxxxxxx> wrote:
> > Ron,
> > 
> > Unlimited tablespace as a privilege actually means "you can write
> to
> > any darned tablespace in the database that you choose to"
> > 
> > you should instead grant quota unlimited to a user on his/her
> > tablespace.
> > 
> > This is one of the "hidden" privileges you get when you grant the
> > role
> > RESOURCE to a user. If you need/want to continue using that role,
> you
> > should immediately after granting it issue "revoke unlimited
> > tablespace
> > from <username>"
> > 
> > Rachel
> > --- "Smith, Ron L." <rlsmith@xxxxxxx> wrote:
> > > I found that when I give a user (touser) unlimited tablespace on
> > > his/her
> > > own tablespace, for some reason the user also gets granted
> > unlimited
> > > tablespace on all tablespaces.  When that happens, the fromuser
> > > touser
> > > option results in all the objects getting put back into the
> > fromuser
> > > tablespace.  When I revoke unlimited tablespace, the import works
> > > fine.
> > > 
> > > Ron Smith
> > > 
> > 
> > 
> > ----------------------------------------------------------------
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > ----------------------------------------------------------------
> > To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at http://www.freelists.org/archives/oracle-l/
> > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> > -----------------------------------------------------------------
> > 
> 
> 
> 
>               
> __________________________________
> Do you Yahoo!?
> New and Improved Yahoo! Mail - Send 10MB messages!
> http://promotions.yahoo.com/new_mail 
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
> 

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: