BI row-level permissions do not require that the security
associations table have a particular format. However, the format of
a security associations table can affect filter performance. This
topic describes a format that supports efficient hierarchy-based filtering.
This format is useful for many common scenarios, because security
policies are often hierarchical. For example, a typical business requirement
is that a manager can see data for all of the employees that he or
she manages either directly or indirectly.
The following figure
depicts two ways to structure a security associations table that documents
each user's place in a simple organizational hierarchy. The sparse
version of the table includes only direct reporting relationships;
information about indirect relationships must be derived. The fully
articulated (or robust) version explicitly includes indirect reporting
relationships along with direct reporting relationships; this is advantageous
for query performance.
The table that uses
the fully articulated format explicitly includes not only the hierarchy's
immediate parent-child relationships, but also every other ancestor-descendant
association (such as grandparent-child and great grandparent-child).
This facilitates simpler queries by eliminating the need to traverse
the hierarchy to find all of the descendants of any particular node.