private static Dictionary <int, bool> CheckSecurity(DbConnection sqlConnection, int parentId, IEnumerable <int> testIds, int userId, int startLevel, string entityName, string parentEntityName, string columnName)
{
var granted = new Dictionary <int, bool>();
var securitySql = Common.GetPermittedItemsAsQuery(sqlConnection, userId, 0, startLevel, PermissionLevel.FullAccess,
entityName, parentEntityName, parentId);
var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
var trueValue = SqlQuerySyntaxHelper.ToBoolSql(dbType, true);
var falseValue = SqlQuerySyntaxHelper.ToBoolSql(dbType, false);
var sql = $@" select
i.id,
{SqlQuerySyntaxHelper.CastToBool(dbType, $"case when pi.{columnName} is null then {falseValue} else {trueValue} end")} as granted
from {SqlQuerySyntaxHelper.IdList(dbType, "@ids", "i")}
left join ({securitySql}) as pi on pi.{columnName} = i.id " ;
using (var cmd = DbCommandFactory.Create(sql, sqlConnection))
{
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", testIds, dbType));
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
granted[Convert.ToInt32(reader["id"])] = (bool)reader["granted"];
}
}
}
return(granted);
}
private static Dictionary <int, int?> GetPermissionLevels(DbConnection sqlConnection, int[] ids, int userId, int groupId, string entityTypeName, string parentEntityTypeName = "", int parentId = 0)
{
var result = new Dictionary <int, int?>();
var securitySql = Common.GetPermittedItemsAsQuery(sqlConnection, userId, groupId, PermissionLevel.Deny, PermissionLevel.FullAccess,
entityTypeName, parentEntityTypeName, parentId);
var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
var sql = $@" select i.id, pi.permission_level from {SqlQuerySyntaxHelper.IdList(dbType, "@ids", "i")}
left join ({securitySql}) as pi on pi.{entityTypeName}_id = i.id " ;
using (var cmd = DbCommandFactory.Create(sql, sqlConnection))
{
cmd.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@ids", ids, dbType));
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
result[Convert.ToInt32(reader["id"])] = Converter.ToNullableInt32(reader["permission_level"]);
}
}
}
return(result);
}
public static string GetSecurityPathSql(DatabaseType dbType, List <RelationSecurityPathItem> items, int contentId)
{
if (items == null)
{
throw new ArgumentNullException(nameof(items));
}
var sqlBuilder = new StringBuilder();
var lastItem = items.Last();
var selects = Enumerable.Repeat(lastItem, 1).Concat(lastItem.Secondary).Select(item => GetSelectExpression(dbType, item));
sqlBuilder.Append($"select c0.content_item_id as id, {string.Join(", ", selects)} from content_{contentId}_united c0 ");
foreach (var item in items)
{
if (item != lastItem)
{
if (item.LinkId.HasValue)
{
throw new ApplicationException("Invalid security path settings: m2m");
}
if (item.Secondary.Any())
{
throw new ApplicationException("Invalid security path settings: secondary");
}
}
if (!item.LinkId.HasValue)
{
if (item.Extensions.Any())
{
var first = item.Extensions.First();
var inner = string.Join("union all " + Environment.NewLine,
item.Extensions.Select(n =>
n.LinkId.HasValue
? $@"select {n.AggAttributeName} as agg, il.linked_item_id as {first.AttributeName} From content_{n.ContentId}_united c
inner join item_link il on c.content_item_id = il.item_id and il.link_id = {n.LinkId.Value} {Environment.NewLine} "
: $"select {n.AggAttributeName} as agg, {n.AttributeName} as {first.AttributeName} From content_{n.ContentId}_united {Environment.NewLine}")
);
sqlBuilder.AppendFormat("inner join ({3}{0}{3}) c{1} on c{1}.agg = c{2}.CONTENT_ITEM_ID{3}", inner, first.Order,
item.JoinOrder, Environment.NewLine);
sqlBuilder.AppendFormat("inner join content_{0}_united c{1} on c{2}.{3} = c{1}.CONTENT_ITEM_ID{4}",
first.RelContentId, item.Order, first.Order, first.AttributeName, Environment.NewLine);
}
else if (!item.IsClassifier)
{
sqlBuilder.AppendFormat("inner join content_{0}_united c{1} on c{2}.{3} = c{1}.CONTENT_ITEM_ID{4}",
item.RelContentId, item.Order, item.JoinOrder, item.AttributeName, Environment.NewLine);
}
}
if (item == lastItem && item.Secondary != null)
{
foreach (var secItem in item.Secondary.Where(n => !n.LinkId.HasValue && !n.IsClassifier))
{
sqlBuilder.AppendFormat("inner join content_{0}_united c{1} on c{2}.{3} = c{1}.CONTENT_ITEM_ID{4}",
secItem.RelContentId, secItem.Order, secItem.JoinOrder, secItem.AttributeName, Environment.NewLine);
}
}
}
sqlBuilder.Append($"where c0.content_item_id in (select id from {SqlQuerySyntaxHelper.IdList(dbType, "@ids", "i")})");
return(sqlBuilder.ToString());
}
internal static string IdList(DatabaseType databaseType, string name, string alias = "i") => SqlQuerySyntaxHelper.IdList(databaseType, name, alias);
