Use of the Alfabet parameter CURRENT_MANDATE in native SQL

The Alfabet parameter CURRENT_MANDATE returns the mandate mask resulting from the current mandate settings the user is logged in with. When using the Alfabet parameter in native SQL query language, you must take into account the way mandate masks are defined and compared in Alfabet.

The assignment of objects to mandates is stored in the database column MANDATEMASK in the database tables for all object classes that can have mandates in the Alfabet database. The mandate mask is an integer that represents a bit string calculated based on the number and kind of mandates assigned to an object.

When a company specifies a new mandate, an object of the class ALFA_MANDATE is created. Each mandate has an ID property. The ID is automatically assigned to the new mandate. The ID is a running number starting with 1 that defines the position of the mandate in the bit string of the mandate mask.

The mandate mask consists of one bit for each mandate. When the object is assigned to a mandate, the bit on the position of the mandate in the mask is set to 1. If the object is not assigned to a mandate, the bit on the position of the mandate is set to 0. The first bit in the bit string (the position 0) is set to 1 if the object is not assigned to any mandate. For objects assigned to a mandate, the first bit in the bit string is set to 0.

To evaluate whether an object is visible to a user, the mandate mask of the object is compared bit-wise with a mandate mask generated from the mandate settings of the current user.

When the mandate-related access permissions for an object are evaluated, the mandate mask of the current object is compared with the mandate mask of the current mandate that the user is logged in with. This mandate mask consists of one bit for each mandate. The bit on the position of the current mandate and the first bit in the bit string is set to 1.

During evaluation of access permissions, the current user mandate mask and the object mandate mask are compared bitwise. If the bit on a position is 1 for both object and user, the resulting bit on the position is 1. If either the user or the object or both has a bit value of 0, the bit on the position is set to 0.

The result is a bit string representing an integer. If the bit string contains at least one bit set to 1, the resulting integer is higher than 0 and the object is visible to the user.

The Alfabet parameter CURRENT_MANDATE returns the current user mandate mask. You can use the parameter in native SQL to compare the bit strings of the current user mandate mask with the MANDATEMASK of the current object. For example, to show a list of all applications the current user can access with his/her current mandate settings, the query can be defined as:

• SELECT app.REFSTR, app.NAME + ' ' + app.VERSION AS 'With your current mandate, you have access to these application objects:'
• FROM APPLICATION app, PERSON user
• WHERE usr.REFSTR = @CURRENT_USER
• AND (bitand(app.MANDATEMASK,@CURRENT_MANDATE)<>0)

To list all applications a user can access with any of the mandates assigned to him/her, the Alfabet parameter CURRENT_MANDATE is not suitable. Instead, the native SQL query must compare the MANDATEMASK property of the objects with the mandate settings of the user that are defined by a reference array with the property MANDATES of the object class PERSON. To show a list of all applications that the current user can access with any of the mandates assigned to him/her, the native SQL query can be defined as:

• SELECT app.REFSTR, app.NAME + ' ' + app.VERSION AS 'According to all your mandates combined, you have access to these application objects:'
• FROM APPLICATION app, PERSON user
• WHERE usr.REFSTR = @CURRENT_USER
• AND 0 <>app.MANDATEMASK &
  • (
  • SELECT ISNULL(SUM(POWER(2,m.ID)),0)+1
  • FROM ALFA_MANDATE m
  • WHERE (usr.MANDATE_MASTER=1
    • OR EXISTS(SELECT *
    • FROM RELATIONS
    • WHERE FROMREF=usr.REFSTR
    • AND PROPERTY = 'MANDATES'
    • AND TOREF=m.REFSTR))
  • )