attach tablespace to user

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • db2admin
    #1

    attach tablespace to user

    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,
    Tags: None
    • Dave Hughes
      #2
      Re: attach tablespace to user

      On Tue, 04 Dec 2007 19:47:17 -0000, db2admin <jagdip@gmail.c omwrote:
      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.

        Comment

        • Dave Hughes
          #3
          Re: attach tablespace to user

          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.

            Comment

            • Serge Rielau
              #4
              Re: attach tablespace to user

              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

                Comment

                Previous template Next
                Working...