By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,779 Members | 1,111 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,779 IT Pros & Developers. It's quick & easy.

attach tablespace to user

P: n/a
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,
Dec 4 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On Tue, 04 Dec 2007 19:47:17 -0000, db2admin <ja****@gmail.comwrote:
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.
Dec 4 '07 #2

P: n/a
On Tue, 04 Dec 2007 20:39:10 -0000, Dave Hughes <da**@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.
Dec 4 '07 #3

P: n/a
Dave Hughes wrote:
On Tue, 04 Dec 2007 20:39:10 -0000, Dave Hughes <da**@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
Dec 5 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.