Defining Security Levels

In ALLBASE/SQL, you create security controls by granting authorities to specific users or groups. An authority is a privilege that enables a user to access the DBEnvironment, create database objects, use SQL statements, preprocess and run application programs containing SQL statements, or maintain the DBEnvironment. By selectively granting and revoking authorities, the DBA can control access to the DBEnvironment as well as to the tables, views, columns, groups, and modules within the DBEnvironment.

You can grant authorities to one of four entities:

A DBEUserID
An authorization group
A class
PUBLIC

A DBEUserID is made up of a user's MPE/iX user and account names connected with the @ symbol. An example is Wolfgang@DBMS, where Wolfgang is the user name, and DBMS is the account name. An authorization group is a named collection of users sharing the same authorities. A class is an entity which may own objects that should not belong to any individual or group.

PUBLIC is a special, nonrestrictive category of user. By granting an authority to PUBLIC, you implicitly grant that authority to any user who has CONNECT authority to the DBEnvironment.

Authority Types

There are five basic types of authority in a security design:

Special authorities
OWNER authority
Table and view authorities
RUN authority
EXECUTE authority
Space authorities

Except for DBA, RESOURCE, and OWNER authorities, ALLBASE/SQL authorities are discrete, not hierarchical; for example, granting CONNECT authority does not automatically grant RUN authority. This relationship is shown in this figure.

Figure 2-8 Relationship among Authorities





               DBA

                | 

     -----------------------------------------------------------------

     |        |     |       |         |           |         |        |

   SECTION-  DBA CONNECT  TABLE-   RESOURCE.....OWNER  MONITOR INSTALL

   SPACE      |           SPACE                   |

             etc.                        ------------------

                                         |        |       |

                                      EXECUTE    RUN    ALTER

                                                        DELETE

                                                        INDEX

                                                        INSERT

                                                        SELECT

                                                        UPDATE

                                                        REFERENCES



       .......implicit grant

       -------explicit grant

        

      OWNER authority can be transferred

Special Authorities

The following authorities are special authorities:

DBA authority
RESOURCE authority
CONNECT authority
MONITOR authority
INSTALL authority

A user with DBA authority has all other authorities plus some special capabilities for DBEnvironment maintenance. A user with DBA authority shares OWNER authority for all objects in the DBEnvironment. DBA authority is required to grant or revoke any of the special authorities.

A user with RESOURCE authority can create tables and authorization groups. The user becomes the owner of the table or group. That user has OWNER authority for the object until ownership is transferred to another user.

A user with CONNECT authority can connect to the DBEnvironment.

A user with MONITOR authority can run SQLMON on the DBEnvironment.

A user with INSTALL authority can install stored sections owned by another user, class, or group.

Owner Authority

The following objects can have owners:

Tables
Views
Modules
Procedures
Authorization groups

The owner of an object can drop that object. The owner of a table, view, module, or procedure can grant authorities for that table, view, module, or procedure to other users. The owner of a module has RUN authority for that module plus the capability to re-preprocess the module. The owner of an authorization group can add members to the group, remove members from the group, or drop the group.

The name of an authorization group must be unique within the DBEnvironment. There cannot be either another owner or another authorization group with the same name on the system.

OWNER authority for a table or view implies all table and view authorities. Even a user with DBA authority can neither revoke table and view authorities from the owner of a table or view, nor revoke RUN authority from the owner of a module. OWNER authority cannot be granted or revoked, but it can be acquired during object creation or transferred to another owner by either the current owner or by a user with DBA authority. If ownership is transferred, the original user no longer has any authorities to access the object unless explicitly granted.

The owner of an object can be an individual user, an authorization group, or a class. OWNER authority is obtained in one of the following ways:

By creating an object. RESOURCE authority is required for users to be able to create ownable objects. Only a user with DBA authority can create an object and assign another individual user, a group, or a class name as the object's owner.
By having DBA authority. All users with DBA authority share ownership of all objects in the DBEnvironment with the actual owners. Users with DBA authority are also exclusive owners of objects owned by authorization groups and classes.
By transferring ownership. Ownership can be transferred from any name to any other name by either the current owner of the object or by a user with DBA authority.

Table and View Authorities

Table and view authorities, listed below, determine which users or groups may have access to the columns of tables for specific tasks, such as selecting, updating, deleting, inserting, or indexing:

ALTER
DELETE
INDEX
INSERT
SELECT
UPDATE
REFERENCES

Table and view authorities are described fully in chapter 5.

RUN Authority

RUN authority determines who has access to specific preprocessed application modules stored in the DBEnvironment. RUN authority is further described in the "Maintenance" chapter.

EXECUTE Authority

