| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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_at_yahoo.com> 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_at_yahoo.com> 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_at_kmg.com> 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_at_freelists.org
> > 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_at_freelists.org
> 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
> -----------------------------------------------------------------
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Tue Jul 13 2004 - 20:09:15 CDT
 |
 |