When and how to modify the view SYSCA.ACCESSIBLE_TABLES

Document ID : KB000049576
Last Modified Date : 14/02/2018
Show Technical Document Details

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.

Question: 

How can the view SYSCA.ACCESSIBLE_TABLES be modified? When should this be done?

Environment:  

Content

Answer: 

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:

  1. 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' ;

  1. 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" ;

  1. 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:

  1. 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.
  2. 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.

Additional Information:

More details about authorizations and security can be found in the CA IDMS Security Administration Guide. 

More details about SQL schemas and data sources, including view definitions,l can be found in the CA IDMS SQL Reference.