Question:
In order to turn on DB level security for just one database we attempted the following in the SRTT:
#SECRTT TYPE=ENTRY,
RESTYPE=DB,
SECBY=OFF
*
#SECRTT TYPE=OCCURRENCE,
RESTYPE=DB,
RESNAME='INFOSQL',
SECBY=INTERNAL
INFOSQL is an SQL Schema name for several Tables and there are two SEGMENT names INFOSQL1 and INFOSQL2 that we want secured.
The SRTT macros above did not result in these two database segments being secured. What did we do incorrectly?
Answer:
In the #SECRTT TYPE=OCCURRENCE macro for RESTYPE=DB the RESNAME must be a DBNAME or SEGMENT name that would be set as the current DBNAME/DICTNAME at runtime.
This can be set by CONNECT TO dbname command for SQL or the current DBNAME/DICTNAME can be set by DCUF command or by a USER or SYSTEM PROFILE processed at signon time.
For SQL the CONNECT TO dbname must include the CATALOG segment where the Schema and Tables are defined as well as the data segment.
In the example SRTT macros shown above RESNAME was a segment that users will never set as current Dbname/Dictname at runtime.
In fact there is a DBNAME defined in the DBTABLE called DB12 that contains the SQL catalog segment and the data segment.
This is the DBNAME that users will have set by DCUF, PROFILE or the CONNECT command.
Example:
CONNECT TO DB12;
SELECT * FROM INFOSQL.EMPLOYEE;
In order for IDMS to do database level security checks for this command, the SRTT must either have RESTYPE=DB on for all databases, or specifically for the DB12 database name;
to secure all databases:
#SECRTT TYPE=ENTRY,RESTYPE=DB,SECBY=INT
to secure just the DB12 database:
#SECRTT TYPE=OCCURRENCE,RESTYPE=DB,RESNAME='DB12',SECBY=INT
Additional Information:
Securing Common Database Resources - CA IDMS - 19.0 - CA Technologies Documentation