Viewing and updating Manage external data store tables
Updates to the SQLite file can only be made by the instance that runs the PSUPDATE job. Only this node can allow changes to the file and will replicate this file out to all nodes during auto discovery.
The data within the SQLite file can be accessed using the API Service (idapi) function ExtDBQueryExec. The query and bind values provided will return result sets that can be used by the caller. This information can contain custom data that is used by exit traps or plugins for business logic decisions.
The SQLite file is replicated between nodes using the File replication service (idfilerep) .
With Manage external data store (DBE) module:
The SQLite file is updated on the node that runs the PSUPDATE task.
Changes made to the SQLite file will not be replicated to other servers until PSUPDATE occurs
The SQLite file can be viewed on all other nodes in read-only mode.
With API Service:
The ExtDBQueryExec function can be used to run SQL statements
Results are returned from the SQL statement and can be used with-in plugins or exit traps to make business logic decisions.
Bravura Security Fabric does not enforce the constraint that only one server update the SQLite data file.
To ensure consistent behavior across all servers in a replicated environment, avoid updating extdb dynamically during runtime; instead, update it only during auto discovery before file replication occurs.
In a replicated environment where it is necessary to dynamically update extdb without file replication following immediately after, all access to extdb should be made via API calls to the server responsible for updating extdb.
Example: Searching extdb tables
Click below to view a demonstration of using various searching methods to locate rules in the hid_policy_wfemail table, which was added when the Functional.hid_policy_wfemail component was installed.
This example demonstrates various searching methods to locate rules in the hid_policy_wfemail table, assuming the Functional.hid_policy_wfemail component was installed.
Access the table
Log in to the Bravura Security Fabric Front-end (PSF) as superuser.
Click Manage external data store.
Locate and select the hid_policy_wfemail table.
Note
This table includes over 200 rules. The default Records to display value is 20. In this case, there is data on more than one page.
Locate the page navigation controls at the top right of the table and click Next to advance to the next page.
Change the Records to display value to 250 and press Enter.
Hover your mouse cursor over the " ? " in a column header to view the description.
Scroll to the bottom of the table, reviewing the various rules along the way.
Use the horizontal scroll bar at the bottom of the table to scroll to the right to see additional columns at the right.
Scroll back to the top of the table.
Search the table
The search field allows you to add a SQL WHERE clause to filter records in the table.
Locate the 'search' field at the top left of the table.
Search for the rule used to send an email when a request authorizer is reminded that their approval is required by entering the following:
EventID='EVENT_AUTH_EMAIL_REMINDER'Click the Search icon (magnifying glass) or press Enter.
Scroll to the right to review the rule details or click the Edit icon.
Clicking the Edit icon at the far left of a row opens a pop-up for the selected rule.
Click X or Cancel to close the pop-up.
Search for the rules used to send an email when a user is terminated at a scheduled date/time by replacing the previous WHERE clause with the following:
EventID like 'EVENT_TERMINATE_%' AND Description= 'scheduled termination'
Note
This clause is searching for substrings and using an AND operator.
Multiple rules are returned.
Editing table values
To edit external data store (extdb) tables:
Log into Bravura Security Fabric as a product administrator with the "Manage external data store" privilege.
From the main menu, click Manage external data store.
Search for or select the table you want to edit.
Tables with many columns can be wide and require horizontal scrolling to edit. Page navigation is located in the top right corner.
Each column header has a "help" string accessible on-mouse-over of the help icon
.Browse the table rows, or use the Search field on the top left side of the table view where you can add SQL WHERE clauses to filter the records, for example:
Attribute='TERM-STATE-CHANGE'or search for substrings
Attribute like 'TERM-%'
Once you identify the row, click the edit icon
(at the start of each row) to edit tables in vertical mode.
Alternatively, you can use other tools, such as the free SQLiteStudio, to edit the instance's
db\extdb.dbSQLite database use on the primary node server.
Allowed IDs
Each column can be configured in the Manage external data store (DBE) module to restrict the value to an ID within the product. An Allowed IDs drop box is available to superusers for each column. Once the Allowed IDs are set, any rows updated or added must match the specified ID. Product administrators only see the setting and cannot change the column setting.
Foreign Keys
When a table references foreign keys, all rows need to respect the foreign key relationship. If a table has foreign keys, a button is available on the left of every row. Destination or source tables can be viewed by clicking this button.
If there is only one foreign key, the button is named after the table being referenced. Otherwise, the button is named ”Foreign keys”, and clicking the button brings up a pop-up that lists each foreign key.
When a foreign key is selected, a query displays the rows in the referenced table that matches the source value.
On a destination table, columns that include a foreign key constraint display a magnifying glass to select the value from the source table.
Changes are not committed until Update is clicked.
Removing a row from the source table might not be permitted until foreign key references are removed in destination tables.
Table configuration options
Each table in the Manage external data store (DBE) module has configuration options that only product administrators with all administrative privileges (superuser) can modify. Superusers can access the configuration options by clicking on the plus ( + ) symbol.
The following options can be set for each table:
Read-only options:
Select Read-only to make the whole table read only.
Specify Read-only rows based on a column value.
Default search query – Once set only certain data will be displayed each time the table is viewed.
Group – Used to organize the tables and make searching for sets of tables easier.
Description – Used to organize tables and make searching for them easier.
Column groups – Use a comma-delimited list to create groups for table columns. An example of this would be to create groups using ”Required,Non-required” as input then assigning columns accordingly.
Instructions – Used to provide table users with additional information about the table or usage instructions.
The following options can be set for each column:
Description – Used to explain in detail the contents of the column if the column name is insufficient.
Allowed IDs – Used to restrict what data can be added to the column.
Read-only – Select to make the column read only.
Uppercase – Select to display text in uppercase.
Display width – Used to set column width.
Click Update in the configuration section to save the settings.
Replication
Replication between nodes can be done through one of the following:
The scheduled auto discovery process which, by default, happens once a day
A Windows scheduled task which, by default, happens once every five minutes
The Manage external data store (dbe) module web interface by clicking Replicate on the page, which happens immediately
The InstanceRunUtility API
Replication can only be triggered by the primary node; that is, the node with auto discovery scheduled.