public static DataRow[] GetRelationSecurityFields(DbConnection sqlConnection)
{
var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
string contentLinkSql = $@"
SELECT
link_id AS link_id,
l_content_id AS content_id,
r_content_id AS linked_content_id
FROM content_to_content
UNION
SELECT
link_id AS link_id,
r_content_id AS content_id,
l_content_id AS linked_content_id
FROM content_to_content
";
var trueValue = SqlQuerySyntaxHelper.ToBoolSql(dbType, true);
var falseValue = SqlQuerySyntaxHelper.ToBoolSql(dbType, false);
var sqlText = $@"
select coalesce(ca3.content_id, ca1.content_id) as path_content_id, coalesce(ca4.CONTENT_ID, cl.linked_content_id) as rel_content_id, ca1.content_id,
{SqlQuerySyntaxHelper.CastToBool(dbType, $"case when ca1.link_id is not null then {trueValue} else {falseValue} end")} as is_m2m,
{SqlQuerySyntaxHelper.CastToBool(dbType, $"case when ca2.attribute_id is not null then {trueValue} else {falseValue} end")} as is_ext,
ca1.is_classifier,
ca1.attribute_id, ca1.attribute_name, ca1.link_id, ca2.ATTRIBUTE_NAME as agg_attribute_name
from CONTENT_ATTRIBUTE ca1
left join ({contentLinkSql}) cl on ca1.content_id = cl.content_id and ca1.link_id = cl.link_id
left join CONTENT_ATTRIBUTE ca4 on ca1.RELATED_ATTRIBUTE_ID = ca4.ATTRIBUTE_ID
left join content_attribute ca2 on ca1.content_id = ca2.content_id and ca2.AGGREGATED = {trueValue}
left join content_attribute ca3 on ca2.RELATED_ATTRIBUTE_ID = ca3.attribute_Id
where ca1.USE_RELATION_SECURITY = {trueValue}
" ;
return(Common.GetDataTableForQuery(sqlConnection, sqlText).AsEnumerable().ToArray());
}
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);
}
public static IEnumerable <DataRow> GetChildFoldersList(DbConnection sqlConnection, QPModelDataContext context, bool isAdmin, int userId, int id, bool isSite, int?folderId, int permissionLevel, bool countOnly, out int totalRecords)
{
totalRecords = -1;
var dbType = DatabaseTypeHelper.ResolveDatabaseType(sqlConnection);
var entityTypeName = isSite ? EntityTypeCode.OldSiteFolder : EntityTypeCode.ContentFolder;
var parentEntityTypeName = isSite ? EntityTypeCode.Site : EntityTypeCode.Content;
var blockFilter = string.Empty;
var useSecurity = !isAdmin;
int parentLevel;
if (entityTypeName == EntityTypeCode.ContentFolder)
{
useSecurity = false;
parentLevel = CommonSecurity.GetEntityAccessLevel(sqlConnection, context, userId, 0, parentEntityTypeName, id);
if (parentLevel == 0)
{
blockFilter += " AND 1 = 0 ";
}
}
else
{
parentLevel = folderId.HasValue
? CommonSecurity.GetEntityAccessLevel(sqlConnection, context, userId, 0, EntityTypeCode.SiteFolder, folderId.Value)
: CommonSecurity.GetEntityAccessLevel(sqlConnection, context, userId, 0, parentEntityTypeName, id);
}
var securitySql = useSecurity ? PermissionHelper.GetPermittedItemsAsQuery(
context, userId, 0, PermissionLevel.Deny, PermissionLevel.FullAccess,
entityTypeName, parentEntityTypeName, id
) : string.Empty;
var childrenParam = SqlQuerySyntaxHelper.CastToBool(dbType,
$@"
CASE WHEN (
SELECT COUNT(FOLDER_ID) FROM {entityTypeName} WHERE PARENT_FOLDER_ID = c.FOLDER_ID
) > 0 THEN 1 ELSE 0 END
");
var query = $@"
SELECT
{(countOnly
? "COUNT(c.FOLDER_ID) "
: $@"
c.FOLDER_ID,
c.NAME,
c.CREATED,
c.MODIFIED,
c.LAST_MODIFIED_BY,
{childrenParam} AS HAS_CHILDREN,
mu.{Escape(dbType, "USER_ID")} as MODIFIER_USER_ID,
mu.FIRST_NAME as MODIFIER_FIRST_NAME,
mu.LAST_NAME AS MODIFIER_LAST_NAME,
mu.EMAIL AS MODIFIER_EMAIL,
mu.{Escape(dbType, "LOGIN")} AS MODIFIER_LOGIN
{(useSecurity
? $", COALESCE(pi.permission_level, {parentLevel}) as EFFECTIVE_PERMISSION_LEVEL"
: string.Empty
)}
"
)} ";
