Your organization has been using SAP Analytics Cloud (SAC) for a significant period, where you implemented row-level security by defining roles in SAC and applying additional filters to the models. The security framework in SAC was functioning seamlessly. However, your organization has now decided to transition to SAP Datasphere. In SAP Datasphere, row-level security is implemented using permission tables and “Data Access Controls” (DACs).
There are many blogs about how DataSphere security works. This blog explores effective methods to simplify the management of users, roles via creation of a role configuration table to streamline updates in the permission tables. It provides a practical approach to managing security in SAP Datasphere analytical models. It assumes that you have somewhat implemented row-level security in Datasphere and are looking to effectively manage users/roles in easier way. This blog introduces a straightforward solution to replicate and enhance the security mechanisms previously used in SAC within the Datasphere environment.
SAP Datasphere implements row-level security using permission tables and data access controls (DAC), which works seamlessly when there is a single permission table for all users. However, if your requirement is complex, managing multiple permission tables can quickly become a maintenance challenge, as administrators must ensure that entries for each user exist in every permission table.
In this blog, we will utilize the DataSphere Sample Sales Fact dataset to demonstrate how to implement security on two specific columns: Product Category ID from the Product Dimension and SalesOrg in the Fact Table. The implementation will leverage two permission tables while maintaining a flexible design that can scale to support security across multiple tables. The main objective is to illustrate how entries in multiple permission tables can easily managed, by dynamically populating tables through a central permission configuration table and a user-to-role mapping table.
After implementing the necessary security measures, this is how column-based security appears.
1. First Method:
This method requires entering details for each user in each permission table (if you have complex security requirements). As the permission administrator adds data, they must ensure that accurate entries are added into the permission tables. If modifications are needed later, the process can become complex.
2. Second Method:
This method involves using an Users table and a Role Configuration table to load data into the permission table with the help of data flows:
– Users Table:
The structure of the Users table is designed to store the user IDs of all users who have access to SAC, along with the roles they require. The permission administrator only needs to add entries for new users in this table.
The Role Configuration table defines details for each role, including criteria, logic, and information for the first and second columns. This setup is done once for each role.
To load data into each permission table, task chains and data flows are used:
– Data flows join the data from the Users table and the Role Configuration table via a view, then load the results into the permission table.
Here is the SQL code used for the view
SELECT ROW_NUMBER() OVER (PARTITION BY PermissionTableName ORDER BY UserName DESC) AS Permission_ID,
“UserName” AS User_ID, “Restriction”, “Criterion”, “Operator”,
“First_Value”, “Second_Value” FROM “Users” U
JOIN “RoleConfig” R ON U.PRDCT_CTGRY_Roles = R.RoleName
WHERE PermissionTableName = ‘PERMISSION_PRDCT_CTGRY’
– Separate data flows are created to load the data in each permission table by running above SQL.
– Task chains link all the data flows together, so when a new user is added to the Users table, the administrator can run the task chain to update the permission table.
This design simplifies the job of the permission administrator and ensures all permissions are maintained in an organized and easily manageable structure.