Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
Cocquerel
Active Contributor

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 (same as the one to store admin roles as recommended by Best practices and recommendations for developing roles in SAP HANA  ). 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 call



 

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:



Conclusion


Having the possibility to run xs command via SQL may helps in many use cases. SAP may implement such SQL APIs in future HANA releases (even if I didn't see it in the roadmap) but, in the meantime, this simple solution can do the job.

Hope you liked this reading and that gives you other ideas of use cases that you may share in comment.

You can find here an other use case that is a bit more complexe ant that allows automatic removal of old temporary HDI containers in developer workspace.

 

 
Labels in this area