SQLScript JWT configuration for SAP HANA

[former_member648857]

This blog post presents a code sample for managing JWT providers and configuring users to use it.

We are in the process of implementing SSO for many of our systems. One of those systems is HANA cockpit, which uses JWT for authentication.

We mostly use SAP Identity Management to provision users, and we haven't found a way yet to automate the whole JWT process through the tool.

SSO configuration for HANA cockpit is easy to do through the GUI or through command line, but we have more than 40 users in a MDC setup. Although we usually only have one tenant in each system, this represents more than 80 users to reconfigure everytime there is an environment refresh. Also, we don't want to manually configure each new provisioned user.

To help in automating the process, I looked around trying to find an SQL Script code sample that would enable me to loop through all users and configure them. I found a couple of blog posts that helped me to write the following code. The script is divided in two parts :

1 - Issuer validation - where I make certain the JWT provider is correctly configured and has the right name. In this part, I only manage the HANA Cockpit JWT provider, but the script could be modified to manage more than one provider.

If the provider isn't created, I create it. If there's alrerady a provider with the ISSUER_NAME, i check to make sure the JWT_PROVIDER_NAME is the right one, or I delete and recreate the provider.

/*#################*\

  Issuer validation

\*#################*/

sel1 = select JWT_PROVIDER_NAME from JWT_PROVIDERS where ISSUER_NAME=''|| v_issuer_name ||'';

select count(*) into v_count1 from :sel1;

  

--No issuer - JWT PROVIDER creation

if v_count1=0 then

  EXECUTE IMMEDIATE 'CREATE JWT PROVIDER COCKPIT_XSA WITH ISSUER '''|| v_issuer_name ||''' CLAIM ''user_name'' AS EXTERNAL IDENTITY';

end if;



--One issuer with the issuer token. Check to see if it has the correct name.

if v_count1 = 1 then

  v_jwt_name = :sel1.JWT_PROVIDER_NAME[1];

  if( v_jwt_name != 'COCKPIT_XSA' ) then

    v_dropJwt := 'DROP JWT PROVIDER '||v_jwt_name||'';

    EXEC(:v_dropJwt);

    EXECUTE IMMEDIATE 'CREATE JWT PROVIDER COCKPIT_XSA WITH ISSUER '''|| v_issuer_name ||''' CLAIM ''user_name'' AS EXTERNAL IDENTITY';

  end if;

end if;

 

2 - Main script - where I get a list of all users that have an email address in their profile, and configure and enable JWT authentication for each with the use of a for loop. As of now, the script configures all users with an email address to user the JWT provider COCKPIT_XSA configured in the previous section. You could change the sel2 SQL query to be more specific to better suit your needs.

  /*###########*\

    Main script

  \*###########*/

  sel2 = select USER_NAME, VALUE from USER_PARAMETERS where PARAMETER='EMAIL ADDRESS';

  select count(*) into v_count2 from :sel2;



  for v_i in 1..v_count2 do

    v_username = :sel2.USER_NAME[v_i];

    v_value = :sel2.VALUE[v_i];

    v_AlterUser := 'ALTER USER '||v_username||' ADD IDENTITY '''||v_value||''' FOR JWT PROVIDER COCKPIT_XSA';

    EXEC(:v_AlterUser);

    v_AlterUser := 'ALTER USER '||v_username||' ENABLE JWT;';

    EXEC(:v_AlterUser);

  end for;

 

Here is the whole script with the variables definition section. Note that the user running the script needs the USER ADMIN privilege to create the JWT provider and assign it to users.

DO

BEGIN

  /*################*\

    Define variables

  \*################*/

  declare v_count1 integer;

  declare v_issuer_name varchar(512) := '<JWT_ISSUER_ID>';

  declare v_jwt_name varchar(256);

  declare v_dropJwt varchar(256);



  declare v_count2 integer;

  declare v_i integer;

  declare v_username varchar(256);

  declare v_value varchar(256);

  declare v_AlterUser varchar(256);



  /*#################*\

    Issuer validation

  \*#################*/

  sel1 = select JWT_PROVIDER_NAME from JWT_PROVIDERS where ISSUER_NAME=''|| v_issuer_name ||'';

  select count(*) into v_count1 from :sel1;



  --No issuer - JWT PROVIDER creation

  if v_count1=0 then

    EXECUTE IMMEDIATE 'CREATE JWT PROVIDER COCKPIT_XSA WITH ISSUER '''|| v_issuer_name ||''' CLAIM ''user_name'' AS EXTERNAL IDENTITY';

  end if;



  --One issuer with the issuer token. Check to see if it has the correct name.

  if v_count1 = 1 then

    v_jwt_name = :sel1.JWT_PROVIDER_NAME[1];

    if( v_jwt_name != 'COCKPIT_XSA' ) then

      v_dropJwt := 'DROP JWT PROVIDER '||v_jwt_name||'';

      EXEC(:v_dropJwt);

      EXECUTE IMMEDIATE 'CREATE JWT PROVIDER COCKPIT_XSA WITH ISSUER '''|| v_issuer_name ||''' CLAIM ''user_name'' AS EXTERNAL IDENTITY';

    end if;

  end if;



  /*###########*\

    Main script

  \*###########*/

  sel2 = select USER_NAME, VALUE from USER_PARAMETERS where PARAMETER='EMAIL ADDRESS';

  select count(*) into v_count2 from :sel2;



  for v_i in 1..v_count2 do

    v_username = :sel2.USER_NAME[v_i];

    v_value = :sel2.VALUE[v_i];

    v_AlterUser := 'ALTER USER '||v_username||' ADD IDENTITY '''||v_value||''' FOR JWT PROVIDER COCKPIT_XSA';

    EXEC(:v_AlterUser);

    v_AlterUser := 'ALTER USER '||v_username||' ENABLE JWT;';

    EXEC(:v_AlterUser);

  end for;

END;

Normally, you should only need to change v_issuer_name  <JWT_ISSUER_ID> with the id of your provider (e.g. http://xsa-99999999-9999-9999-9999-999999999999/UAA/oauth/token) for the script to work properly. Make sure you keep all the quotes as they are, they were a hassle to configure correctly.

Also, it would be easy to modify the script to create a function and distribute it through a package to make it available on multiple systems for ease of use.

Finally, this script is easily modifiable to suit your enterprise's needs. It could be the base for scripts for changing multiple different settings on users. Feel free to use it!