AUTH_DIFF table function¶

Utility table function which returns the difference between the authorities held by two names.

Prototypes¶

AUTH_DIFF(SOURCE VARCHAR(128), SOURCE_TYPE VARCHAR(1), DEST VARCHAR(128), DEST_TYPE VARCHAR(1), INCLUDE_COLUMNS VARCHAR(1), INCLUDE_PERSONAL VARCHAR(1))
AUTH_DIFF(SOURCE VARCHAR(128), DEST VARCHAR(128), INCLUDE_COLUMNS VARCHAR(1), INCLUDE_PERSONAL VARCHAR(1))
AUTH_DIFF(SOURCE VARCHAR(128), DEST VARCHAR(128), INCLUDE_COLUMNS VARCHAR(1))

RETURNS TABLE(
  OBJECT_TYPE VARCHAR(18),
  OBJECT_ID VARCHAR(262),
  AUTH VARCHAR(140),
  SUFFIX VARCHAR(20),
  LEVEL SMALLINT
)

Description¶

This utility function determines the difference in authorizations held by two different entities (as determined by AUTHS_HELD table function). Essentially it takes the authorizations of the SOURCE entity and “subtracts” the authorizations of the DEST entity, the result being the authorizations that need to be granted to DEST to give it the same level of access as SOURCE. This is used in the definition of the COPY_AUTH procedure routine.

Parameters¶

SOURCE: The name to check for existing authorizations.
SOURCE_TYPE: The type of the SOURCE parameter. Specify 'U', 'G', or 'R' for User, Group or Role respectively. If this parameter is omitted, the type will be determined by the AUTH_TYPE scalar function function.
DEST: The intended destination for the authorizations held by SOURCE.
DEST_TYPE: The type of the DEST parameter. Takes the same values as SOURCE_TYPE. If omitted, the type will be determined by the AUTH_TYPE scalar function function.
INCLUDE_COLUMNS: If this parameter is 'Y', column level authorizations will be included.
INCLUDE_PERSONAL: If this parameter is 'Y', and SOURCE identifies a user, then authorizations for the source user’s personal schema will be included in the result. This parameter defaults to 'N' when omitted.

Returns¶

See the AUTHS_HELD table function documentation for a description of the columns of the returned table (this routine is essentially a “subtraction” of two AUTHS_HELD calls hence the output structure is identical).

Examples¶

Show the authorizations directly granted to the DB2INST1 user which the currently logged on user does not possess.

SELECT * FROM TABLE(AUTH_DIFF('DB2INST1', USER, 'N'));

OBJECT_TYPE OBJECT_ID                 AUTH        SUFFIX               LEVEL
----------- ------------------------- ----------- -------------------- ------
PACKAGE     NULLID.POLYH03            CONTROL                               0
INDEX       SYSTOOLS.ATM_UNIQ         CONTROL                               0
INDEX       SYSTOOLS.HI_OBJ_UNIQ      CONTROL                               0
TABLE       SYSTOOLS.HMON_ATM_INFO    CONTROL                               0
TABLE       SYSTOOLS.HMON_COLLECTION  CONTROL                               0
TABLE       SYSTOOLS.POLICY           CONTROL                               0
INDEX       SYSTOOLS.POLICY_UNQ       CONTROL                               0
TABLE       SYSTOOLS.HMON_ATM_INFO    ALTER       WITH GRANT OPTION         1
TABLE       SYSTOOLS.HMON_COLLECTION  ALTER       WITH GRANT OPTION         1
TABLE       SYSTOOLS.POLICY           ALTER       WITH GRANT OPTION         1
PACKAGE     NULLID.POLYH03            BIND        WITH GRANT OPTION         1
TABLE       SYSTOOLS.HMON_ATM_INFO    DELETE      WITH GRANT OPTION         1
TABLE       SYSTOOLS.HMON_COLLECTION  DELETE      WITH GRANT OPTION         1
TABLE       SYSTOOLS.POLICY           DELETE      WITH GRANT OPTION         1
PACKAGE     NULLID.POLYH03            EXECUTE     WITH GRANT OPTION         1
TABLE       SYSTOOLS.HMON_ATM_INFO    INDEX       WITH GRANT OPTION         1
TABLE       SYSTOOLS.HMON_COLLECTION  INDEX       WITH GRANT OPTION         1
TABLE       SYSTOOLS.POLICY           INDEX       WITH GRANT OPTION         1
TABLE       SYSTOOLS.HMON_ATM_INFO    INSERT      WITH GRANT OPTION         1
TABLE       SYSTOOLS.HMON_COLLECTION  INSERT      WITH GRANT OPTION         1
TABLE       SYSTOOLS.POLICY           INSERT      WITH GRANT OPTION         1
TABLE       SYSTOOLS.HMON_ATM_INFO    REFERENCES  WITH GRANT OPTION         1
TABLE       SYSTOOLS.HMON_COLLECTION  REFERENCES  WITH GRANT OPTION         1
TABLE       SYSTOOLS.POLICY           REFERENCES  WITH GRANT OPTION         1
TABLE       SYSTOOLS.HMON_ATM_INFO    SELECT      WITH GRANT OPTION         1
TABLE       SYSTOOLS.HMON_COLLECTION  SELECT      WITH GRANT OPTION         1
TABLE       SYSTOOLS.POLICY           SELECT      WITH GRANT OPTION         1
TABLE       SYSTOOLS.HMON_ATM_INFO    UPDATE      WITH GRANT OPTION         1
TABLE       SYSTOOLS.HMON_COLLECTION  UPDATE      WITH GRANT OPTION         1
TABLE       SYSTOOLS.POLICY           UPDATE      WITH GRANT OPTION         1
TABLESPACE  SYSTOOLSPACE              USE         WITH GRANT OPTION         1
TABLESPACE  SYSTOOLSTMPSPACE          USE         WITH GRANT OPTION         1