Security and User Access

This document describes ways to secure usage of Profitbase Studio including

 

Profitbase Studio and MS SQL Server Access

Profibase Studio Client

Profitbase Studio (PS) can use Integrated Security (the windows user), or a specific SQL Server user. Either way the user needs access to databases and other resources as described here. Use the MS SQL Management Studio to add and manage users and roles.

You could add the ‘sysadmin’ server role, but this will give full control and is only advisable in an environment where users and systems are under control and/or additional security measures are used.

The first administrator that install and use PS to create Server, Solution and Data warehouse databases should have this role.

Example 1 – Read Only User

A user that needs to open Solutions and view content and resulting data warehouse (DW) output, needs the following settings:

  1. Adding an SQL user ‘ReadOnlyUser’ with ‘public’ ticked off in the Server Roles list.
  2. In User Mappings, tick off the ‘db_datareader’ role and use ‘dbo’ default schema for each of the following databases:
    • Solution database (pbSol_nn)
    • Data warehouse database (pbDw_nn)
    • PBMSSRVDBv6 database (the server database, configurable name if multiple PB Servers are used).
    • msdb (Microsoft system database).
  3. If the user also needs access to view schedules etc., also tick off then SQLAgentReader role for the msdb database.

If the User opens PS, he can view definitions and browse results. But if he tries to save or process, he will be denied and get errors.

Example 2 - Regular User

A user that can open solutions, edit content and process it in the DW, needs following settings:

  1. Adding an SQL user ‘RegularUser’ with ‘public’ ticked off in the Server Roles list.
  2. In User Mappings, tick off the ‘db_owner’ role,  and use ‘dbo’ default schema for each of the following databases:
    • Solution database (pbSol_nn)
    • Data warehouse database (pbDw_nn)
  3. In User Mappings, tick off the ‘db_datareader’ role and use ‘dbo’ default schema for the PBMSSRVDBv6 database.
  4. In User Mappings, tick off the ‘SQLAgentOperatorRole’ role and use ‘dbo’ default schema for the msdb database.

This user can not add new SQL Agent Jobs with schedules. Check the MS SQL documentation to see required role(s) for doing this.

Data sources 

In addition access to Source systems (other servers, databases and files etc.) needs to be in place. The Data Source connection settings allow for specifying the username/password used to connect to the source system.

In order to execute Integration Services (SSIS) packages the user as a minimum need to be a member of the db_ssisoperator database role in the msdb database. This role membership is adequate when Studio only executes existing SSIS packages. The db_ssisltduser is the minimum required role membership in the msdb database if Studio needs to rebuild the package each time it is executed. See MSDN site for details.
Also when executing SSIS packages from the Profitbase Studio client, depending on the Operating System, the user may have to "Run As Administrator" to avoid getting an "Access is denied" error message. 

When using Windows integrated security in a multi-server environment (Studio on one machine, solution database on an other machine and source database on a third machine) one must set up appropriate trust relationships beforehand. (Kerberos SQL Server double hop issue may result in "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'")

Requirements connecting to Oracle sources here: Oracle Source 
Requirements connecting to IBM DB2 (iSeries AS400) sources here: IBM DB2 Source
Requirements connecting to Excel sources here: Excel Source

Access Control in Profitbase Studio

Profitbase Studio (PS) comes with role based access control that can be activated per solution.

To start using this, select Solution Management section in the navigator at left and select Access Control.

The first to do is to add a Role. A role can have one or more users and a set of permissions. Permissions are based on the Solution structure as viewed in the navigator. Permissions include:

  • Create – allow new content to be added
  • Read – allow content to be viewed.
  • Write – allow editing of content.
  • Delete – allow deletion of content.
  • Execute – allow processing of content (DW and SSAS).

Permission settings are inherited from parents, e.g. set Read permission on the Solution and this permission is then used throughout the Solution content. You can then add different roles with different users and permission. If a user is a member of more than one role, a combination of the permissions is used.

Users and User Groups can be imported from Active Directory. Users can also be added manually. The User/Group list is shared between Roles so ensure that the right users are ticked off. If the Active Directory is large, use filters to limit user/group request. There are size limits here.

When Roles with Permissions and User/Groups is completed, you must activate this. Ensure that ‘Activate Access Control’ is checked.