EXECUTE authority determines who has access to specific procedures stored in the DBEnvironment. EXECUTE authority is further described in the "Maintenance" chapter.

Space Authorities

The following authorities are space authorities:

SECTIONSPACE
TABLESPACE

A user with SECTIONSPACE authority can store sections in the specified DBEFileSet. If the user does not have SECTIONSPACE authority, the default SECTIONSPACE DBEFileSet is used instead, even if the user has DBA authority.

A user with TABLESPACE authority can store table and long column data in the specified DBEFileSet. If the user does not have TABLESPACE authority, the default TABLESPACE DBEFileSet is used instead, even if the user has DBA authority.

For more information, refer to "Parameters—Grant DBEFileSet Authority" in the Grant section of the "SQL Statements" chapter of the ALLBASE/SQL Reference Manual.

Defining Authorization Groups

An authorization group can be created by any user with RESOURCE or DBA authority, and ownership can be transferred to any user. Authorization groups are created and dropped as objects, but have the attributes of a user because they can:

Be granted any or all authorities.
Have any or all of their authorities revoked.
Own tables, views, modules, and other authorization groups.

When an authorization group is created, its group name is entered into the SYSTEM.GROUP table in the system catalog (for more information, refer to the "System Catalog" chapter of this manual). You cannot create a group using a name that already exists as a DBEUserID or owner name in the DBEnvironment. Conversely, you cannot grant authorities to a valid DBEUserID if the name already exists as an authorization group or as an owner name in the DBEnvironment.

The following DBEUserIDs are reserved and cannot be added to a group or used as a group name:

HPRDBSS
PUBLIC
SEMIPERM
STOREDSECT
SYSTEM
TEMP

In addition, the DBECreator cannot be added to an authorization group.

Each member of the authorization group has the authorities granted to the group. The owner of the group, if not explicitly a member of the group, does not have any of the authorities that have been granted to the group.

After defining groups, you can then control authorities on a group basis instead of an individual user basis.

Determining Group Membership

Group membership is determined by common authority requirements. The DBEUserIDs used in the sample database belong to different departments within the Integrated Peripherals, Inc. organization. Each department or function requires a different set of authorities. Authorization groups are created for each function and DBEUserIDs are added accordingly.

A user can be a member of any number of authorization groups. An authorization group can itself be a member of other authorization groups. A member of an authorization group that is, in turn, a member of a second authorization group is known as an indirect member of the second authorization group. Indirect members of groups have the authorities granted to the group of which they are a direct member and the group of which they are an indirect member. In Figure 2-9, the valid chain shows Kelly as a direct member of GroupA and an indirect member of GroupD and GroupE. Kelly has the authorities granted to GroupA, GroupD, and GroupE.

Although there is no limit on the number of authorization groups that an authorization group can belong to indirectly, as illustrated in Figure 2-9 “Authorization Group Chain”, the chain cannot link back to itself.

Figure 2-9 Authorization Group Chain

Defining Classes

A class is a special category of owner that is neither a conventional DBEUserID nor a group. You may wish to assign ownership of objects to a class when you do not want any individual or group to have automatic access to them. With class ownership, the DBA controls all authorities, since objects that belong to a class can be created and maintained only by the DBA.

Strictly speaking, a class is an owner that does not have CONNECT authority. An example of this is an owner for which there is not a corresponding userid on the system and to which you have not granted CONNECT authority. For a class to be useful, its class name must be different from the name of any existing DBEUserID or group ID.

Differences Between Groups and Classes

You create a group explicitly by using the CREATE GROUP statement. You create a class implicitly by creating objects that have a class name as owner name.

A group has members, all of which have the same group privileges. For example, if user alex is a member of a group Sales then alex can drop or alter objects owned by Sales.

A class does not have members, nor can it use any authorities, though you can grant them if you wish. This can be useful in a scenario in which you want to pre-assign ownership of objects to a DBEUserID for which there is not yet a userid on your system.

As DBA, you retain all authorities over the objects owned by a class and must explicitly grant authority to any user of those objects.

Guidelines for Creating Classes

In designing a security scheme that includes classes, follow these guidelines:

Make sure that a new class name is different from any DBEUserID.
Make sure the class name is not the same as any group name defined in the DBEnvironment.
Choose a class name that reflects the concept behind the class, e.g., PurchDB for Purchasing Department Database.
Do not assign CONNECT authority to a class. Doing so converts the class into a DBEUserID with OWNER authority over objects in the class. This could create problems at a later time if a real userid were created that had the same name as the class.

As an exception to the last rule, you can safely grant CONNECT authority when you actually want the new DBEUserID to become the owner of the object once the correct userid is added to the system.