Tables


SchemaSpy Analysis of mssub_mcp.ms_syst

Generated on Fri Jan 19 05:41 PST 2024

XML Representation
Insertion Order Deletion Order
TABLES 0
VIEWS 35
COLUMNS 510
Constraints 0
Anomalies 0
Routines 95

Database Properties

Database Type: PostgreSQL - 16.1 (Ubuntu 16.1-1.pgdg22.04+1)

Catalog mssub_mcp

Copyright © Lima Buttgereit Holdings LLC d/b/a Muse Systems

This database software and documentation is licensed to you under the terms of the Muse Systems Business Management System License Agreement v1.1 or other written, superseding license that you have obtained from Muse Systems.

Only Muse Systems may license this software to you. Your continued use of this software indicates your agreement to abide by the applicable license terms.

This database may include content copyrighted by and licensed from third parties.

Please see the license or contact us for more information.

muse.information@musesystems.com :: https://muse.systems

Schema ms_syst

Public API for system management oriented data access and operations.

This schema contains procedures, functions, and views suitable for access by applications and integration tools.

Tables

Table / View Children Parents Columns Type Comments
syst_access_accounts 0 0 13 View

Contains the known login accounts which are used solely for the purpose of authentication of users. Authorization is handled on a per-Instance basis within the application.

User Defined Record Supported Operations

  • INSERT
  • SELECT
  • UPDATE
  • DELETE
syst_enum_items 0 0 22 View

The list of values provided by an Enumeration as well as related behavioral and informational metadata.

User Defined Record Supported Operations

  • INSERT
  • SELECT
  • UPDATE
  • DELETE

System Defined Record Supported Operations

  • SELECT
  • UPDATE - See column comments for applicable restrictions.
syst_perm_functional_types 0 0 12 View

Defines application specific areas of applicability to which Permissions and Permission Roles are assigned.

When an application defines varying areas of business controls, Permission Functional Types can be used to group Permissions into their specific areas and limit usage and role assignment by area. Consider an application which supports multiple warehouses containing inventory. The application may define globally applicable Permissions such as the ability to log into the application, but may allow employees to be granted varying degrees of Permission to each individual warehouse’s inventory management features. In this case there would be “Global” Permission Functional Type containing the log in Permission and a “Warehouse” Permission Functional Type for those Permissions and Permission Roles which can vary warehouse by warehouse.

General Usage

Both Permissions and Permission Roles must share a Permission Functional Type since the Permission Functional Type establishes the context of applicability for both.

System Defined Record Supported Operations

  • SELECT
  • UPDATE - See column comments for applicable restrictions.
syst_instance_contexts 0 0 14 View

Instance specific settings which determine how each Instance connects to the defined Application Contexts.

User Defined Record Supported Operations

  • SELECT
  • UPDATE
syst_instances 0 0 18 View

Defines known application instances and provides their configuration settings.

User Defined Record Supported Operations

  • INSERT
  • SELECT
  • UPDATE
  • DELETE
syst_owner_password_rules 0 0 19 View

Defines the password credential complexity standard for a given Owner. While Owners may define stricter standards than the global password credential complexity standard, looser standards than the global will not have any effect and the global standard will be used instead.

User Defined Record Supported Operations

  • INSERT
  • SELECT
  • UPDATE
  • DELETE
syst_hierarchy_items 0 0 15 View

Hierarchy Item records represent a level in the hierarchy of their parent Hierarchy.

General Usage

Each Hierarchy Item record is individually sequenced in its group via the hierarchy_depth column.

Note that once the Hierarchy is active and in use by Hierarchy implementing Components, most changes to the Hierarchy Item records will not be allowed to ensure the consistency of currently used data.

User Defined Record Supported Operations

  • INSERT
  • SELECT
  • UPDATE
  • DELETE

System Defined Record Supported Operations

  • SELECT
  • UPDATE - See column comments for applicable restrictions.
  • DELETE - Only user maintainable records.

Supplemental Notes

