Privileges, Authorities, and Authorizations

Privileges enable users to create, delete, or access database resources. Authority levels provide a method of grouping privileges and higher-level database manager maintenance and utility operations. Together, these act to control access to the database manager and its database objects. Users can access only those objects for which they have the appropriate authorization, i.e., the required privilege or authority.

A user or group can have one or more of the following levels of authorization and privileges:

• Administrative authority (SYSADM or DBADM) gives full privileges for a set of objects.

• System authority (SYSCTRL or SYSMAINT) gives full privileges for managing the system but does not allow access to the data.

• LOAD authority (LOAD) gives LOAD utility privileges to load data into tables.

• Ownership privilege (also called CONTROL privilege in some cases) gives full privileges for a specific object.

• Individual privileges may be granted to allow a user to carry out specific functions on specific objects.

• Implicit privileges may be granted to a user who has the privilege to execute a package. Although users can run the application, they do not necessarily require explicit privileges on the data objects used within the package.

Users with administrative authority (SYSADM or DBADM) or ownership privileges (CONTROL) can grant and revoke privileges to and from others, using the GRANT and REVOKE statements. It is also possible to grant a table, view, or schema privilege to another user if that privilege is held with the WITH GRANT OPTION. However, the WITH GRANT OPTION does not allow the person granting the privilege to revoke the privilege, once granted. You must have SYSADM authority, DBADM authority, or CONTROL privilege to revoke the privilege.

A user or group can be authorized for any combination of individual privileges or authorities. When a privilege is associated with a resource, that resource must already exist. For example, a user cannot be given the SELECT privilege on a table unless that table has previously been created.

NOTE

Care must be taken when an authorization name is given authorities and privileges and no user exists with that authorization name. At some later time, a user can be created with that authorization name and automatically receive all of the authorities and privileges associated with that authorization name .

Tasks and Required Authorizations

Not all organizations divide job responsibilities in the same manner. Table 4.4 lists some other common job responsibilities by titles, the tasks that usually accompany them, and the authorities or privileges that are needed to carry out those tasks.

Table 4.4. Common Job Responsibilities

Job Title	Tasks	Required Authorization
Department Administrator	Oversees the departmental system; creates databases	SYSCTRL authority. SYSADM authority if the department has its own instance
Security Administrator	Authorizes other users for some or all authorizations and privileges	SYSADM or DBADM authority
Database Administrator	Designs, develops, operates, safeguards, and maintains one or more databases	DBADM and SYSMAINT authority over one or more databases; SYSCTRL authority in some cases
System Operator	Monitors the database and carries out backup functions	SYSMAINT authority
Application Programmer	Develops and tests the database manager application programs; may also create tables of test data	BINDADD, BIND on an existing package; CONNECT and CREATETAB on one or more databases; some specific schema privileges; and a list of privileges on some tables
User Analyst	Defines the data requirements for an application program by examining the system catalog views	SELECT on the catalog views; CONNECT on one or more databases
Program End User	Executes an application program	EXECUTE on the package; CONNECT on one or more databases (See the note following this table.)
Information Center Consultant	Defines the data requirements for a query user; provides the data by creating tables and views and by granting access to database objects	DBADM authority over one or more databases
Query User	Issues SQL statements to retrieve, add, delete, or change data; may save results as tables	CONNECT on one or more databases; CREATEIN on the schema of the tables and views being created; and SELECT, INSERT, UPDATE, DELETE on some tables and views

NOTE

If an application program contains dynamic SQL statements, the program end user may need additional privileges to EXECUTE and CONNECT (such as SELECT, INSERT, DELETE, and UPDATE).

Using the System Catalog for Security Issues

Information about each database is automatically maintained in a set of views called the system catalog , which is created when the database is generated. This system catalog describes tables, columns , indexes, programs, privileges, and other objects. The system catalog views list the privileges held by users and the identity of the user granting each privilege, as described in Table 4.5.

Table 4.5. System Catalog Views

System Catalog	Description
SYSCAT.DBAUTH	Lists the database privileges
SYSCAT.TABAUTH	Lists the table and view privileges
SYSCAT.COLAUTH	Lists the column privileges
SYSCAT.PACKAGEAUTH	Lists the package privileges
SYSCAT.INDEXAUTH	Lists the index privileges
SYSCAT.SCHEMAAUTH	Lists the schema privileges
SYSCAT.PASSTHRUAUTH	Lists the server privileges
SYSCAT.ROUTINEAUTH	Lists the routine (functions, methods , and stored procedures) privileges

Considerations:

• SYSADM, SYSMAINT, and SYSCTRL are not listed in the system catalog.

• The CREATE and GRANT statements place privileges in the system catalog.

