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 Schema"
christoph.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:
- 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
{}
- 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;
}
- 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';
ENDImportant to note here:
(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 )
- 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:
- Import the DU with the objects created above into the target system.
- Grant the installUser role to the user that performs the installation and setup activities (e.g. INSTALLUSER).
- As the INSTALLUSER execute the procedure:
call "PRED"."pred.roles::grantSchemaAccess"();
- 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
- 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
This blog post was first published on LBREDDEMANN.ORG
