Securing Database Resources › SQL Security Enforcement › Securing Views

Securing Views

Views in Security Strategy

There are special security considerations associated with creating and accessing views. A view is a logical table derived from one or more base tables, table procedures or views. You can use a view to restrict a user's access to specific columns and rows of the underlying tables.

The benefit of views from a security perspective is that you can give users access to the view without giving them equivalent privileges on the underlying tables.

Checking of view privileges is performed at runtime when either of these cases occurs:

• A dynamic SQL statement is executed (the result of the check is cached until end of transaction).
• A new copy of an access module is physically loaded (the result of the check is cached until another copy is loaded or the system is recycled).

In either case, the actual checks made are identical except for the authorization ID used for checking access to the view:

• The executing user, when statements are executed dynamically.
• The owner of the access module, when statements are precompiled.

View ownership

The method used by CA IDMS internal security to secure views employs information about view ownership. A table-like object, such as a view, is owned by the owner of the schema with which it is associated.

When CA IDMS internal security is in effect for views, a user gains access to the view only if:

• The user holds SELECT privilege on the view.
• The owner of the view holds grantable SELECT privilege on each table-like object referenced by the owner's view.

This approach to view security complies with ANSI standards for SQL.

View example

In the view depicted by the following chart, SCHEMA_A.V1 is owned by user JOHN and references table SCHEMA_B.T2 owned by JANE and view SCHEMA_C.V2 owned by MARY. View V2 in turn references table SCHEMA_D.T3 owned by MIKE.

          ┌──────────────┐
          │ SCHEMA_A.V1  │
          │ (owner JOHN) │
          └──────┬───────┘
                 │
       ┌─────────┴───────────┐
       │                     │
┌──────┴────────┐      ┌─────┴────────┐
│  SCHEMA_B.T2  │      │ SCHEMA_C.V2  │
│  (owner JANE) │      │ (owner MARY) │
└───────────────┘      └─────┬────────┘
                             │
                       ┌─────┴────────┐
                       │ SCHEMA_D.T3  │
                       │ (owner MIKE) │
                       └──────────────┘

What CA IDMS Internal Security Checks

Using the preceding view example, assume that user FRED is issuing a dynamic SELECT against view V1. The security system will check to see the following:

• FRED, the executing user, holds SELECT privilege on view V1
• JOHN, the owner of view V1 holds:
  • Grantable SELECT privilege on table T2
  • Grantable SELECT privilege on view V2
• MARY, the owner of view V2 holds grantable SELECT privilege on table T3

The security system uses the schema name qualifier of the table-like object to determine the authorization ID to be checked. The authorization ID of the schema owner is stored in a row of the SYSTEM RESOURCEGROUP table. For example, to determine security requirements on view SCHEMA_A.V1, CA IDMS in effect asks the security system to check whether the owner of SCHEMA_A has grantable SELECT privilege on table T2 and view V2.

External Security Enforcement for Views

If external security is in effect, only the executing user's privilege to access the view is checked. Neither owner privileges nor authorities to access base tables are checked.