HP 3000 Manuals HP 3000 Manuals
Creating the DBEnvironment Security Scheme
After configuring the DBEnvironment, you must create the security scheme
that will control its use. To do this, you grant and revoke authorities
for specific users and groups with the GRANT and REVOKE statements,
respectively, as described in the ALLBASE/SQL Reference Manual. You can
also control the authority to perform certain maintenance tasks using the
SQLUtil maintenance word, which is a password for SQLUtil. For more
information on the maintenance word, refer to the "SQLUtil" appendix.
Creating Authorization Groups
You can grant all authorities to each DBEUserID individually. However,
if several users require the same set of authorities, you can use an
authorization group. First, create the groups, then add specific users
to them. For example, if all managers need the same authorities, you can
create a group called PurchManagers and add the DBEUserIDs of the
managers to it, as follows:
isql=> CREATE GROUP PurchManagers;
isql=> ADD Margy@Ryan TO GROUP PurchManagers;
isql=> ADD Ron@Hart TO GROUP PurchManagers;
isql=> ADD Sharon@Muldoon TO GROUP PurchManagers;
Managing Authorization Groups
Authorization group management consists of adding members to the group
and removing them when appropriate. An authorization group is owned and
managed by the owner of the group and/or by a user with DBA authority.
To delegate group management to another user, do one of the following:
* assign the user as the owner in the CREATE GROUP statement
(requires DBA authority)
* create the group and transfer ownership to the user (requires DBA
or OWNER authority)
Owners of groups are able to indirectly grant and revoke authorities by
adding and removing members. An example is given in "Granting/Revoking
CONNECT Authority" later in this chapter.
The SQL statements used to manage authorization groups are shown in
Figure 4-2 .
________________________________________________________________________
| |
| |
|CREATE GROUP [Owner.]GroupName |
| |
| |
| {DBEUserID} |
|ADD {GroupName} [,...] TO GROUP TargetGroupName |
| {ClassName} |
| |
| |
| {DBEUserID} |
|REMOVE {GroupName} [,...] FROM GROUP TargetGroupName |
| {ClassName} |
| |
| |
|DROP GROUP GroupName |
| |
| |
| {[TABLE] [Owner.]TableName} |
|TRANSFER OWNERSHIP OF {[VIEW] [Owner.]ViewName } TO NewOwnerName |
| {GROUP GroupName } |
________________________________________________________________________
Figure 4-2. SQL Commands for Authorization Group Management
For more information about these statements, refer to the ALLBASE/SQL
Reference Manual.
Using DBA Authority
DBA authority is for database administrators and users who create,
maintain, and control access to the DBEnvironment.
DBA authority is granted to (and cannot be revoked from) the user who
configures the DBEnvironment (DBECreator). A user with DBA authority can
execute any SQL statement or ISQL command in the DBEnvironment. DBA
authority also gives the user co-ownership of all objects in the
DBEnvironment. A user with DBA authority can even revoke DBA authority
from any other user except the DBECreator.
ALLBASE/SQL does not restrict the number of users that can have DBA
authority, but because DBA authority is so powerful and therefore
potentially dangerous, you should be selective in granting it to others.
You need DBA authority to grant DBA, RESOURCE, CONNECT, RUN and EXECUTE,
and DBEFileSet authority to other users, as in the following examples:
isql=> GRANT DBA TO John@Brock;
isql=> GRANT CONNECT TO John@Brock;
isql=> GRANT RESOURCE TO John@Brock;
The DBECreator
The DBECreator is the user who configures a DBEnvironment with the START
DBE NEW statement.
The DBECreator is able to execute all of the SQLUtil commands and is
given irrevocable DBA authority for the DBEnvironment. The DBECreator's
DBEUserID appears in the DBECon file and is entered into the system
catalog, as shown in the SYSTEM.SPECAUTH view.
There can be only one DBECreator for each DBEnvironment. DBA authority
cannot be revoked from the DBECreator. The DBECreator cannot be added to
an authorization group.
The DBECreator has special capabilities that general users do not have.
The DBECreator can:
* execute the START DBE NEWLOG statement.
* execute the SQLUtil commands (except PURGEFILE).
DBA Functions.
Specific functions requiring DBA authority, and their appropriate SQL
statements, are listed in the appendix "Authorities Required by
ALLBASE/SQL Statements."
Granting Authorities to PUBLIC
PUBLIC is a special, nonrestrictive category of user. By granting RUN
authority or table and view authorities to PUBLIC, you implicitly
grant that authority to any user who has CONNECT authority to the
DBEnvironment.
When you explicitly grant an authority to PUBLIC, you are granting that
authority to all users in the DBEnvironment. Granting authorities to
PUBLIC on an object is not the same as creating a table PUBLIC. Although
you implicitly perform a GRANT ALL TO PUBLIC when you create a table
PUBLIC, you are also specifying the locking strategy for the table. You
can revoke the authorities on a PUBLIC table, but the locking strategy
remains unchanged.[REV BEG]
[REV END]
Granting/Revoking CONNECT Authority
Users cannot access the DBEnvironment until they are explicitly granted
CONNECT authority. A user with CONNECT authority has all table and view
authorities that may have been granted to the special user PUBLIC;
however, granting CONNECT authority does not grant a user any other
privilege. To grant CONNECT authority, use the following statement:
isql=> GRANT CONNECT TO Peter@Crane;
You can revoke CONNECT authority at any time. A user that is currently
connected to the DBEnvironment is allowed to continue their DBE session
after CONNECT authority is revoked, but cannot reconnect once the session
is terminated. Once CONNECT authority is revoked, the user cannot access
the DBEnvironment regardless of any other authorities (except DBA
authority) previously granted.
If CONNECT authority is revoked from a user who owns objects, these
objects, as well as any other authorities the user may have been granted,
are unaffected by the revocation. To revoke CONNECT authority, use the
following statement:
isql=> REVOKE CONNECT FROM Peter@Crane;
Since granting and revoking CONNECT authority does not affect a module's
ownership or other user authorities, DBEnvironment access can be
restricted without restructuring the security scheme. DBA authority is
required to execute the GRANT and REVOKE statements for special
authorities, including CONNECT.
You can centralize access control to the DBEnvironment using
authorization groups. In the sample DBEnvironment the group Purch has
CONNECT authority and all other groups and users are members of Purch.
The DBA can revoke CONNECT authority from Purch to temporarily keep all
users out of the DBEnvironment for maintenance and backup purposes. Note
that if the group being added as a member of another group does not
exist, no error results, since the entry is assumed to be a class name
and not an authorization group. Therefore, assure that a group is
created before it is granted any authority or added to another
authorization group. The following series of statements creates the
sample database CONNECT authority scheme:
isql=> CREATE GROUP Purch;
isql=> ADD PurchManagers TO GROUP Purch;
isql=> ADD PurchDBMaint TO GROUP Purch;
isql=> ADD Purchasing TO GROUP Purch;
isql=> ADD Receiving TO GROUP Purch;
isql=> ADD WareHouse TO GROUP Purch;
isql=> ADD AccountsPayable TO GROUP Purch;
isql=> ADD Tom@Ash TO GROUP Purch;
isql=> GRANT CONNECT TO Purch;
Only a user with DBA authority can grant or revoke CONNECT authority
directly to or from an individual user or an authorization group.
However, you can delegate the ability to indirectly grant CONNECT
authority to a user without granting DBA authority to that user by
transferring ownership of a group with CONNECT authority to that user.
isql=> TRANSFER OWNERSHIP OF GROUP Purch TO Ron@Hart;
Ron owns this authorization group and can control access to the
DBEnvironment by adding members to or removing members from Purch. You
can get the same results by granting CONNECT authority to an
authorization group already owned by a non-DBA user.
NOTE Remember to COMMIT WORK to make your changes permanent.
Granting/Revoking RESOURCE Authority
RESOURCE authority gives a user the ability to create:
* tables
* authorization groups
When you grant RESOURCE authority to an individual user, you are, in
effect, giving the user the capability to create a database. Any
resources created by the user are owned by that user's DBEUserID and are
treated as a separate logical database.
isql=> GRANT RESOURCE TO Annie@Melchoir;
Suppose Annie@Melchoir, a member of the PurchDBMaint group, creates a new
table called Employees. The table's fully qualified name is
Annie@Melchoir.Employees, and belongs to the Annie@Melchoir database
rather than the PurchDB database.
If RESOURCE authority is revoked from Annie@Melchoir, she still has OWNER
authority for those objects she created, but she cannot create any more
objects:
isql=> REVOKE RESOURCE FROM Annie@Melchoir;
In the sample DBEnvironment the group PurchDBMaint has RESOURCE
authority. The members of PurchDBMaint are DBA assistants that create
tables and groups. Once the design of the objects is approved, the DBA
can transfer their ownership so they become part of an existing database.
The following statements establish the PurchDBMaint group:
isql=> CREATE GROUP PurchDBMaint;
isql=> GRANT RESOURCE TO PurchDBMaint;
isql=> GRANT ALL ON PurchDB.Parts TO PurchDBMaint;
isql=> GRANT ALL ON PurchDB.Inventory TO PurchDBMaint;
isql=> GRANT ALL ON PurchDB.SupplyPrice TO PurchDBMaint;
isql=> GRANT ALL ON PurchDB.Vendors TO PurchDBMaint;
isql=> GRANT ALL ON PurchDB.Orders TO PurchDBMaint;
isql=> GRANT ALL ON PurchDB.OrderItems TO PurchDBMaint;
isql=> GRANT SELECT ON PurchDB.VendorStatistics TO PurchDBMaint;
isql=> GRANT SELECT ON PurchDB.PartInfo TO PurchDBMaint;
Users are then added to the PurchDBMaint group:
isql=> ADD Annie@Melchoir TO GROUP PurchDBMaint;
isql=> ADD Doug@Dolan TO GROUP PurchDBMaint;
isql=> ADD David@Bloom TO GROUP PurchDBMaint;
You can remove a user from the PurchDBMaint group to remove the user's
associated RESOURCE authority:
isql=> REMOVE David@Bloom FROM GROUP PurchDBMaint;
To remove a user's OWNER authority, you can transfer ownership of the
object, then grant the required table and view authorities to the user.
Granting/Revoking RUN Authority
RUN authority permits a user or group to execute an already preprocessed
program that the user or group does not own. RUN authority can be
granted by the DBA or the owner of the module.
isql=> GRANT RUN ON PurchDB.Program TO John@Brock;
RUN authority can also be revoked by the DBA or the owner of the module:
isql=> REVOKE RUN ON PurchDB.Program FROM John@Brock;
RUN authority can be granted to a group. For more information on module
authorities, refer to the "Maintenance" chapter.
Granting/Revoking EXECUTE Authority
EXECUTE authority permits a user or group to execute a section stored in
the DBEnvironment that the user does not own. EXECUTE authority can be
granted by the DBA or the owner of the procedure.
isql=> GRANT EXECUTE ON PurchDB.ReportMonitor TO John@Brock;
EXECUTE authority can also be revoked by the DBA or the owner of the
procedure:
isql=> REVOKE RUN ON PurchDB.ReportMonitor FROM John@Brock;
EXECUTE authority can be granted to a group. For more information on
procedure authorities, refer to the "Maintenance" chapter.
Granting/Revoking SECTIONSPACE or TABLESPACE Authority
SECTIONSPACE authority permits the grantee to store sections in the
specified DBEFileSet, as in the following grant to PUBLIC:
isql=> GRANT SECTIONSPACE ON DBEFILESET DBEFileSet1 TO PUBLIC;
TABLESPACE authority permits the grantee to store table and long column
data in the specified DBEFileSet, as in the following grant to the
Warehse group:
isql=> GRANT TABLESPACE ON DBEFILESET DBEFileSet2 TO Warehse;
Refer to complete syntax for the GRANT statement in the ALLBASE/SQL
Reference Manual.
Verification of Authority
ALLBASE/SQL checks the DBECreator name in the DBECon file to make sure
you are the DBECreator whenever you execute the START DBE NEWLOG
statement and certain SQLUtil commands. In addition, ALLBASE/SQL checks
your DBEUserID in the system catalog authorization tables for:
* DBA authority when you perform a START DBE
* CONNECT authority when you enter a CONNECT statement
* DBA or the appropriate authority when you:
* specify an SQL statement that accesses the database (refer
to the appendix, "Authorities Required by SQL Statements,"
for a listing of SQL statements and their respective
authorities)
* run an application program
* preprocess an application program
Interactively, ALLBASE/SQL checks authority each time you issue a
statement. Programmatically, ALLBASE/SQL checks authority of the
embedded SQL statements during preprocessing and for DBA, OWNER, or RUN
authority when a user attempts to run the application program.