analytics/informix/etc/cnv50t92.sql

{ ************************************************************************* }
{                                                                           }
{  Licensed Materials - Property of IBM and/or HCL                                     }
{                                                                           }
{  IBM Informix Dynamic Server                                              }
{  (c) Copyright IBM Corporation 1996, 2004 All rights reserved.            }
{  (c) Copyright HCL Technologies Ltd. 2017.  All Rights Reserved.             }
{                                                                           }
{ ************************************************************************* }
{                                                                           }
{   Title:	cnv50t92.sql						    }
{   Description:							    }
{	Add protected database procedure during database construction       }
{									    }
{ ************************************************************************* }
{ Danish locale (da_dk.ISO-7-Danish) seems to be unique in that it contains }
{ no [,], { or } character at all. That is why we avoid using these         }
{ characters. User should not use subscript operators and change it to      }
{ substr function.                                                          }
{ ************************************************************************* }

create dba procedure informix.systdist (table_id int, column_no int)
   returning int, date, char(1), 
             smallfloat, smallfloat, stat, char(1);

   define v_tabauth      char(8);
   define v_colauth      char(3);
   define is_allowed     int;
   define search_columns int;
   define v_colno        smallint;
   define v_seqno        int;
   define v_constructed  date;
   define v_mode         char(1);
   define v_resolution   smallfloat;
   define v_confidence   smallfloat;
   define v_encdat       stat;
   define v_owner        char(8);
   define user           procedure;
   define v_stattype	 char(1);

-- First verify that the current user has select privileges on this column

   let is_allowed = 0;
   let search_columns = 0;

-- Check sysusers to see if the usertype is 'D', ie., the
-- current user has dba privileges and may see any columns.
   select usertype
      into v_mode
      from informix.sysusers
      where username = user;
   if v_mode = 'D' then
      let is_allowed = 1;
   else
      -- See if the user owns the table, and therefore can see the columns.
      select owner
         into v_owner
	 from informix.systables
	 where tabid = table_id;
      if v_owner = user then
         let is_allowed = 1;
      end if
   end if

   if is_allowed = 0 then
      foreach 
         select tabauth 
            into v_tabauth
            from informix.systabauth
            where tabid = table_id and
                  (grantee = user or
                   grantee = 'public')
         if substr(v_tabauth, 1, 1) = 's' or substr(v_tabauth, 1, 1) = 'S' then
            let is_allowed = 1;
            exit foreach;
         elif substr(v_tabauth, 3, 1) = '*' then
            let search_columns = 1;
         end if
      end foreach
   end if

-- Search syscolauth only if user does not have select 
-- privileges on all columns.  If the user has no select
-- privileges on any column, then we need search no further.

   if is_allowed = 0 and search_columns = 1 then
      foreach
         select colauth
            into v_colauth
            from informix.syscolauth
            where tabid = table_id and
                  colno = column_no and
                  (grantee = user or
                   grantee = 'public')
         if substr(v_colauth, 1, 1) = 's' or substr(v_colauth, 1, 1) = 'S' then
            let is_allowed = 1;
            exit foreach;
         end if
      end foreach
   end if

-- Return with no rows found if not allowed to select from
-- the column designated by (tabid,colno).

   if is_allowed = 0 then
      raise exception -272;
   end if

-- Now find the distribution rows

   foreach
      select  seqno, constructed, mode, 
              resolution, confidence, encdat, type
         into v_seqno, v_constructed, v_mode, 
              v_resolution, v_confidence, v_encdat, v_stattype
         from informix.sysdistrib
         where tabid = table_id and
               colno = column_no
         order by seqno
      return v_seqno, v_constructed, v_mode, 
             v_resolution, v_confidence, v_encdat, v_stattype
         with resume;
   end foreach

-- Engine will return 100 to user

end procedure;

grant execute on informix.systdist to public as informix;