IC89459: ADMIN MOVE TABLE ON TABLE WITH VIEWS - GRANTS NOT PRESERVED ON VIEWS WHEN AUTO_REVAL IS DISABLED OR NO_AUTO_REVAL IS SPECIFIED

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • When issuing ADMIN_MOVE_TABLE for the table with views and the
    dbm cfg AUTO_REVAL is disabled or NO_AUTO_REVAL is specified in
    ADMIN_MOVE_TABLE option, we'll see the error message below:
    
    db2 "create table test.tab2(id1 integer, id2 integer)"
    db2 "insert into test.tab2 values (1,2),(2,3),(3,4),(4,5),(5,6)"
    db2 "create view test.v2 as select * from test.tab2"
    db2 "grant select on test.v2 to newton"
    doas newton "db2 connect to otmdb"
    doas newton "db2 'select * from test.v2'"
    db2 "call
    admin_move_table('TEST','TAB2','','','','','','','','NO_AUTO_REV
    AL','MOVE')"
    
    doas newton "db2 'select * from test.v2'"
    SQL0551N  "NEWTON" does not have the required authorization or
    privilege to
    perform operation "SELECT" on object "TEST.V2".  SQLSTATE=42501
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * users who use admin_move_table procedure                     *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * When issuing ADMIN_MOVE_TABLE for the table with views and   *
    * the                                                          *
    * dbm cfg AUTO_REVAL is disabled or NO_AUTO_REVAL is specified *
    * in                                                           *
    * ADMIN_MOVE_TABLE option, we'll see the error message below:  *
    *                                                              *
    * db2 "create table test.tab2(id1 integer, id2 integer)"       *
    * db2 "insert into test.tab2 values                            *
    * (1,2),(2,3),(3,4),(4,5),(5,6)"                               *
    * db2 "create view test.v2 as select * from test.tab2"         *
    * db2 "grant select on test.v2 to newton"                      *
    * doas newton "db2 connect to otmdb"                           *
    * doas newton "db2 'select * from test.v2'"                    *
    * db2 "call                                                    *
    * admin_move_table('TEST','TAB2','','','','','','','','NO_AUTO *
    * _REV                                                         *
    * AL','MOVE')"                                                 *
    *                                                              *
    * doas newton "db2 'select * from test.v2'"                    *
    * SQL0551N  "NEWTON" does not have the required authorization  *
    * or                                                           *
    * privilege to                                                 *
    * perform operation "SELECT" on object "TEST.V2".              *
    * SQLSTATE=42501                                               *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to v97fp8                                            *
    ****************************************************************
    

Problem conclusion

  • v97fp8 will contain the changes, and the grants in the views
    will be added during the ADMIN_MOVE_TABLE.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC89459

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-01-10

  • Closed date

    2013-04-01

  • Last modified date

    2013-04-01

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    IC91753

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for Linux, UNIX and Windows

Software version:

9.7

Reference #:

IC89459

Modified date:

2013-04-01

Translate my page

Machine Translation

Content navigation