labtech Database

Parameters

Name	Type	Mode
argUserID	int	IN

Definition

BEGIN
	DECLARE _ticketBasedSec, DEBUG TINYINT(1);
	DECLARE _userGroups, _parents, _children, _accessGroups, _userClasses, _missingGroups, _clientList VARCHAR(100000);
	DECLARE _iterations INT(11);
	DECLARE _userPermissions, _userPermissionsHigh BIGINT(11);
	SET group_concat_max_len=100000;
	SET _userGroups = (SELECT TRIM(TRAILING ',' FROM (SELECT Secondary FROM Users WHERE UserID = argUserID LIMIT 0,1))); -- Secondary.TrimEnd(",")
	CALL sp_SplitSetToTempTable(_userGroups,'tempUserGroups');
	SET _userClasses = (SELECT TRIM(BOTH ',' FROM (REPLACE((SELECT ClientID FROM Users WHERE UserID = argUserID LIMIT 0,1),',,',',')))); -- ClientID.Replace(",,",",").Trim(",")
	CALL sp_SplitSetToTempTable(_userClasses,'tempUserClasses');
	SET _userPermissions = (SELECT BIT_OR(Permissions) FROM userclasses JOIN tempUserClasses ON ClassID=myKey);
	SET _userPermissionsHigh = (SELECT BIT_OR(PermissionsHigh) FROM userclasses JOIN tempUserClasses ON ClassID=myKey);	
	SET _ticketBasedSec = ((SELECT flags FROM Users WHERE UserID = argUserID LIMIT 0,1) & 4 = 4); -- (Flags AND 4) = 4
	IF _ticketBasedSec AND !(_userPermissions & 1 = 1) THEN -- If ticket based security is active and the user is not superadmin
		-- Using ticket based security for this user
		DELETE FROM UserSec WHERE UserSec.UserID = argUserID;
		INSERT IGNORE INTO UserSec (UserID,ClientID,ComputerID) (SELECT DISTINCT argUserID,Computers.ClientID,Computers.ComputerID FROM computers JOIN Tickets ON Tickets.ComputerID=Computers.ComputerID WHERE Tickets.UserID= argUserID AND STATUS<3);
		INSERT IGNORE INTO UserSec (UserID,ClientID,ComputerID) (SELECT DISTINCT argUserID,Tickets.ClientID,0 FROM  Tickets WHERE Tickets.UserID= argUserID AND STATUS<3);
	ELSE
		-- Get children groups of all groups that are explicitly assigned to user.
		SET _children = (SELECT TRIM(BOTH ',' FROM (SELECT GROUP_CONCAT(Children SEPARATOR '') FROM MasterGroups JOIN tempUserGroups ON GroupID=myKey))); -- Pre-trimming Children since it's trimmed every time it's used in code anyway.      
		CALL sp_SplitSetToTempTable(_children,'tempChildren');
		-- Get all access groups by merging the explicitly assigned groups and their children groups.
		DROP TEMPORARY TABLE IF EXISTS tempAccessGroups;
		CREATE TEMPORARY TABLE tempAccessGroups
		(PRIMARY KEY myKey(myKey)) ENGINE=MEMORY 
		SELECT DISTINCT(GroupID) myKey FROM MasterGroups JOIN ((SELECT * FROM tempUserGroups a) UNION (SELECT * FROM tempChildren b)) c ON GroupId = c.myKey WHERE GroupType<2;
		DROP TEMPORARY TABLE IF EXISTS GroupPerm;
		DROP TEMPORARY TABLE IF EXISTS GroupPerm2;
		DROP TEMPORARY TABLE IF EXISTS ClientPerm;
		DROP TEMPORARY TABLE IF EXISTS tempMissingGroups;
		-- Get access groups' permissions
		CREATE TEMPORARY TABLE GroupPerm
		(PRIMARY KEY tgp_pk(Groupid), INDEX tgp_idx(ParentId)) ENGINE=MEMORY
		SELECT mg.GroupID AS GroupId,mg.ParentID,(SELECT BIT_OR(p.Permission) FROM Permissions p JOIN tempUserClasses tuc ON p.ClassId = tuc.myKey WHERE p.permid=mg.permissions  GROUP BY p.permid)  AS permission FROM mastergroups mg JOIN tempAccessGroups tag ON mg.GroupID=tag.myKey;
		-- copy groupperm
		CREATE TEMPORARY TABLE GroupPerm2
		(PRIMARY KEY tgp2_pk(Groupid), INDEX tgp2_idx(ParentId)) ENGINE=MEMORY
		SELECT * FROM GroupPerm;
		-- Get Client Permissions
		CREATE TEMPORARY TABLE ClientPerm 
		(PRIMARY KEY tcp_pk(ClientID)) ENGINE=MEMORY
		SELECT ClientID, 
		IFNULL((SELECT BIT_OR(p.Permission) FROM Permissions p JOIN tempUserClasses tuc ON p.ClassId=tuc.myKey WHERE p.permid=Clients.permissions GROUP BY p.permid),0)  AS permission 
		FROM Clients;
		-- Get parent groups for assigned groups
		CREATE TEMPORARY TABLE tempMissingGroups
		(PRIMARY KEY myKey(myKey)) ENGINE=MEMORY
		SELECT DISTINCT gp.parentid AS myKey FROM groupperm gp LEFT JOIN groupperm2 gp2 ON gp.parentid=gp2.groupid WHERE gp.parentid>0 AND gp2.groupid IS NULL;
		SET _iterations = 0;
		WHILE (SELECT COUNT(*) FROM tempMissingGroups) >0 AND _iterations < 10 DO
			-- Get assigned permissions for new found parent groups
			INSERT IGNORE INTO GroupPerm (SELECT mg.GroupID,mg.ParentID,(SELECT BIT_OR(p.Permission) FROM Permissions p JOIN  tempUserClasses tuc ON p.classid=tuc.myKey WHERE p.permid=mg.permissions GROUP BY p.permid)  AS permission FROM mastergroups mg JOIN tempMissingGroups tmg ON mg.GroupId=tmg.myKey);
			INSERT IGNORE INTO GroupPerm2 (SELECT gp.Groupid, gp.ParentId, gp.Permission FROM GroupPerm gp JOIN tempMissingGroups tmg ON gp.groupid=tmg.myKey);
			TRUNCATE tempMissingGroups;
			-- Continue up the tree and find the next set of parent groups
			INSERT INTO  tempMissingGroups (SELECT DISTINCT gp.parentid FROM groupperm gp LEFT JOIN groupperm2 gp2 ON gp.parentid=gp2.groupid WHERE gp.parentid>0 AND gp2.groupid IS NULL);
			SET _iterations = _iterations + 1;          
		END WHILE;		
		DROP TEMPORARY TABLE IF EXISTS tempMissingGroups;
		DROP TEMPORARY TABLE IF EXISTS tempUserClasses;
		-- Propagate permission down the tree for inherited nodes.
		UPDATE GroupPerm gp JOIN GroupPerm2 gp2 ON gp2.groupid=gp.parentid SET gp.permission=gp2.permission WHERE ISNULL(gp.Permission) AND gp2.permission>0;
		WHILE ROW_COUNT() > 0 DO       
			TRUNCATE TABLE  GroupPerm2;
			INSERT INTO Groupperm2 (SELECT * FROM GroupPerm);
			UPDATE GroupPerm gp JOIN GroupPerm2 gp2 ON gp2.groupid=gp.parentid SET gp.permission=gp2.permission WHERE ISNULL(gp.Permission) AND gp2.permission>0;
		END WHILE;
		-- Create tempUserSec with same structure as usersec table but only care about primary key index
		DROP TEMPORARY TABLE IF EXISTS tempUsersec;
		CREATE TEMPORARY TABLE tempUsersec 
		(PRIMARY KEY pKey(Userid,Clientid,Computerid)) ENGINE=MEMORY
		SELECT * FROM usersec LIMIT 0;
		-- Build Group permissions
		IF (SELECT COUNT(*) FROM MasterGroups) > 0 THEN
			IF (_userPermissions & 72057594037927936 = 72057594037927936) OR (_userPermissions & 1 = 1) THEN -- If user has ComputersShowAll or SuperAdmin permissions
				INSERT IGNORE INTO tempUserSec (UserID,ClientID,ComputerID,Permission) (SELECT DISTINCT argUserID,ClientID,Computers.ComputerID,4294967295 FROM computers);
				INSERT IGNORE INTO tempUserSec (UserID,ClientID,ComputerID) (SELECT DISTINCT argUserID,ClientID,0 FROM Clients);
			ELSE          
				-- Build clients nodes based on groups
				INSERT IGNORE INTO tempUserSec (UserID,ClientID,ComputerID) (SELECT DISTINCT argUserID,ControlID,0 FROM `mastergroups` mg JOIN tempAccessGroups tag ON mg.Groupid=tag.myKey WHERE ABS(mg.Control)=1);
				-- Build computer group/client permissions and don't include computers from groups that are not in the user AccessGroups
				INSERT IGNORE INTO tempUserSec (UserID,ClientID,ComputerID,Permission) 
				(SELECT argUserID,ClientID,Computers.ComputerID,BIT_OR(permission) FROM computers 
				JOIN SubGroups ON subgroups.computerid=computers.computerid
				JOIN tempAccessGroups tag ON SubGroups.GroupID=tag.myKey
				JOIN groupperm gp ON gp.groupid = tag.mykey
				GROUP BY computers.computerid);
				IF (_userPermissions & 1125899906842624 = 1125899906842624) OR (_userPermissions & 1 = 1) THEN -- If user has ClientShowAll or SuperAdmin permissions
					INSERT IGNORE INTO tempUserSec (UserID,ClientID,ComputerID) (SELECT DISTINCT argUserID,ClientID,0 FROM Clients);
				END IF;
			END IF;
		ELSE
			INSERT IGNORE INTO tempUserSec (UserID,ClientID,ComputerID) (SELECT DISTINCT argUserID,ClientID,Computers.ComputerID FROM computers);
			INSERT IGNORE INTO tempUserSec (UserID,ClientID,ComputerID) (SELECT DISTINCT argUserID,ClientID,0 FROM Clients);
		END IF;
		-- Only will work after Permissions.Permission is changed to BigInt
		-- Build Client permissions
		UPDATE tempUserSec,ClientPerm SET tempUserSec.Permission=(tempUserSec.Permission | ClientPerm.Permission) 
		WHERE tempUserSec.ClientID=ClientPerm.ClientID 
		AND tempUserSec.UserID = argUserID;
		DROP TEMPORARY TABLE IF EXISTS tempExclusionGroups;
		CREATE TEMPORARY TABLE tempExclusionGroups
		(PRIMARY KEY myKey(myKey)) ENGINE=MEMORY
		 SELECT DISTINCT(GroupID) myKey FROM MasterGroups  
		JOIN ((SELECT * FROM tempUserGroups tug) UNION (SELECT * FROM tempChildren tc)) t ON GroupId = t.myKey WHERE GroupType=2;
		-- remove exclusion groups
		IF !((_userPermissions & 72057594037927936 = 72057594037927936) OR (_userPermissions & 1 = 1)) THEN -- If the User doesn't have ComputersShowAll or SuperAdmin permissions
			-- Get all of the exclusion groups (GroupType = 2) and remove them from the userSec table for the User.
			DELETE tus FROM tempUserSec tus JOIN subgroups sg ON tus.Computerid=sg.Computerid JOIN tempExclusionGroups teg ON sg.Groupid=teg.myKey WHERE tus.UserID = argUserID ;
		END IF;
		DROP TEMPORARY TABLE IF EXISTS tempExclusionsGroups;
		-- now update the usersec table
		DELETE a FROM UserSec a LEFT JOIN tempUserSec b ON a.UserID=b.UserId AND a.clientid=b.clientid AND a.computerid=b.computerid 
		WHERE a.userid=arguserid AND b.userid IS NULL AND b.clientid IS NULL AND b.computerid IS NULL;
		INSERT IGNORE INTO UserSec (UserID,ClientID,ComputerID,Permission) (SELECT * FROM tempUserSec) ON DUPLICATE KEY UPDATE Permission=VALUES(Permission);
		-- clean temp tables				
		DROP TEMPORARY TABLE IF EXISTS tempUserSec;
		DROP TEMPORARY TABLE IF EXISTS ClientPerm;
		DROP TEMPORARY TABLE IF EXISTS GroupPerm;
		DROP TEMPORARY TABLE IF EXISTS GroupPerm2;
		-- If the User has have GroupsShowAll or SuperAdmin permissions
		IF ((_userPermissionsHigh & 562949953421316 = 562949953421316) OR (_userPermissions & 1 = 1)) THEN 
			-- They get access to all the groups in the system (not all the computers potentially)
			SET _accessGroups = (SELECT GROUP_CONCAT(`GroupId` ORDER BY `GroupId`) FROM `MasterGroups`); 
		ELSE
			-- They only get their direct access groups and all the children underneath UNLESS its an exclusion group (GroupType=2)
			SET _accessGroups = (SELECT GROUP_CONCAT(`GroupId` ORDER BY `GroupId`) FROM `MasterGroups` 
									WHERE `GroupType` != 2 
									AND `GroupId` IN(SELECT `tempUserGroups`.`mykey` FROM `tempUserGroups` 
													 UNION 
													 SELECT `tempChildren`.`myKey` FROM `tempChildren`));
		END IF;		
		-- clean rest of temp tables
		DROP TEMPORARY TABLE IF EXISTS tempChildren;
		DROP TEMPORARY TABLE IF EXISTS tempUserGroups;
		DROP TEMPORARY TABLE IF EXISTS tempAccessGroups;
	END IF;
	SELECT _accessGroups;	
END

sp_UpdateUserSec

Parameters

Definition