Creating the DBEnvironment Security Scheme

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 TO GROUP PurchManagers;

   isql=> ADD Ron TO GROUP PurchManagers;

   isql=> ADD Sharon 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:

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 “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;

   isql=> GRANT CONNECT TO John;

   isql=> GRANT RESOURCE TO John;

The DBECreator

The DBECreator is the user who either:

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.

When a DBEnvironment is restored by the superuser with the SQLUtil RESTORE command, the superuser is prompted for the userid to be assigned as the DBECreator. The DBEUserID in the DBECon file is then changed to the name the superuser enters. The DBEUserID of the DBECreator is changed in the system catalog as soon as the DBEnvironment is accessed again.

When a DBEnvironment is restored by a non-superuser with the SQLUtil RESTORE command, the DBECreator name remains the same as it was when the DBEnvironment was saved with the SQLUtil STORE command. If there is no matching login id in the /etc/passwd file, an error occurs and the RESTORE fails. In this case, only the superuser can change the name of the DBEcreator to another valid DBEUserID using the SQLUtil RESTORE command. 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:

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.

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;

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;

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 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;

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:

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;

Suppose Annie, a member of the PurchDBMaint group, creates a new table called Employees. The table's fully qualified name is Annie.Employees, and belongs to the Annie database rather than the PurchDB database.

If RESOURCE authority is revoked from Annie, she still has OWNER authority for those objects she created, but she cannot create any more objects:

   isql=> REVOKE RESOURCE FROM Annie;

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 TO GROUP PurchDBMaint;

   isql=> ADD Doug TO GROUP PurchDBMaint;

   isql=> ADD David TO GROUP PurchDBMaint;

You can remove a user from the PurchDBMaint group to remove the user's associated RESOURCE authority:

   isql=> REMOVE David 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;

RUN authority can also be revoked by the DBA or the owner of the module:

   isql=> REVOKE RUN ON PurchDB.Program FROM John;

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;

EXECUTE authority can also be revoked by the DBA or the owner of the procedure:

   isql=> REVOKE EXECUTE ON PurchDB.ReportMonitor FROM John;

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:

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.