Manage XS Advanced via SQL

Introduction

The main tools for XS Advanced administration are the XS Command-Line Interface and the XS Advanced Cockpit.

For both of them, the prerequisites are to have an XS Advanced user with required role collections and also to be member of the organization/space you want to manage.

If you are using XSA only in the context of Data modeling (HDI Advanced Model) via WebIDE for HANA, you may face the situation that developers have XS Advanced users in development system but only basic database users in quality and production systems. For security reason, you may not want to provide them access to XSA Cockpit in Quality and Production systems.

The issue is that without such access, developers are missing some useful information to maintain their applications. For example, they can not look at application logs that would be useful to troubleshoot deployment issue. Another use case is that they can not check the version number of currently deployed applications. This is specifically useful in case of dependencies with several external HDI containers.

As you may face similar issues, I wanted to share in this blog the solution I developed in my company. The solution I found was to develop some custom SQL wrappers on top XS Client tool.

In the first chapter, I will describe you the steps to enable calling XS commands from SQL. Then, I will share two examples of such SQL wrapper procedures : one for getting application logs and the second one for getting application version number.

Enable calling XS commands from SQL

The solution relies on SDI File Adapter. It allows calling shell script via virtual procedure. Also, the idea was to install the XS CLI on the Data Provisioning Agent server.

For the .sh file, I have created a very generic one so that I can call whatever command via script parameters:

#!/bin/sh

/usr/sap/DHL/hdbxsclient/bin/xs login -a $1 -u $2 -p $3 -o $4 -s $5 --skip-ssl-validation  > /dev/null

shift 5

eval  "/usr/sap/DHL/hdbxsclient/bin/$@"

/usr/sap/DHL/hdbxsclient/bin/xs logout  > /dev/null

For security reason, I didn’t want to store the XS user credentials in the sh file. The option I took was to store those credentials on an HANA table, use  Data Masking to hide the password and put the related HDI container in a separated space. This way, just the object owner of the HDI container where is the table has UNMASK privilege and just Administrators of the space could grant access. In case you would have idea on how to secure even better access to the table, do not hesitate to share in comments.

COLUMN TABLE "hdi_api::xsaco" 

  ("API_URL" VARCHAR(1024),

"USERNAME" VARCHAR(1024),

"PASSWORD" VARCHAR(1024),

"ORGANIZATION" VARCHAR(1024),

"SPACE" VARCHAR(1024))

WITH MASK ("PASSWORD" USING '**********')

UNLOAD PRIORITY 5 AUTO MERGE 

The virtual procedure looks like the following. Note that I have extended the size of the output parameter to the maximum authorized by the file adapter.

VIRTUAL PROCEDURE "hdi_api::fileAdapter_EXEC" (IN PATH NVARCHAR(1024), IN PARAM NVARCHAR(1024), IN FLAG INTEGER, OUT param_3 TABLE (RESULT NVARCHAR(1073741823))) 

CONFIGURATION '{

  "__DP_UNIQUE_NAME__": "EXEC",

  "__DP_HAS_NESTED_PARAMETERS__": false,

  "__DP_USER_DEFINED_PROPERTIES__": {},

  "__DP_INPUT_PARAMETER_PROPERTIES_": [],

  "__DP_RETURN_PARAMETER_PROPERTIES_": [],

  "__DP_VIRTUAL_PROCEDURE__": true,

  "__DP_HAS_INTERNAL_OUTPUT_PARMETER__": false,

  "__DP_DEFAULT_OUTPUT_PARAMETER_INDEX__": 0

}' AT "SDI_fileAdapter"

The procedure that calls the shell script looks like the following. It first gets the credentials from the above table and then runs the xs command provided as parameter.

PROCEDURE "hdi_api::runXSCommand"(IN "ORGANIZATION" VARCHAR(1024),IN "SPACE" VARCHAR(1024),IN "XSCOMMAND" VARCHAR(1024),OUT "OUTPUT" TABLE (RESULT NVARCHAR(1073741823)))

   LANGUAGE SQLSCRIPT

   SQL SECURITY DEFINER

   --DEFAULT SCHEMA <default_schema_name>

   READS SQL DATA AS

BEGIN

DECLARE PARAM NVARCHAR(1024);

SELECT API_URL || ' ' || USERNAME || ' ' || "PASSWORD" || ' ' || ORGANIZATION || ' ' || "SPACE" || ' ' || :XSCOMMAND  INTO PARAM FROM "hdi_api::xsaco"

WHERE ORGANIZATION = :ORGANIZATION AND SPACE = :SPACE; 

CALL "hdi_api::fileAdapter_EXEC"('hdbxsclient/scripts/xsrun.sh', :PARAM, 0, OUTPUT);

END

And that’s all. You can now run XS command via SQL with statement like this:

CALL "Z1A00_SECURITY"."hdi_api::runXSCommand"(

ORGANIZATION => 'airbus'/*<VARCHAR(1024)>*/,

SPACE => 'PRD'/*<VARCHAR(1024)>*/,

XSCOMMAND => 'xs version'/*<VARCHAR(1024)>*/,

OUTPUT => ?

)

Getting application logs use case

The first use case I have implemented was to create a procedure that allows retrieving application logs. It relies on the xs command xs logs. Here is the coding

PROCEDURE "hdi::SP_GET_LOGS"(IN "ORGANIZATION" VARCHAR(1024),IN "SPACE" VARCHAR(1024), IN "APPLICATION" VARCHAR(1024), IN "NB_LINES" INT, IN "ERROR_ONLY" BOOLEAN, OUT "OUTPUT" TABLE (RESULT NVARCHAR(1073741823)))

   LANGUAGE SQLSCRIPT

   SQL SECURITY DEFINER

   --DEFAULT SCHEMA <default_schema_name>

   READS SQL DATA AS

BEGIN

DECLARE ERROR_FILTER VARCHAR(18);

IF :ERROR_ONLY = true THEN

ERROR_FILTER = ' | grep -i "Error"';

ELSE

ERROR_FILTER = '';

END IF;

CALL "Z1A00_Security.hdi_api::runXSCommand"(:ORGANIZATION, :SPACE, 'xs logs '  || :APPLICATION || ' --last ' || :NB_LINES || :ERROR_FILTER, OUTPUT);

END;

Note that I have define an input parameter that allow filtering the result based on “*Error*” pattern.

Here is an example of cal

Get application version number

In this second use case, I have implemented a procedure that allows retrieving application version number. It relies on the xs command xs env that gets environment variables for an application. In order to get the version variable, I had to extract the “user-Provided” section and then parse the json result. Here is the coding:

PROCEDURE "hdi::SP_GET_VERSION"(IN "ORGANIZATION" VARCHAR(1024),IN "SPACE" VARCHAR(1024), IN "APPLICATION" VARCHAR(1024), OUT VERSION_NUMBER NVARCHAR(256))

   LANGUAGE SQLSCRIPT

   SQL SECURITY DEFINER

   --DEFAULT SCHEMA <default_schema_name>

   READS SQL DATA AS

BEGIN

   DECLARE "OUTPUT" TABLE (RESULT NVARCHAR(1073741823));

   CALL "Z1A00_Security.hdi_api::runXSCommand"(:ORGANIZATION, :SPACE, 'xs env ' || :APPLICATION || ' --json', OUTPUT);

   SELECT JSON_VALUE(SUBSTR_BEFORE(SUBSTR_AFTER("RESULT",'User-Provided:'),'Staging environment:'), '$.MTA_METADATA.version') AS "VERSION_NUMBER" INTO VERSION_NUMBER FROM :OUTPUT;

END

Here is an example of call: