OneStream Ancillary Tables Security: A Practical Guide

Security is a critical requirement in OneStream, particularly when implementing People Planning or Thing Planning using Register tools. Done well, it ensures controlled access and protects both data and metadata from unauthorised changes.

In every PLP/TLP rollout, a common question arises early on: how can analysts update and manage register data without being given access to modify the underlying metadata?

If unrestricted access to metadata activities is granted regardless of role, it can lead to unintended consequences such as:

• Adding unnecessary or irrelevant fields
• Modifying field configurations or properties incorrectly
• Uninstalling or altering the register tool

Any of these can cause loss of previously completed work, require significant rework, and impact project timelines.

Defining Roles and Responsibilities

To implement security in OneStream for the People Planning Register based on user roles, the following must be satisfied:

• Admin users (Administrators) should have full access to create tables, modify metadata, and add/edit or read register data.
• End users (Analysts, etc.) should only be able to:
  • Input new employee data (e.g. New Hires)
  • Maintain existing data (e.g. Transfers, Leave)
• End users must not be allowed to perform metadata changes.

Metadata Activities in PLP/TLP (Restricted to Admins)

• Adding new fields or accounts to the register
• Defining Activity Types (e.g. Salary, Travel Expenses)
• Configuring field properties (alias, visibility, format, defaults)
• Setting field order and layout
• Defining planning period ranges
• Installing or uninstalling register tools

Concept of Ancillary Tables

In OneStream, ancillary tables — custom tables often used for staging data, storing non-financial metadata, or supporting custom solutions like People Planning or Thing Planning — sit outside the standard security model. Because they are not structured as multidimensional cube data (Dimensions / Members / Data Units), they cannot be governed by entity- and account-based security in the way cube data can. Ancillary tables exist directly within the database.

In People Planning, ancillary tables are used to store detailed employee-level data outside the cube.

How Security is Structured for Ancillary Tables

Instead of using standard Dimension Member-level security, ancillary table access is managed through three dedicated user-group properties on the OneStream Database Server.

To configure these, navigate to:

System → System Configuration → OneStream Database Server

Then set the following properties:

Property	Purpose	Typical Group
Access Group for Ancillary Tables	Users in this group can read table data	End Users
Maintenance Group for Ancillary Tables	Users in this group can add and edit table data	End Users
Table Creation Group for Ancillary Tables	Users in this group can perform metadata activities on these tables	Administrators

These three properties set the permission ceiling for each role. The next step — inheritance — is what lets one user (an admin) hold all three permissions without being assigned to all three groups individually.

Permission Hierarchy

Before getting into how groups inherit from one another, it helps to be clear that permissions stack in a strict hierarchy. A higher level always implies the lower ones:

In practice, this means:

• A user with metadata access (Admin) must also have Add/Edit and Read access to the table.
• A user with only Add/Edit access must have Read access, but not metadata access.
• A user with only Read access must not have Add/Edit or metadata access.

Inheritance via Nested Groups

OneStream security uses a nested-group structure to deliver this hierarchy without forcing every admin to be a member of every group. A group can be added to another group’s Child Groups list, and when it is, the users in the child group automatically inherit the parent group’s permissions.

In our example, GRP_Group_Finance (the admin group) is added to the Child Groups list of GRP_End_User_Group. This single nesting step gives Finance users everything an end user has — Read and Add/Edit access to the ancillary tables — on top of the metadata access they already get from being the Table Creation Group.

The result is a structured, scalable security model: assign each property once to the right group, then nest groups so admins inherit end-user permissions automatically.

Impact of Security Configuration

After applying the configuration above:

Granular Security Control

The configuration so far governs who can read, edit, or change the structure of the ancillary tables. In some implementations, that is not granular enough — you also need to control which rows a user can see.

A common scenario: a user is responsible for a subset of Profit Centres from a larger list. They should be able to view and interact only with register data corresponding to those specific Profit Centres they are authorised to access.

This kind of row-level filtering is delivered with two OneStream features working together:

1. The DisplayMemberGroup property on dimension members
2. A custom Business Rule that filters data based on the logged-in user’s group memberships

The approach has three steps.

1User Group Configuration

Define a user group for each access scope. Continuing the example:

