HP 3000 Manuals

Controlling Database Access [ ALLBASE/SQL Reference Manual ] MPE/iX 5.5 Documentation


ALLBASE/SQL Reference Manual

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 logon 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 logon name of WOLFGANG@DBMS
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@DBMS
     GRANT SELECT ON Marketing.Quotas TO WOLFGANG@DBMS
     GRANT RESOURCE TO WOLFGANG@DBMS

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@DBMS

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@DBMS 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 logon 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@DBMS can transfer ownership of his
       Composers table to Wendy as follows:

            TRANSFER OWNERSHIP OF TABLE Composers TO WENDY@ROBERTS

   *   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@DBMS 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 logon name concatenated
       with '@' and concatenated with the group 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@RYAN, RON@HART, SHARON@MULDOON 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 logon ID on your system.



MPE/iX 5.5 Documentation