Creating the Database Security Scheme

In addition to the security provided by the operating system and the security inherent in special authorities, which you grant at the DBEnvironment level, you can create a security scheme for individual databases by granting and revoking authorities or by creating view definitions. The next sections describe how to create a database security scheme.

Controlling Table Access with Authorities

You create a security scheme to protect the tables in each database by granting specific authorities to individual DBEUserIDs or groups and revoking them from other DBEUserIDs or groups. Table 5-1 describes the types of table and view authorities.

Table 5-1 Table and View Authorities

Type	Description
ALTER	Lets a user or group add columns to a table. ALTER authority does not apply to views.
DELETE	Lets a user or group delete rows from a table or view. DELETE authority can be granted on any view, but rows cannot be deleted unless the view meets certain criteria. See the section "Designing Views" in the "Logical Design" chapter.
INDEX	Lets a user or group create and drop indexes for a table. INDEX authority does not apply to views.
INSERT	Lets a user or group insert rows into a table or view. INSERT authority can be granted on any view, but rows cannot be inserted unless the view meets certain criteria. See the section "Designing Views" in the "Logical Design" chapter.
SELECT	Lets a user or group look at a table or view and create views on a table or view.
UPDATE	Lets a user or group update all columns in the table or view. UPDATE authority on specific columns lets a user or group update only those columns. UPDATE authority can be granted on any view, but columns cannot be updated unless the view meets certain criteria. See the section "Designing Views" in the "Logical Design" chapter.
REFERENCES	Lets a user or group define referential constraints on all columns in the table. REFERENCES authority on specific columns lets a user or group define referential constraints on only those columns. REFERENCES authority cannot be granted on views.

The owner of a table or view has all table and view authorities for that table or view.

Table and view authorities cannot be revoked from the owner of a table or view; however, ownership can be transferred. When ownership is transferred from a user, that user no longer has any authorities for the table or view unless they are explicitly granted again. You can grant all table and view authorities to a user with the ALL option of the GRANT statement:

   isql=>  GRANT ALL ON PurchDB.Parts TO Peter@Crane;

Authorities for Single Users

The following examples grant and revoke table and view authorities for a single DBEUserID:

   isql=>  GRANT SELECT ON PurchDB.Inventory TO Tom@Ash;

   isql=>  REVOKE SELECT ON PurchDB.Inventory FROM Tom@Ash;

Authorities for Groups

You can also assign authorities to groups. Create a group as in the following example:

   isql=>  CREATE GROUP Purchasing;

Next, add members to the group, as follows:

   isql=>  ADD AJ@BROWN TO GROUP Purchasing;

Finally, assign the appropriate table authorities to the group:

   isql=>  GRANT SELECT, INSERT, DELETE, UPDATE

   > ON PurchDB.OrderItems TO Purchasing;

Note that the creator of a group does not receive the authorities that are assigned to the group by other users. A user with authorization through group membership cannot issue the WITH GRANT OPTION clause of the GRANT statement.

Creating Classes

If you want to create objects that do not have users or groups as owners (and are therefore controlled solely by the DBA), use class ownership. Choose an appropriate class name, using the guidelines in the "Logical Design" chapter, then do one of the following to create the class:

create a table or view with the class name as the owner name
preprocess an application with the class name as owner name
transfer ownership of an object to the class name

For example, the sample DBEnvironment contains several tables owned by the class PurchDB. The table PurchDB.Parts was created with the following statement:

   isql=> CREATE TABLE PurchDB.Parts

          (PartNumber    CHAR(16)  NOT NULL,

          PartName       CHAR(30),

          SalesPrice     DECIMAL(10,2))

          IN WarehFS;

To create a module belonging to a class, specify the class name as the owner in parenthesis in the preprocessor command line:

   : RUN PSQLPAS.PUB.SYS;INFO="(PartsDBE OWNER(PurchDB))"

After creating objects owned by the class, you must grant the specific authorities you wish users or groups to have. Suppose there is a group PurStaff, consisting of DBEUserIDs for members of the Purchasing Department. You could grant authorities to the group with the following statements:

   isql=>  GRANT SELECT, UPDATE ON PurchDB.Parts TO PurStaff;

   isql=>  GRANT RUN ON PurchDB.Program TO PurStaff;

Revoking Table and View Authorities

When you revoke a user's authority or remove a user from a group, that user can no longer perform the functions allowed by that authority. However, any other authorities granted to that user are not affected. By removing AJ@Brown, who is not a DBA, from the PurStaff group, you do not affect his authorities associated with the Receiving group. Also, you do not affect any authorities that AJ@Brown may have granted, as a member of the group, to other users.

Controlling Table Access with Views

The SELECT, INSERT, and DELETE authorities operate at the row level. The UPDATE authority operates at either the row or column level. To restrict SELECT, INSERT, or DELETE authority to certain columns, or to restrict UPDATE authority to certain columns and rows of a table, you can create a view and grant the required authorities on the view.

Assume you have a table containing rows for several departments, but you only want the manager for a particular department to be able to access data for that department. To accomplish this you create a view with a WHERE clause defining only those rows to be accessed, and grant authorities on the views to the appropriate managers. The statement to create the view in Figure 5-1 is:

   isql=>  CREATE VIEW PurchDB.Dept100

   >AS SELECT * FROM PurchDB.Dept WHERE DeptNo=100;

Figure 5-1 Views Restricting Access

