Controlling Database Access

ALLBASE/SQL uses authorities to determine who can issue which SQL statements and who can execute programs that access databases in a DBEnvironment. For complete details about security schemes refer to the ALLBASE/SQL Database Administration Guide.

Authorities

ALLBASE/SQL has the following several kinds of authorities:

Table and view authorities are the following privileges used to access data in a specific table or through a specific view and to add columns and indexes, and create foreign keys referencing a specific table:
SELECT
retrieve rows
INSERT
insert rows
DELETE
delete rows
UPDATE
change one or more columns in a row
ALTER
add new columns to a table
INDEX
create an index for the table
REFERENCES
refer to one or more columns when defining a foreign key in a referencing table
RUN authority is the privilege to execute a specific program module that accesses a DBEnvironment.
EXECUTE
execute a procedure
Special authorities are the following privileges:
CONNECT
connect to a DBEnvironment
RESOURCE
create tables and authorization groups
DBA
issue all SQL statements and to execute any program that accesses an ALLBASE/SQL DBEnvironment
OWNER authority controls specific programs, tables, views, or authorization groups.

Obtaining Authorization

You obtain authority by the following methods:

Configuring a DBEnvironment and automatically becoming a DBA.
Being granted one or more specific authorities.
Owning a table, view, module, or group.

DBA Authority

When a DBEnvironment is configured, DBA authority is automatically given to the login name of the DBECreator.

A user with DBA authority (also referred to as the DBA) has extensive control over data in a DBEnvironment. The DBA can issue almost all the SQL statements and execute all the programs that access the DBEnvironment. The two SQL statements that only a DBECreator can issue are, START DBE NEWLOG and START DBE RECOVER. Some SQL statements only a DBA can issue. Most of these statements are DBEnvironment-wide in scope. For example, only DBAs can grant the special authorities (CONNECT, RESOURCE, and DBA) and define DBEFiles and DBEFileSets. In addition, only a DBA can issue statements that control objects owned by a class name; for example, only DBAs can drop or issue grants for a table owned by a class name.

Grants

All authorities except OWNER authority can be granted by using the GRANT statement. The GRANT statement gives authorities to individual users, to authorization groups, or to all users.

The following grants authorize a user with a login name of Wolfgang to start a DBE session and to retrieve rows from the table named Quotas. Wolfgang can also create his own database because he is also granted RESOURCE authority.

   GRANT CONNECT TO Wolfgang
   GRANT SELECT ON Marketing.Quotas TO Wolfgang
   GRANT RESOURCE TO Wolfgang

The following grants authorize the group named Managers to start a DBE session and all users to retrieve rows from the table Forecast:

   GRANT CONNECT TO Managers
   GRANT SELECT ON Marketing.Forecast TO PUBLIC

The REVOKE statement is used to eliminate authorities:

REVOKE RESOURCE FROM Wolfgang

DBAs can grant or revoke authorities. The only individuals entitled to grant and revoke authorities are users or members of groups that own tables, views, or modules, or those who have received grantable privileges, as described below. Individuals or members of groups that own tables, views, or modules can issue grants for objects they own.

Grantable Privileges

If a grantor specifies the WITH GRANT OPTION clause when issuing the GRANT statement on table and view authorities, the grantee receives not only the privilege, but the authority to grant that same privilege, with or without the WITH GRANT OPTION, to another user. The grantee is also entitled to revoke authorities he or she granted. This kind of privilege is called a grantable privilege. The use of grantable privileges can result in chains of grants.

A cycle in a chain of grants is not allowed; that is, a user cannot be granted the same authority more than once on an object. If a grant of authority causes a cycle, you will receive an error message. The WITH GRANT OPTION clause cannot be specified when the grantee is a group. The following statement grants UPDATE authority to Amanda, who can then grant that authority to individual users or a class:

GRANT UPDATE ON Marketing.Forecast TO Amanda WITH GRANT OPTION;

Users with a grantable privilege can only revoke privileges they have granted and chains they have caused. To revoke the privilege given to the grantee and any subsequent grantees in a chain, the grantor must use the CASCADE option of the REVOKE statement.

Owners can revoke any privilege on their object, but to revoke a privilege that has been given to subsequent grantees, the CASCADE option must be used. The DBA does not have to use the CASCADE option to revoke a grantable privilege from a user. However, if CASCADE is not used, that privilege is removed from the specified grantee only, not from the subsequent chain of grants. Then, an orphaned privilege is created. An orphaned privilege can be given a parent by the DBA with the BY clause of the GRANT statement. For more information on orphaned privileges, refer to "Using the WITH GRANT OPTION Clause" in the chapter "Database Creation and Security" in the ALLBASE/SQL Database Administration Guide.

Ownership

The following six objects have owners associated with them:

Tables
Views
Authorization groups
Modules
Procedures
Rules

These objects can be owned by an individual, an authorization group, or a class; but an object can have only one owner at a time.

An owner becomes associated with an object in one of several ways:

