Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
olaf_fischer
Employee
Employee

Introduction


This is the second part of the blog post HANA Analytical Privileges in SAP DWC - Part 1.

Based on the configuration created in the first part, this post will describe a concrete solution that implements a role based authorization. This will allow us to assign one or more roles to an application user and to assign a combinations of view name and filter definitions to a role.

All SAP DWC relevant entities are part of the CSN located in the appendix. Among this entities there are three configuration tables, that will be use to define the authorizations and to assign them to the application users.

The blog post has three main sections: It will start with an introduction on how the configuration and assignment is done. This is followed by the implementation part itself and ends with an outlook towards potential enhancements.

Configuration


Based on the configuration of Part 1 - let us start with a small example on how to configure a simple authorization filter once the implementation is done:
Example: We want to protect data in the view VIEW_HANA_AP. A user user@company.com should have the permission to access the product id BX-1011.

First step is to define the filter and provide an id for it:

Here the filter id is "PRODUCT_BX" and comes with a restriction on the single column PRODUCTID.



















FILTER_ID COLUMN_NAME SIGN OPTION LOW HIGH
PRODUCT_BX PRODUCTID I EQ BX-1011

Second step is to combine the filter with a view name and assign it to a role:

The filter PRODUCT_BX is assigned to the view VIEW_HANA_AP. As we might want to use this filter configuration globally, the name of the space has been added to ensure a unique name.













ROLE FILTER_ID OBJECT_ID
TEST_ROLE PRODUCT_BX "INBOUND"."VIEW_HANA_AP"

Third and last step is to assign this role to a user:











USER ROLE
USER@COMPANY.COM TEST_ROLE


A Closer Look


Before we start with the implementation, let us have a closer look at the features and functions that will be available:

Filter Definition


Add filters for several columns. This definition shown in the table below would create the where statement:
"PRODUCTID"='BX-1011' AND "TYPECODE"='PR'

The combinations Including with EQ, CP, IN, BT are supported.



























FILTER_ID COLUMN_NAME SIGN OPTION LOW HIGH
PRODUCT_BX PRODUCTID I EQ BX-1011
PRODUCT_BX TYPECODE I EQ PR

Hint: As the filter definition is independent from view name, it can be re-used and assigned to several views. This could be helpful if you have in several views the same set of columns that are used to define the authorization filter.

Filter Combinations


Two filter definitions for the same view are combined via an OR statement. The statement created here is filter PRODUCT_BX OR filter PRODUCT_BZ.


















ROLE FILTER_ID OBJECT_ID
TEST_ROLE PRODUCT_BX "INBOUND"."VIEW_HANA_AP"
TEST_ROLE PRODUCT_BZ "INBOUND"."VIEW_HANA_AP"

This is the resulting where statement
"PRODUCTID"='BX-1011' AND "TYPECODE"='PR'

OR

"PRODUCTID"='BZ-1011' AND "TYPECODE"='PR'

Hint: Combining filters with an OR enable the definition of independent sets of data, that are permitted for the user.

No Filter Found


In case a user requests data and the implementation can't find a filter criteria, the access will be blocked by returning a where statement which is always false ( 1=2 ). In other word: on a protected view the default authorization is "no data access permitted".

Implementation


Configuration in SAP DWC


Import the attached CSN-file (see Appendix) into the space INBOUND. It contains the configuration tables sketched above and views to combine them into the where-statement for the authorization filter

Add the sample values from the introduction to the following tables. For testing, using the Data Editor of the table is best. Larger amounts of configuration data can be upload from a local file.


  • MAPPING_USER_TO_ROLE

  • MAPPING_ROLE_TO_FILTER_ID

  • MAPPING_FILTER_ID_TO_FILTER


Testing of Configuration

  • Open the data preview of view MAPPING_USER_TO_FILTER. Here you should see all user with their assigned filter ids and view names. In addition, there is a column containing the fragement of the where statement representing the filter.

  • Open the data preview of view GET_WHERE_CLAUSE. It will prompt you for a user name and a view name. Fill in the sample values and see the complete where statement in the response. This view is helpful to verify your configuration and check, if the where statement is as expected.
    Note: This view is not shared as it offers access to authorized values for every user. Only for support purposes.

  • Open the data preview of view GET_WHERE_CLAUSE_CURRENT_USER. Here you will see the where statement 1=2, as your current user has a different name than USER@COMPANY.COM. This is the test to check the default behavior.

  • Assign the role TEST_ROLE to your user ( add a row to table MAPPING_USER_TO_ROLE ). Ensure that write your name in upper case. Re-runt he previous step and you should see a proper where-statement.
    Hint: If you prefer lower case, please adjust the view
    GET_WHERE_CLAUSE_CURRENT_USER accordingly.


