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