How to secure SQL access to non-SQL defined data

Products

• IDMS
• IDMS - Database

Issue/Introduction

A common requirement is to allow users to have update or retrieval access via SQL commands to non-SQL defined databases.

The only way to do this is with DB security.

Environment

Release : 19.0 and 18.5

Component : CA IDMS/DB

Resolution

The first thing to do is to update the RHDCSRTT module with the following entries:

         #SECRTT TYPE=ENTRY,SECBY=OFF,RESTYPE=DB
         #SECRTT TYPE=OCCUR,SECBY=INT,RESTYPE=DB,RESNAME='APPLDICT'

The first entry indicates that RESTYPE DB is by default unsecured.
The second entry indicates that dbname APPLDICT is secured.
For the purposes of securing SQL schemas which point to non-SQL databases, it is the dbname being connected to which must be specified here.
This means that it is not possible to secure databases differently at the RHDCSRTT level if they are defined in the same catalog. GRANTs in the catalog will be needed to do that.

Next, define two groups - one to hold the users who can update the database, and one to hold those who will have retrieval-only access. This must be done while connected to SYSTEM.

     CONNECT TO SYSTEM;
     CREATE GROUP "NSQL_ACCESS"
         ADD USER user1

         ;                                                   
     CREATE GROUP "NSQL_SELECT"
         ADD USER user2
         ;                                                        

Now the privileges must be GRANTed. Note that this must be done while CONNECTed to the catalog in which the schema is defined.
In this case, the name of the SQL schema pointing to the network schema is EMPNET.

     CONNECT TO APPLDICT;
     GRANT ACCESS ON TABLE EMPNET.* TO NSQL_ACCESS;
     GRANT SELECT ON TABLE EMPNET.* TO NSQL_SELECT;

If there are other SQL schemas in the same catalog which should not be secured at all, then the following command must be used to unsecure them ("UNSECSCH" is the name of any such schema):

     CONNECT TO APPLDICT;
     GRANT ACCESS ON TABLE UNSECSCH.* TO PUBLIC;

With these definitions in place, users attempting the access for which they do not have the relevent privilege will receive this error message:

     DB005521 T46 C1M339: Missing authorization for EMPNET.<record-name>

To allow users to be able to define new SQL schemas for the non-SQL schema, the following syntax should be used:

     CONNECT TO APPLDICT;
     GRANT CREATE ON SCHEMA EMPNSQL TO NSQL_CREATE;
     GRANT USE ON NONSQL SCHEMA V0100.EMPSCHM TO NSQL_CREATE;
     GRANT USE ON DB EMPDEMO TO NSQL_CREATE;

Note:
* The session must be CONNECTed to the application dictionary when these GRANTs are issued
* The CREATE can also be a DEFINE as DEFINE includes CREATE
* The version number in the NONSQL SCHEMA must be four digits and requires the leading zeroes.

With the above definitions in place, users in group NSQL_CREATE will be able to define an SQL schema like this:

     CREATE SCHEMA EMPNSQL
         FOR NONSQL SCHEMA APPLDICT.EMPSCHM VERSION 100
             DBNAME EMPDEMO
         ;     

The nature of DB security means that with these definitions in place, NRU, AREA and TABL security will also be in force for APPLDICT, so these must be unsecured with GRANT syntax.
The following samples can be used:

     CONNECT TO SYSTEM;
     CREATE RESOURCE CATEGORY NSQL_CATEGORY
         ADD RUNUNIT APPLDICT.*
         ;
     GRANT EXECUTE ON CATEGORY NSQL_CATEGORY TO PUBLIC;
     GRANT USE ON AREA APPLDICT.* TO PUBLIC;

     

     CONNECT TO APPLDICT;
     GRANT ACCESS ON TABLE <schema>.* TO PUBLIC;

Additional Information

Administrating Security for IDMS