Skip to Content

 

Information
Limited support BMC provides limited support for this version of the product. As a result, BMC no longer accepts comments in this space. If you encounter problems with the product version or the space, contact BMC Support.BMC recommends upgrading to the latest version of the product. To see documentation for that version, see BMC AMI Catalog Manager for Db2 13.1.

Revoking privileges on specific objects

Use the following procedure to revoke user privileges on an object without losing cascaded privileges.

  1. Generate an object list that contains the object from which you want to revoke user privileges. For information, see Generating-lists-in-Catalog-Manager.

  2. In the object list, in the Cmd column beside the source object, generate one of the following lists:

    • To generate a list of the user privileges for the object, enter US, and then press Enter. The following example shows the user privileges for a database:

      DEFF-R ----------------------  DATABASE PRIVILEGES  --------------------------
      Command ===>                                                  Scroll ===> PAGE
                                                                                  02
      CMD will show commands for this list.  Type command and press ENTER
      Lists: AU
      QUALIFIER: DATABASE=ACTADMN1
      Cmd User           TBL TSP ADM CTL MNT DIS DRP CPY LOD REO REC REP STA STT STO
      ----v----1----v----2----v----3----v----4----v----5----v----6----v----7----v----
          CATADMDB        G   G   G   G   G   G   G   G   G   G   G   G   G   G   G
          CATDB1          G   G   G   G   G   G   G   G   G   G   G   G   G   G   G
          CATDB1A         Y   Y
          CATDB1B                             Y   Y
          CATDB1C         G   G   G   G   G   G   G   G   G   G   G   G   G   G   G
          RDATLF3         G   G   G   G   G   G   G   G   G   G   G   G   G   G   G
      ******************************  BOTTOM OF DATA  *******************************

    • To generate a user authorizations list for that object, enter UA, and then press Enter.

      DEGA-R -------------------  USER AUTHORIZATIONS LIST  ------------- ROW 1 OF 1
      Command ===>                                                  Scroll ===> PAGE
                                                                                  02
      CMD will show commands for this list.  Type command and press ENTER
      Lists: AU
      QUALIFIER: DATABASE=ACTADMN1
      Command       User      Grantor   Type HowGot  Date       Authorizations
      ----v----1----v----2----v----3----v----4----v----5----v----6----v----7----v----
                    CATADMDB  CATDB1     DB  DBADM   2007-05-08 GGGGGGGGGGGGGGG-----
                    CATDB1    RDAPXB     DB  SYSADM  2007-05-07 GGGGGGGGGGGGGGG-----
                    CATDB1A   CATDB1     DB  DBMNT   2007-05-07 YY             -----
                    CATDB1B   CATDB1     DB  DBMNT   2007-05-07      YY        -----
                    CATDB1C   CATDB1     DB  DBADM   2007-05-08 GGGGGGGGGGGGGGG-----
                    RDATLF3   RDATLF3    DB          2007-04-26 GGGGGGGGGGGGGGG-----
      ******************************  BOTTOM OF DATA  *******************************

    The columns on a object privileges panel or a User Authorizations List panel reflect the privileges associated with the type of object from which the list was generated. To display more information about the fields on the panel, press HELP.

  3. In the Cmd column beside the source user ID, type REVOKE.

  4. Press Enter.The Confirm SQL for Revoke Reassign panel is displayed below:

    DEFF-R              Confirm SQL for Revoke Reassign                1 to 5 of 5
    Command ===>                                                  Scroll ===> PAGE
    Current SQLID. . . . . . .  RDACRJ2
    Reassign GRANTOR . . . . .
    Edit Options . . . . . . .  N         Y/N Modify SQL processing options
    Edit SQL . . . . . . . . .  N         Y/N Edit SQL before executing
    Reassign Grants. . . . . .  N         Y/N Reassign privilege to another SQLID
    View Cascade Report. . . .  N         Y/N View a report of cascaded privileges
    Save in SQL table. . . . .  N         A/Y/R/N A/Y-Append, R-Replace
     Name of saved SQL . . . .  20110121_115552
    Save in PDS. . . . . . . .  N         Y/N Save SQL in PDS
     PDS(member) . . . . . . .  ACT.V10.DATABASE(TEST)
    Execute SQL. . . . . . . .  N         Y/N Execute the SQL
    -------------------------------------  SQL  -----------------------------------
    REVOKE DBADM
           ON DATABASE
              ACTADMN1
         FROM CATDB1
           BY RDAPXB ;
    ******************************* Bottom of data ********************************
  5. On the Confirm SQL for Revoke Reassign panel, you can perform several actions before executing the SQL to revoke privileges:

    1. From the Command line, issue the SET sqlid command to change the value of the current SQLID.

      Warning

      Note

      The ID shown in the Current SQLID field must have the proper authority to perform the specified SQL REVOKE statement. If you hold a primary- or secondary-authorization ID that has the proper authority, you can change the Current SQLID to that authorization ID and complete the CREATE. To change the Current SQLID, use the SET command.

    2. In the Reassign GRANTOR field, type the SQLID of a new grantor for the cascading authorizations.

      Warning

      Note

      With proper authorization, you can issue the SET sqlid command on the Command line of the User List panel to change the value of both the Current SQLID and the Reassign GRANTOR fields.

    3. In the Edit options field, type Y to modify the default values for the options on the Confirm SQL for Revoke Reassign panel. Then, press Enter.The Options panel is displayed. In the Edit SQL and Confirm options field, type Y to display the options for the Confirm SQL panel. Press END to return to the Confirm SQL for Revoke Reassign panel.
    4. In the Edit SQL field, type Y to invoke an ISPF edit session to edit the SQL. Then, press Enter.Press END to save the SQL and return to the Confirm SQL for Revoke Reassign panel.

    5. In the Reassign Grants field, type Y to reassign the privileges to the SQLID specified in the Reassign GRANTOR field. The product will generate executable GRANT statements with the SQLID specified in the Reassign GRANTOR field.

      Error
      Warning

      Revoking a privilege that created a view also drops the view and any dependent INSTEAD OF triggers. To re-create the view and the triggers, you must specify Y for the Reassign Grants field. Catalog Manager then generates the CREATE VIEW and CREATE TRIGGER statements and inserts them into the SQL after the REVOKE statement.

      The CREATE VIEW statement still identifies the revoked SQLID as the creator of the view. Save and edit the SQL to change the creator.

      The Confirm SQL for Revoke Reassign Grants panel is displayed below:

      DBDC-R         Confirm SQL for Revoke Reassign Grants            1 to 12 of 118
      Command ===>                                                  Scroll ===> PAGE

      Current SQLID. . . . . .  RDACRJ2
      Edit Options . . . . . .  N           Y/N Modify SQL processing options
      View Cascade Report. . .  N           Y/N View a report of cascaded privileges
      Edit SQL . . . . . . . .  N           Y/N Edit SQL before executing
      Save in SQL table. . . .  N           A/Y/R/N A/Y-Append, R-Replace
       Name of saved SQL . . .  20110121_115552
      Save in PDS. . . . . . .  N           Y/N Save SQL in PDS
       PDS(member) . . . . . .  ACT.V10.DATABASE(TEST)

      Execute SQL. . . . . . .  N           Y/N Execute the SQL
      -------------------------------------  SQL  -----------------------------------
         REVOKE DBADM
             ON DATABASE
                ACTADMN1
           FROM CATDB1
             BY RDAPXB
                                                                             ;
      --
      -- INSTALL SYSADM1: CSTCXN   SYSADM2: RDAPXB
      -- CATDB1 AS GRANTOR ON 2010-09-17-10.23.39.795807
      -- CATDB1 AS GRANTEE ON 2010-08-11-16.34.35.323882
      -- ASSUMING CATDB1 WAS NEVER INSTALL SYSADM
      --

      The SQL section of the panel shows the sequence of the SQL execution. After revoking the user privileges, the new SQLID executes the GRANT statements.

    6. In the View Cascade Report field, enter Y to view the cascade report, which shows which privileges are affected when the REVOKE action is executed. Then, press Enter.The Cascade List Report for Revoke/Reassign panel is displayed in the following figure. For information about the Cascade List Report, see Generating-the-cascade-report.

      DBDC-R          Cascade list report for revoke/reassign            1 to 11 of 12
      Command ===>                                                  Scroll ===> PAGE

      Current SQLID. . . . . .  RDACRJ2
      Edit Options . . . . . .  N           Y/N Modify SQL processing options
      Save in SQL table. . . .  N           A/Y/R/N A/Y-Append, R-Replace
       Name of saved SQL . . .  20110121_115552
      Save in PDS. . . . . . .  N           Y/N Save SQL in PDS
       PDS(member) . . . . . .  ACT.V10.DATABASE(TEST)
      ------------------------- Report of Cascaded Grants ---------------------------
        Grantee   Grantor  WithGrant  Type  Name
                  Privs     Option
      -------------------------------------------------------------------------------
      --
      -- INSTALL SYSADM1: CSTCXN   SYSADM2: RDAPXB
      -- CATDB1 AS GRANTOR ON 2010-09-17-10.23.39.795807
      -- CATDB1 AS GRANTEE ON 2010-08-11-16.34.35.323882
      -- ASSUMING CATDB1 WAS NEVER INSTALL SYSADM
      --
        CATADMDB  CATDB1       G       DB   ACTADMN1
                  DBADM
        CATDB1A   CATDB1               DB   ACTADMN1
                  CREATETAB, CREATETS
        CATDB1B   CATDB1               DB   ACTADMN1

    7. In the Save in SQL table field, enter A, Y, R, or N to specify whether to save the SQL in the Catalog Manager SQL_Table.

      To perform this action

      Type

      Append the SQL to the SQL in the SQL_Table

      A

      Save the SQL in the SQL_Table

      Y

      Replace the SQL in the SQL_Table

      R

      Discard the SQL

      N

    8. In the Name of saved SQL field, enter a name for the SQL.
    9. In the Save in PDS field, enter Y to save the SQL in a member of a partitioned data set (PDS).The saved SQL uses the ID displayed in the Current SQLID field as the object qualifier. If the SQL is not saved, the ID in the Current SQLID is used only to identify Db2 authority.
    10. In the PDS(member) field, enter the name of the PDS and member.
    11. In the Execute SQL field, enter Y to execute the SQL displayed on the Confirm SQL for Revoke Reassign panel.
    12. Press Enter.The SQL Progress Indicator panel is displayed. The panel automatically refreshes to display the status of the SQL that is being executed.

Related topic

Revoking-privileges

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

BMC AMI Catalog Manager for Db2 12.1