Tech Tip: Utility method that checks for user access to a specific table

PRODUCT: 4D | VERSION: 12.5 | PLATFORM: Mac & Win

Published On: March 29, 2013

The following method returns the access status of the specific user to a specific table. The access status is based on the schema assigned to the table.

  // Method: UTIL_TABLE_CHECK_USER_ACCESS
  // Parameters
  //    $1 - Username
  //    $2 - Table ID
  //
  //    $0 - 1 for Read Only, 2 for Read Write
  // ----------------------------------------------------

C_LONGINT($0;$access_l)
C_TEXT($1;$user_name_t)
C_LONGINT($2;$table_id_l)

C_LONGINT($schema_id_l)
C_LONGINT($read_grp_id_l;$read_write_grp_id_l)
C_TEXT($read_grp_name_t;$read_write_grp_name_t)

If (Count parameters>=2)

  $user_name_t:=$1
  $table_id_l:=$2

  Begin SQL
   SELECT SCHEMA_ID
   FROM _USER_TABLES
   WHERE TABLE_ID = <<$table_id_l>>
   INTO :$schema_id_l;
  End SQL

  Begin SQL
   SELECT READ_GROUP_ID, READ_GROUP_NAME,
          READ_WRITE_GROUP_ID, READ_WRITE_GROUP_NAME
   FROM _USER_SCHEMAS
   WHERE SCHEMA_ID = <<$schema_id_l>>
   INTO :$read_grp_id_l, :$read_grp_name_t,
        :$read_write_grp_id_l, :$read_write_grp_name_t;
  End SQL

  // *** If the group has read/write access to the table.
  If ($read_write_grp_id_l>0)

    // *** If the user belongs to this group.
    If (User in group($user_name_t;$read_write_grp_name_t))
      $access_l:=2 // *** Read Write
    End if
  End if

  If ($access_l=0)

    // *** If the group has read only access to the table.
    If ($read_grp_id_l>0)

      // *** If the user belongs to this group.
      If (User in group($user_name_t;$read_grp_name_t))
        $access_l:=1 // *** Read Only
      End if
    Else
      // *** Everyone has read and write access to the table
      $access_l:=2 // *** Read Write
    End if
  End if
End if

$0:=$access_l