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

Sean Michael Dillon <sdillon_at_surfsouth.com> wrote:
>mlanda_at_vnet.ibm.com wrote:
>> GRANT CONNECT TO TEST IDENTIFIED BY EXAMPLE;
>> ALTER USER TEST DEFAULT TABLESPACE user1 TEMPORARY TABLESPACE TEMP_TS;
>> ALTER USER TEST PROFILE <your profile>;
>> GRANT RESOURCE TO TEST;
>
>Doesn't 'GRANT RESOURCE TO TEST' then give that user RESOURCE authority
>on any tablespace. I wasn't aware the RESOURCE privelege was restricted
>to a user's default tablespace.

Thats correct, RESOURCE is actually a role, which gives access to all tablespaces, and also grants CREATE CLUSTER, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, and CREATE TRIGGER to the user.

The ALTER USER <username> QUOTA UNLIMITED ON <tablespace>; command should be used (replacing UNLIMITED with a value if you want to restrict the space allowed to the user)

-- 
** 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            **