FTM database permissions

The FTM database tables can be classified by using the following criteria:
  • FSM data
  • Configuration data
  • Configuration data (history)
  • Operational data
  • Operational data (history)
  • Operational configuration data
  • Operational configuration data (history)
  • OAC tables (FTM user interface)
  • Other
The following tables show the database access requirements for the database tables and views.

FSM tables and views

The following tables are generally classified as FSM tables:
  • EVENT_CONTEXT_OBJ
  • EVENT_EXP_CONTEXT
  • EVENT_TYPE
  • EXT_PUB_RULE
  • FSM
  • FSM_RESOLVE_ACTION
  • FSM_STATE_REL
  • FSM_TRANSITION
The following views are generally classified as FSM tables:
  • EVENT_TYPES_V
  • FSM_V
The FSM tables hold the metadata that defines the FSM lifecycles that are used by the application. These tables are populated by a database script that is generated from the FTM (Rational®) model. This data is used by FTM at run time to drive the main runtime processing.
Table 1. Typical table access requirements for the FSM tables and views
Operational component INSERT SELECT UPDATE DELETE
FTM runtime (Integration node) - Y - -
FTM OAC (IBM® WebSphere® Liberty) - - - -
Script load (application installation) Y Y - Y
Usage report - - - -

Configuration data tables

The following tables are generally classified as configuration data tables:
  • APP_VERSION
  • APPLICATION
  • CALENDAR_ENTRY
  • CALENDAR_GROUP
  • CHANNEL
  • CLASSIFICATION
  • FORMAT
  • MAPPER
  • PARTY
  • SCHEDULE_ENTRY
  • SERVICE
  • VALIDATOR
  • VALUE
The configuration data tables hold the application configuration data. These tables are populated by a database script that is generated from the FTM (Rational) model or by changes that are made by using the FTM Operations and Administration Console (OAC). This data is used by the FTM runtime and application to supplement the main runtime processing.
Table 2. Typical table access requirements for the configuration data tables
Operational component INSERT SELECT UPDATE DELETE
FTM runtime (Integration node) - Y - -
FTM OAC (IBM WebSphere Liberty) Y Y* Y* -
Script load (application installation) Y Y Y -
Usage report - - - -
* - indicates an exception to these common configuration table access requirements. The OAC has the following access requirements that are different:
  • It requires only SELECT and UPDATE permissions for the APP_VER table.
  • It requires only SELECT permission for the APPLICATION table.
.

Configuration data (history) tables

The following tables are generally classified as historic configuration data tables:
  • H_APP_VERSION
  • H_CAL_PARTY_REL
  • H_CALENDAR_ENTRY
  • H_CALENDAR_GROUP
  • H_CHANNEL
  • H_CLASSIFICATION
  • H_FORMAT
  • H_MAPPER
  • H_PARTY
  • H_SCHEDULE_ENTRY
  • H_SERVICE
  • H_VALIDATOR
  • H_VALUE
The historic configuration data tables hold the history of the application configuration data. These tables are updated by triggers that react to changes in the main configuration tables when populated by a database script that is generated from the FTM (Rational) model. Or, when the main configuration tables are changed by using the FTM OAC.
Table 3. Typical table access requirements for the configuration data (history) tables
Operational component INSERT SELECT UPDATE DELETE
FTM runtime (Integration node) - - - -
FTM OAC (IBM WebSphere Liberty) Y Y - -
Script load (application installation) Y - - -

Operational data

The following tables are generally classified as operational tables:
  • ACTIVITY_BASE
  • BATCH_BASE
  • CAL_OBJ_REL
  • COUNTER
  • ERROR
  • EVENT
  • FRAGMENT_BASE
  • OBJ_BASE
  • OBJ_OBJ_REL
  • OBJ_PARTY_REL
  • OBJ_VALUE
  • TRANSACTION_BASE
  • TRANSMISSION_BASE
  • TXN_PAYMENT_BASE
  • TXN_SECURITIES_BASE,
The operational tables hold the live data that is being processed by the FTM application.
Table 4. Typical table access requirements for the operational tables
Operational component INSERT SELECT UPDATE DELETE
FTM runtime (Integration node) Y Y Y -
FTM OAC (IBM WebSphere Liberty) - Y - -
Script load (application installation) Y - - -
The OBJ_BASE and OBJ_PARTY_REL tables are a special case. OBJ_BASE is the primary operational table, but it is also used by the operational config tables. OBJ_PARTY_REL is used to relate a party (config) to an operational object (including operational config). The OAC and script load process generally require write access to the tables.
Table 5. Table access requirements for the OBJ_BASE table
Operational component INSERT SELECT UPDATE DELETE
FTM runtime (Integration node) Y Y Y -
FTM OAC (IBM WebSphere Liberty) Y Y Y -
Script load (application installation) Y - Y -
The following views are defined on the operational tables that have a common access pattern: see Table 6.
  • ACTIVITY_V
  • BATCH_V
  • FRAGMENT_V
  • TRANSACTION_BASE_V
  • TRANSACTION_V
  • TRANSMISSION_BASE_V
  • TRANSMISSION_V
  • TXN_PAYMENT_V
  • TXN_SECURITIES_V
Table 6. Table access requirements
Operational component INSERT SELECT UPDATE DELETE
FTM runtime (Integration node) Y Y - -
FTM OAC (IBM WebSphere Liberty) - Y - -
Script load (application installation) - - - -
The following views are defined on the operational tables that have a common access pattern: see Table 7.
  • BATCH_V_UPD
  • TRANSMISSION_V_UPD
Table 7. Table access requirements for the OBJ_BASE table
Operational component INSERT SELECT UPDATE DELETE
FTM runtime (Integration node) - - Y -
FTM OAC (IBM WebSphere Liberty) - - - -
Script load (application installation) - - - -

Operational data (history)

The following tables are generally classified as operational data history tables:
  • H_ACTIVITY_BASE
  • H_BATCH_BASE
  • H_CAL_OBJ_REL
  • H_COUNTER
  • H_FRAGMENT_BASE
  • H_OBJ_BASE
  • H_OBJ_VALUE
  • H_TRANSACTION_BASE
  • H_TRANSMISSION_BASE
  • H_TXN_PAYMENT_BASE
  • H_TXN_SECURITIES_BASE
The historic operational tables hold historic copies of the operational data. Typically, only H_OBJ_BASE, H_OBJ_VALUE and H_COUNTER are used.
Table 8. Typical table access requirements for the operational data (history) tables
Operational component INSERT SELECT UPDATE DELETE
FTM runtime (Integration node) Y - - -
FTM OAC (IBM WebSphere Liberty) - Y - -
Script load (application installation) Y - - -

Operational configuration data

The following tables are generally classified as operational configuration data tables:
  • SCHEDULER_TASK_BASE
  • SVC_PARTICIPANT_BASE
The operational configuration data tables hold historic copies of the configuration data that can have an operational lifecycle. The data for these tables can be modeled in the FTM (Rational) model, but these tables extend the OBJ_BASE table and might be managed by an FSM.
Table 9. Typical table access requirements for the operational configuration data tables
Operational component INSERT SELECT UPDATE DELETE
FTM runtime (Integration node) - Y Y -
FTM OAC (IBM WebSphere Liberty) Y Y - -
Script load (application installation) Y Y Y -

Operational configuration data (history)

The following tables are generally classified as operational configuration data (history) tables:
  • H_SCHEDULER_TASK_BASE
  • H_SVC_PARTICIPANT_BASE
The historic operational configuration data tables hold historic copies of the operational configuration data.
Table 10. Typical table access requirements for the operational configuration data (history) tables
Operational component INSERT SELECT UPDATE DELETE
FTM runtime (Integration node) - - - -
FTM OAC (IBM WebSphere Liberty) Y Y - -
Script load (application installation) Y Y - -

Operations and Administration Console (OAC) and other tables

The following tables are less easy to classify into groups that share common access permissions:
  • APP_VERSION
  • APPLICATION
  • DEPLOY_INFO
  • GRP_RES_PERM_REL
  • PRODUCT_INSTANCE
  • RES_PERM,
  • SECURITY_INFO
  • UI_AUDIT_LOG
  • UI_USR_PREFS
  • USAGE_COUNT
  • USAGE_INSTANCE_TYPE
