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 )} " )} ";