Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    37

    Unanswered: grant select ... ( or any other objects )

    hi all,

    how can i grant "select" on all tables in schema x to a user y without listing all names of tables in schema x ...

    GRANT SELECT ON TABLE x.tab1 TO USER y;
    GRANT SELECT ON TABLE x.tab2 TO USER y;
    GRANT SELECT ON TABLE x.tab3 TO USER y;
    GRANT SELECT ON TABLE x.tab4 TO USER y;
    ....
    ....
    and so on ...


    is there any commands like :

    GRANT SELECT ON TABLE x.ALLTABLE TO USER y; ?????? !!!!

    thanks a lot

  2. #2
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    62
    Hi

    In DB2 V7 z/OS this is not possible. You can grant privileges on the database level, which include the SELECT-privilege. Unfortunately you will grant other privileges as well, which are inherited in the database privilege, that you might not want to. For instance, if you grant DBADM, the user can also update, delete, insert all tables in that database, he can create new ones or drop tables and much more including utility-privileges.

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: grant select ... ( or any other objects )

    This is not possible as far as I know

    You will have to grant at the table level

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: grant select ... ( or any other objects )

    You can build a SELECT statement that builds the GRANT statements:

    SELECT CASE WHEN type = 'V'
    THEN 'GRANT SELECT ON ' || RTRIM(creator) || '.' || name || ' TO groupa, groupb;'
    WHEN name LIKE '%_T'
    THEN 'GRANT SELECT, INSERT ON ' || RTRIM(creator) || '.' || name || ' TO groupa, groupb;'
    ELSE 'GRANT INSERT, SELECT, UPDATE, REFERENCES ON ' || RTRIM(creator) || '.' || name || ' TO groupc, groupd;'
    END
    FROM sysibm.systables
    WHERE (creator IN ('SCHEMA' )
    AND type != 'A'
    AND (name LIKE '%_T'
    OR name LIKE '%_RT'
    OR name LIKE '%_V'
    OR name LIKE '%_AT'))


    Originally posted by bab
    hi all,

    how can i grant "select" on all tables in schema x to a user y without listing all names of tables in schema x ...

    GRANT SELECT ON TABLE x.tab1 TO USER y;
    GRANT SELECT ON TABLE x.tab2 TO USER y;
    GRANT SELECT ON TABLE x.tab3 TO USER y;
    GRANT SELECT ON TABLE x.tab4 TO USER y;
    ....
    ....
    and so on ...


    is there any commands like :

    GRANT SELECT ON TABLE x.ALLTABLE TO USER y; ?????? !!!!

    thanks a lot

  5. #5
    Join Date
    Feb 2004
    Posts
    37
    hi walter, sathyaram, dmmac

    thanks for responses ..

    another question:

    what is the reason that i can change to a schema that not exists on DB ...

    for example: set schema YYY

    there is no schema YYY but i dont get any errors ... what is the reason?

    thanks a lot

    bab



    @walter
    hi walter, vielen dank für die antwort ...

  6. #6
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    62
    I think, because you have SYSADM-authority.

Posting Permissions

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