Once the Hierarchy is active and in use by Hierarchy implementing Components, most changes to the Hierarchy Item records will not be allowed to ensure the consistency of currently used data. Also for data consistency reasons, using this API view imposes the fundamental restriction that Hierarchy Item hierarchies can only be constructed top down and deconstructed bottom up. Updates are limited to allowing some naming updates, but not updates in hierarchy changes.

syst_instance_network_rules 0 0 15 View

Defines firewall-like rules, scoped to specific instances, indicating which IP addresses are allowed to attempt authentication and which are not. These rules are applied in their defined order after all global_network_rules and owner_network_rules.

User Defined Record Supported Operations

  • INSERT
  • SELECT
  • UPDATE
  • DELETE
syst_enums 0 0 16 View

Enumerates the enumerations known to the system along with additional metadata useful in applying them appropriately.

User Defined Record Supported Operations

  • INSERT
  • SELECT
  • UPDATE
  • DELETE

System Defined Record Supported Operations

  • SELECT
  • UPDATE - See column comments for applicable restrictions.
syst_identities 0 0 16 View

The identities with which access accounts are identified to the system. The most common example of an identity would be a user name such as an email address.

User Defined Record Supported Operations

  • INSERT
  • SELECT
  • UPDATE
  • DELETE
syst_settings 0 0 28 View

Configuration data which establishes application behaviors, defaults, and provides a reference center to interested application functionality.

User Defined Record Supported Operations

  • INSERT
  • SELECT
  • UPDATE
  • DELETE

System Defined Record Supported Operations

  • SELECT
  • UPDATE - See column comments for applicable restrictions.
syst_sessions 0 0 11 View

Database persistence of user interface related session data.

User Defined Record Supported Operations

  • INSERT
  • SELECT
  • UPDATE
  • DELETE
syst_hierarchies 0 0 17 View

Establishes a hierarchical template for parent/child relationships to follow. The hierarchies relation creates a type of hierarchy which is linked to a specific feature or functional area of the application via a record’s hierarchy_type_id reference. Hierarchies is also the parent relation of Hierarchy Items relation (ms_syst_data.syst_hierarchy_items) records where each Hierarchy Items record represents a level of the hierarchy.

General Usage

Note that once the Hierarchy is active and in use by Hierarchy implementing Components, most changes to the Hierarchy records will not be allowed to ensure the consistency of currently used data.

User Defined Record Supported Operations

  • INSERT
  • SELECT
  • UPDATE
  • DELETE

System Defined Record Supported Operations

  • SELECT
  • UPDATE - See column comments for applicable restrictions.
syst_disallowed_passwords 0 0 1 View

A list of hashed passwords which are disallowed for use in the system when the password rule to disallow common/known compromised passwords is enabled. Currently the expectation is that common passwords will be stored as sha1 hashes.

User Defined Record Supported Operations

  • INSERT
  • SELECT
  • DELETE
syst_applications 0 0 11 View

Describes the known applications which is managed by the global database and authentication infrastructure.

User Defined Record Supported Operations

  • INSERT
  • SELECT
  • UPDATE

System Defined Record Supported Operations

  • SELECT
syst_credentials 0 0 14 View

Hosts the Credentials by which a user or external system will prove its Identity.

General Usage

Note that not all Credential types are available for authentication with all Identity types.

User Defined Record Supported Operations

  • INSERT
  • SELECT
  • UPDATE
  • DELETE
syst_enum_functional_types 0 0 15 View

For those Enumerations requiring Functional Type designation, this table defines the available types and persists related metadata. Note that not all Enumerations require Functional Types.

User Defined Record Supported Operations

  • INSERT
  • SELECT
  • UPDATE
  • DELETE

System Defined Record Supported Operations

  • SELECT
  • UPDATE - See column comments for applicable restrictions.
syst_actions 0 0 18 View

Defines actions which may be taken when a Menu Item is “selected” or the defined Command is entered by a user.

General Usage

The null/not null state of this column must match the null/not null state of the command column.

User Defined Record Supported Operations

  • INSERT
  • SELECT
  • UPDATE
  • DELETE

System Defined Record Supported Operations

  • SELECT
  • UPDATE - See column comments for applicable restrictions.
syst_perms 0 0 18 View

Defines the available system and application permissions which can be assigned to users.

The Permission is divided into the following concepts:

  1. The Permission record itself defines a subject for which application security and control concerns exist.

  2. Each Permission is made up of standard Rights. These Rights are:

    • View - the ability to view data.

    • Maintenance - the ability to change or process existing data.

    • Administration - the ability to create or destroy data.

    • Operations - the ability to perform certain operations or processes.

  3. The Right for each Permission is assigned a Scope of applicability which can limit or extend the grant of a Right. Each Right of the Permission may define which Scopes it supports out of the following possibilities:

    • Unused - The Right does not exist in any meaningful way for the Permission.

    • Deny - The Right is not granted by the Permission grant; this is typically used in cases where other Rights may be granted, for example permitting a user to see a value (View Right), but not to Maintain or perform data Admin tasks (Maint & Admin Rights).

    • Same User - The Right grant is limited in Scope to those records which are in some way designated as belonging to the specific user exercising the Right. Ownership designation will be defined by those functions where a Permission is checked.

    • Same Group - The Right grant is limited in Scope to those records which are in some way designated as belonging to a specific group or groups and to which the user belongs in some way. Ownership designation will be defined by those functions where a Permission is checked.

    • All - The Right grant is not limited in Scope and all records which are subject to the Permission are available to the user.

Permissions are assigned to Permission Roles which are in turn granted to individual users. If a Permission is not assigned to a Permission Role, then the assumption is that the Permission Role’s users are denied all rights granted by the unassigned Permission.

Some Permissions may be dependent on the grants of other more fundamental Permissions. For example, a user may be granted only View Rights to the sales order form, but also granted Maintenance Rights to sales pricing data. In such a case the sales order Rights would dictate that the user does not have the ability to maintain sales pricing in the sales order context.

Specific details of applicability and the determination of Scope boundaries will vary by each specific scenario. Consult individual Permission documentation for specific understanding of how determinations of access are made.

User Defined Record Supported Operations

  • INSERT
  • SELECT
  • UPDATE
  • DELETE

System Defined Record Supported Operations

  • SELECT
  • UPDATE - See column comments for applicable restrictions.
syst_disallowed_hosts 0 0 9 View

A simple listing of “banned” IP address which are not allowed to authenticate their users to the system. This registry differs from the syst_*_network_rules tables in that IP addresses here are registered as the result of automatic system heuristics whereas the network rules are direct expressions of system administrator intent. The timing between these two mechanisms is also different in that records in this table are evaluated prior to an authentication attempt and most network rules are processed in the authentication attempt sequence.

User Defined Record Supported Operations

  • INSERT
  • SELECT
  • DELETE
syst_instance_type_contexts 0 0 11 View

Establishes Instance Type defaults for each of an Application’s defined datastore contexts.

General Usage

In practice, these records are used in the creation of Instance Context records, but do not establish a direct relationship; records in this table simply inform us what Instance Contexts should exist and give us default values to use in their creation.

User Defined Record Supported Operations

  • SELECT
  • UPDATE
syst_access_account_instance_assocs 0 0 14 View

Associates access accounts with the instances for which they are allowed to authenticate to. Note that being able to authenticate to an instance is not the same as having authorized rights within the instance; authorization is handled by the instance directly.

User Defined Record Supported Operations

  • INSERT
  • SELECT
  • UPDATE
  • DELETE
syst_perm_role_grants 0 0 14 View

Establishes the individual permissions which are granted by the given permission role.

General Usage

Note that the absence of an explicit permission grant to a role is an implicit denial of that permission.

User Defined Record Supported Operations

  • INSERT
  • SELECT
  • UPDATE
  • DELETE

System Defined Record Supported Operations

  • SELECT
syst_perm_roles 0 0 14 View

Defines collections of permissions which are then assignable to users.

User Defined Record Supported Operations

  • INSERT
  • SELECT
  • UPDATE
  • DELETE

System Defined Record Supported Operations

  • SELECT
  • UPDATE - See column comments for applicable restrictions.
syst_action_groups 0 0 19 View

Defines groups of actions which can be associated with an invoking primary command.

There are two fundamental roles fulfilled by the Action Group. The first of these is a simple organizing role related to child Action records (ms_syst_data.syst_actions); there is little functional purpose to this first role aside from user interface categorization.

