public List <int> QueryGroupAuthorization(QueryGroupAuthorizationCriteria command)
{
const string query = @"
DECLARE @RequestOrganizationUserGroups TABLE
(
GroupId int
)
DECLARE @AllowedOrganizationUserIds TABLE
(
OrganizationUserEntityId int
)
-- #1 - Identify the groups that belong to the Requested OrganizationUserId
INSERT INTO @RequestOrganizationUserGroups (GroupId)
SELECT
g.GroupEntityId
FROM
[dbo].[GroupMember] gm
INNER JOIN dbo.[Group] g ON gm.GroupId = g.GroupEntityId AND g.DeletedAt IS NULL
WHERE
gm.OrganizationId = @OrganizationId
AND gm.DeletedAt IS NULL
AND gm.OrganizationUserId = @RequestOrganizationUserId
--SELECT * FROM @RequestOrganizationUserGroups roug
--#2 - Identify all the Children (if any) from the Parent Group
;WITH cte AS
(
SELECT
g.GroupEntityId
FROM
dbo.[Group] g
INNER JOIN @RequestOrganizationUserGroups roug ON roug.GroupId = g.GroupEntityId
UNION ALL
SELECT
g.GroupEntityId
FROM
dbo.[Group] g
JOIN cte ON g.ParentGroupId = cte.GroupEntityId
)
--#3 - Get all the OrganizationUserIds that belong to children group
INSERT INTO @AllowedOrganizationUserIds (OrganizationUserEntityId)
SELECT OrganizationUserId
FROM cte
INNER JOIN [dbo].[GroupMember] gm ON gm.GroupId = cte.GroupEntityId
WHERE gm.DeletedAt IS NULL
--SELECT * FROM @AllowedOrganizationUserIds
--#4 Find OrganizationUserIds that have no GROUP associated
INSERT INTO @AllowedOrganizationUserIds (OrganizationUserEntityId)
SELECT
ou.OrganizationUserEntityId
FROM
dbo.OrganizationUser ou
LEFT JOIN [dbo].[GroupMember] gm
ON ou.OrganizationUserEntityId = gm.OrganizationUserId
AND ou.OrganizationId = gm.OrganizationId
WHERE
ou.OrganizationId = @OrganizationId
AND ou.DeletedAt IS NULL
AND ou.RollbackedAt IS NULL
AND
(
(gm.OrganizationUserId IS NULL)
OR
--selects users that were in groups but were deleted
(gm.DeletedAt IS NOT NULL AND gm.OrganizationUserId NOT IN (SELECT OrganizationUserId From [dbo].[GroupMember] WHERE DeletedAt IS NULL))
)
--#5 return data in such a way that can be consumed by EF core (name of the column)
SELECT DISTINCT
ou.*
FROM dbo.OrganizationUser ou
INNER JOIN @AllowedOrganizationUserIds aoui
ON aoui.OrganizationUserEntityId = ou.OrganizationUserEntityId
WHERE ou.OrganizationId = @OrganizationId
";
//TODO: this last block (#5) of code was replaced by the directly selecting the @AllowedOrganizationUserIds table because it already contains group users + users that are not in any group;
/*
* SELECT
* gm.*
* FROM
* dbo.GroupMember gm
* INNER JOIN @AllowedOrganizationUserIds aoui
* ON aoui.OrganizationUserId = gm.OrganizationUserId
* WHERE
* gm.OrganizationId = @OrganizationId
*/
var organizationId = new SqlParameter("@OrganizationId", command.OrganizationId);
var requestOrganizationUserId = new SqlParameter("@RequestOrganizationUserId", command.RequestOrganizationUserId);
return(context.
OrganizationUsers
.FromSql(query, organizationId, requestOrganizationUserId)
.AsNoTracking()
.Select(b =>
b.OrganizationUserEntityId)
.ToList());
}
public Result <QueryGroupAuthorizationResult> QueryGroupAuthorization(QueryGroupAuthorizationCriteria command)
{
if (!command.OrganizationId.HasValue)
{
return(new Result <QueryGroupAuthorizationResult>(GroupAuthorizationServiceErrors.InvalidQueryGroupAuthorizationDataError(nameof(command.OrganizationId))));
}
if (!command.RequestOrganizationUserId.HasValue)
{
return(new Result <QueryGroupAuthorizationResult>(GroupAuthorizationServiceErrors.InvalidQueryGroupAuthorizationDataError(nameof(command.RequestOrganizationUserId))));
}
if (!organizationUserRepository.OrganizationUserExists(command.RequestOrganizationUserId.Value, command.OrganizationId.Value))
{
return(new Result <QueryGroupAuthorizationResult>(GroupAuthorizationServiceErrors.InvalidQueryGroupAuthorizationDataError(nameof(command.RequestOrganizationUserId))));
}
var permissionsForRequestedUser = groupAuthorizationRepository.QueryGroupAuthorization(command);
List <GroupAuthorizationPermission> permissionsResult;
// this way we can support fetching all permitted users, when no target list is sent
if (command.TargetOrganizationUserIdCollection != null && command.TargetOrganizationUserIdCollection.Any())
{
//if all targets are the same as the request, returns allowed
if (command.TargetOrganizationUserIdCollection.All(tou => tou == command.RequestOrganizationUserId))
{
permissionsResult = command.TargetOrganizationUserIdCollection.Select(tou =>
new GroupAuthorizationPermission
{
TargetOrganizationUserId = tou,
Allowed = true
}).ToList();
}
else
{
permissionsResult = new List <GroupAuthorizationPermission>();
foreach (var organizationUserId in command.TargetOrganizationUserIdCollection)
{
permissionsResult.Add(new GroupAuthorizationPermission
{
TargetOrganizationUserId = organizationUserId,
Allowed = permissionsForRequestedUser.Any(ou => ou == organizationUserId)
});
}
}
}
else
{
//send all
permissionsResult = permissionsForRequestedUser.Select(ou => new GroupAuthorizationPermission
{
TargetOrganizationUserId = ou,
Allowed = true
}).ToList();
}
var result = new QueryGroupAuthorizationResult
{
RequestOrganizationUserId = command.RequestOrganizationUserId.Value,
OrganizationId = command.OrganizationId.Value,
Permissions = permissionsResult
};
return(new Result <QueryGroupAuthorizationResult>(result));
}