{ ************************************************************************* }
{									    }
{			   INFORMIX SOFTWARE, INC.			    }
{									    }
{   Title:	cnv50t60.sql						    }
{   Sccsid:	@(#)cnv50t60.sql	9.1	3/13/92  15:05:17                                            }
{   Description:							    }
{	Add protected database procedure during database construction       }
{									    }
{ ************************************************************************* }

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

   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       char(256);
   define v_owner        char(8);
   define user           procedure;

-- 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 v_tabauth[1] = 's' or v_tabauth[1] = 'S' then
            let is_allowed = 1;
            exit foreach;
         elif v_tabauth[3] = '*' 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 v_colauth[1] = 's' or v_colauth[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
         into v_seqno, v_constructed, v_mode, 
              v_resolution, v_confidence, v_encdat
         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
         with resume;
   end foreach

-- Engine will return 100 to user

end procedure;

grant execute on informix.systdist to public as informix;