CA Datacom SQL Security › Implementing the SQL Security Model › View Security

View Security

This section discusses the implementation of view security for SQL in CA Datacom/DB. Enabling views to secure by name gives greater control because it allows control of access to the precise set of tables, rows, and columns defined by a view.

View Security Overview

View security replaces security authorization checks on the individual tables referenced by a view with a check against the view entity itself. This feature is activated in the external security package. For more information about implementing this choice, see Configuring Your System to Enable View Security. The set of access-rights that are securable for a view mirror those securable for a base table.

Whether statements included in a particular plan execute using view security depends upon whether this option was active during preparation of the plan. This is true regardless of whether bind-time plan security is being used, that is, when the view access rights of the creator of the plan are checked at bind-time. For those plans that do not use bind-time plan security, view access rights are checked at execution time, but only if view security was active at bind-time. Otherwise, table-level security is checked.

Whether view security is used for a particular plan is based on the value of the VIEWSEC= Preprocessor plan option. If VIEWSEC= is not specified, whether a plan uses view security is determined by the value of the view-security specification in the SQLOPTION Multi-User startup option. If neither VIEWSEC= nor the view-security specification in SQLOPTION is used, view security is not used for newly bound or rebound plans. For more information on VIEWSEC=, see View Security SQL Preprocessor Option (VIEWSEC=).

View security may be activated or deactivated, intentionally or inadvertently, for existing plans by rebinding them or causing them to auto-rebind. During the rebind, the plan's use of view security is changed using the same set of rules that apply during the initial plan preparation, as described in the preceding paragraph. This means that preexisting plans for which the VIEWSEC option was not specified are changed to use the systemwide default that is in effect at the time of any rebind or auto-rebind. The systemwide default of NO protects these plans from being changed inadvertently.

We do not force rebinds to be performed when this feature is activated. Whether existing plans are rebound is decided by the Security Administrator when the Security Administrator activates view security.

Getting Started

To help ensure that your transition to using view security is smooth, follow these steps:

1. Ensure that your external security package is configured to treat CA Datacom/DB SQL as a closed system, that is, all users are barred from accessing any resource if they have not been given explicit access rights. If this precaution is not taken, users can create and use new views against tables even if no access rights have been given.
2. Use the WITH CHECK OPTION clause in the CREATE VIEW statement to specify that all inserts and updates against a view are checked to ensure that the newly inserted or updated row satisfies the view definition.

   Important If you do not use the WITH CHECK OPTION, view security cannot prevent users from adding rows to tables and views that they are not authorized to access. The execution of insert and update statements can be prevented by revoking the INSERT and UPDATE view access rights using external security. Using the WITH CHECK OPTION clause can prevent such access rights violations, however, and that is why we strongly recommend that you use the WITH CHECK OPTION clause in any updateable view (that is, single-table, no DISTINCT keyword, and no GROUP BY clause) secured with view security.

3. Add view security authorizations to your external security package. Because you can activate view security on individual plans (see View Security SQL Preprocessor Option (VIEWSEC=) for details), the authorizations you choose to implement can span your entire system or be limited to the set of views referenced by a single application.

   While view security cannot be controlled on a view-by-view basis, it can be controlled at the plan level. This benefits users of plan security and provides a level of control if you want to retain the use of table security for certain applications (or if you want to convert only certain applications to the use of view security). The complexity of security administration can be minimized, however, with a systemwide choice of security method.

   Note: You can retain the table authorizations currently in place, but be aware that for applications using view security, such authorizations are ignored.

   CA Datacom Datadictionary Path reports allow you to see the relationships between tables, views, plans, and programs. You can use these reports to help in the conversion of table authorizations into view authorizations. For details, see Adding View Security Authorizations.

4. For the view-security specification in the SQLOPTION Multi-User startup option (in the SYSIN file of your MUF startup JCL), specify YES or NO with regard to whether new and rebound plans are to use view security if they do not have explicit VIEWSEC= Preprocessor specifications. To convert an entire MUF to the use of view security without having to add the VIEWSEC=Y Preprocessor option to existing applications, specify YES for view-security in the SQLOPTION Multi-User startup option and then rebind all of your plans. You can use the SQL Rebind Facility (DBSRFPR) to perform the rebinds if you want to do so. If you do not want existing applications to use view security, either code NO for view-security in the SQLOPTION Multi-User startup option (recommended), or omit the specification. For details, see Adding a Default View Security Specification.
5. See Configuring Your System to Enable View Security, and follow the instructions.