Tag Archives: sap hana

How to give what you don’t have

The development tools and techniques for SAP HANA have been extended and enhanced a couple of times since SAP HANA had first been released in 2011.
For apps developed for SAP HANA XS, the new XSA environment means a considerable change in the way application code accesses the database.

In his post “Synonyms in HANA XS Advanced, Accessing Objects in an External SchemaChristoph Gilde explains in detailed steps how XSA applications can get access to the database schema of XS classic applications. This might not be the loose coupling aimed for with a microservices architecture, but it is an efficient way of building new functionality in XSA while retaining an existing XS classic code base.

Out with the old, in with the new? Not so fast!

One example, where this is being done is the SAP Health application Medical Research Insights 2. Starting with FP 5, several new features have been added as XSA apps, while keeping the base application and the underlying Connected Health Platform as XS classic applications.

Christoph’s post covers all necessary steps to enable such a cross-application data access but relies on a prerequisite that can be hard to fulfil.
The very first step in the tutorial is to create two roles in the XS classic schema that group all required privileges to the XS classic database objects. One of these roles has to include the privileges with the GRANT OPTION that allows granting those privileges further to other users or roles.
It is fairly obvious that this is an extremely wide-ranging privilege that can make controlling who has access to which data and who manages this access very difficult. For this reason, the XS classic repository roles don’t allow the use of the GRANT OPTION.

But Christoph post does not mention anything about repository roles, so what is the problem here, you might ask.

What one can do and what one cannot do

One point is, that having to create roles manually via SQL makes the handling of the application life-cycle more complex. Ideally one wants to have an XS classic application fully contained in one or more DUs that can be transported (installed, imported) together. The less manual installation activities required, the better.

The more important point, though, is that only the owner of an object or a user with the GRANT OPTION privilege can go on and grant other users privileges with the GRANT OPTION.

In case of an application that uses a .hdbschema repository object to define the application schema as recommended, the owner of that schema is the _SYS_REPO user.
This special SAP HANA system user cannot be used to login to the database, so even if one would settle for a manual installation step, there technically is no way to run a GRANT … WITH GRANT OPTION as the _SYS_REPO user.

A way out

So, how can we make _SYS_REPO grant privileges to a user with the GRANT OPTION?
Meet “SQLScript procedure with DEFINER RIGHTS“!
This setting allows executing the procedure as the user that created it.

For repository procedures, this “creator” is always _SYS_REPO.
By now you probably see where this is going.

To have _SYS_REPO grant with GRANT OPTION, we create following repository objects:

  1. external_access_g.hdbrole
    (see Christoph’s post for the two types of roles for external access)
    This one is just an empty role file.
    Don’t get confused by the ‘pred’ prefix in the package name, this is just the name I used for the demo package and without functional relevance

     

    role pred.roles::pred_external_access_g
    {}
    
  2. external_access.hdbrole
    This one contains all access privileges without the GRANT OPTION.
    For the sake of this example, I chose SELECT on the schema.

     

    role pred.roles::pred_external_access
    {
    catalog schema "PRED": SELECT;
    }
    
  3. grantSchemaAccess.hdbprocedure
    PROCEDURE "PRED"."pred.roles::grantSchemaAccess" ( )
    LANGUAGE SQLSCRIPT
    SQL SECURITY DEFINER
    DEFAULT SCHEMA PRED
    AS
    BEGIN
    exec 'GRANT SELECT on schema PRED to "pred.roles::pred_external_access_g" WITH GRANT OPTION';
    END
    

    Important to note here:

    • SQL SECURITY DEFINER – this really is core to this approach. Leave it on the default INVOKER and it won’t work.
    • no READS SQL DATA – we use Dynamic SQL in this procedure which counts as READ/WRITE access.
    • the GRANT statement is executed via ‘exec’.
      If you try to put the GRANT in the procedure body directly, you will get the following error
      message:Error while activating /pred/roles/grantSchemaAccess.hdbprocedure:[pred.roles:grantSchemaAccess.hdbprocedure] Syntax error in procedure object: feature not supported;
      ddl statement 'GRANT SELECT on schema PRED to "pred.roles::pred_external_access_g" WITH GRANT OPTION' is not supported in procedure/function: line 11 col 5 (at pos 288)
      

    (BTW: To whoever decided that it was a good thing to disable selecting text in the console area of the Web-Based Development Workbench Editor: it’s not!
    Being able to copy error message output is rather important and having to manually change the ‘user-select’ option for the CSS style is rather cumbersome )

  4. Another role that covers the privilege to run the procedure
    role pred.roles::installUser
    {
    sql object pred.roles::grantSchemaAccess: //Objecttype: PROCEDURE
    EXECUTE;
    }
    

