Skip to main content
Version: On prem: 15.0.1

Omada Data Warehouse

Omada Identity Data Warehouse (ODW) is a dedicated identity management data warehouse. It is designed for use as a data source for compliance applications such as Omada Identity.

This chapter discusses the security concepts within ODW such as database access, Kerberos authentication, and licensing. These are all features that you must understand when you plan security in your Omada Identity implementation.

In ODW, data security is based on actor roles and ownership. The type of data a user is permitted to view is based on the role of the user. ODW contains the following actor roles:

  • Auditor
  • System owner
  • Resource owner
  • Context owner
  • Identity owner

The Auditor role has read access to all data. The other roles have read access to a subset of data, based on ownership. A system owner has read access to the systems he owns, but not to any other system. Similar for resource owners, identity owners and context owners.

When users connect to ODW through a report or any other client, they log on with their AD account. For each actor role, ODW contains a set of views that implement the data security. The Auditor views expose all data, but they can only be queried by accounts that possess the odw_auditor database role. The views for the other actor roles expose data depending on the user’s ownership, and can be queried by accounts that possess the odw_reader database role.

In order to determine the user’s ownership, the user must be identified. In ODW, the user is the identity that owns the account that is logged on. The user’s ownership is determined as all the systems, resources, identities and contexts that the identity owns.

Example

The identity Wayne Perkins owns the system GWG and the account WAYPER. The WAYPER account has been granted the Reader role in Data Warehouse.

When WAYPER browses the reports, he can see data for GWG, but not for any other system.

Accounts that neither possess the odw_auditor nor the odw_reader database role cannot see any data.

In addition to these actor roles, administrative access to the database needs to be configured for administrative users.

  • Administrative access: Users who run imports and exports, or need to view operational data (such as the Operation Reports) must have elevated rights to databases.
  • Application access: Applications connect to ODW with read access to all data. ODW relies on the application to enforce data security.

Setting up database access​

Users can access the Omada Identity Data Warehouse database using integrated security. You must grant all relevant users read access to the database. Typically, there are different relevant users for each environment.

Assign users read access
  1. Create database logins for the relevant users or groups. For example, Domain Users.
  2. Add the logins as users in the main Data Warehouse database.
  3. Assign the odw_reader database role to the users.

The odw_reader role grants the permission to the database views in the main Data Warehouse database. The views enforce row level security based on data ownership.

Assign auditor read access
  1. Create database logins for the relevant users or groups. For example, a dedicated Data Warehouse Auditor group.
  2. Add the logins as users in the main Data Warehouse database.
  3. Assign the odw_auditor database role to the users. The odw_auditor role grants the permission to the AUDIT database views in the main Data Warehouse database.
Assign access permissions to three type of users on-prem

You can create database logins and grant database access to three types of users: Administrators, Auditors, and Users.

  1. Run the two SQL scripts CreateLogins.sql and CreateUsers.sql. In a default installation, they are located at C:\Program Files\Omada Identity Suite\Datawarehouse\Support Files.
  2. The three groups each have different levels of access to the database. You must run CreateLogins before CreateUsers.
  3. Open the CreateLogins script in SSMS.
  4. Change the parameters in the DECLARE section for @AdministrativeGroup, @AuditorGroup, and @UserGroup to the groups used.
  5. Run the script.
  6. Open the CreateUsers script in SSMS and change the parameters in the DECLARE section for @AdministrativeGroup, @AuditorGroup, and @UserGroup to the groups used.
  7. I If you do not use the default database names, edit the database name in USE [OIS Data Warehouse].
  8. Run the script.
Enable report server access on-prem

In addition to database read rights, you must give ODW Report readers browse rights to the reports on the SQL Server Reporting Services (SSRS) server.

  1. In SSRS Report Manager, go to Folder Settings -> New Role Assignment.
  2. Select the Browser checkbox and click OK to save the new role assignment.

License on-prem​

The ODW license key entered in the installation wizard is written to the database during the installation. The license is stored in the table tblApplicationSetting in the Omada Identity Data Warehouse database in a row where the Key column has the value licenseString. You can only have one license string in the table at any given time.

After installation, you can only update the license string manually. You can use the following piece of SQL to update the license string in the database:

USE [OIS Data Warehouse]
 GO
 UPDATE [tblApplicationSetting] set [ValueStr] = '<INSERT LICENSE STRING VALUE HERE>'
 WHERE [Key] = 'licenseString'
 GO

To update the license string manually, you must have run the Omada ODW Configuration Package at least once after installation/upgrade. If you are not using the default database names, you must also modify the USE statement to point to the main ODW database.

Omada Identity Data Warehouse and ES Portal Authorization​

This advanced configuration section provides information that enables you to configure ODW and ES authorization to suit your company’s business needs.

If you want to, you can use Omada Identity Data Warehouse basic security as an alternative to the standard model where users are authenticated against the database using their Active Directory credentials.

Users are authenticated in the Omada Identity Portal with a username and password, and a user token is created for the session. The user token is written to the Omada Identity Data Warehouse database in the table ReportSecurityToken, along with the OISID of the identity and a definition of whether the user is an auditor or not.

important

