models/UB/RLS.js

/**
 * UnityBase Row Level Security routines. For use in rls mixin.
 * @author MPV
 * Comment by Felix: Внимание! Для Оракла НЕЛЬЗЯ начинать алиас таблицы с символа подчеркивания '_'
 */

/*global Session: false, TubDataStore */
/**
 * @namespace
 */
var RLS = UB.ns('RLS');
global['$'] = RLS;

var
    roleCache; // it's safe to cache roles here because uba_role table is readonly in production

RLS.initCache = function(){
    var
        rInst;
    if (!roleCache){
        roleCache = {};
        rInst = new TubDataStore('uba_role');
        // here is direct SQL because permission to uba_role dose not exist for non-supervisor user's
        rInst.runSQL('select ID, name FROM uba_role', {});
        while (!rInst.eof){
            roleCache[rInst.get(1)] = rInst.get(0); // roleCashce['admin'] = 1 e.t.c.
            rInst.next();
        }
    }
};

RLS.currentOwner = function(){
    return '( [mi_owner] = :(' + Session.userID + '): )';
};

/**
 * todo - OPTIMIZE using role cache
 * @param user
 * @param groupname
 * @return {String}
 */
RLS.userInGroup = function(user, groupname){
    return "exists (select 1 from UBA_USERROLE ur inner join UBA_ROLE r ON ur.RoleID = r.ID WHERE  r.name = :('" + groupname + "'): AND ur.UserID = :(" + user + "): )";
};

/**
 * is current ( Session.userID) user have role with name groupname
 * @param groupname group name from uba_role
 * @return {*}
 */
RLS.currentUserInGroup = function(sender,groupname){
var
	groupID;

    RLS.initCache();
    groupID = roleCache[groupname];
    if(groupID && new RegExp('\\b' + groupID + '\\b').test(Session.userRoles)) {
		return '(1=1)'
	}else{
		return '(0=1)'
	}
/*    if ( RLS.isOracle(sender.entity) ){
        return '( BITAND(' + Session.userRoles + ',' + (roleCache[groupname] || 0) + ')!=0 )';
    }
    else {
        return '( (' + Session.userRoles + ' & ' + (roleCache[groupname] || 0) + ')!=0 )';
    }
*/
};

/* Session.userRoles is comma separated string of group ID's
  RLS.currentUserInGroupByFieldName = function(sender, fieldName){
      return '( (' + Session.userRoles + ' & ' + (fieldName || 0) + ')!=0 )';

};*/


/**
*   Check user in adm subtable
*   no user group check performed!
*/
RLS.userInAdmSubtable = function(sender, user){
    return 'exists (select 1 from ' + sender.entity.name + '_adm admsubtable where admsubtable.instanceID = [ID] and admsubtable.admSubjID = :(' + user + '): )';

};

/**
 *   Check staff unit in adm subtable by admOrgUnitID field
 */
RLS.currentStaffUnitInDocAdmSubtable = function(sender, admSubTableName){
    var roles = Session.uData.roles;
    if (/\badmins\b/.test(roles)){
        return '1=1';
    }
    else if (/\ballSeeing\b/.test(roles)){
        return '1=1';
    }
	else if (Session.uData.orgUnitIDs === ''){
		return '1=0';
	}
	else {
        var orgUnitIDs = Session.uData.orgUnitIDs ? Session.uData.orgUnitIDs : -1;
        var result = 'exists (select 1 from ' + admSubTableName + ' admsubtable where admsubtable.instanceID = [ID] and (admsubtable.admOrgUnitID IN (' + orgUnitIDs + ') OR ' +
            'admsubtable.admSubjID IN ('+Session.userRoles+','+Session.userID+')))';
        return result
	}
    //return '1=1';
};

/**
 *  Check sender staff unit
 */
RLS.currentSender = function (sender, user){
    if (Session.uData.orgUnitIDs === ''){
        return '1=0';
    }
    else {
        return '[sendEmployee.staffUnitID] = ' + Session.uData.staffUnitID;
    }
};

/**
 *  Check receiver staff unit
 */
RLS.currentReceiver = function (sender, user){
    if (Session.uData.orgUnitIDs === ''){
        return '1=0';
    }
    else {
        return 'exists (select 1 from fld_receiver rec where rec.docID = [ID] and rec.receiveOrganization = ' + Session.uData.orgUnitIDs.split(',')[0] + ')';
    }
};

RLS.isOracle = function(entity){
    var dialect = entity.connectionConfig.dialect;
    return ((dialect  === TubSQLDialect.Oracle) || (dialect  === TubSQLDialect.Oracle9) ||
          (dialect  === TubSQLDialect.Oracle10) || (dialect  === TubSQLDialect.Oracle11));
};

/** Check user or any of user groups in adm subtable
/*  xmax using ORACLE
* _todo check oracle syntax!!
*/
RLS.userOrUserGroupInAdmSubtable = function(sender, user){
    var result = 'exists (select 1 from ' + sender.entity.name + '_adm admsubtable where admsubtable.instanceID = [ID] and admsubtable.admSubjID in (select ur.RoleID from uba_userrole ur where ur.UserID = :(' + user + '): union select ' + user;
    if ( RLS.isOracle(sender.entity) ){
        return result + ' from dual ))';
    }
    else {
        return result + '))';
    }

  //exists (select 1 from ubm_navshortcut_adm admsubtable where admsubtable.instanceID = navsh.ID and admsubtable.admSubjID in (select RoleID from uba_userrole where UserID = 1000000161401 union select 1000000161401)))
};

RLS.currentUserInAdmSubtable = function(sender){
    return this.userInAdmSubtable(sender, Session.userID);
};

RLS.currentUserOrUserGroupInAdmSubtable = function(sender){
    return this.userOrUserGroupInAdmSubtable(sender, Session.userID);
};


RLS.depForUser = function(sender, user){
    var isOra = RLS.isOracle(sender.entity);
    return '(select ounit.parentID ' +
        'from org_user ouser, org_employee oe, org_employeeonstaff oeos, ' +
        '  org_staffunit osu, org_unit ounit ' +
        'where oe.id = ouser.employeeID ' +
        'and oeos.employeeID = oe.id ' +
        (isOra ?
        'and sysdate between oeos.mi_dateFrom and oeos.mi_dateTo ' :
        'and getdate() between oeos.mi_dateFrom and oeos.mi_dateTo ') +
        'and osu.id = oeos.staffUnitID ' +
        'and ounit.id = osu.ID ' +
        'and ouser.userID = ' + user + ')';
};

RLS.staffUnitForUser = function(sender, user){
    return '(select oeos.staffUnitID ' +
        'from org_user ouser, org_employee oe, org_employeeonstaff oeos ' +
        'where ouser.userID = ' + user + ' ' +
        'and oe.id = ouser.employeeID ' +
        'and oeos.employeeID = oe.id ' +
        (isOra ?
        'and sysdate between oeos.mi_dateFrom and oeos.mi_dateTo)' :
        'and getdate() between oeos.mi_dateFrom and oeos.mi_dateTo)')
        ;
};

RLS.userInIncdocRecipient = function(sender, user){
    return this.staffUnitForUser(sender, Session.userID) + ' in (select b.destID from doc_incdoc_org_stunit b where b.sourceID = [ID])';
};