Exago supports a variety of approaches to make sure that users can only access the data that is assigned to them. These approaches can eliminate the need to create different reports for each user. This can be done in one of four ways. Using either column, schema, database, or custom SQL based tenancy.
Column Based Tenancy
The most basic multi-tenant environment is when each table, view and stored procedure has one or more columns that indicate which user(s) has access to each row.
To set column based tenancy in Exago:
- Create a Parameter for each tenant column.
NOTE. For these parameters set Hidden to False.
- For each Data Object click the Tenant Columns dropdown. Use the Tenant Columns menu to match each tenant column in the Data Object with its corresponding Parameter.
- When initializing Exago through the API, set the value of each tenant parameter for the current user.
Schema Based Tenancy
Some multi-tenant environments create multiple tables/views/stored procedures with the same name and columns but different database schema. Information is then stored in the appropriate table based on database schema.
To set schema based Tenancy in Exago:
- On the Data Source set ‘Schema/Owner Name (blank for default)’ to any valid value.
- For each table/view/stored procedure create a Data Object. In the Name dropdown select the object that utilizes the schema value used in step 1. This will tell Exago that for this Data Object it should retrieve the schema from the Data Source.
- When initializing Exago through the API, set the schema on the Data Source for the current user.
Database Based Tenancy
Another way to assure that each user can only access their data is to provide a separate database for each user. In this situation each database should have the same tables, views and stored procedures.
To support database based tenancy in Exago:
- Create a Data Source and corresponding Data Objects using any one of the Databases.
- When initializing Exago through the API, set the connection string on the Data Source to access the appropriate database for the current user.
Custom SQL Based Tenancy
Multi-Tenant security can also be assured by using Custom SQL for all Data Objects. Exago can pass parameter values into each SQL statement to filter data based on user.
To set Custom SQL based tenancy in Exago:
- For each Data Object open the Custom SQL menu and create the desired SQL utilizing parameters to assure only appropriate information is available.
NOTE. Parameters should be surrounded by single quotes.
- When initializing Exago through the API, set the value of any parameters utilized in the SQL for the current user.