Well done, we have now everything in place. Let us continue and adjust the definition of the Analytical Privilege to use our new configuration.

Configuration in DB Explorer


Open the DB Explorer and open an SQL console with the user INBOUND#FILTER.

Run the following statement:
CREATE or replace procedure APP_USER_DEPENDENT_FILTER_VIEW_HANA (OUT OUT_FILTER VARCHAR(5000))
LANGUAGE SQLSCRIPT SQL SECURITY DEFINER READS SQL DATA AS
BEGIN
-- adjust the view name to your needs
declare view_name varChar(100) := '"INBOUND"."VIEW_HANA_AP"';

-- the select always returns exactly one record
declare lt_return table ( "WHERE_STATEMENT" varChar(5000) );
lt_return = select top 1 "WHERE_STATEMENT"
from "INBOUND".GET_WHERE_CLAUSE_CURRENT_USER(view_name => :view_name);
OUT_FILTER = :lt_return.where_statement[1];
END;

In part 1 of this blog post, we have returned static values. Now we derive the where statement dynamically by calling the view GET_WHERE_CLAUSE_CURRENT_USER.

Note that we have defined the view name as part of the procedure. If you followed that blog post 1:1, there is no need to adjust. If you apply the blog post on a different view, please adjust the view name here.

End-2-End Testing


Back to SAP DWC, open the data preview on table VIEW_DATA_AP. It should now return the subset of data defined in your configuration. Perform that same steps on the shared view VIEW_CONSUMPTION and in your analytical model respective SAC Story.

Congratulations, the sample implementation is up and running.

From Sample to Project


The authorization configuration doesn't change - just add additional user, roles, filters etc. to the configuration tables. There is no need to add additional entities.

This is different for the Analytical Privileges and HANA views. To enable the Analytical Privileges for a given data view, we have to create a set of HANA entities like the protected view, procedure for retrieving the where-statement, analytical privileges and select grants and more.

Below you find the step-by-step approach. I would recommend to bundle all steps into a single procedure PROTECT_VIEW with the parameter "View Name" and if relevant, the users for granting select rights.

Once done, import the protected view into SAP DWC (see Part 1 of the blog post) and share the table with the CONSUMPTION space.

Step-by-Step: Protect a View


Insert your view name and run the script below:
create or replace view View_DATA_AP
as select * from <your custom view name>;
with STRUCTURED PRIVILEGE CHECK;

Also we need to create a new procedure for retrieving the where statement. At runtime we can't determine the view name for which we are called. This we workaround by a view specific procedure that we assign to the analytical privilege and which contains the static view name. In the script sample below, replace the <> with your view name.
CREATE or replace procedure APP_USER_DEPENDENT_FILTER_<view_name> (OUT OUT_FILTER VARCHAR(5000))
LANGUAGE SQLSCRIPT SQL SECURITY DEFINER READS SQL DATA AS
BEGIN
-- adjust the view name to your needs
declare view_name varChar(100) := '<your custom view name>';

-- the select always returns exactly one record
declare lt_return table ( "WHERE_STATEMENT" varChar(5000) );
lt_return = select top 1 "WHERE_STATEMENT"
from "INBOUND".GET_WHERE_CLAUSE_CURRENT_USER(view_name => :view_name);
OUT_FILTER = :lt_return.where_statement[1];
END;

Once done, we need to create the Analytical Privilege - again we need unique names. Replace the <view_name> with your view name.
create structured privilege View_HANA_Analytical_Priv_<view_name>
for select
on <view_name>
CONDITION PROVIDER APP_USER_DEPENDENT_FILTER_<view_name>;