The second fundamental role establishes the Primary Command component of a user commandline interaction model. The Primary Commands defined by Action Groups express the general action a user wishes to take: “new”, “open”, “display” are examples of such generalized actions. These generalized actions in turn can then be specialized using a secondary Command (defined by Actions) to a specific system function: “new purchase order”, “new sales order”, “open sales order”, “display report” are examples showing how the generalized commands (Action Group) are tied to specific Action commands (Actions).

User Defined Record Supported Operations

  • INSERT
  • SELECT
  • UPDATE
  • DELETE

System Defined Record Supported Operations

  • SELECT
  • UPDATE - See column comments for applicable restrictions.
syst_access_account_perm_role_assigns 0 0 10 View

Assigns Permission Roles to Access Accounts providing an MCP authentication mechanism.

User Defined Record Supported Operations

  • INSERT
  • SELECT
  • DELETE
syst_owner_network_rules 0 0 15 View

Defines firewall-like rules, scoped to specific owners, indicating which IP addresses are allowed to attempt authentication and which are not. These rules are applied in their defined order after all global_network_rules and before all instance_network_rules.

User Defined Record Supported Operations

  • INSERT
  • SELECT
  • UPDATE
  • DELETE
syst_global_network_rules 0 0 14 View

Defines firewall-like rules that are global in scope indicating which IP addresses are allowed to attempt authentication and which are not. This also includes the concept of global defaults applied to new Owner IP address rules. These rules are applied in their defined ordering prior to all other rule sets.

User Defined Record Supported Operations

  • INSERT
  • SELECT
  • UPDATE
  • DELETE
syst_global_password_rules 0 0 18 View

Establishes a minimum standard for password credential complexity globally. Individual Owners may define more restrictive complexity requirements for their own accounts and instances, but may not weaken those defined globally.

System Defined Record Supported Operations

  • SELECT
  • UPDATE - See column comments for applicable restrictions.
syst_owners 0 0 11 View

Identifies instance owners. Instance owners are typically the clients which have commissioned the use of an application instance.

User Defined Record Supported Operations

  • INSERT
  • SELECT
  • UPDATE
  • DELETE
syst_menu_items 0 0 18 View

Individual menu entries which can serve as actionable menu items, grouping items, references to other menus to include recursively, or any appropriate combination thereof.

User Defined Record Supported Operations

  • INSERT
  • SELECT
  • UPDATE
  • DELETE

System Defined Record Supported Operations

  • SELECT
  • UPDATE - See column comments for applicable restrictions.
  • DELETE - Only user maintainable records.
syst_password_history 0 0 10 View

Keeps the history of access account prior passwords for enforcing the reuse password rule.

User Defined Record Supported Operations

  • INSERT
  • SELECT
  • DELETE
syst_application_contexts 0 0 15 View

Applications are written with certain security and connection characteristics in mind which correlate to database roles used by the application for establishing connections. This table defines the datastore contexts the application is expecting so that Instance records can be validated against the expectations.

User Defined Record Supported Operations

  • INSERT
  • SELECT
  • UPDATE
  • DELETE

System Defined Record Supported Operations

  • SELECT
  • UPDATE - See column comments for applicable restrictions.
  • DELETE - Only user maintainable records.
syst_instance_type_applications 0 0 10 View

A many-to-many relation indicating which Instance Types are usable for each Application.

General Usage

Note that creating ms_syst_data.syst_application_contexts records prior to inserting an Instance Type/Application association into this table is recommended as default Instance Type Context records can be created automatically on INSERT into this table so long as the supporting data is available. After insert here, manipulations of what Contexts Applications require must be handled manually.

User Defined Record Supported Operations

  • INSERT
  • SELECT
  • DELETE
syst_menus 0 0 15 View

Establishes user configurable menu and navigation definitions for use in the User Interfaces of the application.

User Defined Record Supported Operations

  • INSERT
  • SELECT
  • UPDATE
  • DELETE

System Defined Record Supported Operations

  • SELECT
  • UPDATE - See column comments for applicable restrictions.
  • DELETE - Only user maintainable records.