Hi,

is it possible to have certain user create all table and objects in
specific tablespace?

i mean , when they create table without specifying any tablespace,
their tables should go into say ABC tablespace instead USERSPACE1.

is it possible to attached schemas to tablespaces ?

regards,

On Tue, 04 Dec 2007 19:47:17 -0000, db2admin <jagdip@gmail.com> wrote:

> is it possible to attached schemas to tablespaces ?

Simple stuff first: no :-)

With that out of the way...

> is it possible to have certain user create all table and objects in
> specific tablespace?
>
> i mean , when they create table without specifying any tablespace,
> their tables should go into say ABC tablespace instead USERSPACE1.

Yes, this is possible, although whether the method will be acceptable I
don't know:

Simply ensure that tablespace "ABC" is the only one the user holds the
"USE" privilege on (which is necessary to create tables). In other words,
"REVOKE USE OF TABLESPACE USERSPACE1 FROM USER username" and similarly for
any other tablespaces the user holds the USE privilege on. This won't
prevent the user from reading tables in USERSPACE1 (or other tablespaces)
- the USE privilege is only invoked when creating tables (or indexes).
However, it will (obviously) mean that the user is only able to create
tables (and indexes) in tablespace ABC and nowhere else. If that's
acceptable, great - otherwise I'm afraid I don't know of another method.


HTH,

Dave.

On Tue, 04 Dec 2007 20:39:10 -0000, Dave Hughes <dave@waveform.plus.com>
wrote:

[snip]
> Simply ensure that tablespace "ABC" is the only one the user holds the
> "USE" privilege on (which is necessary to create tables). In other
> words, "REVOKE USE OF TABLESPACE USERSPACE1 FROM USER username" and
> similarly for any other tablespaces the user holds the USE privilege on.

Oops - quick correction to the above that's just occurred to me:

....for any other *regular* tablespaces the user holds the USE privilege
on. Leave the temporary tablespace privileges alone...


Cheers,

Dave.

Dave Hughes wrote:
> On Tue, 04 Dec 2007 20:39:10 -0000, Dave Hughes <dave@waveform.plus.com>
> wrote:
>
> [snip]
>> Simply ensure that tablespace "ABC" is the only one the user holds the
>> "USE" privilege on (which is necessary to create tables). In other
>> words, "REVOKE USE OF TABLESPACE USERSPACE1 FROM USER username" and
>> similarly for any other tablespaces the user holds the USE privilege on.
>
> Oops - quick correction to the above that's just occurred to me:
>
> ...for any other *regular* tablespaces the user holds the USE privilege
> on. Leave the temporary tablespace privileges alone...
DB2Admin,

You piqued my interest. This issue is a bit of a pet peeve of mine.
Would you mind giving background on what you are trying to do. Feel free
to email me directly.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab