Access Restrictions by Workgroups

Hi All,

Today I want to share with you a quite advanced customization that can significantly enhance your data security.

Lets start with business task first:

• Customer want to limit visibility of certain documents and entities between different users and group of users:
  • Users should see the entity or document if he is creator, owner or belongs to a workgroup that can see the document.
  • If users see the entity or document than all his managers should also see the same document.
  • Users who don’t belong to assigned workgroups should not see the document or entity.
• It is possible that documents or entities are assigned to multiple workgroups simultaneously
  • For example, car sales department and insurance sales department should see the same opportunity if the work on the same deal.
  • If assigned groups are independent than managers of both groups should see the same deal.

If we summarize it, we get following: Customer needs access restrictions for documents and entities based on multiple assigned workgroups with hierarchy escalations.

Unfortunately Acumatica does not support this function, but we can easily customize it. For the time being, i’ll focus on Opportunities, Sales Orders and Business Accounts for that restriction.
Let me break down the process in steps.

Multiple Workgroups Assignment

First thing we need to do is to assign multiple workgroups to entities. For that we need to create a custom table – EntityWorkgroup. This table will bring a link between Entity or Document and workgroup.

Next thing here is to assign workgroups to entities thought a custom grid that we can add where it is needed.

These configurations allows us to assign who should see the record beside owner and creator.

Also as a bonus, we can automatically insert workgroups based on selected customers/vendors and inventory items. This approach will significantly simplify data entry.
To share code between entities we can encapsulate common logic in the data view. Everywhere where we use this view, Acumatica will trigger logic for populating workgroups.

public class EntityRestrictionAutomation<SourceNote, BAccountField, InventoryField>
	: PXSelect<EntityWorkgroup,
		Where<EntityWorkgroup.refNoteID, Equal<Current<SourceNote>>>>
	where SourceNote : class, IBqlField
	where BAccountField : class, IBqlField
	where InventoryField : class, IBqlField
{
	public EntityRestrictionAutomation(PXGraph graph, Delegate @delegate)
		: base(graph, @delegate)
	{
		Initialize(graph);
	}
	public EntityRestrictionAutomation(PXGraph graph)
		: base(graph)
	{
		Initialize(graph);
	}
	private void Initialize(PXGraph graph)
	{
		graph.Initialized += InitLastEvents;
	}
	private void InitLastEvents(PXGraph graph)
	{
		if (typeof(SourceNote) != typeof(BAccountField))
			graph.FieldUpdated.AddHandler(
				BqlCommand.GetItemType(typeof(BAccountField)), typeof(BAccountField).Name, BAcclountID_FieldUpdated);

		if (typeof(SourceNote) != typeof(InventoryField))
			graph.FieldUpdated.AddHandler(
				BqlCommand.GetItemType(typeof(InventoryField)), typeof(InventoryField).Name, InventoryID_FieldUpdated);
	}

	protected virtual void BAcclountID_FieldUpdated(PXCache cache, PXFieldUpdatedEventArgs e)
	{
	}
	protected virtual void InventoryID_FieldUpdated(PXCache cache, PXFieldUpdatedEventArgs e)
	{
	}
}

Code Restrictions

Next thing we need to do is restrictions. We need to modify data entry screens to show only records that are allowed. And unfortunately there is no really a way to do that without customization.
Also remember that we need to modify 2 places on every screen:

1. Data View query (PXSelect) – select from data view is used in may different places, like redirects and navigation buttons. We need to limit records that can be selected to only records that can be viewed by logged in user.
2. Selectors query (PXSelector) – selectors are used for selecting a specific record in the data entry screen. We need to limit it as well not not allow logged in user select inappropriate data.

Also to simplify a bit customization, I suggest to create a new BQL function that will check hierarchy and allows us to return only appropriate records. Technically when I build this function, I used OwnedUser as a reference.

public class OwnedGroup<FieldNote, OperandUser> : IBqlComparison
	where FieldNote : IBqlField
	where OperandUser : IBqlOperand, new()
{
	private IBqlCreator _operandUser;
	public void Verify(PXCache cache, object item, List<object> pars, ref bool? result, ref object value)
	{
		result = null;
		value = null;
	}

	public virtual bool AppendExpression(ref SQLExpression exp, PXGraph graph, BqlCommandInfo info, BqlCommand.Selection selection)
	{
		bool status = true;

		if (info.Fields is BqlCommand.EqualityList list) list.NonStrict = true;

		SQLExpression opUser = null;
		if (!typeof(IBqlCreator).IsAssignableFrom(typeof(OperandUser)))
		{
			if (info.BuildExpression) opUser = BqlCommand.GetSingleExpression(typeof(OperandUser), graph, info.Tables, selection, BqlCommand.FieldPlace.Condition);
			info.Fields?.Add(typeof(OperandUser));
		}
		else
		{
			if (_operandUser == null) _operandUser = _operandUser.createOperand<OperandUser>();
			status &= _operandUser.AppendExpression(ref opUser, graph, info, selection);
		}

		Query qin = new Query();
		qin[typeof(EPCompanyTreeH.workGroupID)].From(typeof(EPCompanyTreeH))
			.Join(typeof(EPCompanyTreeMember))
			.On(SQLExpression.EQ(typeof(EPCompanyTreeH.parentWGID), typeof(EPCompanyTreeMember.workGroupID))
								.And(Column.SQLColumn(typeof(EPCompanyTreeMember.active)).EQ(1))
				.And(Column.SQLColumn(typeof(EPCompanyTreeMember.userID)).EQ(opUser)))
			.Where(new SQLConst(1).EQ(1));

		Query qout = new Query();
		//Append Tail removes main object, so we fieldNote will not be mapped. Skipping conditions for AppendTail
		if (info.Tables == null || info.Tables.Count <= 0 || info.Tables.Contains(BqlCommand.GetItemType<FieldNote>()))
		{
			qout[typeof(EntityWorkgroup.refNoteID)].From(typeof(EntityWorkgroup))
				.Where(Column.SQLColumn(typeof(EntityWorkgroup.workGroupID)).In(qin)
					.And(SQLExpression.EQ(typeof(EntityWorkgroup.refNoteID), typeof(FieldNote))));
		}
		else
		{
			qout[typeof(EntityWorkgroup.refNoteID)].From(typeof(EntityWorkgroup))
				.Where(Column.SQLColumn(typeof(EntityWorkgroup.workGroupID)).In(qin));
		}

		qout.Limit(-1); // prevent limiting of IN subqueries
		exp = exp.In(qout);

		return status;
	}
}

