Securing Database Resources › SQL Security Enforcement › Security Checking for Precompiled SQL Statements

Security Checking for Precompiled SQL Statements

Precompiled SQL Statements

User-written programs or SQL routines may contain embedded SQL statements that are precompiled and included in an access module prior to runtime. Security checking for embedded SQL statements is performed in one of two ways, depending on how security on the database being accessed is controlled.

External Security

If external security is in effect for the database, dynamic security checking is performed on all SQL statements, precompiled or not. When the SQL session is started, a security check determines if external security is in force for the database to which the session is connected. If so, this information is cached for the duration of the session.

CA IDMS issues the security checks as it executes each statement and caches the information for the life of the database transaction or task. The name of the access module is passed as part of the security check and is used as an authorized program filter.

This method of security checking for SQL statements complies with government requirements.

CA IDMS Internal Security

If CA IDMS internal security is in effect for the database, security checking for precompiled SQL statements takes a pre-authorized approach that requires the owner of the access module to hold all privileges necessary to execute every SQL statement in the module. For example, the owner must hold the appropriate table access privilege for each table accessed by an SQL statement in the module.

If this condition is met, then the owner of the access module can execute it. The owner can give execution privilege on the access module to other users if the owner holds the necessary grantable privileges.

Advantages of the Pre-authorized Approach

The pre-authorized security approach for SQL statements minimizes the overhead of security checking at runtime.

It also eliminates the need to grant all users the privileges needed to execute the SQL statements in the access module. Only the owner must have those privileges; other users simply require execution privilege on the access module. This means that executing the program is the only way the users can access the resources because they hold no privileges independent of the access module.

The pre-authorized security approach for SQL statements complies with the ANSI SQL standard.