Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2012
    Posts
    119

    Unanswered: minimum set of privileges/authorities to create a table

    Hi all,
    I need to restrict as much as possible the privileges for the user who performs table creations on a particular tablespace.
    I'm using DB2 10.5 on Linux.

    Could you help me identify and set this minimum set of privileges?
    Thanks

  2. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    69
    Provided Answers: 7
    Hi,

    Have you tried to find this information at the CREATE TABLE statement link in the DB2 Knowledge Center?
    Regards,
    Mark.

  3. #3
    Join Date
    Mar 2012
    Posts
    119
    I've had a look now... there are a combination of privileges/authorities.
    I'm looking for the minimum set of privileges (that is, allowing the user to do the least on the database), so I would say...
    ◦USE privilege on the table space
    ◦CREATEIN privilege on the schema (since it already exists)

    Do you agree this is the best choice?
    Thanks

  4. #4
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    69
    Provided Answers: 7
    If there is no need to reference other tables which this user didn't create in the CREATE TABLE statement, then yes, these ones + CONNECT, CREATETAB authorities on the database.
    Regards,
    Mark.

  5. #5
    Join Date
    Apr 2012
    Posts
    1,006
    Provided Answers: 16
    The trouble with the minimum you propose is that it can be un-necessarily restrictive. It can be that a table DDL might also include indexes for that table, views or MQTs on that table, the DDLmight include or reference other schema-names (other than the default for the auth-id), there may need to be special grants or roles that reference this table, there may need to be runstats-profiles created specifically for this table and its indexes, if the table is partitioned by range, or partitioned by hash then other pre-requisite objects might need to pre-exist. If static-SQL is used there may be packages to bind. If optimization hints are used there can be other pre-reqs. These things can change over time, even after you've long left the project or site.
    So if you un-necessarily restrictive then you will create an high-cost to support environment. On option is to have an account specifically for database-deployment related activities, which has all necessary rights and permissions.- that can be wider than connect and creatin/USE. Sometimes too much role separation is this area is not cost effective.

  6. #6
    Join Date
    Mar 2012
    Posts
    119
    Quote Originally Posted by db2mor View Post
    The trouble with the minimum you propose is that it can be un-necessarily restrictive. It can be that a table DDL might also include indexes for that table, ......
    That's a good point!
    The additional grants the user would need are the INDEX and TRIGGER privileges on the table, since the table will have indexes and triggers defined on it.
    Are they included with the grants we talked about before?

    And if not, how can they be given to the user on tables that don't exist already, provided that all the privileges are granted at installation time?

  7. #7
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    69
    Provided Answers: 7
    Quote Originally Posted by Robert1973 View Post
    The additional grants the user would need are the INDEX and TRIGGER privileges on the table, since the table will have indexes and triggers defined on it.
    Are they included with the grants we talked about before?

    And if not, how can they be given to the user on tables that don't exist already, provided that all the privileges are granted at installation time?
    + ALTERIN on schema to create triggers. You have to have additional privileges of course if you reference another tables in the trigger.
    See the CREATE TRIGGER statement description.
    Regards,
    Mark.

  8. #8
    Join Date
    Mar 2012
    Posts
    119
    Quote Originally Posted by mark.bb View Post
    + ALTERIN on schema to create triggers. You have to have additional privileges of course if you reference another tables in the trigger.
    See the CREATE TRIGGER statement description.
    So no other grant needed for index creation on the new table? Does the same apply also for contraints?

    If so, in summary what I would need for the creation of a table, eventually including indexes, triggers and constraints, will be:
    ◦USE privilege on the table space
    ◦CREATEIN and ALTERIN privilege on the schema
    ◦CONNECT, CREATETAB authority on the database

    Is that correct?

    Last question: if I want to remove the possibility for a user with such privileges to create tables in other tablespaces, should I explicity remove any privilege?

    Thanks

  9. #9
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    69
    Provided Answers: 7
    Quote Originally Posted by Robert1973 View Post
    So no other grant needed for index creation on the new table? Does the same apply also for contraints?

    If so, in summary what I would need for the creation of a table, eventually including indexes, triggers and constraints, will be:
    ◦USE privilege on the table space
    ◦CREATEIN and ALTERIN privilege on the schema
    ◦CONNECT, CREATETAB authority on the database

    Is that correct?
    ...
    Sorry for answering your question with another question, but is this so hard to check the authorizations needed for every possible statement which your user is allowed to execute in the DB2 Knowledge Center?
    Regards,
    Mark.

  10. #10
    Join Date
    Mar 2012
    Posts
    119
    Hi Mark,
    from my point of view it's more than simply checking on DB2 Knowledge Center, either because there are many combinations of privileges that can be granted and I need the most restrictive or because there can be side effects like the ones pointed out above... so sorry if my question sounds so trivial to you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •