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).
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 the 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.
Here’s the checklist of what you need to enable RLS for your DAC:
PX.SM.IRestricted and
PX.SM.IIncludable in your DAC;
PX.SM.BaseAccess;
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.
Change class declaration:
public class YourMainDac : IBqlTable, IRestricted, IIncludable
Add 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 Included
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);
}
}
}
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);
}
}
}
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>
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>.
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<Required<TBAccountID>>>>().
If you start a base query without a
Where
clause, any
.Join<T>()
call will be ignored.
Any .Join<T>()
will be ignored completely if the query is built using one of these classes:
PXSelectGroupBy;
PXSelectGroupByOrderBy;
PXSelectJoinGroupBy<Table, Join, Aggregate>
That is, any
PXSelect
type, which is missing either
Join
or
Where
generic type.
Because of that, there’s a chance you try to apply
.WhereAnd<T>()
to a field from a table that should’ve been joined before that via
.Join<T>().
But this join didn’t apply, thus making an invalid SQL query, which will result in the error:
Unknown column 'Table.Field' in 'where clause'
This is related to the implementation of the
Select3 /
Select5 /
Select6
classes that are responsible for internal query configuration.
You can’t fix them, because they are sealed – thus you can’t inherit and override methods.
The option to create a new
PXSelect
with a custom
Select
is dangerous – this code isn’t provided in the
App_Code
folder of the Acumatica instance.
Because of this, you will have to resort to disassembly, which might produce a crooked version of the code. That might cause you to spend too much time ironing out issues because you will have to reinvent the wheel.
.Join<T>()
completely;
PXSelect
types that have both
Where
and
Join;
Where
type you need to use, you can set a placeholder
Where<True, Equal<True>>.
Performance overhead of this additional “filter” will be negligible, and you will have a properly implemented underlying query builder object.
Andrii Tkachenko, Acumatica MVP.
Get the latest insights on exponential technologies delivered straight to you