problem:
i have role with multiple policies and every policy have multiple permissions relates.
so i need to validate end user to insert new role with restriction to prevent repeate roles with the same policies and permissions.
solution:
create stored procedure for sql database to check if role (policies and related permissions) exists befor
if exist the procedure will return the name for rule and application will show error message that the role is with the same permissions exists before you can use it.
1- create user defined type to pass to procedure which will be passed from application and checked by procedure.
Create Type RolePermissionType
as Table (
policyid int,
TenantId int,
PermissionId int
)
-------------------------------------------------------------------------------------------------------------------
2-create the procedure based on input with type RolePermissionType.
Create Procedure [dbo].[sp_Check_RoleExist](@Mypermission RolePermissionType readonly , @RoleNameOut nvarchar(200) OUTPUT) as
begin
declare @itemsCount int =(select count(*) from @Mypermission);
declare @QueryCount int ;
declare @RoleId int;
declare @RoleName nvarchar(200)
PRINT '@itemsCount = ' + CONVERT(varchar(10), @itemsCount)
DECLARE db_cursor CURSOR FOR
SELECT roleId ,a.Name
FROM [PolicyServerDb].[dbo].[PermissionAssignments] ps
inner join [PolicyServerDb].dbo.Roles a on a.id =ps.RoleId and a.PolicyId=32
group by RoleId ,Name
having count(roleId) =@itemsCount
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @RoleId ,@RoleName
WHILE @@FETCH_STATUS = 0
BEGIN
set @QueryCount =(
select count(*) from
(
select PolicyId,TenantId,PermissionId from [PolicyServerDb].[dbo].[PermissionAssignments]
where RoleId=@RoleId
intersect select PolicyId,TenantId,PermissionId from @Mypermission
)as x )
if(@QueryCount = @itemsCount)
set @RoleNameOut = @RoleName
else
set @RoleNameOut=''
FETCH NEXT FROM db_cursor INTO @RoleId ,@RoleName
END
CLOSE db_cursor
DEALLOCATE db_cursor
end
-------------------------------------------------------------------
3-C# function which returns rule name in case roleExists
public string GetRoleNameByPermissionAssignments(PackageViewModel model)
{
string rolenameout = string.Empty;
DataTable dt = new();
dt.Clear();
_ = dt.Columns.Add("policyid");
_ = dt.Columns.Add("TenantId");
_ = dt.Columns.Add("PermissionId");
foreach (PolicyPermissions item in model.PolicyPermissions)
{
foreach (SimplePermission permission in item.permissions)
{
_ = dt.Rows.Add(item.PolicyId, model.TenantId, permission.PermissionId);
}
}
using (PolicyServerDbContext context = new())
{
SqlParameter Par1 = new("@Mypermission", dt)
{
TypeName = "dbo.RolePermissionType",
Direction = ParameterDirection.Input
};
SqlParameter Par2 = new("@RoleNameOut", rolenameout)
{
SqlDbType = SqlDbType.NVarChar,
Size = 200,
Direction = ParameterDirection.Output
};
_ = context.Database.ExecuteSqlRaw("exec dbo.sp_Check_RoleExist @Mypermission={0}, @RoleNameOut={1} out", Par1, Par2);
if (Par2.Value != DBNull.Value)
{
rolenameout = (string)Par2.Value;
}
}
return rolenameout;
}
if return empty string this meaning that role with passed permissions not exists.
DTO Passed To Function:
public class PackageViewModel
{
public int RoleId { get; set; }
public string RoleName { get; set; }
public string PolicyId { get; set; }
public string Tenant { get; set; }
public string TenantId { get; set; }
public string TenantProtected { get; set; }
public string Description { get; set; }
public bool AssignForChildTenant { get; set; }
public int SelectedPolicyId { get; set; }
public List<PolicyPermissions> PolicyPermissions { get; set; }
}
public class PolicyPermissions
{
public int PolicyId { get; set; }
public int ParentId { get; set; }
public string PolicyName { get; set; }
public List<SimplePermission> permissions { get; set; }
}
public class SimplePermission {
public int PermissionId { get; set; }
public string PermissionName { get; set; }
}
