The SQL Provisioner exports records to a SQL database.
Provisionable Objects |
|
---|---|
Provisioning Eligibility |
|
Provisioning Keys | Not Supported |
Bi-directional Reprovision All | Not Supported |
CoreServer.SqlServers
.SqlConnector.SqlProvisioners
._
).Using Table Name Prefix to provision multiple SQL Provisioner's worth of data to the same Target Database is currently supported, but not recommended. The underlying framework no longer supports table prefixes, resulting in a variety of unsupported workarounds to implement this functionality. It is unclear if this capability will be supportable in the long term. |
SQL Provisioner requires permissions to create and alter tables, and manage all rows within those tables.
SQL> GRANT CREATE, SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA reporting_database TO user_role; |
In addition to the various operational records (People, Groups) the SQL Provisioner will write some Reference Data to the Target Database. Reference Data can be used to understand foreign keys in the provisioned operational data. Reference Data is fully provisioned when the plugin is initially configured, and then updated whenever there are changes to the Reference Data. The tables provisioned as Reference Data are available in the Table Inventory, below.
Note that while Reference Data is updated in real time, there is no further event issued on these changes (ie: there will be no change reflected on the Primary Objects that link to the Reference Data via foreign keys).
The Target Database Schema is a subset of the regular database schema, and only reflects records that are ordinarily eligible for provisioning. Table names are prefixed with sp_
(unless configured otherwise) to distinguish them from operational tables, and also to avoid conflicting with existing code (technical: to avoid auto-binding to existing models).
Table | Type | Derived From | Available Since |
---|---|---|---|
sp_ad_hoc_attributes | Operational | ad_hoc_attributes | v5.0.0 |
sp_addresses | Operational | addresses | v5.0.0 |
sp_cous | Reference | cous | v5.0.0 |
sp_email_addresses | Operational | email_addresses | v5.0.0 |
sp_external_identities | Operational | external_identities | v5.0.0 |
sp_external_identity_roles | Operational | external_identity_roles | v5.0.0 |
sp_group_members | Operational | group_members | v5.0.0 |
sp_group_owners | Operational | v5.0.0 Provisional | |
sp_groups | Operational | groups | v5.0.0 |
sp_identifiers | Operational | identifiers | v5.0.0 |
sp_names | Operational | names | v5.0.0 |
sp_people | Operational | people | v5.0.0 |
sp_person_roles | Operational | person_roles | v5.0.0 |
sp_pronouns | Operational | pronouns | v5.0.0 |
sp_telephone_numbers | Operational | telephone_numbers | v5.0.0 |
sp_types | Reference | types | v5.0.0 |
sp_urls | Operational | urls | v5.0.0 |
The Target Database Schema will not automatically be removed if the plugin configuration is deleted. Drop the schema manually if it is no longer required. |
The Target Database Schema and Reference Data are automatically updated whenever the SqlProvisioner configuration is saved (PAR-SqlProvisioner-1, PAR-SqlProvisioner-2). It is also possible to manually perform either operation by viewing the plugin configuration and clicking the appropriate button.
Resyncing Reference Data will also resync all Groups, since these are effectively Reference Data as well (PAR-SqlProvisioner-3).
Deleting the SqlProvisioner will not delete either the target database schema or the reference data (PAR-SqlProvisioner-4). These tables must be deleted manually (if desired).