Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367

    Unanswered: sql0551n - privilege granted to group

    v9.7 FP5


    In dev env, I granted DBADM with DATAACCESS to group X.

    When a user who is part of group X tries to create a view, he's getting:

    SQL0551N "USER" does not have the required authorization or privilege to
    perform operation "SELECT" on object "xxxxxxxx".
    SQLSTATE=42501


    This is due to "Group privileges are not considered for any table or view specified in the CREATE VIEW statement."

    The same applies to create trigger, open cursor, etc...
    IBM Restrictions on the use of group privileges when executing DDL statements or binding packages - United States


    So, I created role X and granted DBADM to this role and then granted role X to this user. create view is working.

    However, I don't want to grant anything to specific user IDs. If I grant role X to the group, create view is failing for the same reason as before because "Roles granted to groups are not considered for packages containing SQL, views, materialized query tables (MQT), triggers and SQL routines."


    Are there any other options? To allow users to create views, etc... without granting them explicit privileges?

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Well, roles essentially function as groups, so granting something to a role, then granting role to a user is equivalent to granting something to a group, then adding a user to the group. The difference is that group membership is outside the DBM control and therefore has security implications, whereas role "membership" is fully within the DBM control.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by n_i View Post
    Well, roles essentially function as groups, so granting something to a role, then granting role to a user is equivalent to granting something to a group, then adding a user to the group.
    Yes, I realized that.

    I guess I would need to do explicit grants to all users that need to create views, etc...? This is too messy and not easy to manage. We don't want to do any grants to users. Any alternative here?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2girl View Post
    Yes, I realized that.

    I guess I would need to do explicit grants to all users that need to create views, etc...? This is too messy and not easy to manage. We don't want to do any grants to users. Any alternative here?
    The only explicit grant that you need is "GRANT ROLE foo TO USER bar". All object privileges etc will be granted to the role. It's the same as adding users to a particular OS group, only you will do it instead of asking a sysadmin.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by n_i View Post
    The only explicit grant that you need is "GRANT ROLE foo TO USER bar".
    "GRANT ROLE foo TO USER xxx" is what I suggested. There are many developers, they go though a 3-level approval process in order to get added to a DB2 group (using a tool that create/modifies users). With this ROLE thing, I'd have to do grant for every user ID and not sure how it's going to get approved.

    I guess there is no other option. Thanks.

Posting Permissions

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