Re: New tablespace, new user with full access to tablespace?

danny de bie <danny_at_larix.com> wrote:
>I'd like to create a new tablespace and a single new user. I want
>to limit the new user's access to the single new tablespace, and I
>want him to be able to do anything in this tablespace.
>
>This may be a FAQ, or just a TSQ (Terminally Stupid Question), but
>I can not find any doc on how to accomplish this.
>
>Any suggestions? Many thanks in advance!

This is assuming you are using Oracle 7, stuff in <>'s is for you to replace with what you want:

CREATE TABLESPACE <tablespace_name>
DATAFILE <datafile_path> SIZE <size>
<any storage parameters etc>;

CREATE USER <username>
IDENTIFIED BY <password>
DEFAULT TABLEPACE <tablespace_name>
QUOTA UNLIMITED ON <tablespace_name>
<any other user stuff you want>;

You then need to grant the privs you require to the user, such as:

GRANT CREATE SESSION TO <username>;
GRANT CREATE TABLE TO <username>;
etc.

-- 
** Mark Styles aka Small       -- Opinions expressed here are my own --   **
** marks_at_teleord.co.uk         -- unless otherwise specified         --   **
**           This whole world's wild at heart and weird on top            **