This month, we have some exciting additions for ETL/ELT capabilities, continued updates that improve security, governance and IT operations as well as updates for our embedded partners.
SQL Transformations for improved ETL/ELT
Last year, we launched Workato FileStorage that allows users to store, create and share large files within Workato. You can receive files from connectors and store it Workato FileStorage or Receive data as a bulk CSV stream from Salesforce actions, store it as a CSV file in Workato FileStorage, and share it across projects.
While managing and working with files natively in Workato is great, how could we use one or more CSV files to help transform data within a Recipe? The new SQL Transformation connector helps give the “T” in ETL/ELT more power.
Data transformation is an essential part of ETL (Extract, Transform, Load) and ELT (Extract, Load, Transform) use cases. ETL and ELT are both data integration processes that are used to move and manipulate data from one system to another. The transformation step in these processes is crucial as it allows data to be cleaned, formatted, and transformed into a format that is usable by the target system.
In the real world, data transformation is used in a variety of ways to improve business processes and decision-making. One of the main benefits of data transformation is that it allows organizations to standardize and cleanse their data, making it more accurate and reliable. This is especially important for companies that have multiple sources of data, such as from different departments or systems. By transforming the data, organizations can ensure that it is consistent and can be easily combined for analysis.
Employee Data Management: Human Resources departments often need to manage large amounts of employee data, such as personal information, job titles, and salary information. By transforming this data, HR can ensure that it is accurate and up-to-date, and can easily combine it with other data sources, such as location, payroll or benefits information.
SQL Transformations connector allows users perform complex data transformations on the incoming data from multiple sources. This is a useful tool to perform transformations on the extracted data, before it is loaded to a specific destination.
So how do we combine Workato FileStorage and SQL Transformations together to solve for these ETL common use cases?
Let’s look at the example. We want to migrate customer data into a new CRM system. We have a CSV of all of our customers, but we want to do a few things before we move it over to the new CRM. First, we want to combine the first name and last name into one field. Second, we want to enrich our customer data by adding the customer contract start and end date as we have those as custom fields in Salesforce. We will use a customer contract metadata export to join the data. Lastly, we want to make sure we are not importing any duplicate records, so will perform an aggregate count to only pull one customer record. Using SQL allows us to transform a variety of different patterns from a single query.
Now, if you have used a Workato Collection, this may seem similar by also providing a way to perform SQL queries on a given collection. However, by using SQL Transformations along with Workato File Storage, you are able to access GBs worth of data and also have a life beyond a given job. By transforming output into a Workato File, you can now share across Jobs and Recipes.
Learn more about the beta SQL Transformation connector in the documentation.
SCIM Provisioning
Last month introduced enhancements to SAML sync for Workato roles. This month we expanded user provisioning even more with the introduction of SCIM support.
Cross-domain Identity Management (SCIM) is a standard protocol that allows for the automation of user provisioning and management across multiple systems and platforms. It is designed to help automate the entire user lifecycle management process from a new user joining the company, through changes of access during the life of the user, all to the end when the user leaves. This streamlines the process of managing user identities and access to resources, making it easier for organizations to manage and secure their systems.
One of the main reasons why SCIM should be used is its ability to simplify the process of managing user identities and access to resources. With SCIM, organizations can easily provision and deprovision users across multiple systems and platforms, without the need for manual intervention. This helps to reduce the risk of human error and ensures that users have the appropriate access to resources at all times. As an example, users can update a collaborator’s Workato role directly from their IdP by accessing the “workato_role” attribute.
Another benefit of SCIM is its ability to improve security. By automating the process of managing user identities and access, SCIM can help to reduce the risk of unauthorized access to Workato assets and functions. This is because SCIM ensures that users are only given access to the resources they need to perform their job, and that their access is revoked as soon as they no longer need it. This could be a specified HR role that has sensitive data in a Lookup table.
Once you have enabled SCIM for your workspace, you configure it by going to the SCIM provisioning option in the Workspace settings. This will allow you to to do the following:
- Full lifecycle management actions (Create users, update user attributes like workato_role, and deprovision users with supported IdP’s like Okta, OneLogin, CyberArk Idaptive)
- Ability to provision single or multiple Workato Workspaces from identity platforms
- Ability to remove environment / Workspace access from identity platforms
- Ability to provision, update, deprovision users at the individual or group level
- Track all access related changes on audit logs across all environments and differentiate between automatic and manual changes
One example of how SCIM is used in the real world is in an organization that has multiple systems and platforms, such as a CRM system, an HR system, and a financial system. With SCIM, the organization can easily manage and provision users across these systems, without the need for manual intervention. This not only saves time and resources, but also ensures that users have the appropriate access to resources at all times. This allows Workato to be provisioned just like any other common application for an organization’s users.
SCIM is an important tool for organizations to streamline the process of managing user identities and access to resources in Workato. It simplifies the process of provisioning and deprovisioning users, improves security, and reduces the risk of human error. With SCIM, organizations can ensure that users have the appropriate access to resources at all times, making it easier to manage and secure their systems.
Learn more about account provisioning with SCIM by checking out the documentation.
Governance and Access Control
Role based access control (RBAC) and granular permissions are important for maintaining security and control over access to sensitive information within an organization. They are designed to ensure that only authorized users have access to sensitive information and resources, and that they can only perform the actions that they are authorized to perform.
RBAC is a security model that is based on the principle of role-based access. In this model, users are assigned to specific roles and are granted access to resources and information based on their role. For example, an employee in the finance department might be assigned the role of “financial analyst” and be granted access to financial data, while an employee in the marketing department might be assigned the role of “marketing analyst” and be granted access to marketing data.
Granular permissions, on the other hand, refer to the level of control that is granted to users over specific resources such as Lookup tables, deployment or API platform rights in Workato. Instead of granting broad access to all resources, granular permissions allow for a more fine-grained approach to access control, allowing organizations to grant access to only the specific resources that a user needs to perform their job. In Workato, this means going beyond all or nothing level of access but rather, provide CRUD like access for set of permissions such as View Only, Modify, or Delete.
Financial institutions use RBAC and granular permissions to control access to financial data and resources. For example, an employee in the accounting department might be assigned the role of “accountant” and be granted access to financial data, while an employee in the compliance department might be assigned the role of “compliance analyst” and be granted access to a subset of financial data, such as compliance reports.
This role would be pre established and could be replicated in Workato via SCIM mentioned above. In turn, the “accountant” role may have a lookup table for cost centers and would need to update them from time to time to use across various Recipes. Others could benefit from viewing this Lookup table in their own automations. However, “Accountant” should be the only role to delete or modify the Lookup table.
Over the coming months, you will see more access control enhancement as part of a larger effort to provide more granular permissions across the platform.
With the introduction of SCIM support and continued efforts to provide RBAC and granular permissions, organizations are positioned to maintain security and control over access to sensitive functions within Workato all while providing a full audit log of account changes.
This month brings the following access control enhancements.
- Permissions for Lookup Tables
- Granular Permissions for API platform
- On-Prem Role based access
Embedded APIs
Workato Embedded partners delivering customer-facing integrations at scale benefit from relying on Embedded Partner Platform APIs to programmatically create and manage customer accounts and assets, including recipes and connections. Knowing the impact of Embedded APIs, Workato continues to add endpoints that will further automate your integration solution for scale.
Releases in the latter part of 2022 included an additional six connectors:
- List all platform connectors
- Delete/Disconnect Connection via API
- QBO: Obtain RealmId via API
- Get Manage Custom Role Permissions via API
- Pass email id while adding a member
- CRUD APIs for Custom Connectors
These API endpoints will enhance your automation of tenanted account access to team members, assess or manage connector usage, or enhance your customer data with email IDs. For additional information on endpoint calls, check out this Workato Doc.
Embedded Custom OAuth
Released in the fall of 2022, Custom OAuth profiles is another way that Workato Embedded partners can control their customers’ experience. Instead of using Workato’s registered OAuth client to ask a customer to authorize Workato to access their application, embedded partners are now able to register their own OAuth clients, so it appears as if they’re asking for authorization themselves, and with just the permissions they require.
This gives you control over the app’s branding, permission scopes, and OAuth profile. You may plan to create a custom OAuth profile for security reasons in situations where data cannot flow through an app not belonging to you, or if you have permissions to scopes on your own applications that Workato does not support.
Partners can curate the experience for Salesforce, Microsoft Dynamics CRM, HubSpot, Microsoft Sharepoint, Outlook, Google Sheets, Google Drive, OneDrive and Zoom. More information can be found in this Workato Doc.