Restricting A User To A Specific Tablespace

[OraCool]

Hi,

I have tablespaces a,b,c,d,e. I have created a user 'x' and specified quota unlimited on d,e and 0 on a,b,c. On creating user 'x' I find that he can create and insert data in a table on tablespace 'a'. So quota 0 does not restrict him.

Is there another way to restrict user 'x' to tablespace d & e.

PS: All TS are locally managed with auot extent management.

Thanks In Advance

 

[sem]

Once you have a right to insert into that table your tablespace quota doesn't matter. Though you're not allowed to create anew table there. Why don't you REVOKE insert right on that table(s)?

Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

OraCool,

Dima/Sem is (as always) right on. If, in fact, you are able to "CREATE TABLE...TABLESPACE B" with no explicit QUOTA, then could you please confirm whether or not the table-creating user may explicitly (or via a ROLE) have the "UNLIMITED TABLESPACE" privilege?

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 18:00 (29Oct04) UTC (aka "GMT" and "Zulu"),
@ 11:00 (29Oct04) Mountain Time

 

[OraCool]

Thanks Guys,

Here is the scenario.

USer 'x' should have his objects restricted to tabsp 'd' & 'e' only.

User 'x' is a new user for a new app. Hence the zero quota on other tbsps.

Is it a default for a newly created user to have rights on all existing tbsps?

Thanks

 

[SantaMufasa]

OraCool,

No, users do not, by default, have the UNLIMITED TABLESPACE privilege. But, occasionally I have found DBAs "GRANT UNLIMITED TABLESPACE to PUBLIC" (or similar), which results in the users access to all tablespaces. That's why I was hoping that you could rule out such a GRANT.

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 15:17 (01Nov04) UTC (aka "GMT" and "Zulu"),
@ 08:17 (01Nov04) Mountain Time

 

[OraCool]

Santa,

I created user 'x' with quota on the relevant tbsps (d,e) and zero on the others. The only grant statement issued for user 'x' was connect and resource.

I wonder where he is getting privileges for a,b,c from.

Nice situation though, educational

 

[SantaMufasa]

OraCool,

Please post the results of this query:

select privilege from dba_sys_privs where grantee = 'PUBLIC';

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 16:32 (02Nov04) UTC (aka "GMT" and "Zulu"),
@ 09:32 (02Nov04) Mountain Time