Coding Each AOF Rule Type › Security Rules › AOF Variables Available in an SEC Rule › OPAU Variables for SQL Security Events

OPAU Variables for SQL Security Events

The Relational Data Framework, System State Manager, and all other SQL-related components of CA OPS/MVS use the standard CA OPS/MVS security exit and AOF security rules. The following variables are available in security rules:

SEC.AUSQCAID

A single-character code that represents the environment where the SQL request originated

Data Type: Character, read-only

Source: The environment from which the SQL request originated

Possible Values:

• A-ADDRESS SQL (OPS/REXX)
• C-TSO/E CLIST
• D-Remote Automate system
• E-RDF table editor
• O-OPSQL command processor (or SQL alias)
• R-TSO/E REXX
• T-OPSSMTBL command processor (or STATETBL alias)

Sample Value: A

SEC.AUSQFTXT

The complete text of the SQL statement. The maximum size of an SQL statement in CA OPS/MVS is currently 2048 characters.

Data Type: Character, read-only

Source: The SQL statement

Sample Value: SELECT * FROM STCTBL WHERE NAME=‘VTAM’

SEC.AUSQFUCD

The function code returned by the OPSQL command processor. The function code should always be S for SQL.

Data Type: Character, read-only

SEC.AUSQHVCN

The count of host variable references that occur within the SQL statement. This variable can be used as a loop count limit for reading host variable names and values using the SEC.AUSQHVIX facility for obtaining a specific host variable name and value.

Data Type: Integer, read-only

Source: The SQL statement control block created by the SQL syntax compiler.

Sample Value: 3

SEC.AUSQHVIX

The host variable reference sequence number for obtaining a host variable name or value using the SEC.AUSQHVNA and SEC.AUSQHVVL variables. The maximum usable value is the value of SEC.AUSQHVCN.

The initial value is 0.

Data Type: Integer, read/write

Source: Set by user for host variable name and value retrieval

Sample Value: 2

SEC.AUSQHVNA

The host variable name of the host variable pointed to by the SEC.AUSQHVIX index number. The SQL syntax compiler creates a table of host variable names in the order they occur in the statement. The index number points to the name entry to retrieve.

Data Type: Character, read-only

Source: The SQL statement control block created by the SQL syntax compiler.

Sample Value: VNAME

SEC.AUSQHVVL

The host variable value of the host variable pointed to by the SEC.AUSQHVIX index number. Values for host variables are provided for host variable names in the SQL statement in a separate table at the time that the SQL statement is executed. In an SQL cursor statement, the values are supplied when the OPEN cursor statement is executed. The DECLARE cursor statement has no values for the host variables in the SQL statement.

Data Type: Character/Numeric, read-only

Source: The SQL host variable input value table (VIL) provided at SQL statement execution.

Sample Value: CICSAOR

SEC.AUSQRQTY

The SQL request type.

Data Type: Character, read-only.

Source: The SQL verb of the SQL statement.

Possible Values:

• A-Access a SQL table
• U-Update a SQL table

Sample Value: A

SEC.AUSQSMTY

The SQL statement type.

Data Type: Character, read-only.

Source: The SQL verb of the SQL statement.

Possible Values:

• CA-Alter table add column; AUSQRQTY=U
• CD-Alter table drop column; AUSQRQTY=U
• CL-Close cursor; AUSQRQTY=A
• CT-Create table; AUSQRQTY=U
• DC-Declare cursor; AUSQRQTY=A
• DE-Delete rows; AUSQRQTY=U
• DT-Drop table; AUSQRQTY=U
• FE-Fetch rows; AUSQRQTY=A
• IN-Insert row; AUSQRQTY=U
• OP-Open cursor; AUSQRQTY=A
• SE-Select rows; AUSQRQTY=A
• UP-Update rows; AUSQRQTY=U

Sample Value: SE

SEC.AUSQSQST

The first 128 characters of an SQL statement

Data Type: Character, read-only

Source: The SQL statement

Sample Value: DROP TABLE TABLE

SEC.AUSQSYNA

The list of system names to which the request is being sent. If the request is being sent to the local system, it may be empty or it may contain the words ALL or EXT.

Data Type: Character, read-only

Source: The system keyword on the SQL request

Sample Value: SYSA

SEC.AUSQTBLS

A word delimited list of table names referenced in the SQL statement. Host variable table names are resolved to their values. Most simple SQL statements have only one table reference that is usually a literal value. More complex SQL statements such as joins and sub-select clauses will reference more than one table.

Data Type: Character, read-only

Source: The SQL statement control block created by the SQL syntax compiler and the host variable value table.

Sample Value: STCTBL DB2TBL

Example: Read Host Variable Names and Values into Stem Variable Array

The following sample code shows how all the host variable names and values in a SQL statement can be read into a stem variable array for use in:

• Security authorization decisions
• Monitoring of critical table changes from sources outside the OPS/MVS address space.

The host variable names can also be created as simple REXX variables with the correct values.

hvnam.0=SEC.AUSQHVCN             /* Count of host variables */ 
Do ix=1 To hvnam.0               /* Find all the host variables */ 
   SEC.AUSQHVIX=ix               /* Set the host variable index */ 
   hvnam.ix=SEC.AUSQHVNA         /* Get the host variable name */ 
   hvval.ix=SEC.AUSQHVVL         /* Get the host variable value */
   vrc=VALUE(hvnam.ix,hvval.ix)  /* Create the Rexx variable */
End