public List <RBACItemInfo> GetAllRoles() { List <RBACItemInfo> result = new List <RBACItemInfo>(); Dictionary <string, object> sqlParams = new Dictionary <string, object>(); sqlParams.Add("@ApplicationId", this.ApplicationID); sqlParams.Add("@ItemType", Convert.ToInt32(0 /*ItemType.Role*/)); DataSet dsResults = _DAL_RBAC.GetDatasetFromSQL( "select ItemId, Name, Description, ItemType" + " from netsqlazman_ItemsTable" + " where ApplicationId = @ApplicationId" + " and ItemType = @ItemType" + " order by Name", sqlParams, false); if ((dsResults != null) && (dsResults.Tables.Count > 0) && (dsResults.Tables[0].Rows.Count > 0)) { DataTable resultTable = dsResults.Tables[0]; foreach (DataRow nextRow in resultTable.Rows) { RBACItemInfo itemInfo = new RBACItemInfo(); itemInfo.ItemID = Convert.ToInt32(nextRow["ItemId"]); itemInfo.ItemName = Convert.ToString(nextRow["Name"]); itemInfo.Description = Convert.ToString(nextRow["Description"]); itemInfo.ItemType = RBACItemType.Role; result.Add(itemInfo); } } dsResults.Dispose(); return(result); }
public List <RBACItemInfo> GetGrantedItemsForUser(RBACUserInfo user, bool onlyExplicitGrants) { List <RBACItemInfo> result = new List <RBACItemInfo>(); Dictionary <string, object> sqlParams = new Dictionary <string, object>(); sqlParams.Add("@DBUserSid", user.DBUserCustomSID); sqlParams.Add("@ApplicationID", _ApplicationID); DataSet dsUserRights = _DAL_RBAC.GetDatasetFromSQL( "select t1.ItemId, t1.Name, t1.ItemType, t1.Description, t2.AuthorizationType, 'Direct' as Grantor " + "from netsqlazman_ItemsTable as t1, netsqlazman_Authorizationstable t2 " + "where t1.ApplicationID = @ApplicationID " + " and t2.objectSid = @DBUserSid " + " and t1.ItemId = t2.ItemId and t2.AuthorizationType in (1, 3) " + "union all " + "select t4.ItemId, t4.Name, t4.ItemType, t4.Description, t5.AuthorizationType, 'Role' as Grantor " + "from netsqlazman_ItemsHierarchyTable t3, netsqlazman_ItemsTable t4, netsqlazman_AuthorizationsTable t5 " + "where t3.ItemId = t4.ItemId " + "and t5.AuthorizationType in (1, 3) " + "and t5.ItemId = t3.MemberOfItemId " + "and t3.MemberOfItemId in " + "( " + "select t1.ItemId " + "from netsqlazman_ItemsTable as t1, netsqlazman_Authorizationstable t2 " + "where t1.ApplicationID = @ApplicationID and t2.objectSid = @DBUserSid " + "and t1.ItemId = t2.ItemId and t2.AuthorizationType in (1, 3) " + ") " + "order by 3 asc, 4 asc, 2 asc", sqlParams, true); //"select t1.ItemId, t1.Name, t1.ItemType, t1.Description, t2.AuthorizationType, 'Direct' as Grantor " + //"from netsqlazman_ItemsTable as t1, netsqlazman_Authorizationstable t2 " + //"where t1.ApplicationID = @ApplicationID " + //" and t2.OwnerSid = CONVERT(varchar(max),ownerSid,2) " + /*" and t2.objectSid = @DBUserSid " +*/ //" and t1.ItemId = t2.ItemId and t2.AuthorizationType in (1, 3) " + //"union all " + //"select t4.ItemId, t4.Name, t4.ItemType, t4.Description, t5.AuthorizationType, 'Role' as Grantor " + //"from netsqlazman_ItemsHierarchyTable t3, netsqlazman_ItemsTable t4, netsqlazman_AuthorizationsTable t5 " + //"where t3.ItemId = t4.ItemId " + //"and t5.AuthorizationType in (1, 3) " + //"and t5.ItemId = t3.MemberOfItemId " + //"and t3.MemberOfItemId in " + //"( " + //"select t1.ItemId " + //"from netsqlazman_ItemsTable as t1, netsqlazman_Authorizationstable t2 " + //"where t1.ApplicationID = 73 and t2.OwnerSid = CONVERT(varchar(max),ownerSid,2) " + /*"where t1.ApplicationID = @ApplicationID and t2.objectSid = @DBUserSid " +*/ //"and t1.ItemId = t2.ItemId and t2.AuthorizationType in (1, 3) " + //") " + //"order by 3 asc, 4 asc, 2 asc", sqlParams, true); //DataSet dsUserRights = _DAL_RBAC.GetDatasetFromSQL( // "select t1.ItemId, t1.Name, t1.ItemType, t1.Description, t2.AuthorizationType, 'Direct' as Grantor " + // "from netsqlazman_ItemsTable as t1, netsqlazman_Authorizationstable t2 " + // "where t1.ApplicationID = @ApplicationID " + // " and t2.OwnerSid = @DBUserSid " + /*" and t2.objectSid = @DBUserSid " +*/ // " and t1.ItemId = t2.ItemId and t2.AuthorizationType in (1, 3) " + // "union all " + // "select t4.ItemId, t4.Name, t4.ItemType, t4.Description, t5.AuthorizationType, 'Role' as Grantor " + // "from netsqlazman_ItemsHierarchyTable t3, netsqlazman_ItemsTable t4, netsqlazman_AuthorizationsTable t5 " + // "where t3.ItemId = t4.ItemId " + // "and t5.AuthorizationType in (1, 3) " + // "and t5.ItemId = t3.MemberOfItemId " + // "and t3.MemberOfItemId in " + // "( " + // "select t1.ItemId " + // "from netsqlazman_ItemsTable as t1, netsqlazman_Authorizationstable t2 " + // "where t1.ApplicationID = @ApplicationID and t2.OwnerSid = @DBUserSid " + /*"where t1.ApplicationID = @ApplicationID and t2.objectSid = @DBUserSid " +*/ // "and t1.ItemId = t2.ItemId and t2.AuthorizationType in (1, 3) " + // ") " + // "order by 3 asc, 4 asc, 2 asc", sqlParams, true); foreach (DataRow dr in dsUserRights.Tables[0].Rows) { if ((onlyExplicitGrants == false) || (dr["Grantor"].ToString() == "Direct")) { RBACItemInfo item = new RBACItemInfo(); item.ItemID = Convert.ToInt32(dr["ItemId"]); item.ItemName = dr["Name"].ToString(); item.Description = Convert.ToString(dr["Description"]); item.ItemType = (RBACItemType)Enum.Parse(typeof(RBACItemType), dr["ItemType"].ToString()); bool alreadyExists = false; foreach (RBACItemInfo nextItem in result) { if (nextItem.ItemID == item.ItemID) { alreadyExists = true; break; } } if (alreadyExists == false) { result.Add(item); } } } return(result); }