• Privileges granted to users by the system will have SYSIBM as the grantor.

• Users with SYSADM and DBADM authorities can grant and revoke SELECT privilege on the system catalog views.

The following statement retrieves all authorization names with privileges:

  SELECT DISTINCT GRANTEE, GRANTEETYPE, 'DATABASE' FROM SYSCAT.DBAUTH   UNION   SELECT DISTINCT GRANTEE, GRANTEETYPE, 'TABLE' FROM SYSCAT.TABAUTH   UNION   SELECT DISTINCT GRANTEE, GRANTEETYPE, 'PACKAGE' FROM SYSCAT.PACKAGEAUTH   UNION   SELECT DISTINCT GRANTEE, GRANTEETYPE, 'INDEX' FROM SYSCAT.INDEXAUTH   UNION   SELECT DISTINCT GRANTEE, GRANTEETYPE, 'COLUMN' FROM SYSCAT.COLAUTH   UNION   SELECT DISTINCT GRANTEE, GRANTEETYPE, 'SCHEMA' FROM SYSCAT.SCHEMAAUTH   UNION   SELECT DISTINCT GRANTEE, GRANTEETYPE, 'SERVER' FROM SYSCAT.PASSTHRUAUTH   ORDER BY GRANTEE, GRANTEETYPE, 3;  

Periodically, the list retrieved by this statement should be compared with lists of user and group names defined in the operating system security facility. You can then identify those authorization names (userids) that are no longer valid and revoke their privileges.

To retrieve all authorization names that are directly authorized to access the table EMPLOYEE with the qualifier DSNOW:

  SELECT DISTINCT GRANTEETYPE, GRANTEE FROM SYSCAT.TABAUTH   WHERE TABNAME = 'EMPLOYEE' AND TABSCHEMA = 'DSNOW'   UNION   SELECT DISTINCT GRANTEETYPE, GRANTEE FROM SYSCAT.COLAUTH   WHERE TABNAME = 'EMPLOYEE'AND TABSCHEMA = 'DSNOW';  

To retrieve all authorization names that have been directly granted DBADM authority:

  SELECT DISTINCT GRANTEE   FROM SYSCAT.DBAUTH WHERE DBADMAUTH = 'Y';  

To find out who can update the table EMPLOYEE with the qualifier DSNOW, issue the following statement:

[View full width]

 
 [View full width] 
  SELECT DISTINCT GRANTEETYPE, GRANTEE FROM SYSCAT.TABAUTH   WHERE TABNAME = 'EMPLOYEE' AND TABSCHEMA = 'DSNOW' AND (CONTROLAUTH = 'Y' OR  UPDATEAUTH = 'Y' OR UPDATEAUTH = 'G')   UNION   SELECT DISTINCT GRANTEETYPE, GRANTEE FROM SYSCAT.DBAUTH   WHERE DBADMAUTH = 'Y'   UNION   SELECT DISTINCT GRANTEETYPE, GRANTEE FROM SYSCAT.COLAUTH   WHERE TABNAME = 'EMPLOYEE' AND TABSCHEMA = 'DSNOW'   AND PRIVTYPE = 'U';  

NOTE

This retrieves any authorization names with DBADM authority, as well as those names to which CONTROL or UPDATE privileges have been directly granted. However, it will not return the authorization names of users who hold only SYSADM authority.

To retrieve a list of the database privileges that has been directly granted to an individual authorization name:

  SELECT * FROM SYSCAT.DBAUTH   WHERE GRANTEE = USER AND GRANTEETYPE = 'U';  

To retrieve a list of the table privileges that were directly granted by a specific user: (The keyword USER in these statements is always equal to the value of a user's authorization name. USER is a read-only special register.)

  SELECT * FROM SYSCAT.TABAUTH WHERE GRANTOR = USER;  

The following statement retrieves a list of the individual column privileges that were directly granted by a specific user:

  SELECT * FROM SYSCAT.COLAUTH WHERE GRANTOR = USER;  

During database creation, SELECT privilege on the system catalog views is granted to PUBLIC. In most cases, this does not present any security problems. For very sensitive data, however, it may be inappropriate, because these tables describe every object in the database. If this is the case, consider revoking the SELECT privilege from PUBLIC, then grant the SELECT privilege as required to specific users. Granting and revoking SELECT on the system catalog views is done in the same way as for any view, but you must have either SYSADM or DBADM authority to do this.

To retrieve the owner and name of every table on which a user's authorization name has been directly granted SELECT privilege:

  SELECT TABSCHEMA, TABNAME   FROM SYSCAT.TABAUTH   WHERE GRANTEETYPE = 'U' AND   GRANTEE = USER AND   SELECTAUTH = 'Y'