Skip to Content

As the Beasty Boys once pointed out “You gotta fight for your right (to party)”. Well if you want to party with HANA views and tables it helps to know how to handle privileges.

Problem 1: Data Preview Error after successful activation

Assume that you are a user that imported some data into some schema (e.g. SFLIGHT). You can create (attribute, analytic, calculation) views for this schema. The activation of these views work fine. When trying to access the data (e.g. Data Preview), you get the following error:

Error Executing Query: SAP DBTech JDBC: [391] (at 106): invalidated view: <YourSchema>/<YourView>: line 1 col 107 (at pos 106)

Solution:

The user owning the schema must give the _SYS_REPO user the privileges WITH GRANT OPTION. Thus as the respective schema owner you must execute

GRANT SELECT ON SCHEMA <YourSchema> TO _SYS_REPO WITH GRANT OPTION

All views in the repository belong to _SYS_REPO by definition. Thus even if you own the schema, the user _SYS_REPO will own the view. In order to process the underlying objects, the user _SYS_REPO needs to pass the privileges onto the respective user.

Problem 2: Repository content does not show up after successful activation

Once you activate checked-in content this content should show up in the catalog. However you may notice that you can push F5 (refresh) forever and nothing shows up.

Solution:

This is because activation is processed by the _SYS_REPO user. Consequently the resulting catalog entities will belong to _SYS_REPO and not to your user. Thus you need to grant yourself the privileges to see your content. You can do this by calling stored procedures that will grant you the desired privileges.

call _SYS_REPO.GRANT_SCHEMA_PRIVILEGE_ON_ACTIVATED_CONTENT(‘create any’,'”your_schema”‘,’your_username’);

call _SYS_REPO.GRANT_PRIVILEGE_ON_ACTIVATED_CONTENT(‘select’, ‘”your_table”‘, ‘your_user’);

Please note the sequence of the quoation marks. First a single quotation mark ( ‘ ), then a double quotation mark ( ” ). This allows for mixed case schema and table names. If you only use single quotation marks, your schema and table name must be in upper case only.

You have to execute these calls from the SQL Editor. For all the details you may want to consult the hana security guide.

To report this post you need to login first.

12 Comments

You must be Logged on to comment or reply to a post.

  1. Bill Ramos

    Hi Jens,

        It seems strange that you need to add the WITH GRANT OPTION to _SYS_REPO. I get having to GRANT SELECT permissions to get things to work, but adding WITH GRANT means that the _SYS_REPO user can GRANT permissions to other users. In general, the WITH GRANT option should be used sparingly in shops where you need to grant the minimum number of privileged to users.A rouge piece of code that ran under the _SYS_REPO account could grant SELECT permissions to other users without you knowing – very bad. Can you do a quick check to see if you can get the scenario to work without the WITH GRANT OPTION?

    Thanks,

    Bill

    (0) 
    1. Jens Odenheimer Post author

      Hi Bill,

      no, it does not work wihout the GRANT OPTION (I even really checked it). The reason is that all views that were created in the repository belong to _SYS_REPO, even if they were modeled with another user. Hence you need to pass the option.

      Cheers,

      Jens

      (0) 
    2. Lars Breddemann

      Hi Bill,

      Jens is right about the WITH GRANT option.

      In fact, this is key to the whole permission concept implemented into the activation process of the repository.

      Active objects are created by the virtual user _SYS_REPO (it’s not possible to logon to _SYS_REPO) into schema _SYS_BIC.

      These active objects (calculation views, analytic views etc.) need to have access to their base objects and they must be made accessible to the user who creates them.

      So, what _SYS_REPO does is, it grants access to the created runtime objects to the creator of the models and this implicitly requires to re-grant access to the base objects as well.

      These permissions are not hidden, so they can easily be found in the respective system views.

      As the repository is the only active part in HANA that could assume the security context of _SYS_REPO this is not a security issue.

      – Lars

      (0) 
      1. Bill Ramos

        Hi Jen & Lars,

            Thanks for the clarification on the WITH GRANT OPTION. Just seemed kind of strange – but I guess that’s why the blog post 🙂 .

        Regards,

        Bill

        (0) 
  2. srihariram chendamarai

    Hi Jens,

    When i execute for second Query “CALL _SYS_REPO.GRANT_SCHEMA_PRIVILEGE_ON_ACTIVATED_CONTENT” it throws a error

    insufficient privilege: Not authorized. so what are all ROLES should i need to execute this query. currently i have PUBLIC and CONTENT_ADMIN.

    (0) 
        1. Jens Odenheimer Post author

          Hi,

          no. The workspace and project concepts only exist within the studio (i.e. locally on your PC). In order to migrate objects from one system to the other (e.g. DEV->QA), you need to use the delivery unit concept. The delivery units may then be exported/imported from/to different systems.

          I have briefly described how to assign delivery units in another article:

          http://scn.sap.com/community/developer-center/hana/blog/2012/12/21/hana-development-xs-odata-services

          Cheers,

          Jens

          (0) 

Leave a Reply