Home » SQL & PL/SQL » SQL & PL/SQL » How to set the the user so that he cant access other tablespace?
How to set the the user so that he cant access other tablespace? [message #254061] Wed, 25 July 2007 12:04 Go to next message
biz_me
Messages: 8
Registered: July 2007
Location: singapore
Junior Member
Hi,
I have create 2 new tablespace, space1 and space2.
I want user 1 to be able to access space1 but stop user1 to access space2.
What is the revoke privileges to stop user1?

I try to search on line but no ans..

Please help
Re: How to set the the user so that he cant access other tablespace? [message #254065 is a reply to message #254061] Wed, 25 July 2007 12:14 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
ALTER USER ... QUOTA 0 ON ...;
Re: How to set the the user so that he cant access other tablespace? [message #254066 is a reply to message #254061] Wed, 25 July 2007 12:18 Go to previous messageGo to next message
yoonus.it@gmail.com
Messages: 109
Registered: June 2007
Location: kuwait
Senior Member
Hi,

have you already created users?

if u haven't created user

create users with default tablespace
space1 for user1
space2 for user2


if u do like this user1 cant access space2 and user2 cannot access space1.

don't grant any roles that will override this like(resource).

you can create user like

create user user1
identified by password
default tablespace space1
quota unlimited on space1;

if already created user use alter user command..

Hop it will help you Smile

blessings
unus

[Updated on: Wed, 25 July 2007 12:23]

Report message to a moderator

Re: How to set the the user so that he cant access other tablespace? [message #254146 is a reply to message #254061] Wed, 25 July 2007 22:15 Go to previous messageGo to next message
biz_me
Messages: 8
Registered: July 2007
Location: singapore
Junior Member
Hi thanks for u guys advice..
So do i type this way

Alter user with default tablespace
space1 for user1


According to your reply, lets say there are 3 tablespace already create: space1, space2, space3

if i create the user1 with default tablespace of space1, that means he can access space 1 but not space2 and space3.
Am i right?

Thanks

Re: How to set the the user so that he cant access other tablespace? [message #254158 is a reply to message #254146] Thu, 26 July 2007 00:51 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
User can't create an object in a tablespace (even its defauly tablespace) unless it has a quota on it (or UNLIMITED TABLESPACE privilege).

To give quota see Frank's post.

Regards
Michel
Previous Topic: How to set/change the maximum size of an existing table?
Next Topic: ORA-00936 when using distinct
Goto Forum:
  


Current Time: Sat Dec 03 14:15:59 CST 2016

Total time taken to generate the page: 0.06179 seconds