internal static string GetSelectGroupContentPermissionSql(int groupID, GroupAdministration.AssociationTypes associationType) { StringBuilder sql = new StringBuilder(); string selectColsContentTable = "C.FeatureName, C.FeatureValue FROM " + CONTENT_TBLE_NAME + " C "; if (associationType == GroupAdministration.AssociationTypes.Assign) { sql.Append("SELECT X." + GROUP_ID_COL_XREFTBL + ", "); sql.Append(selectColsContentTable); sql.Append(","); sql.Append(GROUP_CONTENT_XREF_TBL + " X "); sql.Append("WHERE "); sql.Append("X." + GROUP_CONTENT_FEATUREID_COL + " = C.ID "); sql.Append("AND X.GroupID = " + groupID); } else { sql.Append("SELECT " + groupID + " as GroupID,"); sql.Append(selectColsContentTable); sql.Append("WHERE C.ID NOT IN"); sql.Append("("); sql.Append(" SELECT X." + GROUP_CONTENT_FEATUREID_COL); sql.Append(" FROM " + GROUP_CONTENT_XREF_TBL + " X"); if (associationType == GroupAdministration.AssociationTypes.UnAssign) { sql.Append(" WHERE X.GroupID = " + groupID); } sql.Append(")"); } sql.Append(" GROUP BY C.FeatureName, C.FeatureValue"); sql.Append(";"); return(sql.ToString()); }
public GroupPermissions GetGroupPermissions(MGGroup group, GroupAdministration.AssociationTypes associationType) { if (group == null) { Logger.LogError(5, "NULL group found can not find permissions."); return(null); } GroupPermissions groupPermissions = null; GroupOperations groupOps = null; Logger.Log("Getting group permissions for group with ID " + group.ID + " and name " + group.Name + "..."); try { Logger.Log("Getting group security permission key value pairs ..."); // Extract the Application Level list of groups, along with the relevant cross references to Users and the content etc ... groupOps = new GroupOperations(Lcf); Logger.Log("Start getting groups->content lookup..."); List <MGSecurityTag> groupContentList = groupOps.GetGroupContentDictionary(group.ID, associationType); if (groupContentList == null) { Logger.LogError(5, "Got NULL list for group with name " + group.Name + " and ID " + group.ID + " groups->content lookup, abandoning getting group permissions!"); return(null); } Logger.Log("Start getting groups->display lookup..."); List <MGSecurityTag> groupDisplayList = groupOps.GetGroupDisplayDictionary(group.ID, associationType); if (groupDisplayList == null) { Logger.LogError(5, "Got NULL list for group with name " + group.Name + " and ID " + group.ID + " groups->display lookup, abandoning getting group permissions!"); return(null); } Logger.Log("Start getting groups->functionality lookup..."); List <MGSecurityTag> groupFunctionalityList = groupOps.GetGroupFunctionalityDictionary(group.ID, associationType); if (groupFunctionalityList == null) { Logger.LogError(5, "Got NULL list for group with name " + group.Name + " and ID " + group.ID + " groups->functionality lookup, abandoning getting group permissions!"); return(null); } Logger.Log("Finished getting group security permission key value pairs."); groupPermissions = GetGroupPermissions(group, groupContentList, groupDisplayList, groupFunctionalityList); if (groupPermissions == null) { Logger.LogError(5, "Failed to get group permissions for group with name " + group.Name + " and ID " + group.ID + "!"); return(null); } Logger.Log("Finished getting group permissions for group with name " + group.Name + " and ID " + group.ID + "."); } catch (Exception ex) { Logger.LogError(5, "Error getting all group permissions at " + ex.StackTrace); groupPermissions = null; } finally { if (groupOps != null) { groupOps.Finish(); } } return(groupPermissions); }
/// <summary> /// Getting Group to Display Permission Information /// </summary> /// <param name="groupID">Group ID to get permission for.</param> /// <param name="associationType">Assign, UnAssign, NotAssigned</param> /// <returns>List of MGSecurityTag</returns> public List <MGSecurityTag> GetGroupDisplayDictionary(int groupID, GroupAdministration.AssociationTypes associationType) { List <MGSecurityTag> groupTags = null; string sql = null; bool addIDcol = false; bool addDescCol = false; Logger.Log("Start getting the group to display dictionary for group id = " + groupID + " and assiciation type."); try { if (dbInfo.ColumnExists(GroupQB.DISPLAY_TBLE_NAME, "ID")) { addIDcol = true; } if (dbInfo.ColumnExists(GroupQB.DISPLAY_TBLE_NAME, "Description")) { addDescCol = true; } sql = GroupQB.GetSelectGroupDisplayPermissionSql(groupID, addIDcol, addDescCol, associationType); List <string[]> data = dbInfo.GetDataList(sql); if (data == null) { Logger.LogError(5, "Error getting group to display permissions for sql: " + sql); return(null); } else if (data.Count == 0) { Logger.Log("No record was found in the database for sql :" + sql); return(new List <MGSecurityTag>()); } Logger.Log("Start building the Security Dictionary."); Dictionary <int, List <MGSecurityTag> > dict = BuildSecurityDictionary(data); if (dict == null) { Logger.LogError(5, "Error, got Null Security Dictionary when getting group to display dictionary . Quitting!"); return(null); } else if (dict.Count == 0) { Logger.LogError(5, "Error, got Empty Security Dictionary when getting group to display dictionary. Quitting!"); return(null); } else if (dict.Count > 1) { Logger.LogError(5, "Invalid number of entries forud in the Security Dictionary when getting group to display dictionary"); return(null); } else if (!dict.ContainsKey(groupID)) { Logger.LogError(5, "Error, required group id is not found in the Security Dictionary when getting group to display dictionary. Quitting!"); return(null); } Logger.Log("Start Getting Security Tag when Getting group to display dictionary."); groupTags = dict[groupID]; if (groupTags == null) { Logger.LogError(5, "Error, Null Security Tag found when getting group to display dictionary. Quitting!"); return(null); } } catch (Exception ex) { Logger.LogError(5, "Error Getting Group to display Permission Information at " + ex); return(null); } return(groupTags); //// TODO: make this safe //return BuildSecurityDictionary(data)[groupID]; }
/// <summary> /// Getting Group to Contenet Permission Information /// </summary> /// <param name="groupID">Group ID to get permission for.</param> /// <param name="associationType">Assign, UnAssign, NotAssigned</param> /// <returns>List of MGSecurityTag </returns> public List <MGSecurityTag> GetGroupContentDictionary(int groupID, GroupAdministration.AssociationTypes associationType) { List <MGSecurityTag> groupTags = null; string sql = null; Logger.Log("Start getting the group to content dictionary given a group id and assiciation type."); try { sql = GroupQB.GetSelectGroupContentPermissionSql(groupID, associationType); // TODO: make this checking and single list from single entry dictionary retrieval into a method List <string[]> data = dbInfo.GetDataList(sql); if (data == null) { Logger.LogError(5, "Error getting group to content permissions for sql: " + sql); return(null); } else if (data.Count == 0) { Logger.Log("No record was found in the database for sql :" + sql); return(new List <MGSecurityTag>()); } Logger.Log("Start building the Security Dictionary."); bool isCheckForUniqVals = false; Dictionary <int, List <MGSecurityTag> > dict = BuildSecurityDictionary(data, isCheckForUniqVals); if (dict == null) { Logger.LogError(5, "Error, got Null Security Dictionary. Quitting!"); return(null); } else if (dict.Count == 0) { Logger.LogError(5, "Error, got Empty Security Dictionary. Quitting!"); return(null); } else if (dict.Count > 1) { Logger.LogError(5, "TODO: write log"); return(null); } else if (!dict.ContainsKey(groupID)) { Logger.LogError(5, "Error, required group id is not found in the Security Dictionary. Quitting!"); return(null); } Logger.Log("Start Getting Security Tag."); groupTags = dict[groupID]; if (groupTags == null) { Logger.LogError(5, "Error, Null Security Tag found. Quitting!"); return(null); } } catch (Exception ex) { Logger.LogError(5, "Error Getting Group to Content Permission Information at " + ex); return(null); } return(groupTags); }
internal static string GetSelectUsersForAGroupSql(int groupID, string filterString, GroupAdministration.AssociationTypes associationType) { StringBuilder sql = new StringBuilder(); string selectFromUser = "******" + USER_ID_GENERAL_COL + ",U." + USER_NAME_COL + ",U." + USER_EMAIL_COL + ",U." + USER_JOBTITLE_COL + " FROM " + USER_TBLE_NAME + " U "; sql.Append("SELECT "); sql.Append(selectFromUser); filterString = DatabaseHelper.SQL_INJECTION_CHECK_PARAMETER(false, filterString); //USE SQL INJECTION for search string string filterSQL = " WHERE "; if (filterString != null && filterString != string.Empty) { filterSQL += "(U." + USER_NAME_COL + " like '%" + filterString + "%' OR "; filterSQL += "U." + USER_EMAIL_COL + " like '%" + filterString + "%' OR "; filterSQL += "U." + USER_JOBTITLE_COL + " like '%" + filterString + "%')"; filterSQL += " AND "; } if (associationType == GroupAdministration.AssociationTypes.Assign) { sql.Append(","); sql.Append(GROUP_USER_XREF_TBL + " X "); sql.Append(filterSQL); sql.Append("U." + USER_ID_GENERAL_COL + " = X." + USER_ID_COL); sql.Append(" AND "); sql.Append("X." + GROUP_ID_COL_XREFTBL + " = " + groupID); } else { sql.Append(filterSQL); sql.Append("U." + USER_ID_GENERAL_COL + " NOT IN"); sql.Append("("); sql.Append("SELECT X." + USER_ID_COL); sql.Append(" FROM " + GROUP_USER_XREF_TBL + " X"); if (associationType == GroupAdministration.AssociationTypes.UnAssign) { sql.Append(" WHERE X." + GROUP_ID_COL_XREFTBL + " = " + groupID); } sql.Append(")"); } sql.Append(" ORDER BY "); sql.Append("U." + USER_NAME_COL); sql.Append(";"); return(sql.ToString()); }
internal static string GetSelectGroupDisplayPermissionSql(int groupID, bool includeIDCol, bool includeDescCol, GroupAdministration.AssociationTypes associationType) { StringBuilder sql = new StringBuilder(); string idCol = String.Empty; string descCol = String.Empty; string selectColsDisplayTable = string.Empty; string orderByCol = "D.FeatureValue"; if (includeIDCol) { idCol = ",D.ID"; } if (includeDescCol) { descCol = ",D.Description"; orderByCol = "D.Description"; } selectColsDisplayTable = "D.FeatureName,D.FeatureValue" + idCol + descCol + " FROM " + DISPLAY_TBLE_NAME + " D "; if (associationType == GroupAdministration.AssociationTypes.Assign) { sql.Append("SELECT X." + GROUP_ID_COL_XREFTBL + ", "); sql.Append(selectColsDisplayTable); sql.Append(","); sql.Append(GROUP_DISPLAY_XREF_TBL + " X "); sql.Append("WHERE "); sql.Append("X." + GROUP_DISPLAY_FEATUREID_COL + " = D.ID "); sql.Append("AND X.GroupID = " + groupID); } else { sql.Append("SELECT " + groupID + " as GroupID,"); sql.Append(selectColsDisplayTable); sql.Append("WHERE D.ID NOT IN"); sql.Append("("); sql.Append("SELECT X." + GROUP_DISPLAY_FEATUREID_COL); sql.Append(" FROM " + GROUP_DISPLAY_XREF_TBL + " X"); if (associationType == GroupAdministration.AssociationTypes.UnAssign) { sql.Append(" WHERE X.GroupID = " + groupID); } sql.Append(")"); } sql.Append(" GROUP BY D.FeatureName, D.FeatureValue"); sql.Append(" ORDER BY "); sql.Append(orderByCol); sql.Append(";"); return(sql.ToString()); }