The manager for department 100 is Tom@Ash. The statement to grant authorities for department 100 data is:

   isql=>  GRANT SELECT,INSERT,DELETE,UPDATE

   > ON PurchDB.Dept100 TO Tom@Ash;

The example in Figure 5-1 restricts access to certain rows, but you can also restrict access to specific columns by eliminating sensitive columns from the view definition. For example, you might wish to eliminate the salary column from a view of a department's personnel. Remember, however, that if you want to update the base tables through a view, you must include in the view definition all base table columns that were created with the NOT NULL option. Other restrictions apply to using the INSERT, UPDATE, and DELETE authorities on views. Refer to "Designing Views" in the "Logical Design" chapter of this guide.

ALLBASE/SQL uses views to restrict access to the system catalog tables. The information that is used internally is critical to ALLBASE/SQL operations and should never be modified. Therefore, views are created on the system tables and SELECT authority is initially given to users with DBA authority. The base tables cannot be accessed by any user.

Using the GRANT OPTION Clause

A grantable privilege is a privilege obtained as a result of a grant given with the GRANT OPTION. The DBA, the owner of a table or view, or users with a grantable privilege can give a grantee a table or view privilege and, if their authority is direct (not through group membership), the ability to grant that same privilege to other users.

To revoke a grantable privilege from a user and revoke the chain of grants that may have been created by the user with the grantable privilege, use the REVOKE statement with the CASCADE option. Because a privilege cannot be revoked from the DBA or owner, cascading does not continue past that user. Therefore, a DBA or owner should not be included in the chain of grants. The DBA need not use the CASCADE option; but if it is not used, and a chain of grants exists, then an orphaned privilege is created. Orphaned privileges are discussed later in this chapter.

In this example the owner of a table grants grantable privileges to two managers:

Owner:

   isql=>  GRANT ALL on PurchDB.Parts

   > TO MGR1@DBMS, MGR2@DBMS WITH GRANT OPTION;

The managers grant a grantable select privilege to their employees:

MGR1@DBMS:

   isql=>  GRANT SELECT ON PurchDB.Parts

   > TO EMP11@DBMS WITH GRANT OPTION;

MGR2@DBMS:

   isql=>  GRANT SELECT ON PurchDB.Parts

   > TO EMP21@DBMS WITH GRANT OPTION;

The employees authorize their co-workers to also have and grant SELECT authority:

EMP11@DBMS:

   isql=>  GRANT SELECT ON PurchDB.Parts

   > TO EMP12@DBMS WITH GRANT OPTION;



   EMP21@DBMS:



   isql=>GRANT SELECT ON PurchDB.Parts

   > TO EMP22@DBMS WITH GRANT OPTION;

The security scheme would look like this:

Figure 5-2 Example Database Security Scheme

If the table owner wants to revoke privileges from everyone, it is a simple procedure:

   isql=>  REVOKE ALL on PurchDB.Parts

   > FROM MGR1@DBMS, MGR2@DBMS CASCADE;

However, if EMP21@DBMS is a DBA, the cascading stops on that chain at EMP21@DBMS, and EMP22@DBMS retains the grantable select privilege. Generally, a user would not grant authority to a DBA or owner, because he or she has it already; however, EMP21@DBMS could have been given DBA authority or had ownership transferred to him or her after the chain of grants was established.

If a DBA revokes SELECT authority without specifying the CASCADE option from EMP11@DBMS, then EMP12@DBMS is left with an orphaned privilege.

Orphaned Privileges

An orphaned privilege is one that was received from a grantor who no longer has authorization to grant or revoke that privilege. Orphaned privileges are created in the following ways:

When a DBA revokes a privilege, without the CASCADE option, from a user who has granted privileges to others.
When the DBA uses the BY clause of the GRANT statement to name a grantor who does not have the privilege to grant or revoke a privilege.
When a member of a group grants a privilege but is then removed from the group.




	NOTE: Avoid orphaned privileges. Orphaned privileges make it impossible to use cascading to revoke a chain of grants.

An orphaned privilege exists if the grantor is not one of the following:

Owner of the object
A member of a group with OWNER authority
A user with DBA authority
A grantee with the right to grant appropriate authority

The ways to eliminate an orphaned privilege are:

The owner of the object can grant the privilege with the WITH GRANT OPTION clause to recreate the authorization that was destroyed.
The DBA can grant the privilege with the WITH GRANT OPTION clause and use the BY option to name a grantor who is authorized to grant and revoke privileges.

Using the WITH GRANT OPTION Clause and Authorization Groups

There are two approaches to granting authorities:

Creating authorization groups. With this approach, authority is granted to an authorization group where members are added or removed from the group.
Creating a chain of grants. With this approach, the WITH GRANT OPTION clause is used to grant authorities to individual users or classes, resulting in a chain of grants.

Mixing the two has complex effects, and in some cases is not allowed; we suggest that you do not mix them. For example, if you are using the WITH GRANT OPTION clause in an environment that uses group authorizations, be aware that removing a member from a group does not necessarily mean that member no longer has access to a table. The user could have been granted a privilege from another user via a grantable privilege.

If there are breaks in the chain of grants, it is difficult to maintain the security scheme. Breaks can occur in the following ways:

When cascading stops because a DBA or owner is included in the chain of grants.
When an orphaned privilege is created.

If you need to know the state of your security scheme, query the following system views:

SYSTEM.TABAUTH
SYSTEM.COLAUTH
SYSTEM.SPECAUTH
SYSTEM.GROUP
SYSTEM.SPACEAUTH