Generate Grant and Revoke Statements for Access Control Privileges

If access to an object is accidentally granted or revoked, use the DDL Activity Report to generate statements to restore the correct access control privileges.
GRANT and REVOKE statements provide access control to Db2 objects. These privileges and authorities can be granted or revoked for different combinations of authorization IDs and roles. Only an explicitly granted privilege can be revoked.
If you accidentally grant or revoke a privilege, you can use the DDL Activity Report to generate a corresponding GRANT or REVOKE statement to restore the access control privilege to the correct state.
When the REVOKE_DEP_PRIVILEGES system parameter is set to YES, REVOKE statements that are generated by the DDL Activity Report REDO/UNDO option
do not include the keywords INCLUDING DEPENDENT PRIVILEGES and NOT INCLUDING DEPENDENT PRIVILEGES except for the following system privileges:
  • DBADM
  • DATAACCESS
  • ACCESSCTRL
Verify that the generated REVOKE statements are correct before execution. Executing generated REVOKE statements may result in SQL errors or unexpected and potentially corrupting activity.
  1. Follow these steps:
  2. Select
    Process Log
    from the CA Log Analyzer for DB2 for z/OS Main Menu.
    The
    Process Log - Report Specification
    panel (LAPRM) opens.
  3. Select
    DDL Activity
    and press
    Enter
    .
    The
    DDL Activity Report Options
    panel (LAPRDDL) opens.
  4. Complete the following fields:
    1. Specify
      U
      (to create UNDO DDL) or
      R
      (to create REDO DDL) in the
      Output Format
      field.
    2. Specify
      D
      (Detail) in the
      Level of Detail
      field.
    3. (Optional) Specify
      I
      (Include) in the
      Primary Authids
      field under
      Log Data Filter
      Options
      and enter the authorization ID of the person that executed the grant or revoke.
    4. Specify
      O
      (Only) in the
      Grant/Revoke
      field to include only Grant and Revoke records in the report.
    5. Specify
      X
      (Exclude) in the
      Bind/Rebind
      field to exclude Bind and Rebind records.
    6. (Optional) Use other
      Miscellaneous Options
      to include or exclude other types of data from the DDL report.
  5. Press
    PF3
    until the
    Report Source Specification
    panel (LAPRLRNG) appears.
  6. Specify the approximate time range of when the privilege was modified in the
    Log Processing Range
    section.
  7. Press
    PF3
    (End).
    The
    Output Specification
    panel (LAPOS14) appears.
  8. Specify a data set name for the GRANT/REVOKE statements in the
    Dataset Name
  9. Press
    PF3
    until the
    Report Submission Screen
    panel (LAPREPEX) opens.
  10. Specify
    B
    or
    O
    in
    Execution Mode
    and press
    Enter.
    CA Log Analyzer for DB2 for z/OS generates the GRANT/REVOKE statements based on the specified execution mode and saves to the specified destination.