Securing a Multitenant Database API with DreamFactory

SaaS applications generally tend to manage customer data in a multitenant database, meaning all customers share the same database. The data is secured, or segmented, by a unique identifier that maps a particular table record to a customer. This approach to data management means the SaaS can avoid the overhead and complexity of managing customer data within separate databases.

Increasingly, SaaS customers are asking for API-based access to their data. There are many reasons to ask for data access outside of the typical SaaS user interface, including the desire to create custom reports, integrate the data with other systems, or even just to ensure a backup of the data is on hand should the SaaS suffer an outage or worse, close its doors.

This feature request can be both a blessing and a curse for the SaaS provider. Building the API from scratch will undoubtedly require a great deal of time and effort. The commitment is even more painful when one considers that at least initially only a small portion of the overall customer base will use the API. On the other hand this could provide a new revenue stream because it’s reasonable for API access to be made available as a paid tier add-on.

SaaS providers lacking the time, skills, or desire to build and maintain this API might want to consider automating the API generation using DreamFactory. In addition to automatically generating an API for the SaaS database, DreamFactory’s role-based access controls can be used to isolate each customer’s data via unique API keys, all with no coding required.

Configuring Row-Level Database API Filters

 DreamFactory’s role-based API access control feature is bundled into the web-based administration console included with all versions of the platform. It’s also possible to create, view, edit, and delete access roles using the system API, meaning SaaS providers could automatically generate a role and associated API key as part of the customer onboarding process. To get started though, it’s easiest to use the administrative interface, so let’s have a look at that.

To begin, log into your DreamFactory instance (you can start a free hosted trial at https://genie.dreamfactory.com/register if you’d like to follow along with the remainder of this post) and click on the Roles tab. Click Create and you’ll be presented with the following screen:

I’ve populated the fields with a representative role name and description that might be used to clearly identify the role as being associated with the customer Acme, Inc. Click the Next button and after pressing the purple plus (+) button on the left you’ll be presented with the following screen:

There are five available fields on this screen:

• Service: This select box is used to select the API (or APIs) that will be accessible via this role. In this case you'll choose the API associated with your multitenant database.
• Component: This select box is used to restrict API access to specific endpoints. For instance perhaps you want to restrict customers to a specific set of tables, views, or stored procedures (this will almost certainly be the case as a precursor to row-level filtering).
• Access: This field is used to restrict API access according to HTTP method. All data-related HTTP methods are supported, including GET, POST, PUT, PATCH, and DELETE.
• Requester: This field is used to determine how the role can interact with APIs. In the typical use case you’ll select only API, meaning the user can access the endpoints by accessing them directly either programmatically or through an API client. The SCRIPT option should be enabled when the user will be accessing the specified endpoints by way of a DreamFactory scripted service (see the guide for more information about scripted services - https://guide.dreamfactory.com/docs/integrating-business-logic-into-your-apis/).
• Advanced Filters:You’ll use this field to restrict data access to records matching a specific condition. In the case of a multitenant database you’ll want to ensure that a WHERE clause is added to every query that ensures only those records matching a specific customer ID are returned.

With these concepts in mind, the following screenshot presents the configuration interface with example values:

After saving the role it’s time to associate it with an API key.

Associating the Role with an API Key

Now we’ll generate an API key and associate it with the newly created role. Click on the Apps tab and press Create. You’ll be presented with the following screen:

There are six fields on this screen:

• Application name: This field is used to assign a recognizable name to the API key. It’s just for administrative purposes.
• Description: Like the application name, the description is used for administrative purposes.
• Active: This determines whether the API key will be active.
• App Location: DreamFactory offers a few interesting features pertaining to applications hosted on a file storage service like AWS S3 or locally on the same web server. In this case, the API will be accessed programmatically or via an API client therefore this field should be set to “No Storage Required”.
• Assign a Default Role Filter: This field is used to quickly filter the values otherwise populated in the “Assign a Default Role” field. It’s useful when your DreamFactory instance is managing dozens or even hundreds of roles.
• Assign a Default Role: You’ll use this field to choose the desired role.

With these concepts in mind, the following screenshot presents the configuration interface with example values:

Querying the Multitenant Database API

Suppose your SaaS provides sales-related reporting to companies. You might manage a table that looks like this:

Therefore when Acme, Inc. queries the API, the SQL clause WHERE customer_id = 456 should always be added to any query:

SELECT * FROM sales WHERE customer_id = 456;

This clause will automatically be added whenever the customer queries the sales table via the API:

GET api/v2/mysql/_table/sales

Let’s confirm this by opening an API client such as Postman and issuing a query accompanied by the newly generated API key: 

Sure enough we’ve successfully restricted the output to only those records having a customer ID of 456!

The customer is also free to take advantage of various parameters made available to the generated API, such as order and limit. For instance perhaps the customer is only interested in the most recent 10 sales:

/api/v2/mysql/_table/sales?limit=10&order=sold_at%20DESC

Other Considerations

It’s often the case that internal SaaS multitenant database tables contain data that shouldn’t be exposed to third-parties. Additionally, you might want to include additional information in the API response, perhaps drawn from another database or service. You can easily accomplish this using a scripted service. Once created, instead of pinging the database API directly the customer would instead request data from the scripted service, which would be responsible for limiting the returned columns to a specific set, obfuscating sensitive data, transforming data, and making additional API calls. The process for doing so is out of the scope of this article, however perhaps we’ll soon write a followup to this post with additional details! In the meantime if you’d like to learn more about this approach, check out our guide at https://guide.dreamfactory.com/docs/ or get in touch with our team to schedule a demo.