StarQuest Technical Documents

Creating Restricted Views in DB2 for z/OS

Last Update: 16 November 2018
Product: StarSQL
Version: 5.x
Article ID: SQV00SQ022

Purpose

This document explains the requirements and steps necessary to create restricted views 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 reduce the number of tables returned, the system administrator can create restricted views of the system catalog tables. 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
• SYSROUTINES
• SYSSYNONYMS

Example 1 shows a sample SQL statement that was used to create a restricted view of SYSTABLES for users USER, USER2, and USER3. You must create a 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;

 

Example 2 shows the SQL statements to create a restricted view of SYSTABLES for CURRENT SQLID, and includes the necessary grants. Note that the views of the other system tables do not need to be restricted.

CREATE VIEW STARSQL.SYSTABLES AS SELECT * FROM SYSIBM.SYSTABLES WHERE CREATOR = CURRENT SQLID;
CREATE VIEW STARSQL.SYSCOLUMNS AS SELECT * FROM SYSIBM.SYSCOLUMNS;
CREATE VIEW STARSQL.SYSKEYS AS SELECT * FROM SYSIBM.SYSKEYS;
CREATE VIEW STARSQL.SYSINDEXES AS SELECT * FROM SYSIBM.SYSINDEXES;
CREATE VIEW STARSQL.SYSRELS AS SELECT * FROM SYSIBM.SYSRELS;
CREATE VIEW STARSQL.SYSFOREIGNKEYS AS SELECT * FROM SYSIBM.SYSFOREIGNKEYS;
CREATE VIEW STARSQL.SYSPROCEDURES AS SELECT * FROM SYSIBM.SYSPROCEDURES;
CREATE VIEW STARSQL.SYSROUTINES AS SELECT * FROM SYSIBM.SYSROUTINES;
CREATE VIEW STARSQL.SYSSYNONYMS AS SELECT * FROM SYSIBM.SYSSYNONYMS;

grant select on STARSQL.SYSTABLES to public;
grant select on STARSQL.SYSCOLUMNS to public;
grant select on STARSQL.SYSKEYS to public;
grant select on STARSQL.SYSINDEXES to public;
grant select on STARSQL.SYSRELS to public;
grant select on STARSQL.SYSFOREIGNKEYS to public;
grant select on STARSQL.SYSROUTINES to public;
grant select on STARSQL.SYSSYNONYMS to public;

After creating the restricted view(s), enter the collection ID that you used in the CREATE VIEW statement (STARSQL in the example above) as the SQL Catalog Qualifier in the StarSQL data source setup screen, or specify CatQual=STARSQL in a connection string.

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)

Additional Information

Restricted views can also be created using StarAdmin Classic (an optional installer provided for use with 32-bit StarSQL). Contact StarQuest support for further information.