HP 3000 Manuals HP 3000 Manuals
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:
[REV BEG]
* 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.
[REV END]
___________________________________________________________________________
| |
| 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 |
___________________________________________________________________________
Figure 2-8. Relationship among Authorities
Special Authorities
The following authorities are special authorities:
* DBA authority
* RESOURCE authority
* CONNECT authority[REV BEG]
* MONITOR authority
* INSTALL authority[REV END]
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.
[REV BEG]
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.[REV END]
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.
[REV BEG]
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.[REV END]
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 , the chain cannot link back to itself.
![[FFN13]](../../pic3k/M017037/FFN133c50.gif)
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.