Row-Level Security (RLS for short in this article) is an Acumatica feature that allows users to configure visibility of information on a row-to-row level for each login (according to their role).
Feature explanation
Row visibility is configured using *restriction groups*. There are different types of restriction groups, which also differ in their way of handling cross-group relations.
To understand how this works, I suggest you read official Row-Level Security manual (first 6 chapters are general use; the rest are explaining individual module configurations, which might not be needed in your task).
Types of restriction groups and rules on how they interact with each other are explained in the Types of Restriction Groups chapter. It also has some examples with useful diagrams, which make it easier to understand.
Implement your custom row level security setup
Here's the checklist of what you need to enable RLS for your DAC:
- Implement
PX.SM.IRestrictedandPX.SM.IIncludablein your DAC; - A graph inherited from
PX.SM.BaseAccess; - ASPX page for the graph;
- Sitemap record in your customization/build;
I'm certain you can handle the last one on your own, so this article will only show an example for the first 3 steps.
> ❗ Don't forget to change:
> - Namespace;
> - YourMainDac to your DAC name in method names, comments and queries;
> - YourChildDac to your DAC name in method names, comments and queries;
> - KeyID to your key field name in generic type declaration, comments and queries;
> - ChildKeyID to your child key field name in generic type declaration, comments and queries;
> - Screen ID and title.
Implement the interfaces
Change class declaration:
public class YourMainDac : IBqlTable, IRestricted, IIncludableAdd these fields to the DAC:
- For
IRestricted:
#region GroupMask
/// <summary>
/// Holds visibility per user role.<br/>
/// Used to identify whether logged in user can see the record.<br/>
/// Visibility configured at EC102000 YourMainDac Access screen .
/// </summary>
[PXDBGroupMask]
public virtual byte[] GroupMask { get; set; }
public abstract class groupMask : BqlByteArray.Field<groupMask> { }
#endregion GroupMask- For
IIncludable:
#region Included
/// <summary>
/// An unbound Boolean field that is provided for implementation of the <see cref="PX.SM.IIncludable"/>
/// interface, which is a part of the row-level security mechanism of Acumatica.
/// </summary>
[PXBool]
[PXUIField(DisplayName = "Included")]
[PXUnboundDefault(false, PersistingCheck = PXPersistingCheck.Nothing)]
public virtual bool? Included { get; set; }
public abstract class included : BqlType<IBqlBool, bool>.Field<included> { }
#endregion IncludedCreate the graph
Acumatica didn't provide guideline to setup custom RLS setup, so I used `PX.Objects.AP.APAccess` as an example. I tried to keep the result as clean and minimalistic as possible:
using System;
using System.Collections;
using PX.Data;
using PX.Objects.AR;
using PX.SM;
namespace YourProjectNamespace.RowLevelSecurity
{
/// <summary>
/// EC102000 YourMainDac Access
/// </summary>
public class YourMainDacAccess : BaseAccess
{
#region Views
#region YourMainDacView
public PXSelect<YourMainDac> YourMainDacView;
protected virtual IEnumerable yourMainDacView()
{
if (Group.Current == null || string.IsNullOrEmpty(Group.Current.GroupName))
yield break;
var yourMainDacs = PXSelect<YourMainDac,
Where2<Match<Current<RelationGroup.groupName>>,
Or2<Match<Required<YourMainDac.groupMask>>,
Or<YourMainDac.groupMask, IsNull>>>>
.Select(this, new object[] { new byte[0] });
bool inserted = Group.Cache.GetStatus(Group.Current) == PXEntryStatus.Inserted;
if (!inserted)
{
foreach (var item in yourMainDacs)
{
YourMainDacView.Current = item;
yield return item;
}
yield break;
}
foreach (YourMainDac item in yourMainDacs)
{
if (item.GroupMask == null)
continue;
RelationGroup group = Group.Current;
bool anyGroup = false;
for (int i = 0; i < item.GroupMask.Length && i < group.GroupMask.Length; i++)
{
if (group.GroupMask[i] != 0x00 && (item.GroupMask[i] & group.GroupMask[i]) == group.GroupMask[i])
{
YourMainDac.Current = item;
yield return item;
}
anyGroup |= item.GroupMask[i] != 0x00;
}
if (!anyGroup)
{
YourMainDac.Current = item;
yield return item;
}
}
}
#endregion YourMainDacView
#region Groups
protected virtual IEnumerable group()
{
return GroupDelegate(this, true);
}
#endregion Groups
#endregion Views
#region ctor
public YourMainDacAccess()
{
YourMainDacView.Cache.AllowDelete = false;
PXUIFieldAttribute.SetEnabled(YourMainDacView.Cache, null, false);
PXUIFieldAttribute.SetEnabled<YourMainDac.recordCD>(YourMainDacView.Cache, null);
PXUIFieldAttribute.SetEnabled<YourMainDac.included>(YourMainDacView.Cache, null);
}
#endregion ctor
#region Cache Attached
[PXMergeAttributes(Method = MergeMethod.Merge)]
[RelationGroups(typeof(RelationGroup.groupName), Filterable = true)]
protected virtual void _(Events.CacheAttached<RelationGroup.groupName> e) { }
[PXMergeAttributes(Method = MergeMethod.Append)]
[PXSelector(typeof(Search<YourMainDac.recordCD>),
typeof(YourMainDac.recordCD),
typeof(YourMainDac.description))]
protected virtual void _(Events.CacheAttached<YourMainDac.recordCD> e) { }
#endregion Cache Attached
#region Events Handlers
protected void _(Events.RowInserted<RelationGroup> e, PXRowInserted baseMethod)
{
if (!(e.Row is RelationGroup group))
return;
baseMethod?.Invoke(e.Cache, e.Args);
group.SpecificModule = typeof(YourMainDac).Namespace;
}
protected void _(Events.RowSelected<RelationGroup> e)
{
if (!(e.Row is RelationGroup group))
return;
var hasGroupName = !string.IsNullOrEmpty(group.GroupName);
Save.SetEnabled(hasGroupName);
YourMainDacView.Cache.AllowInsert = hasGroupName;
}
protected virtual void _(Events.FieldDefaulting<YourMainDac.recordCD> e)
{
e.Cancel = true;
}
protected virtual void _(Events.FieldUpdated<YourMainDac.recordCD> e)
{
if (!(e.Row is YourMainDac row) || e.NewValue is null)
return;
if (!(PXSelectorAttribute.Select<YourMainDac.recordCD>(e.Cache, row, e.NewValue) is YourMainDac record))
throw new PXSetPropertyException(ErrorMessages.ElementDoesntExist, e.NewValue);
record.Included = true;
PXCache<YourMainDac>.RestoreCopy(row, record);
e.Cache.SetStatus(row, PXEntryStatus.Updated);
}
protected virtual void _(Events.RowSelected<YourMainDac> e)
{
if (!(e.Row is YourMainDac record) || e.Cache.GetStatus(record) != PXEntryStatus.Notchanged)
return;
if (record.GroupMask == null)
{
record.Included = true;
return;
}
foreach (var maskByte in record.GroupMask)
{
if (maskByte == 0x00)
continue;
record.Included = true;
return;
}
}
protected virtual void _(Events.RowPersisting<YourMainDac> e)
{
if (!(e.Row is YourMainDac record))
return;
if (record.RecordCD is null)
{
e.Cache.Delete(record);
e.Cancel = true;
return;
}
if (e.Operation == PXDBOperation.Insert)
{
e.Cancel = true;
return;
}
RelationGroup group = Group.Current;
if (record.GroupMask == null || group == null || group.GroupMask == null)
return;
if (record.GroupMask.Length < group.GroupMask.Length)
{
byte[] mask = record.GroupMask;
Array.Resize<byte>(ref mask, group.GroupMask.Length);
record.GroupMask = mask;
}
for (int i = 0; i < group.GroupMask.Length; i++)
{
if (group.GroupMask[i] == 0x00)
continue;
record.GroupMask[i] = record.Included == true
? (byte)(record.GroupMask[i] | group.GroupMask[i])
: (byte)(record.GroupMask[i] & ~group.GroupMask[i]);
}
}
#endregion Events Handlers
#region Persist
public override void Persist()
{
populateNeighbours(Users);
populateNeighbours(YourMainDac);
populateNeighbours(Users);
base.Persist();
PXSelectorAttribute.ClearGlobalCache<Users>();
PXSelectorAttribute.ClearGlobalCache<YourMainDac>();
}
#endregion Persist
#region Helper methods
public static IEnumerable GroupDelegate(PXGraph graph, bool includeInsertedRecords)
{
PXResultset<Neighbour> set = PXSelectGroupBy<Neighbour,
Where<Neighbour.leftEntityType, Equal<customerType>>,
Aggregate<GroupBy<Neighbour.coverageMask,
GroupBy<Neighbour.inverseMask,
GroupBy<Neighbour.winCoverageMask,
GroupBy<Neighbour.winInverseMask>>>>>>.Select(graph);
foreach (RelationGroup group in PXSelect<RelationGroup>.Select(graph))
{
bool visible = !string.IsNullOrEmpty(group.GroupName) || includeInsertedRecords;
bool relatedToTheModule = group.SpecificModule == null || group.SpecificModule == typeof(YourMainDac).Namespace;
// Each group is assigned to a specific type. If this group's type is a part of the module configured on this screen - show it
bool relatedToModuleTypes = UserAccess.InNeighbours(set, group);
if ((visible && relatedToTheModule) || relatedToModuleTypes)
{
yield return group;
}
}
}
#endregion Helper methods
}
}RelationGroupsAttribute.cs:
using System;
using System.Collections;
using PX.Data;
namespace YourProjectNamespace.RowLevelSecurity
{
public class RelationGroupsAttribute : PXCustomSelectorAttribute
{
public RelationGroupsAttribute(Type type) : base(type) { }
public virtual IEnumerable GetRecords()
{
return YourMainDacAccess.GroupDelegate(_Graph, false);
}
}
}
Add ASPX page
Self-explanatory, nothing uncommon:
<%@ Page Language="C#" MasterPageFile="~/MasterPages/FormDetail.master" AutoEventWireup="true"
ValidateRequest="false" CodeFile="EC102000.aspx.cs" Inherits="Page_EC102000"
Title="YourMainDac Access" %>
<%@ MasterType VirtualPath="~/MasterPages/FormDetail.master" %>
<asp:Content ID="cont1" ContentPlaceHolderID="phDS" runat="Server">
<px:PXDataSource ID="ds" runat="server" Visible="True" Width="100%" TypeName="YourProjectNamespace.RowLevelSecurity.YourMainDacAccess"
PrimaryView="Group">
<CallbackCommands>
<px:PXDSCallbackCommand CommitChanges="True" Name="Save" />
<px:PXDSCallbackCommand Name="Delete" Visible="false" />
<px:PXDSCallbackCommand Name="CopyPaste" Visible="false" />
<px:PXDSCallbackCommand Name="First" PostData="Self" StartNewGroup="True" />
</CallbackCommands>
</px:PXDataSource>
</asp:Content>
<asp:Content ID="cont2" ContentPlaceHolderID="phF" runat="Server">
<px:PXFormView ID="formGroup" runat="server" Width="100%" DataMember="Group" Caption="Restriction Group"
DefaultControlID="edGroupName" TemplateContainer="">
<Template>
<px:PXLayoutRule runat="server" StartColumn="True" LabelsWidth="SM" ControlSize="M" />
<px:PXSelector ID="edGroupName" runat="server" DataField="GroupName" />
<px:PXTextEdit ID="edDescription" runat="server" DataField="Description" />
<px:PXDropDown ID="edGroupType" runat="server" DataField="GroupType" />
<px:PXCheckBox ID="chkActive" runat="server" DataField="Active" />
</Template>
</px:PXFormView>
</asp:Content>
<asp:Content ID="cont3" ContentPlaceHolderID="phG" runat="Server">
<px:PXTab ID="tab" runat="server" Height="168%" Width="100%" SelectedIndex="1">
<Items>
<px:PXTabItem Text="Users">
<Template>
<px:PXGrid ID="gridUsers" BorderWidth="0px" runat="server" Height="150px" Width="100%"
AdjustPageSize="Auto" AllowSearch="True" SkinID="DetailsInTab" DataSourceID="ds"
FastFilterFields="FullName,Username">
<Levels>
<px:PXGridLevel DataMember="Users">
<Mode AllowAddNew="True" AllowDelete="False" />
<RowTemplate>
<px:PXLayoutRule runat="server" StartColumn="True" LabelsWidth="SM" ControlSize="M" />
<px:PXCheckBox ID="chkIncluded" runat="server" DataField="Included" />
<px:PXSelector ID="edUsername" runat="server" DataField="Username" TextField="Username" />
<px:PXTextEdit ID="FullName" runat="server" DataField="FullName" />
<px:PXTextEdit ID="edComment" runat="server" DataField="Comment" />
</RowTemplate>
<Columns>
<px:PXGridColumn DataField="Included" TextAlign="Center" Type="CheckBox" Width="30px" AllowCheckAll="True" />
<px:PXGridColumn DataField="Username" Width="300px" />
<px:PXGridColumn DataField="FullName" Width="200px" />
<px:PXGridColumn DataField="Comment" Width="300px" />
</Columns>
<Layout FormViewHeight="" />
</px:PXGridLevel>
</Levels>
<AutoSize Enabled="True" />
<Mode AllowDelete="False" />
<EditPageParams>
<px:PXControlParam ControlID="gridUsers" Name="Username" PropertyName="DataValues["Username"]" Type="String" />
</EditPageParams>
</px:PXGrid>
</Template>
</px:PXTabItem>
<px:PXTabItem Text="YourMainDacs">
<Template>
<px:PXGrid ID="gridYourMainDacs" BorderWidth="0px" runat="server" Height="150px" Width="100%" AdjustPageSize="Auto" AllowSearch="True" SkinID="DetailsInTab" TabIndex="400" DataSourceID="ds" FastFilterFields="AcctCD,AcctName">
<ActionBar>
<Actions>
<Save Enabled="False" />
<Delete Enabled="False" />
<EditRecord Enabled="False" />
</Actions>
</ActionBar>
<Levels>
<px:PXGridLevel DataMember="YourMainDacView">
<Mode AllowAddNew="True" InitNewRow="true" AllowDelete="False" />
<Columns>
<px:PXGridColumn DataField="Included" TextAlign="Center" Type="CheckBox" Width="30px" AllowCheckAll="True" />
<px:PXGridColumn DataField="YourMainDacCD" />
<px:PXGridColumn DataField="Description" Width="350px" />
</Columns>
<RowTemplate>
<px:PXLayoutRule runat="server" StartColumn="True" LabelsWidth="SM" ControlSize="M" />
<px:PXCheckBox ID="chkYourMainDacIncluded" runat="server" DataField="Included" />
<px:PXSelector ID="edRecordCD" runat="server" DataField="RecordCD" AllowEdit="True" CommitChanges="True" />
<px:PXDropDown ID="edDescription" runat="server" DataField="Description" Enabled="False" />
</RowTemplate>
<Layout FormViewHeight="" />
</px:PXGridLevel>
</Levels>
<AutoSize Enabled="True" />
<Mode AllowDelete="False" />
</px:PXGrid>
</Template>
</px:PXTabItem>
</Items>
<AutoSize Container="Window" Enabled="True" MinHeight="250" MinWidth="300" />
</px:PXTab>
</asp:Content>
Apply to the view
Acumatica created a Match type to apply RLS to the BQL query.
If you only need to filter target DAC (one that has RLS setup), you need to add this type to your query:
Match<Current<AccessInfo.userName>>If you need to check the child DAC, you can specify the table to which RLS should be applied:
Where<Match<YourMainDac, Current<AccessInfo.userName>>>Both versions of Match<> are unary operators, so it might be hard to append this operator to the existing Where clause.
Easiest trick for that - add it in And<> at the end of the Where<> clause.
If Where is complicated and the first option doesn't help, try to use Where2<UnaryOperator, NextOperator>.
Apply to the in-code query
In the simplest version, this step doesn't differ from the “Apply to the view” section.
However, this path will lead you to lots of duplicated code.
Solution - extract join/where adding logic into the separate method. I prefer to use extensions for that:
using PX.Data;
namespace YourProjectNamespace.RowLevelSecurity
{
public static class QueryExtensions
{
/// <summary>
/// Join <see cref="YourMainDac"/> to the target query and add filter to WHERE clause to hide records logged in user isn't allowed to see
/// </summary>
/// <remarks>
/// Set of visible <see cref="YourMainDac"/>s is determined by the row-level security configuration at EC102000 YourMainDac Access screen.
/// </remarks>
/// <typeparam name="Table">Main DAC type in the query</typeparam>
/// <typeparam name="KeyID">Foreign key field from one of the tables in the query, containing key ID value to join by</typeparam>
/// <param name="query">Target query</param>
/// <returns>Modified query</returns>
public static PXSelectBase<Table> HideInvisibleYourMainDacs<Table, KeyID>(this PXSelectBase<Table> query)
where Table : class, IBqlTable, new()
where KeyID : class, IBqlField
{
query.Join<InnerJoin<YourMainDac,
On<YourMainDac.KeyID, Equal<KeyID>>>>();
query.WhereAnd<Where<Match<YourMainDac, Current<AccessInfo.userName>>>>();
return query;
}
/// <summary>
/// Join <see cref="YourChildDac"/> and <see cref="YourMainDac"/> to the target query
/// and add filter to WHERE clause to hide records which logged in user isn't allowed to see
/// </summary>
/// <remarks>
/// Set of visible <see cref="YourMainDac"/>s is determined by the row-level security configuration at EC102000 YourMainDac Access screen.<br/>
/// This extension created for cases when you don't have key ID to join to, but have a DAC with foreign key to the <see cref="YourChildDac"/>.<br/>
/// <see cref="YourChildDac"/> serves only as a link to the <see cref="YourMainDac"/>.<br/>
/// If you have a key that contains key id, consider using <see cref="HideInvisibleYourMainDacs{Table, KeyID}(PXSelectBase{Table})"/>.
/// </remarks>
/// <typeparam name="Table">Main DAC type in the query</typeparam>
/// <typeparam name="ChildKeyID">Foreign key field from one of the tables in the query, containing child key ID value to join by</typeparam>
/// <param name="query">Target query</param>
/// <returns>Modified query</returns>
public static PXSelectBase<Table> HideInvisibleYourChildDacs<Table, ChildKeyID>(this PXSelectBase<Table> query)
where Table : class, IBqlTable, new()
where ChildKeyID : class, IBqlField
{
query.Join<
InnerJoin<YourChildDac,
On<YourChildDac.ChildKeyID, Equal<ChildKeyID>>,
InnerJoin<YourMainDac,
On<YourMainDac.KeyID, Equal<YourChildDac.KeyID>>>>>();
query.WhereAnd<Where<Match<YourMainDac, Current<AccessInfo.userName>>>>();
return query;
}
}
}With these extensions, all you need is to add .HideInvisibleYourMainDacs<T, T.KeyID> before .Select() to apply RLS.
If necessary, you can also create additional extension methods with more complicated logic.
> For instance, in our project, we needed to make a separate service to identify employees logged in users can see - based on the row-level security configuration for the main DAC, but with consideration of the main DAC properties as well.
> To cover that case, I had to grab an instance of the service via the Autofac, retrieve a list of accessible employees from it, and then add WhereAnd<Where<TBAccountID, In<Requred<TBAccountID>>>>().
Dynamic join limitations
If you start a base query without a Where clause, any .Join<T>() call will be ignored - see