When an individual creates one of the five objects, that individual becomes its owner. The owner name is derived from the individual's login name. To create a table or group, you need DBA or RESOURCE authority. To create a module, you need DBA or CONNECT authority. To create a view, you need DBA, SELECT, or OWNER authority for the tables and views it is based on.
A DBA or the owner of an object can transfer ownership of the object to another individual, a group, or a class by using the TRANSFER OWNERSHIP statement. The ownership of modules cannot be transferred. Wolfgang can transfer ownership of his Composers table to Wendy as follows:
TRANSFER OWNERSHIP OF TABLE Composers TO Wendy
A DBA can create any of these objects and name the owner in the statement that creates the object. Other users can name any group as owner when creating an object if they are a member of that group. With the following statements, a DBA creates a group called Managers; a DBA or a member of Managers can assign ownership of the table named Salary to that group when creating the table:
CREATE GROUP Managers CREATE TABLE Managers.Salary...

When you refer in an SQL statement to a table, a view, a module, or an authorization group, you specify both the owner's name and the name of the object. If you own the object, however, you can omit the owner's name. When Wolfgang retrieves information from the Parts table, for example, he must specify the owner name. For example:

SELECT PartNumber FROM PurchDB.Parts

The system views belong to special owners named SYSTEM and CATALOG. Therefore when you refer to one of the system views, you must specify that name:

   SELECT * FROM System.Table
   or
   SELECT * FROM Catalog.Table

Default Owner Rules

In several statements, when a name is specified, such as table name, rule name, group name, or index name, specification of the owner name is optional. The method of determining the default owner when no owner is specified is as follows:

If the name is within a CREATE PROCEDURE statement (except for the procedure name itself), and it is not within a CREATE SCHEMA statement in that procedure, then the default owner is the procedure's owner.
If the name is within a CREATE SCHEMA statement and it is not within a CREATE PROCEDURE statement in that schema, then the default owner name is the authorization name of that schema.
If you have specified an owner using the ISQL SET OWNER command, everything you create will be owned by the owner specified in that command.
If you use the -o option to specify an alternate DBEUserID prior to preprocessing an application containing embedded SQL statements, then the owner specified is the default owner of the module.
If none of the above apply, then the default owner name is the current DBEUserID. The DBEUserID is the login name.

In CREATE INDEX, CREATE RULE, DROP INDEX, DROP RULE, the default owner for the index or rule name, respectively, has additional possible values which are described with those statements.

Ownership Privileges

The following summarizes the privileges that extend to users or members of groups that own objects:

Group owners can add members to and remove them from their group as well as drop the group.
Group members have ownership privileges over all objects owned by their group.
Group members have all privileges granted to the group.
Table owners can add columns to the table or drop the table.
They can add and drop constraints.
They can create and drop indexes for the table. They can grant and revoke authorities for the table, and transfer their ownership to another owner. They can retrieve data from the table, change the data, update statistics, lock the table, and create views on the table. Transferring ownership of a table transfers the ownership of indexes, constraints, and rules defined on the table. And grantor of privileges by owner also changes.
Index owners can drop their indexes. The index owner must be the same as the owner of the table the index is defined upon. Index ownership is transferred along with the ownership of the table the index is defined upon.
View owners can drop their view. They can grant and revoke authorities for the view and transfer their ownership to another owner. They can also access data through their views.
Module owners can execute, validate, and drop their modules. They can grant and revoke RUN authority for their modules. Ownership of modules cannot be transferred.
Procedure owners can drop their procedures. They can grant and revoke EXECUTE authority for their procedures, and they can transfer ownership to another owner.
Rule owners can drop their rules. The rule owner must be the same as the owner of the table the rule is defined upon. Rule ownership is transferred along with the ownership of the table the rule is defined upon.

Authorization Groups

An authorization group is a named collection of users or other groups. The CREATE GROUP statement is used to define groups, and the ADD TO GROUP statement is used to associate individuals or other groups with the group. The GRANT statement assigns authorities to a group. All three statements are used in the following example:

   CREATE GROUP PurchManagers
   ADD Marguerite, Ron, Sharon TO GROUP PurchManagers
   GRANT SELECT on PurchDB.Parts TO PurchManagers

Any member of the group PurchManagers can select data from table PurchDB.Parts. Authorization groups have several advantages as described here:

Groups simplify authorization. They make it possible to grant authorities to multiple users or groups with one GRANT statement. In addition, as new users need authorities, the DBA can simply add them to a group already possessing the appropriate authorization.
Groups make control over the type of data access independent of control over who can access data. For example, the owner of a table can grant different types of access (SELECT, UPDATE, etc.) to a group; but who belongs to the group is controlled by the DBA or the group's owner, not by the table's owner.

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, because objects that belong to a class can be created and maintained only by the DBA. For a class to be useful, its class name must be different from the name of any existing DBEUserID or group name.

A DBA can create a class by doing one of the following:

Creating a table or view with the class name as owner name.
Preprocessing an application with the class name as owner name.
Transferring ownership of an object to a 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:

   CREATE TABLE PurchDB.Parts
                (PartNumber  CHAR(16)  NOT NULL,
                PartName  CHAR(30),
                SalesPrice  DECIMAL(10,2))
             IN WarehFS;

After creating objects owned by the class, you must grant the specific authorities you wish users or groups to have. Suppose you have a group PurStaff consisting of DBEUserIDs for members of the Purchasing department. You could grant authorities to the group as follows:

GRANT SELECT, UPDATE ON PurchDB.Parts to PurStaff;

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 use the class name as the owner name.

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

A class does not have members, nor can it use any authorities, although you can grant them if you wish. This can be useful in a scenario in which you want to preassign ownership of objects to a DBEUserID which has no login ID on your system.