SQL security exits
Overview
The SQL security exit feature allows you to replace the security provided by the Model 204 SQL catalog (CCACAT) with the security of your external package to control the use of SQL data definition language (DDL) and access to SQL objects via data manipulation language (DML) statements. Instead of using CCACAT to check privileges for DDL usage or SQL object access, you can write external security exits, called DDLPRIV and DMLPRIV, that are called every time a user tries to access the Model 204 SQL catalog.
The DDLPRIV and DMLPRIV SQL security exits are called only if an external security package is specified as being active for SQL. Otherwise, standard Model 204 SQL privilege checking is performed; Model 204 never performs both types of checking.
See DDLPRIV exit and DMLPRIV exit for more information.
Requirements
To use the SQL security exit feature, your site must use the Model 204 SQL product, Connect★, and one of the external security packages discussed earlier in this manual.
Security exit functions
The SQL security exit feature is designed to:
- Check access privileges down to the column level.
- Recognize all the standard types of SQL access (SELECT, INSERT, DELETE, UPDATE).
-
Control a user’s access to all DDL statements via the security package already in place at your site.
If access to a DDL or DML statement is rejected by the security exit, Model 204 terminates the request (the processing of an SQL statement) but not the user’s session.
Your role as security officer
As the security officer for your site, you can write two SQL security exits: one for DDL statements, DDLPRIV, and one for DML statements, DMLPRIV. Within the security exits you need to:
- Determine what rules to use for your site.
- Set up any necessary parameters for the appropriate validation request to the external security package.
- Return back to the Model 204 SQL Server the results of the security validation request so that Model 204 can either allow processing of the user’s Model 204 request or terminate the request with an error message.
SQL security exit restrictions
SQL security exits must be written in IBM 370 Assembly language and use Model 204 register conventions and the Model 204 Assembly macros for subroutine entry and exit.
FIPS compliance
If your site is using a security exit, Model 204 SQL does not comply with Federal Information Processing Standard (FIPS) 127-1 (level 2 of ANSI X3.135-1989) for SQL. However, if you are not using a security exit, Model 204 SQL continues to comply with the standard.
Register conventions for IBM and Model 204
The following table lists IBM and Model 204 register conventions, which you might need to know when writing security exits.
| IBM register (REG) name | Model 204 register name |
|---|---|
| 0 | X0 |
| 1 | X1 |
| 2 | T1 |
| 3 | T2 |
| 4 | T3 |
| 5 | T4 |
| 6 | T5 |
| 7 | RC |
| 8 | R6 |
| 9 | R5 |
| 10 (X‘a’) | R4 |
| 11 (X‘b’) | R3 |
| 12 (X‘c’) | R2 |
| 13 (X‘d’) | R1 |
| 14 (X‘e’) | PD |
| 15 (X‘f’) | RL |
When writing a security exit, do not use the CSAVE and CRSTR macros, which save and restore (IBM) Registers 13, 14, and 15. The security exits use IBM Register 13 as a work register to pass the return code back to the caller.
Using the CDTB module
When you write SQL security exit routines, store them in the Model 204 CDTB module. If your site chooses to use an external security manager (Security Server (formerly RACF), CA-Top Secret, or CA-ACF2) and SQL security exits to replace Model 204’s SQL security, you are responsible for managing this module and maintaining its contents. Information about managing the CDTB module is available in the Rocket Model 204 Installation Guide for your operating system.
Within the CDTB module, Model 204 supplies the dummy SQL security exit routines DDLPRIV and DMLPRIV. The combination of these two routines provides a model for the SQL security exits. That is, Model 204 passes all necessary information to the SQL security exit so that your exit can issue the appropriate validation requests to the external security manager (ESM).
The dummy routines contained in the module serve the following purposes:
- Prevent unresolved references when the Model 204 Online is linked. (You can also avoid unresolved references by defining the routine names as weak external symbols using the WXTRN instruction.)
-
Include the Assembler code required to accept the necessary parameter values passed by Model 204 SQL and return an appropriate return code to Model 204 based on the validation request of the external security manager.
Note: The dummy exits supplied by Model 204 are written with a return code of 9, which indicates that the exits are not active. For more information about the available return codes, see Return codes for DDLPRIV and DMLPRIV security exits.
- Help to illustrate the use of Model 204 register conventions and the ENTER and EXIT macros used in Model 204 subroutines.
Even if your site does not use an external security interface, you need to link CDTB into your Model 204 Online to avoid unresolved references.
DDLPRIV exit
Currently Model 204’s SQL processor checks a user’s privileges to access SQL objects when compiling an SQL query for that user. To check privileges, the compiler calls a dictionary routine that has access to privilege data in CCACAT. You can instead use the DDLPRIV exit to check DDL statements before processing.
One kind of privilege checking used in DDL processing is to make sure that the user has Model 204 system manager privileges and that the user’s Model 204 user ID (called the authorization ID in SQL) matches the user ID associated with the SQL object that the DDL statement is acting upon.
When checking system manager’s privileges for, for example, CA-Top Secret, the user ID corresponds to an accessor ID (ACID), and the DDL statement corresponds to a nonstandard resource. The SQL security exit does not check for Model 204 system manager privileges as such, but checks for the set of privileges that you create, corresponding to a system manager’s role.
In a different example, a user named JOAN creates a schema and another user, ADRIAN, attempts to drop it. ADRIAN is not be allowed to do so, because the user ID associated with the schema (JOAN) does not match the user ID of the user trying to drop the schema (ADRIAN).
Security for DDL statements
All DDL statements in Model 204’s SQL processing are protected by some level of security. The DDL statements supported in Connect★ are:
- GRANT
- REVOKE
- CREATE SCHEMA
- CREATE VIEW
- CREATE TABLE
- DROP SCHEMA
- DROP VIEW
- DROP TABLE
- ALTER TABLE
- SET SCHEMA
- SET USER
Parameters passed to SQL
The parameters passed to DDLPRIV from Model 204 are:
DDLPRIV userid, authid, ddl_statement, PRIVOUT
Where:
| userid | Is the address of a character string containing the user’s Model 204 user ID. The string is terminated with a binary zero. The userid parameter is passed in T1. |
| authid | Is the address of a character string containing the Model 204 user ID associated with the SQL object being operated on by the DDL statement. The string is terminated with a binary zero. The authid parameter is passed in T2. |
| ddl_statement |
Is an integer code describing the type of DDL statement. The ddl_statement parameter is passed in T3. The codes are listed in the following table. |
| PRIVOUT |
Is the return code from the call passed back to Model 204 in R1. The return codes are described in Return codes for DDLPRIV and DMLPRIV security exits. |
| Statement code | DDL statement type |
|---|---|
| 40 | CREATE SCHEMA |
| 41 | CREATE VIEW |
| 42 | CREATE TABLE |
| 53 | SET USER |
| 54 | SET SCHEMA |
| 55 | GRANT |
| 56 | REVOKE |
| 57 | DROP SCHEMA |
| 58 | DROP TABLE |
| 59 | DROP VIEW |
| 81 | ALTER TABLE |
Disallowing access to SQL statements
If your site does not, for example, allow users access to GRANT and REVOKE statements, you can code DDLPRIV to return an error return code without invoking any security package services. You can similarly code any other DDL statement that you do not want to make available to your users.
CREATE VIEW checking
To use the CREATE VIEW statement, the user must have both the correct privileges to use the statement and at least SELECT access to the SQL objects referenced in the VIEW.
In this case Model 204 calls DDLPRIV to check the user’s DDL privilege, and then calls DMLPRIV an appropriate number of times to validate the user’s access to SQL objects.
DMLPRIV exit
When compiling an SQL query for a user, the Model 204 SQL processor checks the user’s privileges to access SQL objects. To check privileges, the compiler calls a dictionary routine that has access to privilege data in CCACAT. The arguments to the routine are the user’s ID, the full name of the SQL object, and the type of access the user requires to run the query.
You can also use the DMLPRIV security exit to check user privileges. The types of SQL objects for which privileges are checked are TABLE, VIEW, and COLUMN. The types of access for which privilege checking is done are:
- INSERT
- UPDATE
- SELECT
- DELETE
External security packages do not have access privileges named SELECT, INSERT, UPDATE, or DELETE. Rocket Software, therefore, suggests that you define your SQL objects as pseudo data set names and then map the SQL privileges to those of the external security package. For appropriate access codes to use when validating pseudo data set names, see Access codes and pseudo data set names for SQL privileges.
Column level checking
SQL UPDATE access type checking can be done at the individual column level. For UPDATE, DMLPRIV has an additional argument that is an array of column names.
If the user does not have the appropriate privileges required by the query, then further processing of the query is terminated and Model 204 issues an error message.
View checking
A VIEW is regarded as an independent SQL object for the purposes of checking access privileges. Once a user’s access to a view has been established, no further check of the objects that compose the view is made. That is, for DML, no differentiation is made between a view or a base table.
Parameters passed to SQL
The parameters passed to DMLPRIV from Model 204 are:
DMLPRIV userid, schema, table, cols, priv, retcode
where:
| userid | Is the address of a character string containing the user’s Model 204 user ID. The string is terminated with a binary zero. The userid parameter is passed in T1. |
| schema | Is the address of a character string containing the schema name. The string is terminated with a binary zero. The schema parameter is required and is passed in T2. |
| table | Is the address of a character string containing the view or table name. The string is terminated with a binary zero. The table parameter is passed in T3. The schema and table names cannot be concatenated, because the length of the combined name might be greater than your security package can tolerate for a resource name. Specifying the schema and table names separately also provides greater flexibility in defining the rules for your site. |
| cols | Points to an array of column names (that is, cols is a pointer to a list of pointers). The array is terminated by a binary zero. The cols parameter is passed in T4. If privilege checking is not required at the column level, this is a null value. |
| priv |
Is an integer code describing the type of access. The priv parameter is passed in T5. The access codes are listed in the following table. |
| retcode | Is the return code from the call passed back to Model 204 in R1. |
| Access code | SQL privileges | CA-ACF2 | Security Server | CA-Top Secret |
|---|---|---|---|---|
| 0 | SELECT | execute | execute | execute |
| 1 | INSERT | read | read | read |
| 2 | UPDATE | write | write | write |
| 3 | DELETE | allocate | alter | create |
Return codes for DDLPRIV and DMLPRIV security exits
The following table lists the values of and meanings of return codes for DDLPRIV and DMLPRIV security exits.
If, for example, your site does not use the AUTHID in the rules for checking DDL privileges, you can ignore the parameter in your SQL security exit. As a result, DDLPRIV never returns a return code of 8.
| Value | Meaning |
|---|---|
| 0 | Success. The DDL or DML statement will be executed. |
| 1 | The user’s privileges are not sufficient. |
| 2 | Model 204 does not recognize the user ID. |
| 3 | Model 204 does not recognize the access type. |
| 4 | Model 204 does not recognize the schema name. |
| 5 | Model 204 does not recognize the table name. |
| 6 | Model 204 does not recognize the column name. |
| 7 | Model 204 does not recognize the DDL statement type. |
| 8 | Model 204 does not recognize the AUTHID. |
| 9 | User exits are inactive: use normal security. |
Installing the SQL security exits
DMLPRIV and DDLPRIV must be included as part of the Model 204 CDTB module.
You can then assemble and link CDTB following the instructions in the Rocket Model 204 Installation Guide for your operating system.