Restrictions on privilege revocation

1. UserA creates a user-defined function named UserA.UDFA.
2. UserA grants EXECUTE on UserA.UDFA to UserB.
3. User B then creates a user-defined function UserB.UDFB that is sourced on UserA.UDFA.

At this point, UserA attempts to revoke the EXECUTE privilege on UserA.UDFA from UserB. The revoke succeeds or fails based on the following criteria:

• If UserB has the EXECUTE privilege on UserA.UDFA only from UserA, the revoke fails with an accompanying message that indicates that a dependency on this privilege.
• If UserB has the EXECUTE privilege on UserA.UDFA from another source, directly or indirectly, the EXECUTE privilege that was granted by UserA is revoked successfully.

For distinct types, the following objects that are owned by the revokee can have dependencies:

• A table that has a column that is defined as a distinct type
• A user-defined function that has a parameter that is defined as a distinct type
• A stored procedure that has a parameter that is defined as a distinct type
• A sequence that has a parameter that is defined as a distinct type

For user-defined functions, the following objects that are owned by the revokee can have dependencies:

• Another user-defined function that is sourced on the user-defined function
• A view that uses the user-defined function
• A table that uses the user-defined function in a check constraint or user-defined default clause
• A trigger package that uses the user-defined function

For JARs (Java classes for a routine), the following objects that are owned by the revokee can have dependencies:

• A Java user-defined function that uses a JAR
• A Java stored procedure that uses a JAR

For stored procedures, a trigger package that refers to the stored procedure in a CALL statement can have dependencies.

For sequences, the following objects that are owned by the revokee can have dependencies:

• Triggers that contain NEXT VALUE or PREVIOUS VALUE expressions that specify a sequence
• Inline SQL routines that contain NEXT VALUE or PREVIOUS VALUE expressions that specify a sequence

One way to ensure that the REVOKE statement succeeds is to drop the object that has a dependency on the privilege. To determine which objects are dependent on which privileges before attempting the revoke, use the following SELECT statements.

For a distinct type:

• List all tables that are owned by the revokee USRT002 that contain columns that use the distinct type USRT001.UDT1:

  SELECT * FROM SYSIBM.SYSCOLUMNS WHERE
    TBCREATOR = 'USRT002'      AND
    TYPESCHEMA = 'USRT001'     AND
    TYPENAME = 'UDT1'          AND
    COLTYPE = 'DISTINCT';                                      

• List the user-defined functions that are owned by the revokee USRT002 that contain a parameter that is defined as distinct type USRT001.UDT1:

  SELECT * FROM SYSIBM.SYSPARMS WHERE
    OWNER = 'USRT002'          AND
    TYPESCHEMA = 'USRT001'     AND
    TYPENAME = 'UDT1'          AND
    ROUTINETYPE = 'F'; 

• List the stored procedures that are owned by the revokee USRT002 that contain a parameter that is defined as distinct type USRT001.UDT1:

  SELECT * FROM SYSIBM.SYSPARMS WHERE
    OWNER = 'USRT002'          AND
    TYPESCHEMA = 'USRT001'     AND
    TYPENAME = 'UDT1'          AND
    ROUTINETYPE = 'P'; 

• List the sequences that are owned by the revokee USRT002 that contain a parameter that is defined as distinct type USRT001.UDT1:

  SELECT SYSIBM.SYSSEQUENCES.SCHEMA, SYSIBM.SYSSEQUENCES.NAME
    FROM SYSIBM.SYSSEQUENCES, SYSIBM.SYSDATATYPES WHERE
      SYSIBM.SYSSEQUENCES.DATATYPEID = SYSIBM.SYSDATATYPES.DATATYPEID AND
      SYSIBM.SYSDATATYPES.SCHEMA ='USRT001' AND      
      SYSIBM.SYSDATATYPES.NAME   ='UDT1';     

For a user-defined function:

• List the user-defined functions that are owned by the revokee USRT002 that are sourced on user-defined function USRT001.SPECUDF1:

  SELECT * FROM SYSIBM.SYSROUTINES WHERE
    OWNER = 'USRTOO2'            AND
    SOURCESCHEMA = 'USRTOO1'     AND
    SOURCESPECIFIC = 'SPECUDF1'  AND
    ROUTINETYPE = 'F';                                      

• List the views that are owned by the revokee USRT002 that use user-defined function USRT001.SPECUDF1:

  SELECT * FROM SYSIBM.SYSVIEWDEP WHERE
    DCREATOR = 'USRTOO2'       AND
    BSCHEMA = 'USRT001'        AND
    BNAME = 'SPECUDF1'         AND
    BTYPE = 'F';                       

• List the tables that are owned by the revokee USRT002 that use user-defined function USRT001.A_INTEGER in a check constraint or user-defined default clause:

  SELECT * FROM SYSIBM.SYSCONSTDEP WHERE
    DTBCREATOR = 'USRT002'      AND
    BSCHEMA = 'USRT001'         AND
    BNAME = 'A_INTEGER'         AND
    BTYPE = 'F';         

• List the trigger packages that are owned by the revokee USRT002 that use user-defined function USRT001.UDF4:

  SELECT * FROM SYSIBM.SYSPACKDEP WHERE
    DOWNER = 'USRT002'         AND
    BQUALIFIER = 'USRT001'     AND
    BNAME = 'UDF4'             AND
    BTYPE = 'F';         

For a JAR (Java class for a routine), list the routines that are owned by the revokee USRT002 and that use a JAR named USRT001.SPJAR:

SELECT * FROM SYSIBM.SYSROUTINES WHERE
  OWNER = 'USRT002'          AND
  JARCHEMA = 'USRT001'       AND
  JAR_ID = 'SPJAR';   

For a stored procedure that is used in a trigger package, list the trigger packages that refer to the stored procedure USRT001.WLMLOCN2 that is owned by the revokee USRT002:

SELECT * FROM SYSIBM.SYSPACKDEP WHERE
  DOWNER = 'USRT002'         AND
  BQUALIFIER = 'USRT001'     AND
  BNAME = 'WLMLOCN2'         AND
  BTYPE = 'O';   

For a sequence:

• List the sequences that are owned by the revokee USRT002 and that use a trigger named USRT001.SEQ1:

  SELECT * FROM SYSIBM.SYSPACKDEP WHERE
    BNAME = 'SEQ1'
    BQUALIFIER = 'USRT001'
    BTYPE = 'Q'
    DOWNER = 'USRT002'
    DTYPE = 'T';

• List the sequences that are owned by the revokee USRT002 and that use a inline SQL routine named USRT001.SEQ1:

  SELECT * FROM SYSIBM.SYSSEQUENCESDEP WHERE
    DCREATOR = 'USRT002'
    DTYPE = 'F'
    BNAME = 'SEQ1'
    BSCHEMA = 'USRT001';