Re: What to GRANT a developing user ?

Atif Ahmad Khan wrote:

> I have created a user 'test' and given him permissions to
> create a session and create a table. Now when the user logs
> in he tries to create a table and gets the following error :
>
> SQL> create table test (name char(2), id number);
> create table test (name char(2), id number)
> *
> ERROR at line 1:
> ORA-01950: no privileges on tablespace 'SYSTEM'
>
> Is this the right solution to the problem ? Also are there
> any other permissions that I need to give him besides "create
> table" and "create session" ?

Case One:

     If you want the tables to be created is in SYSTEM, you can increase his tablespace quota in SYSTEM:

     ALTER USER username QUOTA nnn ON SYSTEM.

     Alternatively,

     You can grant the UNLIMITED TABLESPACE *system* privilege to the developer.

     But, it is not a good strategy because SYSTEM is used to store the data dictionary and it cannot be taken offline. It is very difficult for you to manage the database if users' objects are created in SYSTEM. Especially, if the developer has been granted the unlimited tablespace privilege, it will lead to detrimental effect.

Case Two:

     If you want to create tables in another tablespace, say data_ts, you can increase his tablespace quota in data_ts and set it as default.

     ALTER USER username DEFAULT TABLESPACE data_ts QUOTA nnn ON data_ts.

     It is highly recommend to do in this way.

     Alternatively,

     You can create tables in another tablespace besides the default one, SYSTEM.

     CREATE TABLE tablename ( ... ) TABLESPACE data_ts.

  It is more recommended to set a quota on each tablespace so that the DBA can be easier to manage the resource and predict the size of each tablespace, hence the size of datafiles.      

-- 
Name        : Lun Wing San
Title       : Senior Consultant of Lun's Oracle Solution Company
              System Administrator and Oracle DBA of the Quick Response Center
              Oracle Developer of the Hong Kong Productivity Council
Email Addr. : sunny_at_hkpc.org
Telephone   : 852-2788-5841