StarQuest Technical Documents

Creating Restricted Views in DB2 for z/OS

Last Update: 14 March 2012
Product: StarSQL
Version: 5.x
Article ID: SQV00SQ022

Purpose

The simpliest way to create a restricted view is to use Classic StarAdmin (a custom install option included with StarSQL 6.0 and earlier). This document explains the requirements and steps necessary to create restricted views manually on DB2 for z/OS. Example SQL statements used to create restricted views are provided. The GRANT SQL is also provided.

Solution

Querying the SYSIBM.SYSTABLES will generate a list of all tables and views for all authorization ids. The SQLTables ODBC function is defined in such a way that the application can decide if it wants to "filter" the list based on e.g. authorization id. StarSQL always shows all available tables (when using SYSIBM) and reports in SQLGetInfo that all tables may not be accessible by the user.

To scale down the number of tables brought down, the system administrator needs to create restricted views. For example: creating a restricted view based on CURRENT SQLID, will allow user ABCD to only see tables ABCD.XXXX. Restricted views can be created based on information in SYSTABAUTH and/or SYSUSERAUTH catalog tables.

StarSQL requires that views be created for the following tables:

• SYSTABLES
• SYSCOLUMNS
• SYSKEYS
• SYSINDEXES
• SYSRELS
• SYSFOREIGNKEYS
• SYSPROCEDURES

Example 1 shows a sample SQL statement that was used to create a restricted view of SYSTABLES. You must create a restricted view for each table that is required by StarSQL (see 'Required Tables').

SET CURRENT SQLID = 'SYSDBA';
COMMIT;
CREATE VIEW SYSDBA.SYSTABLES AS SELECT * FROM SYSIBM.SYSTABLES
WHERE (CREATOR = 'USER')
OR (CREATOR = 'USER2')
OR (CREATOR = 'USER3');
COMMIT;

READ-ONLY

Some applications require index information in order to perform updates on tables. If your application has this requirement you can restrict users to READ-ONLY access by creating a restricted view of the SYSIBM.SYSTABLES catalog table. However, all remaining tables in the list(s) above (see 'Required Tables') MUST exist under the selected AUTHID. This can be accomplished by creating empty tables based on the structure of the associated SYSIBM catalog tables. For example, execute the following SQL:

CREATE TABLE SYSDBA.SYSCOLUMNS LIKE SYSIBM.SYSCOLUMNS
CREATE TABLE SYSDBA.SYSKEYS LIKE SYSIBM.SYSKEYS
CREATE TABLE SYSDBA.SYSINDEXES LIKE SYSIBM.SYSINDEXES
CREATE TABLE SYSDBA.SYSRELS LIKE SYSIBM.SYSRELS
CREATE TABLE SYSDBA.SYSFORIEGNKEYS LIKE SYSIBM.SYSFORIEGNKEYS
CREATE TABLE SYSDBA.SYSPROCEDURES LIKE SYSIBM.SYSPROCEDURES

Note: IN DATABASE <DATABASE_NAME> must be appended to each SQL statement if it has not been entered in the StarSQL data source Advanced Options Screen.

After creating the restricted view(s), enter the AUTHID that you used in the "CREATE VIEW" statement (SYSDBA in the example above) as the SQL Catalog Qualifier in the StarSQL data source setup screen.

GRANT COMMANDS

Prior to StarSQL package binding, execute the following commands:

GRANT BINDADD TO <AUTHID>
GRANT CREATE IN COLLECTION <COLLID> TO <AUTHID>

Then proceed to bind the new packages. After the StarSQL packages are bound on the HOST, BINDADD authority can be revoked.

Then you need to grant other authids permissions to use the packages with this command:

GRANT EXECUTE on each StarSQL package (usually SWRC0000 & SYSIBM), such as
GRANT EXECUTE ON PACKAGE STARCOLL.SWRC0000 TO PUBLIC)

Site Map
Products | Trial Download | Customer Support | Partners | About StarQuest | Press & Events | Contact StarQuest

©Copyright 2012 StarQuest Ventures, Inc. All rights reserved.

Legal Notice • Privacy Policy
Email contact@starquest.com with site comments.