Previous Topic: Securing ViewsNext Topic: Securing SQL Access to Databases

Securing Database Resources › SQL Security Enforcement › Securing SQL routines

Securing SQL routines

SQL routines are SQL-invoked procedures or functions written in the SQL procedural language, that require a number of different objects to be built, processed and executed in the dictionary. The following table gives an overview of these objects showing the dictionary area containing the object, the dictionary entity, the entity subtype, and the name and description. The objects are created while processing the create command for an SQL routine.

Dictionary Area

Entity

Type

Name and Description

DDLCAT DDLCATX

TABLE

PROCEDURE or FUNCTION

A table-like object (procedure or function) contained in the routine schema of the SQL catalog

DDLCAT DDLCATX

TABLE

LOCAL VARIABLES OWNER

One to many table-like objects contained in the routine schema. The names are constructed from ExternalName and nnnn, a serial number starting with 0000 up to n, the number of compound statements in the routine + 1, as follows: SQLLOCnnnnExternalName (1)

DDLDML

MODULE

PROCESS

An IDD process module that contains the CA ADS source code, generated for the SQL routine. The name is constructed from ExternalName, as follows: PREMAP-ExternalName (1)

DDLDCLOD

LOAD MODULE

RCM

The RCM (Resource Control Module) module associated with the SQL routine. The name of the RCM is given by External Name (2)

DDLDCLOD

LOAD MODULE

DIALOG

The dialog module associated with the SQL routine. The name of the dialog is given by ExternalName

DDLCAT DDLCATX

AM

AM

The AM (Access Module) contained in the routine schema and associated with the SQL routine. The name of the AM is given by ExternalName (2)

DDLCATLOD

LOAD MODULE

AM

The AM (Access Module) load module associated with the SQL routine. The name of the AM is given by ExternalName.

(1) The object is not accessed when the SQL routine is invoked.

(2) The object is only accessed when the AM needs to be recompiled.

To invoke an SQL routine a user must have the SELECT privilege on the SQL procedure or function and the EXECUTE privilege on the AM associated with the SQL routine. If CA ADS run time security is implemented then additional privileges might be required to execute the CA ADS dialog associated with the SQL routine.

To create an SQL routine the user must have the CREATE and SELECT privileges on the SQL procedure or function and the CREATE, ALTER and DROP privileges on the AM associated with the SQL routine.

To drop an SQL routine the user must have the DROP privileges on the SQL procedure or function and the DROP privilege on the AM associated with the SQL routine.

Note: Dropping an SQL routine will remove all the associated dictionary objects. The optionally granted privileges will also have been removed, except for the privileges granted on the access module, which are preserved. A REVOKE command can be used to remove the privileges if needed.

How to Grant Privileges to Invoke an SQL Procedure

Assume the SQL procedure GET_EMPLOYEE contained in the SQL schema HR_APPL with an external name GETEMPL. needs to be called by users of the group HR_ DEP.

grant select on HR_APPL.GET_EMPLOYEE to HR_DEP;
grant execute on access module HR_APPL.GETEMPL to HR_DEP;

How to Grant Privileges to Define and Maintain an SQL Procedure

Assume the SQL procedure GET_EMPLOYEE contained in the SQL schema HR_APPL with an external name GETEMPL. needs to be defined and maintained by users of the group HR_DEV.

grant define on HR_DEP.GET_EMPLOYEE to HR_DEV;
grant select on HR_DEP.GET_EMPLOYEE to HR_DEV;
grant define on access module HR_DEP.GETEMPL to HR_DEV;
grant execute on access module HR_APPL.GETEMPL to HR_DEV;