The SQL Provisioning Plugin provisions CO Person and CO Group records to a SQL database.
Operations
Registry CO Person Transaction | SQL Action |
---|---|
Add | Synchronize the CO Person and their CO Group Memberships |
Edit | Synchronize the CO Person and their CO Group Memberships |
Enter Grace Period | No changes (unless attributes change as part of grace period) |
Expiration / Becomes Inactive | Synchronize the CO Person and their CO Group Memberships |
Unexpire / Becomes Active | Synchronize the CO Person and their CO Group Memberships |
Delete | Remove the CO Person |
Manual Provision | Synchronize the CO Person and their CO Group Memberships |
Registry CO Group Transaction | Changelog Action |
---|---|
Add | Synchronize the CO Group and its Memberships |
Edit | Synchronize the CO Group and its Memberships |
Delete | Remove the CO Group |
Manual Provision | Synchronize the CO Group and its Memberships |
Reference Data
In addition to the usual CO Person and CO Group data, the SQL Provisioning Plugin will write 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 following models are provisioned as Reference Data:
- COU
- CoTermsAndConditions
- OrgIdentitySource (as of Registry v4.0.0)
Installation
This is a non-core plugin, see Installing and Enabling Registry Plugins for more information.
Configuration
- Create a new, empty database where the Provisioner will write. This database is the Target Database.
- Define a new Server in Registry.
- Servers > Add a New Server
- ServerType: SQL
- On the next page, enter the configuration information for the Target Database.
- Configure a new Provisioning Target in Registry.
- Configuration > Provisioning Targets > Add Provisioning Target
- Plugin: SqlProvisioner
- On the next page, select the Server created in the previous step.
- Configuration > Provisioning Targets > Add Provisioning Target
The Target Database must be available when the configuration is saved. The Provisioner will instantiate the target database schema when the configuration is saved, and also populate the initial reference data.
The Provisioner will write one COs worth of records to the Target Database. Multiple COs should not share the same Target Database.
Target Database Schema
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_
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 | cm_ad_hoc_attributes | v3.3.0 |
sp_addresses | Operational | cm_addresses | v3.3.0 |
sp_co_groups | Operational | cm_co_groups | v3.3.0 |
sp_co_group_members | Operational | cm_co_group_members | v3.3.0 |
sp_co_people | Operational | cm_co_people | v3.3.0 |
sp_co_person_roles | Operational | cm_co_person_roles | v3.3.0 |
sp_co_t_and_c_agreements | Operational | cm_co_t_and_c_agreements | v3.3.0 |
sp_email_addresses | Operational | cm_email_addresses | v3.3.0 |
sp_identifiers | Operational | cm_identifiers | v3.3.0 |
sp_names | Operational | cm_names | v3.3.0 |
sp_telephone_numbers | Operational | cm_telephone_numbers | v3.3.0 |
sp_urls | Operational | cm_urls | v3.3.0 |
sp_cous | Reference | cm_cous | v3.3.0 |
sp_co_terms_and_conditions | Reference | cm_co_terms_and_conditions | v3.3.0 |
sp_org_identity_sources | Reference | cm_org_identity_sources | v4.0.0 |
Currently, all records associated with a CO Person are provisioned, including those that are created via Organizational Identity Sources and Registry Pipelines. This may result in what appear to be multiple copies of the same record (eg: multiple Names for the same person) in the Target Database. Viewing the CO Person record will also show the multiple copies, albeit with additional metadata to explain their origins.
As of Registry v4.0.0, relevant models have a foreign key org_identity_source_id
that points to the sp_org_identity_sources Reference Table, which allows for determining the provenance of a given attribute. (If the value is NULL, the attribute is a direct Registry value.) The Operational and Reference Tables can be joined together, with SQL such as
SELECT sp_email_addresses.*, sp_org_identity_sources.description FROM sp_email_addresses INNER JOIN sp_org_identity_sources ON sp_email_addresses.org_identity_source_id=sp_org_identity_sources.id WHERE sp_email_addresses.co_person_id=2650;
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.
Updating the Target Database Schema and Reference Data
The Target Database Schema and Reference Data are automatically updated whenever the SqlProvisioner configuration is saved. As of Registry v4.0.0, 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 CO Groups, since these are effectively Reference Data as well.
Building Views for Application Integration
Some view definitions that may be helpful for application integration are available here.
Database Permissions
SqlProvisioner requires permissions to create and alter tables, and manage all rows within those tables. It is recommended that each SqlProvisioner instance operate within it target schema.
SQL> GRANT CREATE, SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA reporting_database TO user_role;