HP 3000 Manuals HP 3000 Manuals
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.