Granting view schema for DB2 zSeries on mainframe

Posted on 2006-03-30
Last Modified: 2010-08-05
Hi experts,

I am trying to grant users a privilege to view some schemas on my DB2 mainframe. After looking around, I can only find this:

I don't want the users to be able to either create, drop, or alter. They only need to be able to see the schema and select tables in the schema.

Would you please kindly advise me on how to do this?

Thank you!!!
Question by:Irmawaty

    Accepted Solution

    Irmawaty, grant select permissions to the user or group per table with a command like this:

    Grant SELECT on table schema.tablename to user userid


    Grant SELECT on table schema.tablename to group usergroup

    You can't grant select privileges at the schema level.

    hope this helps,
    LVL 7

    Assisted Solution

    if there are alot of tables in the schema, granting select will be difficult, you can use following to create a script file:

    SELECT 'GRANT SELECT ON ' || TABSCHEMA || '.' || TABLENAME || ' to user userA;' from syscat.tables where tabschema = 'DB2ADMIN';

    LVL 13

    Assisted Solution

    easiest way to do this is to use the db2 Control Center.
    Go to User abd Group Objects
    Select DB Users
    Select the user name, right click and choose change
    From the tab selections, choose tables
    Then choose Add table
    Then select the schema name the tables
    From the list of tables, highlight each table name in blue by clicking on it once, or click the entire group of tanles.
    Click ok or apply
    The tables are now put in new conatiner where you can set the select priv. You can do this one table at a time or highlight all the tables and adjust your priv allocations.
    Click apply


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
    Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now