The following tables show the database access requirements for each operational component:
Table 11. Typical table access requirements for the FTM runtime
Name INSERT SELECT UPDATE DELETE
DEPLOY_INFO Y Y - -
GRP_RES_PERM_REL - - - -
PRODUCT_INSTANCE Y Y - -
RES_PERM - - - -
SECURITY_INFO - - - -
UI_AUDIT_LOG - - - -
UI_USR_PREFS - - - -
USAGE_COUNT Y Y Y -
USAGE_INSTANCE_TYPE Y Y - -
APP_VERSION - Y - -
APPLICATION - Y - -
Table 12. Typical table access requirements for the FTM OAC
Name INSERT SELECT UPDATE DELETE
DEPLOY_INFO - Y - -
GRP_RES_PERM_REL - Y - -
PRODUCT_INSTANCE - Y - -
RES_PERM - Y - -
SECURITY_INFO - Y - -
UI_AUDIT_LOG Y - - -
UI_USR_PREFS Y Y Y Y
USAGE_COUNT - Y - -
USAGE_INSTANCE_TYPE - Y - -
APP_VERSION - Y Y -
APPLICATION - Y - -
Table 13. Typical table access requirements for script loading
Name INSERT SELECT UPDATE DELETE
DEPLOY_INFO - - - -
GRP_RES_PERM_REL Y Y Y Y
PRODUCT_INSTANCE - - - -
RES_PERM Y Y Y Y
SECURITY_INFO Y Y Y Y
UI_AUDIT_LOG - - - -
UI_USR_PREFS - - - -
USAGE_COUNT - - - -
USAGE_INSTANCE_TYPE - - - -
APP_VERSION Y Y Y -
APPLICATION Y Y Y -
Table 14. Typical table access requirements for the usage report
Name INSERT SELECT UPDATE DELETE
PRODUCT_INSTANCE Y Y - -
USAGE_COUNT Y Y - -
USAGE_INSTANCE_TYPE Y Y - -
Table 15. Typical table access requirements for the purge utility
Name INSERT SELECT UPDATE DELETE
PURGE_LIST Y Y Y Y
ERROR - - - Y
H_OBJ_VALUE - - - Y
OBJ_VALUE - - - Y
COUNTER - - - Y
H_COUNTER - - - Y
H_SVC_PARTICIPANT_BASE - - - Y
SVC_PARTICIPANT_BASE - - - Y
H_ACTIVITY_BASE - - - Y
ACTIVITY_BASE - - - Y
H_TXN_PAYMENT_BASE - - - Y
TXN_PAYMENT_BASE - - - Y
H_TXN_SECURITIES_BASE - - - Y
TXN_SECURITIES_BASE - - - Y
H_TRANSACTION_BASE - - - Y
TRANSACTION_BASE - Y - Y
H_BATCH_BASE - - - Y
BATCH_BASE - Y - Y
H_FRAGMENT_BASE - - - Y
FRAGMENT_BASE - Y - Y
H_TRANSMISSION_BASE - - - Y
TRANSMISSION_BASE - Y - Y
OBJ_BASE - Y - Y
H_OBJ_BASE - - - Y
OBJ_OBJ_REL - - - Y
OBJ_OBJ_REL - Y - Y
OBJ_PARTY_REL - - - Y
CAL_OBJ_REL - - - Y
ACTIVITY_BASE - - - Y
PURGE_LIST - - - Y
SCHEDULER_TASK_BASE - - - Y
SCHEDULE_ENTRY - - - Y
EVENT - - - Y

Read-only views

The following views are read-only.
  • APP_CURRENT_VERSION_V
  • APP_VERSION_V
  • AUDIT_LOG_V
  • CHANNELS_V
  • FSM_RESOLVE_ACT_V
  • GRP_RES_PERMS_V
  • GROUPS_V
  • OBJ_REL_V
  • SVC_PARTPNT_STAT_V
  • SVC_PARTICIPANT_V
  • SERVICE_PARTICIPANT_V
  • TXN_PARTY_REL
Table 16. Operational component access requirements for the read-only (SELECT) views
Name FTM runtime (Integration node) FTM OAC (IBM WebSphere Liberty)
APP_CURRENT_VERSION_V Y Y
APP_VERSION_V - Y
AUDIT_LOG_V - Y
CHANNELS_V Y -
FSM_RESOLVE_ACT_V - Y
GRP_RES_PERMS_V - Y
GROUPS_V - Y
OBJ_REL_V - Y
SVC_PARTPNT_STAT_V Y -
SVC_PARTICIPANT_V - Y
SERVICE_PARTICIPANT_V Y Y
TXN_PARTY_REL Y Y