What options do I have for securing CA Datacom DataQuery (DQRY)?
Article ID: 16402
Updated On:
Products
Issue/Introduction
Clients may want to implement row-level security for DataQuery (DQ). This document discusses the options available.
What are the options available for securing queries made though DataQuery? We'd like to limit the access to only specific rows of certain tables. Are the options that we can use impacted by the level of security we specify?
Environment
Component: DB
Resolution
In terms of CA Datacom, once security is enabled then it is automatically turned on for all access routes. The difference between security level 03 & security level 05 is that at level 05 you can (and would need to) specify a distinct set of permissions for DataQuery (DQ) access, which could be the same as or different from the access allowed for applications using SQL. If you leave your security at level 03, then the access via SQL & DQ would use the same access path. This means that if you are using external security, you would only need one set of statements in RACF to handle that authorization.
If you have applications that access Datacom through SQL, the best way to limit a user from accessing certain rows is create SQL views that only show the rows to which you want to grant access. You can create as many views as necessary to customize the unique accesses desired. Then using external security give the end users RACF access to the appropriate view(s), and revoke the RACF access they had to the base table.
This will work for all applications using SQL to access the tables; and it will work for users that use DQ in SQL mode for the same table access.
However, native DQ mode cannot use views. Users will get error DQ229E trying to access views using DQ, which is because of this restriction.
CA Dataquery (DQ) provides the capability of having row-level security in DQL mode through the use of Conditions and Restrictions defined within the DQ application security component. It allows definition of a rudimentary “view-like” subset of the data – called a Condition – that identifies the data elements that are qualified. This is a hard condition, i.e., regardless of the rest of the FIND statements, this condition is added to the query as an AND statement, and will return a result set that meets the user-specified FIND criteria as well as the additional Condition criteria. The easy way to remember this is that the Condition controls what data is returned.
Next, a collection of Conditions can be applied to individual users (or to groups), and this is called a Restriction. For example, one user might have a Restriction to only see Table ABC data with a state code of CA or NM or WA as one condition; another user might have that Condition plus another Condition that shows all data for only left-handed people. The first user has one Condition, and the second has two Conditions; these are maintained on the individual user records (or on a user group definition if they are being used). Consequently, USERs have RESTRICTIONs about the CONDITIONs to access DATA ROWs.
These Conditions and Restrictions apply only to DQL mode; they cannot be managed through External Security definitions, as the content of the Condition is defined as DQ code internally within DQ. The reason that the views work with External Security is that they are defined as distinct SQL entities, and utilize SQL security features, whereas the Condition/Restriction is actually DQL FIND…WHERE code snippets that become part of the user’s query.
To summarize, the alternatives for establishing row-level security are:
1. Convert all DQL code into SQL, and then prohibit the user from using DQL (DQ native mode) and control all access through SQL & views;
2. Convert the DQ reports into another CA Datacom/DB or SQL-based language (like COBOL or CA Ideal);
3. Create the different possible Conditions internally, and then restrict the users as needed. Note that the benefit to using this process is that changes can be implemented and activated spontaneously, and there is no need for a security administrator and no need to refresh the SAF definitions. At the same time, there is no report of people attempting to retrieve records beyond their scope; in fact, there is no need for a report, as the Condition prevents it by definition.
Additional Information
If you want more information about how DQ uses Conditions and Restrictions, you can see the DocOps pages for this functionality:
https://docops.ca.com/ca-datacom/15-1/en/administrating/security-overview/ca-dataquery-internal-security/securing-data-access-for-dql/limiting-access-to-rows-using-conditions-and-restrictions
and then the subordinate pages
Feedback
