MicroStrategy ONE
Compliance Telemetry Hierarchy
User Entity
A User Entity can inherit project access privileges from two Sources:
- User (self)
- User Groups (all direct and indirect parent user groups)
User Entities can be found in lu_user_entity_view.
Source Entity
A Source can inherit project access privileges from three Privilege Sources:
- User (self)
- User Groups (all direct and indirect parent user groups)
- Security Roles
Sources can be found in lu_source_entity_view and Privilege Sources can be found in lu_privilege_source_view.
Relationship Tables
The relationship table rel_user_entity_source captures the relationship between a User Entity and its Source of privileges. Sources may be the User itself or a parent User Group.
The relationship table rel_source_privilege_source_scope captures the relationship between a Source and its Privilege Source. Privilege sources may be the User itself, a parent User Group, or a Security Role. It also captures the relationship between a Privilege Source and its Scope. A Scope is the set of projects the privileges are applicable for.
The relationship table rel_privilege_source_privilege_group captures the relationship between a Privilege Source and a Privilege Group. A privilege group is a set of unique privileges applied to a Privilege Source. Multiple Privilege Sources may share the same Privilege Group if they have the same applied privileges.
The relationship table rel_privilege_group_privilege captures the relationship between a Privilege Group and its unique set of Privileges.
Joining rel_user_entity_source, rel_source_privilege_source_scope, rel_privilege_source_privilege_group , and rel_privilege_group_privilege will give you an enumerated list of all Privileges available to a User Entity based on their Sources’ Privilege Sources.
The table fact_user_entity_resolved_privilege captures the list of resolved privileges for a given User Entity. When we join rel_user_entity_source, rel_source_privilege_source_scope, rel_privilege_source_privilege_group , rel_privilege_group_privilege withfact_user_entity_resolved_privilege we get a resolved list of all Privileges available to each User Entity and their respective Product. The resolution process allows us to relate all Privileges available to a User Entity, as well as each Privilege’s Source and Privilege Source.
For more information about project access privileges, see List of Privileges.
rel_user_entity_source
A User can inherit privileges directly from two Sources:
- User (self)
- User Groups (all direct and indirect parent user groups)
The relationship table rel_user_entity_source captures the relationship between a User Entity and its Source of privileges. This includes relationships between a User and itself and a User with its parent User Groups.
|
Column |
Description |
Data-Type |
|---|---|---|
|
user_entity_id |
The auto-generated numeric ID for the User Entity. |
bigint(20) |
|
source_id |
The auto-generated numeric ID for the Source corresponding to a User or User Group. |
bigint(20) |
|
audit_timestamp |
The timestamp of when the License Audit was triggered. This is sent by the Intelligence server to Kafka logs. |
timestamp |
|
metadata_id |
The auto-generated numeric ID of the corresponding metadata. |
bigint(20) |
|
insert_ts |
MicroStrategy internal use. The timestamp the ETL inserted the row into the database. |
timestamp |
lu_user_entity_view
The list of all possible User Entities. Therefore, it contains Users and Contacts. It is a view based on lu_entity limited to entity_type_ids in (1,4).
|
Column |
WH Table Column |
Description |
Data-Type |
|---|---|---|---|
|
user_entity_id |
entity_id |
The auto-generated numeric ID value for User Entity. |
bigint(20) |
|
user_entity_name |
entity_name |
The name of the User Entity that has privileges. |
varchar(255) |
| user_entity_desc | entity_desc | The description of the User Entity. | varchar(255) |
|
user_entity_type_id |
entity_type_id |
The User Entity type can be: User (1) Contact (4) |
int(11) |
| metadata_id | metadata_id | The ID for the corresponding metadata for each Privilege Source. All User Entities are stored at the metadata level. | bigint(20) |
|
user_entity_guid |
entity_guid |
The GUID of User Entity. |
varchar(32) |
| creation_timestamp | creation_timestamp | The UTC timestamp of when the User Entity was first created in the metadata. | datetime |
|
modification_timestamp |
modification_timestamp |
The latest UTC timestamp from when the User Entity object was last changed. The value will continually update as the object is modified. |
datetime |
| status | status |
The Status of the User Entity. The Status can be: Enabled (1) Disabled (0) |
varchar(32) |
lu_user_entity_type_view
A User Entity can be of type: User or Contact. Therefore, this lookup table contains a list of two static elements. This lookup table is a view based on lu_entity_type limited to entity_type_ids in (1,4).
|
Column |
Description |
Data-Type |
|---|---|---|
|
user_entity_type_id |
The fixed ID for User Entity type. |
int(11) |
|
user_entity_type_desc |
Description for User Entity Type. A User Entity can be of type: User (1) Contact (4) |
varchar(255) |
lu_source_entity_view
The list of all possible Sources. Therefore, it contains Users and User Groups. It is a view based on lu_entity limited to entity_type_ids in (1,2).
|
Column |
WH Table Column |
Description |
Data-Type |
|---|---|---|---|
|
source_id |
entity_id |
The auto-generated ID for the Source entity. |
bigint(20) |
|
source_name |
entity_name |
The name of the Source. |
varchar(255) |
| source_desc | entity_desc | The description of the Source. | varchar(255) |
|
source_type_id |
entity_type_id |
The Source type can be: User (1) User Group (2) |
int(11) |
| metadata_id | metadata_id | The ID for the corresponding metadata for each Privilege Source. All sources are stored at the metadata level. | bigint(20) |
|
user_entity_guid |
entity_guid |
The GUID of Source. |
varchar(32) |
| creation_timestamp | creation_timestamp | The UTC timestamp of when the Source was first created in the metadata. | datetime |
|
modification_timestamp |
modification_timestamp |
The latest UTC timestamp from when the Source object was last changed. The value will continually update as the object is modified. |
datetime |
| status | status |
The Status of the Source. The Status can be: Enabled (1) Disabled (0) |
varchar(32) |
rel_source_privilege_source_scope
A Source can get its privileges from three Privilege Sources:
- User (self),
- User Groups (all direct and indirect Parent User Groups) and
- Security Roles (applied to User or Parent User groups)
The relationship table rel_source_privilege_source_scope captures the relationship between the Source entity from rel_user_entity_source and their Privilege Source. Each row also contains a Scope which indicates the list of projects the Privilege Source is applicable to.
|
Column |
Description |
Data-Type |
|---|---|---|
|
source_id |
The auto-generated numeric ID value for a Source entity. |
bigint(20) |
|
privilege_source_id |
The auto-generated numeric ID value for a Privilege Source entity. |
bigint(20) |
| scope_id | The auto-generated numeric ID value for a scope of projects. | bigint(20) |
|
audit_timestamp |
The timestamp the License Audit was triggered, sent by the Intelligence server to Kafka logs. |
timestamp |
| metadata_id | The auto_generated numeric ID value for the corresponding Metadata | bigint(20) |
|
insert_ts |
MicroStrategy internal use. The timestamp the ETL inserted the row into the database. |
timestamp |
lu_scope
The Scope attribute was introduced to optimize SQL query and ETL performance. It is not intended to be used in ad-hoc reports. The level of assigning Privileges can differ depending on the Privilege Source.
Security Roles can grant a set of privileges but restrict them to a few projects. Whereas, User and User Group privileges are applicable globally for all the projects. To determine which privilege is assigned to which list of project(s), Scope is used. It represents the list of projects for which the privilege source is applicable.
If Privilege Source (from rel_source_privilege_source_scope) is a Security Role, Scope would be a positive number representing subset of projects in the metadata. If Privilege Source is User or User group, we assign a default scope_ID (-metadataId) representing all the projects in the metadata.
|
Column |
Description |
Data-Type |
|---|---|---|
|
scope_id |
The auto-generated numeric ID for each unique list of projects. |
bigint(20) |
|
scope_desc |
The list of project ID’s in the metadata. |
longtext |
rel_scope_project
The relationship table between Scope and Project. Scope is used to represent the list of projects for which a User/User Group inherits a list of privileges from Security role. This table maintains the relationship between scope and the corresponding projects.
|
Column |
Description |
Data-Type |
|---|---|---|
|
scope_id |
The auto-generated numeric ID value for scope. |
bigint(20) |
|
project_id |
The auto-generated numeric ID value for projects. |
bigint(20) |
|
metadata_id |
The ID for the corresponding metadata for each Project. |
bigint(20) |
rel_privilege_source_privilege_group
A relationship table between Privilege Source (which is User, User group, or Security Role) and their corresponding set of privileges represented by the Privilege Group.
|
Column |
Description |
Data-Type |
|---|---|---|
|
privilege_source_id |
The auto-generated numeric ID value for a Privilege Source entity. |
bigint(20) |
|
privilege_group_id |
The auto-generated numeric ID value for a Privilege Group representing the set of Privileges for a Privilege Source. |
bigint(20) |
|
audit_timestamp |
The timestamp the License Audit was triggered. This is sent by the Intelligence server to Kafka logs. |
timestamp |
|
metadata_id |
The auto generated ID for a metadata. |
bigint(20) |
| insert_ts | MicroStrategy internal use. The timestamp the ETL inserted the row into the database. | timestamp |
lu_privilege_source_view
The list of all possible Privilege Sources. Therefore, it contains Users, User Groups and Security Roles. A view based on lu_entity limited to entity_type_ids in (1,2,3).
|
Column |
WH Table Column |
Description |
Data-Type |
|---|---|---|---|
| privilege_source_id | entity_id | The auto-generated ID for Privilege Source entity. | bigint(20) |
| privilege_source_name | entity_name | The name of the Privilege Source. | varchar(255) |
| privilege_source_desc | entity_desc | The description of Privilege Source. | varchar(255) |
| privilege_source_type_id | entity_type_id |
The Privilege Source type can be: User (1) User Group (2) Security Role (3) |
int(11) |
| metadata_id | metadata_id | The ID for the corresponding metadata for each Privilege Source. All sources are stored at the metadata level. | bigint(20) |
|
privilege_source_guid |
entity_guid |
The GUID of Privilege Source. |
varchar(32) |
| creation_timestamp | creation_timestamp | The UTC timestamp of when the Privilege Source was first created in the metadata. | datetime |
|
modification_timestamp |
modification_timestamp |
The latest UTC timestamp from when the Privilege Source object was last changed. The value will continually update as the object is modified. |
datetime |
| status | status |
Status of Privilege Source. The Status can be: Enabled Disabled |
varchar(32) |
lu_privilege_source_type_view
A Privilege Source can be of Type: User, User group or Security Role. Therefore this lookup table contains a list of three static elements. This lookup table is a view based on lu_entity_type limited to entity_type_ids in (1,2,3).
|
Column |
Description |
Data-Type |
|---|---|---|
| privilege_source_type_id | The fixed ID for privilege source type. | int(11) |
| privilege_source_type_desc |
The description for Privilege Source Type. Privilege Source type can be: User (1) User Group (2) Security Role (3) |
varchar(255) |
lu_privilege_group
This table is used for internal purpose only. A Privilege Group represents a unique set of privileges applied to a Privilege Source. Multiple Privilege Sources with the same set of privileges will be assigned the same Privilege Group.
|
Column |
Description |
Data-Type |
|---|---|---|
|
privilege_group_id |
The auto-generated numeric ID value. |
bigint(20) |
|
privilege_group_desc |
A set of privileges. |
varchar(4096) |
rel_privilege_group_privilege
This is a relationship table between Privilege Groups and their set of Privileges. The join between rel_privilege_source_privilege_group, rel_privilege_group_privilege, and lu_privilege gives a list of privileges that are assigned directly to each Privilege Source. Such a list includes only the directly assigned privileges and not the inherited privileges.
|
Column |
Description |
Data-Type |
|---|---|---|
|
privilege_id |
The fixed ID value for the privilege. |
int(11) |
|
privilege_group_id |
The auto-generated ID value for privilege group. |
bigint(20) |
fact_user_entity_resolved_privilege
This table contains the resolved list of Privileges and their associated Product (including those directly applied to a user, those from a parent group, and those from a security role applied to a user or parent group) for each User Entity in the metadata.
|
Column |
Description |
Data-Type |
|---|---|---|
|
user_entity_id |
The auto-generated ID value for the User Entity. |
bigint(20) |
|
privilege_id |
The fixed ID value for the Privilege. |
smallint(6) |
|
product_id |
The fixed ID value for the Product. |
smallint(6) |
|
audit_timestamp |
The timestamp the License Audit was triggered. It is sent by the Intelligence server to Kafka logs. |
timestamp |
|
license_entity_status_id |
The fixed ID Value for the status of the entity. |
tinyint(4) |
|
metadata_id |
The auto-generated ID value for the metadata. |
bigint(20) |
| insert_ts | MicroStrategy Internal Use. The timestamp the ETL inserted the row into the database. | timestamp |
lu_license_entity_status_view
An entity (from lu_user_entity_view, lu_privilege_source_view, lu_source_entity_view) in the license model can be either enabled or disabled. Therefore this lookup table contains a list of two static elements. This lookup table is a view based on lu_account_status limited to account_status_id in (0,1) for enabled/disabled.
|
Column |
Description |
Data-Type |
|---|---|---|
|
license_entity_status_id |
The fixed numeric ID for status. |
tinyint(4) |
|
license_entity_status_desc |
The description for Status can be: Enabled (1) Disabled (0) |
varchar(25) |
lu_product
lu_product is lookup table for all the Products. To access each product a user needs to have a set of privileges.
|
Column |
Description |
Data-Type |
|---|---|---|
|
product_id |
The fixed numeric ID for the Product. |
int(11) |
|
product_desc |
The description for the Product. |
varchar(255) |
lu_privilege
The static list of all Privileges. For more information about project access privileges, see List of Privileges.
|
Column |
Description |
Data-Type |
|---|---|---|
|
privilege_id |
The fixed numeric ID for Privileges. This is the source column for the Privilege attribute. |
int(11) |
|
privilege_desc |
The description for the Privilege. |
varchar(255) |