IC71059: WRONG SELECT PERMISSIONS FOR VIEWS REFFERING TO TABLES IN ANOTHER DATABASE

APAR status

• Closed as program error.

Error description

• Views which refer to tables in another database retrieve wrong
  select permissions if the owner of the current database does not
  have DBA privileges in the refered database.
  
  A subsequent GRANT statement fails with
  
    302: No GRANT option or illegal option on multi-table view.
  
  The SELECT statement fails with
    272: No SELECT permission for myview.
  
  The GRANT statement does not fail when the view contains a
  subscript of a character field like col[m,n]. Instead it will
  grant select privileges for this view column only.
  
  grant select on "informix".myview2 to "user1" as "informix";
  
  is changed to
  
  grant select(v_col) on "informix".myview2 to "user1" as
  "informix";
  

Local fix

• Grant DBA privileges to the database owner in the second
  database:
  GRANT DBA to "user1"
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users with Informix views that contain subscripted columns,  *
  * and try to extend the permission chain with GRANT.           *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Customer was observing inconsistent behavior while granting  *
  * select permissions on Informix views. If the view had a      *
  * subscripted column, the grant would succeed, but if the view *
  * contained the full column, the grant would fail.             *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to 11.50xC9 or later  (on 11.50 family)              *
  * Upgrade to 11.70xC2 or later  (on 11.70 family)              *
  ****************************************************************
  

Problem conclusion

• The problem was caused by incorrect handling of the permissions
  when creating a view. Permissions for subscripted columns were
  not being assigned in the same way as those for non-subscripted
  columns. New behavior makes the permissions on subscripted
  columns up to par with those of non-subscripted columns. (with
  the exception of UPDATE privilege which is unset by default in
  subscripted columns).
  
  Upgrade to 11.50xC9 or later  (on 11.50 family)
  Upgrade to 11.70xC2 or later  (on 11.70 family)
  

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:

Fix information

• Fixed component name

  IBM IDS ENTRP E

• Fixed component ID

  5724L2304

Applicable component levels

• RB15 PSY

     UP