Revoking privileges granted by multiple IDs

A user can be granted the same privilege by multiple IDs at different times, but that privilege and any dependent privileges can be simultaneously revoked.

About this task

Begin general-use programming interface information.Suppose that DBUTIL1 grants the CREATETAB privilege to PGMR01 and that DBUTIL2 also grants the CREATETAB privilege to PGMR01. The second grant is recorded in the catalog, with its date and time, but it has no other effect until the grant from DBUTIL1 to PGMR01 is revoked. After the first grant is revoked, Db2 must determine the authority that PGMR01 used to grant CREATETAB to OPER1. The following diagram illustrates the situation; the arrows represent the granting of the CREATETAB privilege.

Figure 1. Authorization granted by two or more IDs
Begin figure description. Authorization granted by two or more IDs. End figure description.

Suppose that DBUTIL1 issues the GRANT statement at Time 1 and that DBUTIL2 issues the GRANT statement at Time 2. DBUTIL1 and DBUTIL2 both use the following statement to issue the grant: 

GRANT CREATETAB ON DATABASE DB1 TO PGMR01 WITH GRANT OPTION;

At Time 3, PGMR01 grants the privilege to OPER1 by using the following statement: 

GRANT CREATETAB ON DATABASE DB1 TO OPER1;

After Time 3, DBUTIL1's authority is revoked, along with all of the privileges and authorities that DBUTIL1 granted. However, PGMR01 also has the CREATETAB privilege from DBUTIL2, so PGMR01 does not lose the privilege. The following criteria determine whether OPER1 loses the CREATETAB privilege when DBUTIL1's authority is revoked:

  • If Time 3 comes after Time 2, OPER1 does not lose the privilege. The recorded dates and times show that, at Time 3, PGMR01 could have granted the privilege entirely on the basis of the privilege that was granted by DBUTIL2. That privilege was not revoked.
  • If Time 3 precedes Time 2, OPER1 does lose the privilege. The recorded dates and times show that, at Time 3, PGMR01 could have granted the privilege only on the basis of the privilege that was granted by DBUTIL1. That privilege was revoked, so the privileges that are dependent on it are also revoked.End general-use programming interface information.