And last but not least, we need to grant the access to our space user for INBOUND and CONSUMPTION and for the Ina/MDS user in space CONSUMPTION:
GRANT STRUCTURED PRIVILEGE View_HANA_Analytical_Priv_<view_name> TO INBOUND;
GRANT STRUCTURED PRIVILEGE View_HANA_Analytical_Priv_<view_name> TO CONSUMPTION;
GRANT STRUCTURED PRIVILEGE View_HANA_Analytical_Priv_<view_name> TO CONSUMPTION#INA<UID>;

Conclusion


Now that we have a central place to maintain the analytical privileges cross users and views and know how to onboard additional views, what is next?

When using the solution described you might find limitations or missing functionality. Here is a list of known topics that you might want to enhance:

  • Additional selection options: In addition to the implemented including option EQ, BT, CP, IN you might want to add the excluding option or introduce a variable for the e.g. LOW value.
    => see view MAPPING_USER_TO_FILTER

  • The current mapping from configuration to Where-Statement assumes one criteria per dimension. Example: If you define a filter for three single values, you can't add three times an EQ - but have to use the IN option.
    => adjust the table MAPPING_FILTER_ID_TO_FILTER and the view GET_WHERE_CLAUSE

  • Integration with SAP BW: SAP BW/4 HANA offers an export of authorizations (see this blog post). The table RSDWC_RSEC_DAC contains the result of the authorization export. It comes with a where-statement describing the authorization and could be attached to the implementation described in this blog. Note that it could use the where-statement as-is.


I’d be happy to hear your thoughts, ideas and comments on this blog post. Let me know in the comments!

APPENDIX