You can see here how we can use deep BQL and SQLTree features to control SQL generated using our function.
Later we can just use OwnedGroup<,> function in any BQL selects, like here:

//Data Views
public PXSelect<CROpportunity,
	Where<
		Where<
			EntityRestrictionFeature.isEnabled, NotEqual<True>,
			Or<CROpportunity.createdByID, Equal<Current<AccessInfo.userID>>,
			Or<CROpportunity.ownerID, Equal<Current<AccessInfo.userID>>,
			Or<CROpportunity.ownerID, OwnedUser<Current<AccessInfo.userID>>,
			Or<CROpportunity.noteID, OwnedGroup<CROpportunity.noteID, Current<AccessInfo.userID>>>>>>>>>
	Opportunity;
	
//Selectors
[PXSelector(typeof(Search2<CROpportunity.opportunityID,
	LeftJoin<BAccount, On<BAccount.bAccountID, Equal<CROpportunity.bAccountID>>,
	LeftJoin<Contact, On<Contact.contactID, Equal<CROpportunity.contactID>>>>,
	Where<
		Where<
			EntityRestrictionFeature.isEnabled, NotEqual<True>,
			Or<CROpportunity.createdByID, Equal<Current<AccessInfo.userID>>,
			Or<CROpportunity.ownerID, Equal<Current<AccessInfo.userID>>,
			Or<CROpportunity.ownerID, OwnedUser<Current<AccessInfo.userID>>,
			Or<CROpportunity.ownerID, OwnedGroup<CROpportunity.noteID, Current<AccessInfo.userID>>>>>>>>,
	OrderBy<Desc<CROpportunity.opportunityID>>>),
	Filterable = true)]

Settings Switch

It is also good practice to add a Enable/Disable switch from this restrictions feature. Switch will allows you to be more protected from issues (where you can temporary disable validation) or enhance testing (where you don’t need to have valuation)

To do that we can simple add a selector to PreferencesSecurity DAC and allow to control it from the Database and UI.

We also can create a constant that will simplify BQL queries.

public static class EntityRestrictionFeature
{
	public class isEnabled : PX.Data.BQL.BqlBool.Constant<isEnabled>
	{
		public isEnabled()
			: base(IsEnabled())
		{
		}
	}
	public static bool IsEnabled()
	{
		return PXDatabase.GetSlot<EntityRestrictionDefinition>(typeof(EntityRestrictionDefinition).Name, typeof(PreferencesSecurity)).Enabled;
	}
	public class EntityRestrictionDefinition : IPrefetchable
	{
		public bool Enabled { get; set; }
		public void Prefetch()
		{
			using (PXDataRecord rec = PXDatabase.SelectSingle<PreferencesSecurity>(new PXDataField<PreferencesSecurityExt.usrApplyWorkgroupRestrictions>()))
			{
				if (rec != null) Enabled = rec.GetBoolean(0) ?? false;
			}
		}
	}
}

Just want to highlight that i’m using here data caching with slots that is described in a separate article.

Generic Inquires

As soon as you have completed three steps above you already can test data entry screens. However this is not all. In modern Acumatica Generic Inquires are used as entry points for many different documents and entities, so we need to apply our restrictions there as well.

Please note that all standard and new generic inquiries will show records without restrictions. It is not bad actually, as for reporting purposes you may need to see all data. However if you need to expose GI to users, than you need to apply restrictions to each GI independently.

Luckily GIs supports grouping and we can join document or records to the allowed workgroups and than filter out records where current user does not have access. To eliminate duplicates we can groups primary record by keys.
Here you can see an example of GI with Access Restrictions:

Please note that GI has a hidden parameter that is defaulted to the current user. This parameter is used in join conditions to limit data selected and improve performance in the end.

Limitations

This solution is pretty nice and can be used easily. However it has few essential points you need to know:

• Code is written on Acumatica 2019 R1
• Records are restricted only on screens where custom code is added. On the all other standard screens, we still will see all records. For example if you go to Business Accounts screen, you will see all opportunities. However you can hide these tabs with access rights or add customization there as well.
• Performance of GI may be slow on Big Data due to multiplication of the records and aggregation.
• There is currently an issue in navigation from GI to Entry Screen – wrong record may be opened after drill down. Issues is due to bug in PX.TM.EPCompanyTreeH table. This issues is fixed already and should be delivered with 2019R1 Update 2

Source Code / Customization

I want to share with you a customization code, so feel free to use and modify it whether you need it.

Original project is published on GitHub:
https://github.com/smarenich/WorkgroupRestrictions

Basically that is it. Now you can enjoy filtering by workgroups in your Acumatica!
Hope it helps and have a nice development!