To enable this feature, use the SSRS service account setting tblApplicationSetting. The setting must contain the name of the account that the SSRS reports use to connect to the Omada Identity Data Warehouse database. You must also grant users valid access to the Omada Identity Data Warehouse.

To override the standard behavior of the alternative security option, you can implement dbo.CustomGetActiveIdentity(), which is called by dbo.GetActiveIdentity().

ES Portal authentication methods​

The Portal can be configured for either Basic or Integrated authentication.

Typically, on-premise installations use Integrated authentication where users log on with their Active Directory account. Cloud installations typically use Basic authentication where users log on with their Omada Identity account.

No matter which authentication method is used, members of the Auditors user group are able to access the Auditor reports, while members of the Managers and System Owners user groups are able to access My reports.

ODW

Single Sign-on via Identity Provider​

In addition to Windows Integrated Security and Basic Authentication, Omada Identity supports Single Sign-on via a 3rd party Identity Provider like *Microsoft Entra ID or Active Directory Federation Service. For further details, please refer to the Authentication and Single Sign-on section.

Basic Authentication on-prem​

info

Omada does not recommend using Basic authentication in production environments. Basic authentication does not provide the same level of security as the Integrated authenticated options. In consequence, if the Basic authentication is used, your Omada Identity installation will not be protected against, for example, password reuse.

Follow these steps to configure the Portal for Basic authentication:

  1. Start ES portal, go to Setup -> Administration -> More -> Master Settings and set Allow Basic Authentication for OIS portal to True.

  2. Exit ES portal to save Master Settings.

  3. Use Internet Information Services (IIS) Manager to configure authentication settings for the website. Enable Anonymous Authentication and disable all others.

  4. Use SQL Server Management Studio to edit tblApplicationSetting in the Omada Identity Data Warehouse database. Set SsrsServiceAccount to the name of the account that the SSRS reports use to connect to the Omada Identity Data Warehouse database.

    By default, this is the Application pool identity of the Portal and NOT the account that runs the Reporting Services service.

    For example, if you configure the system as shown below, this is the Application Pool identity of the Omada Identity Portal’s website. The Anonymous Authentication Credentials have been set to use the Application Pool identity.

    In addition, the SSRS data source for ODW have been configured to use Windows integrated security:



    note

    Depending on the version of the SQL Server Reporting Services, the Windows Integrated security option might be called differently (for example, "Windows Integrated security" or "Log into the data source as the user viewing the report").

  5. Use SQL Server Management Studio to grant the odw_auditor and odw_reader database roles to the account that has been configured as the SsrsServiceAccount.

  6. Update the SSIS configuration file C:\Program Files\Omada Identity Suite\Datawarehouse\Common\Omada ODW WebService.dtsConfig with the credentials used when the import calls ES portal. Moreover, set the UseDefaultCredentials property to False in order to use the credentials from the config file.

note

The above is set automatically during installation through Omada Identity Installation Tool but needs to be set manually when switching between security models or after "manual" installation.

Forms authentication on-prem​

Follow the steps below to configure the Portal for Forms authentication:

  1. Use Internet Information Services (IIS) Manager to configure authentication settings for the web site. Enable Anonymous Authentication and disable all others.

  2. Use SQL Server Management Studio to edit tblCustomerAuth in the Omada Identity database. Make sure that the value in the AuthenticationType column is set to Forms.

  3. Use SQL Server Management Studio to grant the odw_reader and odw_auditor database role to the Active Directory Domain Users group, either directly or via another Active Directory group.

  4. Update the SSIS configuration file C:\Program Files\Omada Identity Suite\Datawarehouse\Common\Omada ODW WebService.dtsConfig with the credentials used when the import calls ES portal. Moreover, set the UseDefaultCredentials property to False in order to use the credentials from the config file.

note

The above is set automatically during installation through Omada Identity Installation Tool but needs to be set manually when switching between security models or after "manual" installation.

Integrated Authentication on-prem​

info

Before configuring Integrated Authentication, be sure that you have correctly configured Basic Authentication. Basic Authentication should be configured at least for the Administrator/Active Operation Admin. This is a precaution to ensure proper login capability for these users in case the Authentication method is switched from the Integrated to Basic.

Follow the steps below to configure the Portal for Integrated authentication:

  1. Start ES portal, go to Setup -> Administration -> More -> Master Settings and set Allow Basic Authentication for OIS portal to False.

  2. Save the Master Settings.

  3. Use Internet Information Services (IIS) Manager to configure authentication settings for the web site. Enable Windows Authentication and disable all others.

  4. Use SQL Server Management Studio to edit tblApplicationSetting in the Omada Identity Data Warehouse database. Even though the SsrsServiceAccount setting is not being used for Integrated authentication, it must be configured for security reasons. If the setting is empty, the Auditor reports will not show any data.

  5. Use SQL Server Management Studio to grant the odw_reader and odw_auditor database role to the Active Directory Domain Users group, either directly or via another Active Directory group.

  6. Update the SSIS configuration file C:\Program Files\Omada Identity Suite\Datawarehouse\Common\Omada ODW WebService.dtsConfig with the credentials used when the import calls ES portal. Moreover, set the UseDefaultCredentials property to False in order to use the credentials from the config file.

note

The above is set automatically during installation through Omada Identity Installation Tool but needs to be set manually when switching between security models or after "manual" installation.