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