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

DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows

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 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