• GRP_EndUser_Group_BU_A is a group that contains users who can access a parent-level Profit Centre hierarchy.
• GRP_EndUser_Group_BU_A_12345 is a narrower group, scoped only to the 12345 member within that hierarchy.

GRP_EndUser_Group_BU_A is then added as a Child Group of GRP_EndUser_Group_BU_A_12345. This means the users in GRP_EndUser_Group_BU_A inherit access to 12345 along with the members they already have access to.

2DisplayMemberGroup on the Dimension Member

For each Profit Centre member, set the Display Member Group property to the user group that should see it.

For example, member 12345 has its Display Member Group set to GRP_EndUser_Group_BU_A_12345. The remaining members in the hierarchy are assigned to GRP_EndUser_Group_BU_A.

3Business Rule Logic

The DashboardDataSet Business Rule retrieves the groups associated with the currently logged-in user, stored in the userGroups collection. It then iterates through each member in the Profit Centre hierarchy and evaluates the DisplayMemberGroup property of each member. If userGroups contains the assigned DisplayMemberGroup, the member is added to the result returned by the rule.

This ensures that only the members aligned with the logged-in user’s group memberships are returned and displayed.

' Loop through the base members of the Profit Centre hierarchy
' and include only those whose DisplayMemberGroup matches one of
' the logged-in user's groups (or the Administrators group).
For Each memberObj As Member In baseMembers
    Dim memberName As String = memberObj.Name
    Dim memInfo As Member = BRApi.Finance.Members.GetMember( _
        si, DimType.UD1.Id, memberName)

    If Not (memInfo Is Nothing) Then
        Dim displayMemberGroupValue As Guid = memInfo.DisplayMemberGroupUniqueID

        If (userGroups.Contains(displayMemberGroupValue) _
            Or userGroups.Contains(AdminGroupGUID)) Then
            strUD1FilterScript.Append("U1#" + memberName + ",")
        End If
    End If
Next

Return strUD1FilterScript

The list of Profit Centre members returned by this rule is bound to the dropdown control on the Register Dashboard:

' Resolve the comma-separated U1# filter string into the actual
' Profit Centre members for the dropdown component.
Dim ProftMems As List(Of MemberInfo) = _
    BRApi.Finance.Metadata.GetMembersUsingFilter( _
        si, "ProfitCenterDetail", UD1Filter.ToString, True)

The dropdown will now contain only the members the logged-in user can access:

The register itself is bound to a Data Adaptor whose query takes the selected Profit Centre as a parameter:

Select
    Entity,
    Code2 as ProfitCenter,
    Code1 as Account,
    FORMAT(Sum(Value),  'N2') as AnnualAmount,
    FORMAT(Sum(NCode1), 'N2') as Jul,
    FORMAT(Sum(NCode2), 'N2') as Aug,
    FORMAT(Sum(NCode3), 'N2') as Sep,
    FORMAT(Sum(NCode4), 'N2') as Oct,
    FORMAT(Sum(NCode5), 'N2') as Nov,
    FORMAT(Sum(NCode6), 'N2') as Dec,
    FORMAT(Sum(NCode7), 'N2') as Jan,
    FORMAT(Sum(NCode8), 'N2') as Feb,
    FORMAT(Sum(Code9),  'N2') as Mar,
    FORMAT(Sum(Code10), 'N2') as Apr,
    FORMAT(Sum(Code11), 'N2') as May,
    FORMAT(Sum(Code12), 'N2') as Jun
from XFW_TLP_Register
where WFProfileName = '|WFProfile|'
  and WFTimeName    = '|WFTime|'
  and Code2         = '|!MemberListProfitCentre_TLPT_BU_A_All!|'
Group By Entity, Code2, Code1

Selecting All in the dropdown returns register data for every Profit Centre the logged-in user has access to.

Bringing It Together

OneStream gives you two complementary layers of control over ancillary table data. The Database Server group properties — Access, Maintenance, and Table Creation — set the baseline for who can read, edit, and change the structure of the tables, with nested groups providing clean inheritance for admin roles. For implementations that need finer control, the combination of DisplayMemberGroup and a Business Rule lets you filter rows by the logged-in user’s group memberships, so each user sees only the slice of register data they are responsible for.

Together, these mechanisms produce a security model that is both scalable across roles and precise at the row level — one that protects the integrity of your PLP/TLP rollout without getting in the way of day-to-day analyst work.