Granting view schema for DB2 zSeries on mainframe

Posted on 2006-03-30
Medium Priority
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

TAzGa earned 672 total points
ID: 16335198
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,

Assisted Solution

sachinwadhwa earned 664 total points
ID: 16335539
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

ghp7000 earned 664 total points
ID: 16336310
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
In this video I will demonstrate how to set up Nine, which I now consider the best alternative email app to Touchdown.
From store locators to asset tracking and route optimization, learn how leading companies are using Google Maps APIs throughout the customer journey to increase checkout conversions, boost user engagement, and optimize order fulfillment. Powered …
Suggested Courses

600 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