When and how to modify the view SYSCA.ACCESSIBLE_TABLES
Article ID: 49576
Updated On:
Products
Issue/Introduction
The view SYSCA.ACCESSIBLE_TABLES is used to provide a list of data sources for many tools accessing IDMS databases via a server or web connection. As delivered, the view will provide a list of all available data sources whenever it is called. At times clients prefer to restrict this list in order to provide greater security or to improve performance in executing the view.
Environment
IDMS - All Supported releases
Resolution
SYSCA.ACCESSIBLE_TABLES is often used by tools accessing mainframe data as meta-data query, to determine which data sources are defined. In some cases it may be desirable to restrict the presentation of what data is available to people using these tools, either for security or performance reasons. That can be easily done by altering the syntax for the view. Steps to do that follow.
The syntax for this view, as delivered, is:
*+ CREATE VIEW SYSCA.ACCESSIBLE_TABLES
*+ ( SCHEMA, TABLE, TYPE ) AS
*+ SELECT SCHEMA,
*+ NAME,
*+ TYPE
*+ FROM SYSTEM.TABLE
*+ WHERE TYPE NOT IN ('N', 'F')
*+ AND ACCESS(SCHEMA, NAME) = 'Y'
*+ UNION
*+ SELECT NAME,
*+ SUBSTR(RSYN_NAME_079, 1, 18),
*+ 'N'
*+ FROM SYSTEM.SCHEMA,
*+ SYSDICT."S-010",
*+ SYSDICT."SRCD-113",
*+ SYSDICT."RCDSYN-079"
*+ WHERE DICTIONARY = CURRENT DATABASE
*+ AND NTWKSCHEMA <> ' '
*+ AND S_NAM_010 = NTWKSCHEMA
*+ AND S_SER_010 = VERSION
*+ AND RSYN_NAME_079 NOT BETWEEN 'SR0' AND 'SR9'
*+ AND ACCESS(NAME, RSYN_NAME_079) = 'Y'
*+ AND "S-SRCD"
*+ AND "RCDSYN-SRCD"
*+ ;
Note that this is a UNION of two subqueries. The first reads the catalog and will return data sources defined there (tables, views, routines); the second reads the dictionary and returns data sources that are SQL schemas mapped to network databases.
This view can be modified any number of ways. Before making any modifications, however, it is recommended that you create a new view under a different name (SYSCA.ACCESSIBLE_T_BACKUP, for example) for your reference, so that you have a clean starting point in the event that you need to make other modifications in the future. A few options of ways in which this view can be modified are outlined below:
- If you want to exclude all access to SQL schemas that are mapped to network databases, so that the end user sees only native SQL tables, views, and routines (including table procedures, procedures, and stored procedures), then alter this by removing everything from the word UNION until the end, so that it includes only the first subquery, like this:
CREATE VIEW SYSCA.ACCESSIBLE_TABLES
(SCHEMA, TABLE, TYPE ) AS
SELECT SCHEMA,
NAME,
TYPE
FROM SYSTEM.TABLE
WHERE TYPE NOT IN ('N', 'F')
AND ACCESS(SCHEMA, NAME) = 'Y' ;
- If you want to display _only_ data sources that are SQL schemas mapped directly to network databases (excluding SQL tables, views, and routines), then eliminate the first subquery and the word UNION, like so:
CREATE VIEW SYSCA.ACCESSIBLE_TABLES
(SCHEMA, TABLE, TYPE ) AS
SELECT NAME,
SUBSTR(RSYN_NAME_079, 1, 18),
'N'
FROM SYSTEM.SCHEMA,
SYSDICT."S-010",
SYSDICT."SRCD-113",
SYSDICT."RCDSYN-079"
WHERE DICTIONARY = CURRENT DATABASE
AND NTWKSCHEMA <> ' '
AND S_NAM_010 = NTWKSCHEMA
AND S_SER_010 = VERSION
AND RSYN_NAME_079 NOT BETWEEN 'SR0' AND 'SR9'
AND ACCESS(NAME, RSYN_NAME_079) = 'Y'
AND "S-SRCD"
AND "RCDSYN-SRCD" ;
- If you want to restrict access to one or more specific schemas, you can add a WHERE clause to the view as follows:
CREATE VIEW SYSCA.ACCESSIBLE_TABLES
(SCHEMA, TABLE, TYPE ) AS
SELECT SCHEMA,
NAME,
TYPE
FROM SYSTEM.TABLE
WHERE TYPE NOT IN ('N', 'F')
AND ACCESS(SCHEMA, NAME) = 'Y'
AND SCHEMA = 'DEMOEMPL' -- additional WHERE clause
UNION
SELECT NAME,
SUBSTR(RSYN_NAME_079, 1, 18),
'N'
FROM SYSTEM.SCHEMA,
SYSDICT."S-010",
SYSDICT."SRCD-113",
SYSDICT."RCDSYN-079"
WHERE DICTIONARY = CURRENT DATABASE
AND NTWKSCHEMA <> ' '
AND S_NAM_010 = NTWKSCHEMA
AND S_SER_010 = VERSION
AND RSYN_NAME_079 NOT BETWEEN 'SR0' AND 'SR9'
AND ACCESS(NAME, RSYN_NAME_079) = 'Y'
AND "S-SRCD"
AND "RCDSYN-SRCD"
AND NAME LIKE 'EMP%' ; -- additional WHERE clause
Note that in this sample the schema name is restricted in both subqueries, in different ways. In the first subquery (which will return tables, views, and routines) you can restrict the data sources to those associated with one specific schema via the WHERE clause SCHEMA = 'DEMOEMPL'. Note that in this first subquery the column that represents the schema name is called SCHEMA. The second subquery, after the UNION (which returns data sources that are network records accessed via an SQL schema that refers to them), restricts the data sources to all schema that begin with the digits 'EMP' via a mask in the WHERE clause NAME LIKE 'EMP%' . Note that in this second subquery the name of the column that represents the schema name is called 'NAME'.
If your goal is to restrict the data sources to those associated with specific schema(s), make certain that you code the WHERE clause on the appropriate subquery. You can use any SQL mode of comparison to enforce this selection: a direct equality, a mask, a range of values specified in an IN clause, an additional subquery, etc.
If you wish to be even more restrictive, you could add a WHERE clause limiting the selection to specific data source names as well.
In addition to the above, there are two important points to note:
- The end-user must be GRANTed authorization to an SQL-defined data source or it will not be displayed in the list; this is enforced by the clause AND ACCESS(SCHEMA, NAME) = 'Y' in the first subquery.
- Any SQL schema defined to reference a network schema must be defined with the clause DBNAME <database-name>, where <data-base name> is the DBname specified in the CONNECT statement for this session. That is enforced by the clause WHERE DICTIONARY = CURRENT DATABASE in the second subquery.
Feedback
