Previous Topic: SYSTEM.RESOURCENext Topic: SYSTEM.RESOURCEGROUP

Security Database Information and DSECTs › Database Resource Security Tables › SYSTEM.RESOURCEAUTH

SYSTEM.RESOURCEAUTH

Purpose

A row of this table represents the privileges that have been granted to an authorization ID on an SQL-defined database resource.

Columns

Column name

Data type

Description of contents

AUTHID

CHAR(18)

The authorization ID of the user or group to whom the privileges have been granted.

RESOURCETYPE

CHAR(4)

The resource type keyword:

  • 'DACC'—Access module
  • 'QSCH'—Schema
  • 'TABL'—Table

RESOURCENAME

CHAR(60)

The resource name.

RUNTIMEAUTH

BINARY(2)

Runtime privileges that have been granted on the resource.

For RESOURCETYPE 'DACC':

  • 1—EXECUTE(1)

For RESOURCETYPE 'QSCH'(2) and 'TABL':

  • 1—SELECT
  • 2—INSERT
  • 4—UPDATE
  • 8—DELETE
  • nn—(The sum of two or more of the above, representing multiple privileges)
  • 15—ACCESS (all of the above)
  • 128—OWNER(3)
  • 143—ALL (ACCESS and OWNER)

RUNTIMEAUTHW

BINARY(2)

The runtime privileges a user represented by AUTHID may grant to other authorization IDs.(4)

DEFNAUTH

BINARY(2)

Definition privileges that have been granted on the resource:

  • 1—CREATE
  • 2—ALTER
  • 4—DROP
  • 8—DISPLAY
  • 16—USE
  • nn—(The sum of two or more of the above, representing multiple privileges)
  • 31—DEFINE (CREATE, ALTER, DROP, DISPLAY, USE)
  • 32—REFERENCES
  • 63—DEFINE and REFERENCES
  • 128—OWNER(5)
  • 191—ALL (DEFINE, REFERENCES, and OWNER)(6)

DEFNAUTHW

BINARY(2)

The definition privileges a user represented by AUTHID may grant to other authorization IDs.(5) (6)

OTHERAUTH

BINARY(2)

(Not applicable—initialized to binary zeros.)

CTIME

TIMESTAMP

Time created.

UTIME

TIMESTAMP

Time of the last update.

CUSER

CHAR(18)

ID of the user who created the resource authorization.

UUSER

CHAR(18)

ID of the user who last updated the resource authorization.

FILLER

BINARY(16)

(Reserved—initialized to spaces.)

(1) An occurrence of a row with RESOURCETYPE 'DACC' and RUNTIMEAUTH 1 (EXECUTE) results when privilege is granted on the access module directly as an SQL-defined database resource, not as part of a category, which is a system resource.

(2) An occurrence of a row with RESOURCETYPE 'QSCH' and a nonzero value in RUNTIMEAUTH results only when a schema is created. RUNTIMEAUTH is 143 (ALL) and remains 143 even if schema ownership is transferred because the runtime privileges cannot be revoked. All other occurrences of a row with RESOURCETYPE 'QSCH' refer to definition privileges only and, therefore, contain zero in RUNTIMEAUTH and RUNTIMEAUTHW.

(3) The OWNER privilege for a table can be granted only with a GRANT ALL PRIVILEGES statement and revoked only with a REVOKE ALL PRIVILEGES statement. However, it is possible for the value of RUNTIMEAUTH (and RUNTIMEAUTHW) to be 128 (OWNER) if all privileges are granted on a table and then access privileges are revoked.

(4) Runtime privileges for RESOURCETYPE 'QSCH' are grantable only in the sense that they are automatically transferred when ownership of the schema is transferred.

(5) OWNER privilege is never assigned to DEFNAUTH (or DEFNAUTHW) for resource types 'DACC' and 'TABL'.

(6) When an SQL schema is created, the value of RESOURCETYPE is 'QSCH' and the value of DEFNAUTH is 191 (ALL: DEFINE, REFERENCES, and OWNER). REFERENCES and definition privileges can be revoked; ownership can be transferred but not revoked.