Analysis Models - Security
This page deals with security subjects related to IFS Analysis Models, e.g. Row Level Security and specific SQL Server users.
IFS Cloud DB Service User
To successfully extract data to the storage layer in SQL Server it is necessary to make sure that the executing user is granted read access to all involved objects. This is handled by using the built-in IFS Cloud service user <IFSINFO>, defined during installation of IFS Cloud.
The <IFSINFO> user has its own schema in the database and the schema will contain all necessary views needed to support the IFS delivered Tabular models. These views are created during installation or during runtime:
- Product specific help views are created during installation.
- Tabular framework specific views are created during installation
- Information Source related Access Views, Fact/Dimension views, are created on demand by using the Information Sources page in the IFS Cloud client.
The Access Views reads either online or from data mart and represent the read interface for Information Sources. The Information Sources are designed to supply full read access to the <IFSINFO> user.
SQL Server Access
To enable the Tabular framework to function properly, it is necessary to define one user in SQL Server that has the necessary access to create and remove objects in the database, to access and run Agent job as well as being able to access the SSIS Catalog properly. In SSAS user is needed that has privileges in SSAS to remove and deploy Tabular Models but also to read SSAS metadata.
SQL Server DB Access
A SQL Server Authentication type user. - A SQL Server user that has the rights to create and remove objects in the database, to access and run Agent jobs, to access the SSIS Catalog etc. - The user must have a database role of at least db_ddladmin. - The user is referred to as the SQL_USERNAME and defined in the Environment Parameters page.
SQL Server Administration
A SQL Server Authentication type user, preferrably the same user as the SQL Server DB Access User mentioned above. - A SQL Server user that has the rights to create and administer access rights etc in the SQL Server instance. - Typical needed server roles are public, securityadmin, serveradmin, setupadmin, sysadmin
The SSIS Catalog needs to be configured properly in order to:
- Act as a place holder for framework related SSIS packages that control:
- Read information from IFS Cloud database (Oracle)
- Access SSIS specific objects in the SQL Server database
- Access SSAS to be able to handle different actions related Tabular Models
With a proper setup during installation of environment, where the IFS Cloud DB Service User, the SQL Server DB Access User and the SSAS Access User are defined, these settings should be enough to ensure that access via the SSIS Catalog will work properly.
A SQL Server Analysis Services user.
- Typically an admin user that has the rights to remove, create, process tabular databases.
- The user should be a Windows user and it must be defined in SSAS.
- The user is defined in the Environment Parameters page.
Data Load Information
The following section provides some overview information about a Data Load, e.g. when data is transferred from IFS Cloud database to the storage layer in SQL Server.
- To perform a data load, the Analysis Models specific environment must have been setup properly. Read this link for more details. The IFS Cloud database user name and password has to be supplied in the environment parameters, i.e. the <IFSINFO> user and the password for that user.
- A data load consists of a set of Tabular Data Sources that defines the source views in IFS Cloud database that should be used. When reading from a source view in the <IFSINFO> schema, it is important that no security/user specific filters are applied. This is guaranteed by the core related Information Source views and also by other used core views. However, it is important to make sure that this is handled when developing custom specific sources.
Filtering of information with respect to the user must be done in the Tabular models and not when extracting the data.
- If a view that is referenced by a Tabular Data Source does not exist in the <IFSINFO> schema, the load run will give an error. The common error is that necessary Access Views have not been created. Use the information about Access Views to learn how to create required views.
- The default language of the <IFSINFO> user will also be used when it comes to all data that is language dependent. This means that if the <IFSINFO> has language en (English) as the default language, translatable texts will also be transferred as English texts. If the purpose is to view information in an Tabular model in e.g. French, the model can support French object translations (pls see standard SSAS documentation) but the language dependent data will still be in English. This means that the transferred translatable attributes as e.g. descriptions, will only be available in the SQL Server database in one language, e.g. English. Thus, IFS Cloud specific translations in one language can only be transferred via a data load.
Data Load Configuration Handling
Configuration of Analysis Models specific data loads is essential to make sure that the correct data volumes are transferred to the SQL Server database, but also that the loads are executed at a suitable time or interval.
There are three load types to choose from for each Tabular Data Source. If the load type Incremental is supported, a default non-editable
WHERE clause is automatically added. If that record doesn't suit your needs, you will need to go for the Conditional option and enter a suitable
However, to be able to change the
WHERE condition the user must be granted the DEFINE_SQL system privilege.
Security Filtering in SQL Server
The Tabular framework along with the used Information Sources makes sure that data is not filtered on user level when data loading is performed, i.e. that the different Information Sources (and other sources) supply data without considering security filters. In most cases the raw data from IFS Applications should be extracted and defined in the SQL Server database. Filtering of data, based on user access, must be handled in the Tabular models. Currently only the General Ledger Model supports RLS at a company level. Other core models do not currently provide any built-in support for user based filtering, also called Row Level Security.
Row Level Security (RLS) can be handled in at least two ways:
- Use the functionality in SSAS Tabular to define roles and what the roles are allowed to access.
Look up the public SSAS Tabular documentation for more information.
- Build IFS Cloud specific user related RLS into the Tabular model.
- Requires that necessary user security information is transferred from IFS Cloud to the SQL Server table layer. This could be e.g. information related to allowed users per site or company.
- Mappings between IFS Cloud logon users and the internal user identity are required.
- Please contact the BMRA team in IFS R&D to get more information about this subject.