The CSN File containing the table and view definitions.
Select and copy the whole line, e.g. via shift-cursor down or triple click.
{  "definitions": {    "GET_WHERE_CLAUSE_CURRENT_USER": {      "kind": "entity",      "elements": {        "USER": {          "@EndUserText.label": "User",          "type": "cds.String",          "length": 100,          "key": true,          "notNull": true        },        "OBJECT_ID": {          "@EndUserText.label": "Object Id",          "type": "cds.String",          "length": 100,          "key": true,          "notNull": true        },        "WHERE_STATEMENT": {          "@EndUserText.label": "WHERE_STATEMENT",          "type": "cds.String",          "length": 5000        }      },      "@EndUserText.label": "GET_WHERE_CLAUSE_CURRENT_USER",      "@ObjectModel.modelingPattern": {        "#": "DATA_STRUCTURE"      },      "@ObjectModel.supportedCapabilities": [        {          "#": "DATA_STRUCTURE"        }      ],      "@Analytics.dbViewType": "TABLE_FUNCTION",      "params": {        "VIEW_NAME": {          "@EndUserText.label": "View Name",          "type": "cds.String",          "length": 100,          "default": "\"INBOUND\".\"VIEW_HANA_AP\""        }      },      "@DataWarehouse.consumption.external": true,      "@DataWarehouse.tableFunction.script": "\ndeclare currentUser varchar(100) := upper(SESSION_CONTEXT('XS_APPLICATIONUSER'));\n\nresult = select \"USER\",\"OBJECT_ID\",\"WHERE_STATEMENT\"  \n         from get_where_clause( user_name => :currentUser, view_name => :VIEW_NAME);\n\nreturn :result;"    },    "MAPPING_ROLE_TO_FILTER_ID": {      "kind": "entity",      "@EndUserText.label": "MAPPING_ROLE_TO_FILTER_ID",      "@ObjectModel.modelingPattern": {        "#": "DATA_STRUCTURE"      },      "@ObjectModel.supportedCapabilities": [        {          "#": "DATA_STRUCTURE"        }      ],      "elements": {        "ROLE": {          "@EndUserText.label": "Role",          "type": "cds.String",          "length": 100,          "key": true,          "notNull": true        },        "OBJECT_ID": {          "@EndUserText.label": "Object Id",          "type": "cds.String",          "length": 100,          "key": true,          "notNull": true        },        "FILTER_ID": {          "@EndUserText.label": "Filter Id",          "type": "cds.String",          "length": 100,          "key": true,          "notNull": true        }      }    },    "MAPPING_FILTER_ID_TO_FILTER": {      "kind": "entity",      "@EndUserText.label": "MAPPING_FILTER_ID_TO_FILTER",      "@ObjectModel.modelingPattern": {        "#": "DATA_STRUCTURE"      },      "@ObjectModel.supportedCapabilities": [        {          "#": "DATA_STRUCTURE"        }      ],      "elements": {        "FILTER_ID": {          "@EndUserText.label": "Filter Id",          "type": "cds.String",          "length": 100,          "key": true,          "notNull": true        },        "COLUMN_NAME": {          "@EndUserText.label": "Column Name",          "type": "cds.String",          "length": 100,          "key": true,          "notNull": true        },        "SIGN": {          "@EndUserText.label": "Sign",          "type": "cds.String",          "length": 100,          "notNull": true        },        "OPTION": {          "@EndUserText.label": "Option",          "type": "cds.String",          "length": 100,          "notNull": true        },        "LOW": {          "@EndUserText.label": "Low",          "type": "cds.String",          "length": 100,          "notNull": true        },        "HIGH": {          "@EndUserText.label": "High",          "type": "cds.String",          "length": 100        }      }    },    "MAPPING_USER_TO_ROLE": {      "kind": "entity",      "@EndUserText.label": "MAPPING_USER_TO_ROLE",      "@ObjectModel.modelingPattern": {        "#": "DATA_STRUCTURE"      },      "@ObjectModel.supportedCapabilities": [        {          "#": "DATA_STRUCTURE"        }      ],      "elements": {        "USER": {          "@EndUserText.label": "User",          "type": "cds.String",          "length": 100,          "key": true,          "notNull": true        },        "ROLE": {          "@EndUserText.label": "Role",          "type": "cds.String",          "length": 100,          "key": true,          "notNull": true        }      }    },    "MAPPING_USER_TO_FILTER": {      "kind": "entity",      "elements": {        "USER": {          "@EndUserText.label": "User",          "type": "cds.String",          "length": 100,          "key": true,          "notNull": true        },        "OBJECT_ID": {          "@EndUserText.label": "Object Id",          "type": "cds.String",          "length": 100,          "key": true,          "notNull": true        },        "FILTER_ID": {          "@EndUserText.label": "Filter Id",          "type": "cds.String",          "length": 100,          "key": true,          "notNull": true        },        "WHERE_STATEMENT": {          "@EndUserText.label": "WHERE_STATEMENT",          "type": "cds.String",          "length": 100        }      },      "query": {        "SELECT": {          "from": {            "join": "inner",            "args": [              {                "join": "inner",                "args": [                  {                    "ref": [                      "MAPPING_USER_TO_ROLE"                    ]                  },                  {                    "ref": [                      "MAPPING_ROLE_TO_FILTER_ID"                    ]                  }                ],                "on": [                  {                    "ref": [                      "MAPPING_USER_TO_ROLE",                      "ROLE"                    ]                  },                  "=",                  {                    "ref": [                      "MAPPING_ROLE_TO_FILTER_ID",                      "ROLE"                    ]                  }                ]              },              {                "ref": [                  "MAPPING_FILTER_ID_TO_FILTER"                ]              }            ],            "on": [              {                "ref": [                  "MAPPING_ROLE_TO_FILTER_ID",                  "FILTER_ID"                ]              },              "=",              {                "ref": [                  "MAPPING_FILTER_ID_TO_FILTER",                  "FILTER_ID"                ]              }            ]          },          "columns": [            {              "ref": [                "MAPPING_USER_TO_ROLE",                "USER"              ]            },            {              "ref": [                "MAPPING_ROLE_TO_FILTER_ID",                "OBJECT_ID"              ]            },            {              "ref": [                "MAPPING_ROLE_TO_FILTER_ID",                "FILTER_ID"              ]            },            {              "xpr": [                "case",                "when",                {                  "ref": [                    "OPTION"                  ]                },                "=",                {                  "val": "EQ"                },                "and",                {                  "ref": [                    "SIGN"                  ]                },                "=",                {                  "val": "I"                },                "then",                {                  "val": "\""                },                "||",                {                  "ref": [                    "COLUMN_NAME"                  ]                },                "||",                {                  "val": "\""                },                "||",                {                  "val": " = "                },                "||",                {                  "val": "'"                },                "||",                {                  "ref": [                    "LOW"                  ]                },                "||",                {                  "val": "'"                },                "when",                {                  "ref": [                    "OPTION"                  ]                },                "=",                {                  "val": "CP"                },                "and",                {                  "ref": [                    "SIGN"                  ]                },                "=",                {                  "val": "I"                },                "then",                {                  "val": "\""                },                "||",                {                  "ref": [                    "COLUMN_NAME"                  ]                },                "||",                {                  "val": "\" CP '"                },                "||",                {                  "ref": [                    "LOW"                  ]                },                "||",                {                  "val": "'"                },                "when",                {                  "ref": [                    "OPTION"                  ]                },                "=",                {                  "val": "BT"                },                "and",                {                  "ref": [                    "SIGN"                  ]                },                "=",                {                  "val": "I"                },                "then",                {                  "val": "\""                },                "||",                {                  "ref": [                    "COLUMN_NAME"                  ]                },                "||",                {                  "val": "\" BETWEEN '"                },                "||",                {                  "ref": [                    "LOW"                  ]                },                "||",                {                  "val": "' AND '"                },                "||",                {                  "ref": [                    "HIGH"                  ]                },                "||",                {                  "val": "'"                },                "when",                {                  "ref": [                    "OPTION"                  ]                },                "=",                {                  "val": "IN"                },                "and",                {                  "ref": [                    "SIGN"                  ]                },                "=",                {                  "val": "I"                },                "then",                {                  "val": "\""                },                "||",                {                  "ref": [                    "COLUMN_NAME"                  ]                },                "||",                {                  "val": "\" IN ("                },                "||",                {                  "ref": [                    "LOW"                  ]                },                "||",                {                  "val": ")"                },                "else",                {                  "val": "UNSUPPORTED: SIGN: "                },                "||",                {                  "ref": [                    "SIGN"                  ]                },                "||",                {                  "val": " OPTION: "                },                "||",                {                  "ref": [                    "OPTION"                  ]                },                "end"              ],              "as": "WHERE_STATEMENT"            }          ]        }      },      "@EndUserText.label": "MAPPING_USER_TO_FILTER",      "@ObjectModel.modelingPattern": {        "#": "DATA_STRUCTURE"      },      "@ObjectModel.supportedCapabilities": [        {          "#": "DATA_STRUCTURE"        }      ],      "@DataWarehouse.consumption.external": false,      "@DataWarehouse.sqlEditor.query": "select \n    \"MAPPING_USER_TO_ROLE\".\"USER\",\n // \"MAPPING_USER_TO_ROLE\".\"ROLE\", \n    \"MAPPING_ROLE_TO_FILTER_ID\".\"OBJECT_ID\",\n    \"MAPPING_ROLE_TO_FILTER_ID\".\"FILTER_ID\",\n // \"MAPPING_FILTER_ID_TO_FILTER\".\"COLUMN_NAME\",\n // \"MAPPING_FILTER_ID_TO_FILTER\".\"SIGN\",\n // \"MAPPING_FILTER_ID_TO_FILTER\".\"OPTION\",\n // \"MAPPING_FILTER_ID_TO_FILTER\".\"LOW\",\n // \"MAPPING_FILTER_ID_TO_FILTER\".\"HIGH\"\n \n    CASE\n        WHEN \"OPTION\" = 'EQ' AND \"SIGN\" = 'I' \n            THEN '\"' || \"COLUMN_NAME\" || '\"' || ' = ' || '''' || \"LOW\" || ''''\n        WHEN \"OPTION\" = 'CP' AND \"SIGN\" = 'I' \n            THEN '\"' || \"COLUMN_NAME\" || '\" CP ''' || \"LOW\" || ''''\n        WHEN \"OPTION\" = 'BT' AND \"SIGN\" = 'I' \n            THEN '\"' || \"COLUMN_NAME\" || '\" BETWEEN ''' || \"LOW\" || ''' AND ''' || \"HIGH\" || ''''           \n        WHEN \"OPTION\" = 'IN' AND \"SIGN\" = 'I' \n            THEN '\"' || \"COLUMN_NAME\" || '\" IN (' || \"LOW\" || ')'\n        ELSE 'UNSUPPORTED: SIGN: ' || \"SIGN\" || ' OPTION: ' || \"OPTION\"\n    END AS WHERE_STATEMENT\n    \nfrom \"MAPPING_USER_TO_ROLE\"\n\ninner join \"MAPPING_ROLE_TO_FILTER_ID\" \n    on \"MAPPING_USER_TO_ROLE\".\"ROLE\" = \"MAPPING_ROLE_TO_FILTER_ID\".\"ROLE\"\n\ninner join \"MAPPING_FILTER_ID_TO_FILTER\" \n    on \"MAPPING_ROLE_TO_FILTER_ID\".\"FILTER_ID\" = \"MAPPING_FILTER_ID_TO_FILTER\".\"FILTER_ID\" "    },    "GET_WHERE_CLAUSE": {      "kind": "entity",      "elements": {        "USER": {          "@EndUserText.label": "User",          "type": "cds.String",          "length": 100,          "key": true,          "notNull": true        },        "OBJECT_ID": {          "@EndUserText.label": "Object Id",          "type": "cds.String",          "length": 100,          "key": true,          "notNull": true        },        "WHERE_STATEMENT": {          "@EndUserText.label": "WHERE_STATEMENT",          "type": "cds.String",          "length": 5000        }      },      "@EndUserText.label": "GET_WHERE_CLAUSE",      "@ObjectModel.modelingPattern": {        "#": "DATA_STRUCTURE"      },      "@ObjectModel.supportedCapabilities": [        {          "#": "DATA_STRUCTURE"        }      ],      "@Analytics.dbViewType": "TABLE_FUNCTION",      "params": {        "USER_NAME": {          "@EndUserText.label": "User Name",          "type": "cds.String",          "length": 100        },        "VIEW_NAME": {          "@EndUserText.label": "View Name",          "type": "cds.String",          "length": 100,          "default": "\"INBOUND\".\"VIEW_HANA_AP\""        }      },      "@DataWarehouse.consumption.external": false,      "@DataWarehouse.tableFunction.script": "declare count_items integer;\ndeclare counter             integer;\ndeclare filter_full         varchar(10000)   := '';\ndeclare filter_string       varchar(1000)    := '';\ndeclare current_item_name   varchar(50)      := '';\n\ndeclare lt_return table (\n\t\t\"USER\" varChar(100),\n\t\t\"OBJECT_ID\" varChar(100),\n\t\t\"WHERE_STATEMENT\" varChar(5000)\n\t\t);\n\ndeclare appl_user varchar(100) := :USER_NAME;\ndeclare object_id varchar(100) := :VIEW_NAME;\n\nlt_filter_items = select \"USER\",\"OBJECT_ID\",\"FILTER_ID\",\"WHERE_STATEMENT\"  \n    from \"MAPPING_USER_TO_FILTER\"\n    where user = :appl_user\n      and object_id = :object_id;\n\ncount_items = RECORD_COUNT(:lt_filter_items);\n\nif count_items = 0 then\n        -- if no filter is found, no data should be exposed\n    \tfilter_full = '1=2';\n    \tinsert into :lt_return values (:user_name, :object_id, :filter_full);\n    else\n\n    /* \n        Boundle filter items into a full effective where statement\n        Structure: where ( Dim1=A AND Dim2=B ) OR ( Dim3=C AND Dim4=D )\n    */\n        current_item_name = :lt_filter_items.filter_id[1];\n        \n        for counter in 1..count_items do\n        \n            filter_string = :lt_filter_items.where_statement[counter];\n        \n            if counter = 1 then \n                filter_full = concat('(', filter_string);\n            else\n                if current_item_name = :lt_filter_items.filter_id[counter] then \n                    filter_full = concat(concat(:filter_full, ' AND '), filter_string);\n                else\n                    filter_full = concat(concat(:filter_full, ') OR ('), filter_string);\n                    current_item_name = :lt_filter_items.filter_id[counter];\n                end if;\n            end if;\n        \n            if counter = count_items then \n                filter_full = concat( :filter_full,')');\n            end if;\n            \n        end for;\n        \n        insert into :lt_return values (:user_name, :object_id, :filter_full);\n        \n    end if;\n\nreturn :lt_return;"    }  },  "version": {    "csn": "1.0"  },  "meta": {    "creator": "CDS Compiler v1.19.2"  },  "$version": "1.0"}