With these objects in place, the setup procedure for granting privileges looks like this:

  1. Import the DU with the objects created above into the target system.
  2. Grant the installUser role to the user that performs the installation and setup activities (e.g. INSTALLUSER).
  3. As the INSTALLUSER execute the procedure:
    call "PRED"."pred.roles::grantSchemaAccess"();
    
  4. Check that the assignment was successful:
    select *
    from "GRANTED_PRIVILEGES"
    where
    grantee like 'pred_riles::external_access%'
    
    GRANTEE                             GRANTEE_TYPE GRANTOR   OBJECT_TYPE  SCHEMA_NAME  ...  PRIVILEGE IS_GRANTABLE IS_VALID
    pred.roles::pred_external_access    ROLE         _SYS_REPO SCHEMA       PRED         ...  SELECT    FALSE        TRUE
    pred.roles::pred_external_access_g  ROLE         _SYS_REPO SCHEMA       PRED         ...  SELECT    TRUE         TRUE
    
  5. Revoke the role to execute the procedure from the INSTALLUSER again.
    As this is a setup-time-only activity, the role and the privilege should not be granted to anyone once the setup is done.
    To see who the role has been granted to run the following SQL statements:

     

    select *
    from "GRANTED_PRIVILEGES"
    where object_type = 'PROCEDURE'
    and object_name = 'pred.roles::grantSchemaAccess';
    

    –> only the procedure pred.roles::installUser should be returned here.

    select *
    from "GRANTED_ROLES"
    where
    role_name = 'pred.roles::installUser';
    

    –> only _SYS_REPO should be returned here.

Once these steps are successfully finished, the remaining procedure is the same as described in Christoph’s blog post, continuing at step 2.

Due to the critical nature of granting WITH GRANT OPTION one might also consider to set up auditing the roles and the procedure so that any use of them gets logged.

There you go, now you know!

Cheers,
Lars

HANA dude analyses Python error and this happened next… (plus: something with Andy Warhol)

Fresh out of the Christmas/New Year vacation I yesterday received this question from one of my colleagues:

Hi Lars

I am trying to set up a demo to show MDC at FKOM. I am trying to configure MDC via the Python script method – although I have an issue where I am missing a file – the error from the convertMDC.Py script says “no module named ConfigMgr.Py”

I remember you demoed this method at the Architecture Summit in Sydney – did you come across this issue?”

(He was talking about this nice event and the Multi Database Container feature of SAP HANA.)

Now as I was on my way to see the very nice Andy Warhol – Ai Weiwei exhibition currently on display in Melbourne, I couldn’t logon to my test system to look into the issue.

Due to my “root-cause-analysis reflex” from my stint in SAP support, I followed up today and was able to verify my initial hypothesis: there must be a search path setting not quite right.

These are the steps I took:

1. check the convertMDC.py file:

The first two lines in this Python file are


import os, time, sys, subprocess, getopt
import ConfigMgrPy

which is where the error message comes from.

Python is trying to load the listed modules but fails with the ConfigMgr module.

Checking the Python documentation I figured out that there are a couple of places Python looks for the modules.

One option to specify these places is the environment variable PYTHONPATH. Therefore I checked this variable on my system:

2. check PYTHONPATH environment variable:


>echo $PYTHONPATH
  /usr/sap/T07/SYS/global/hdb/custom/python_support:/usr/sap/T07/HDB07/exe/python_support:/usr/sap/T07/HDB07/dewdftzldc05:/usr/sap/T07/HDB07/exe:/usr/sap/T07/HDB07/exe/testscripts

Reformatting this string gave me this list:


/usr/sap/T07/SYS/global/hdb/custom/python_support
/usr/sap/T07/HDB07/exe/python_support
/usr/sap/T07/HDB07/dewdftzldc05
/usr/sap/T07/HDB07/exe
/usr/sap/T07/HDB07/exe/testscripts

Nice, but didn’t really gain a lot of insight here…

3. Checking the path from within Python:

In hindsight this should have been the first thing to do, but hey, I am thinking and learning on my feet here…


> python
Python 2.7.10 (sap:1, Jul  6 2015, 10:21:20)
[GCC 4.7.2 20130108 [gcc-4_7-branch revision 195014]] on linux2
Type "help", "copyright", "credits" or "license" for more information.
Traceback (most recent call last):
File "/etc/pythonstart", line 7, in <module>
import readline
ImportError: No module named readline

I decided to ignore this error message. Not sure why this happens on my system!

From the Python documentation I knew that I need the sys module for further investigation, so I load this and print the current search path via sys.path:


>>> import sys
>>> sys.path
['', '/usr/sap/T07/SYS/global/hdb/custom/python_support', '/usr/sap/T07/HDB07/exe/python_support', '/usr/sap/T07/HDB07/dewdftzldc05', '/usr/sap/T07/HDB07/exe', '/usr/sap/T07/HDB07/exe/testscripts', '/usr/sap/T07/HDB07/exe/Python/lib/python27.zip', '/usr/sap/T07/HDB07/exe/Python/lib/python2.7', '/usr/sap/T07/HDB07/exe/Python/lib/python2.7/plat-linux2', '/usr/sap/T07/HDB07/exe/Python/lib/python2.7/lib-tk', '/usr/sap/T07/HDB07/exe/Python/lib/python2.7/lib-old', '/usr/sap/T07/HDB07/exe/Python/lib/python2.7/lib-dynload', '/usr/sap/T07/HDB07/exe/Python/lib/python2.7/site-packages']
>>>

Ok, so there we find all our PYTHONPATH folders as well as some other folders that seem to belong to the Python installation.

4. Load the missing module

Now I thought: “Hey, lets try and load this module manually!


>>> import ConfigMgr

Ok, no error message so far.

Then the following should work!


>>> dir (ConfigMgrPy)
['CUSTOMER', 'Configuration', 'ConfigurationException', 'HOST', 'LayeredConfiguration', 'READONLY', 'RemoteConfig', '__doc__', '__file__', '__name__', '__package__', 'createUUID', 'expandProfileVars', 'getDaemonInfo', 'getLinkInfo', 'getRootPath', 'sapgparam', 'stringToBool']

Geeze, I am such a great Python hacker… *cough* 😐

But where does the module come from?

Easy as pie:


>>> ConfigMgrPy
<module 'ConfigMgrPy' from '/usr/sap/T07/HDB07/exe/ConfigMgrPy.so'>

5. ALL WRONG – this only works on a system where you DON’T have the problem!

Clearly, my fancy schmanzy root cause analysis would not have helped my colleague, because on his system Python didn’t find the module in the first place.

So what to do in this case?

I’d say, looking for the ConfigMgrPy file or folder or whatever Python loads for modules might be a good idea.

Since there is no hint in the Python documentation that ConfigMgr belongs to the standard Python stuff, I have to assume that it belongs to SAP HANA.

Which limits the search area for the object to the SAP HANA installation folders.

On my system that would be the /usr/sap/<SID>/ folder.

Searching for files on Linux always makes me google for examples and this is what I came up with this time:


find -L /usr/sap/T07 -name 'ConfigMgr*'

So far so good, but what’s the -L for?” you might ask.

When trying this thing out on my system, I didn’t use the -L immediately and found… nothing at all.

Only after trying a couple of name pattern that definitively should have yielded some hits it dawned on me that there might be something else preventing the find program to find the files.

And in fact, find does not follow symbolic links in files systems by default.

Looking into the installation folder on my system I can see that symbolic links are what we use for SAP HANA:


ls -la /usr/sap/T07
total 16
drwxr-xr-x  4 t07adm sapsys 4096 Apr 15  2015 .
drwxr-xr-x 17 root   sapsys 4096 Dec 10 18:30 ..
lrwxrwxrwx  1 t07adm sapsys   22 Apr 15  2015 HDB07 -&gt; /hana/shared/T07/HDB07
[...]

Means: without the -L option, find doesn’t go down this path.

Having figured this out, find of course gave me what I was looking for:


find -L /usr/sap/T07 -name 'ConfigMgr*'
/usr/sap/T07/SYS/exe/hdb/ConfigMgrPy.so
/usr/sap/T07/HDB07/exe/ConfigMgrPy.so

And with this information my colleague could have modified the PYTHONPATH variable on his system to fix the problem (which he wrote me is what he has done meanwhile anyhow…).

There you go – now you know.

Happy New Year everyone and have a great 2016!