Replicating ERP Users to HANA Live (HEC) for Fiori

[MattHarding]

Hi All,

I'm looking at how to replicate ERP users to a HANA Live instance so that users can single sign-on and be given appropriate permissions to odata services and the like.  I've looked at the AAA mentioned in this post but because we're using HANA Live for oData and other non-analytical privileges requirements also; then this is not a complete solution (may still be a part of the puzzle).

From all my searching, I can't see anything that comes out of the box to do this, and all I can think of is to sync user and roles from ERP and write an xsjs script that I schedule to run nightly which maps this to users and privileges appropriately.

Note - An added complexity is that our HANA Live is in HEC so not all features and authorisations may be available for this.

Does anyone know of an ongoing solution to manage provisioning of users (without Identity Management and including removing privileges and users) to do this; or alternatively have a SQLScript/XSJSscript to do this that they can share?

Thanks,

Matt

[MattHarding · Accepted solution]

Slightly easier option for the SQL Script challenged who want control in ABAP and use the secondary database connection (transaction dbco to set-up but will exist on your SLT server already if you want an example).

Code provided as is without advice or warranty and is my sandpit example to check it works:

REPORT ymwh_test.

 CONSTANTS:
   constv_blank_date  TYPE datum VALUE '00000000',
   constv_global_role TYPE agr_name VALUE 'ZGENERICROLEEVERONEHAS'.

 TYPES:
   BEGIN OF typ_hana_user_detail,
     logon_ticket_enabled TYPE string,
     activated            TYPE string,
   END OF typ_hana_user_detail.

 START-OF-SELECTION.
   DATA:
     lt_hana_users        TYPE TABLE OF string,
     lt_hana_check_user   TYPE TABLE OF typ_hana_user_detail,
     lt_abap_users        TYPE TABLE OF xubname,
     lt_check_permissions TYPE TABLE OF xubname,
     lr_users             TYPE REF TO data.

 * Retrieve all active users by filtering the generic role that is applied to all real users and filtering by those not time-sliced out

   SELECT uname INTO TABLE @lt_abap_users
     FROM agr_users AS a INNER JOIN usr02 AS b
       ON a~uname = b~bname
     WHERE
       a~agr_name = @constv_global_role AND
       ( a~to_dat >= @sy-datum OR to_dat = @constv_blank_date ) AND " Role still assigned
       ( b~gltgb >= @sy-datum OR b~gltgb = @constv_blank_date ). " User still active

 * Get all active HANA users by filtering by in users with SAML activated flag and are not time-sliced out
   TRY .
       DATA(lo_conn) = NEW cl_sql_statement( con_ref = cl_sql_connection=>get_connection( '001:R:C' ) ).
 *     Get HANA Users by getting all active users first
       DATA(lo_results) = lo_conn->execute_query( |select "USER_NAME" from "SYS"."USERS" where USER_DEACTIVATED = 'FALSE' AND IS_SAP_LOGON_TICKET_ENABLED = 'TRUE' and (VALID_UNTIL is null or VALID_UNTIL > CURRENT_TIMESTAMP)| ).

       GET REFERENCE OF lt_hana_users INTO lr_users.
       lo_results->set_param_table( lr_users ).
       lo_results->next_package( ). " Puts the results set into the table
       lo_results->close( ).
     CATCH cx_sql_exception INTO DATA(o_exception).
       WRITE: |Error querying HANA for Users: |, o_exception->get_text( ).
       EXIT.
   ENDTRY.
   lt_check_permissions = lt_abap_users.
   LOOP AT lt_abap_users ASSIGNING FIELD-SYMBOL(<ls_abap_user>).
     LOOP AT lt_hana_users TRANSPORTING NO FIELDS
       WHERE table_line = <ls_abap_user>.
       WRITE: / |User is correctly set-up in HANA already: { <ls_abap_user> }|.
       DELETE lt_hana_users.
     ENDLOOP.
     IF sy-subrc <> 0.
       " add user
       WRITE: / |Add user: { <ls_abap_user> }|.
       " Check if user exists already in any shape or form
       TRY.
           lo_results = lo_conn->execute_query( |select "IS_SAP_LOGON_TICKET_ENABLED", "USER_DEACTIVATED" from "SYS"."USERS" where USER_NAME = '{ <ls_abap_user> }'| ).
           REFRESH lt_hana_check_user.
           GET REFERENCE OF lt_hana_check_user INTO lr_users.
           lo_results->set_param_table( lr_users ).
           lo_results->next_package( ). " Puts the results set into the table
           lo_results->close( ).
           READ TABLE lt_hana_check_user INDEX 1 ASSIGNING FIELD-SYMBOL(<ls_hana_user>).
           IF sy-subrc = 0.
             WRITE: / 'User exist in database'.
             IF <ls_hana_user>-logon_ticket_enabled = 'FALSE'.
               WRITE: / 'ERROR: Manual user intervention required: User exists but does not have SAP Logon access - Created manually?'.
               CONTINUE. " to next user
             ELSEIF <ls_hana_user>-activated = 'FALSE'.
               WRITE: / 'User is deactivated)'.
               lo_conn->execute_query( |alter user { <ls_abap_user> } ACTIVATE| ).
               CONTINUE.
             ENDIF.
           ENDIF.
         CATCH cx_sql_exception INTO o_exception.
           WRITE: |Error checking Users exists in HANA: |, o_exception->get_text( ).
           EXIT.
       ENDTRY.
       " Create new user as they don't exist
       TRY.
           lo_conn->execute_query( |create user { <ls_abap_user> } with Identity ANY for SAML Provider BID FOR SAP LOGON TICKET FOR SAP ASSERTION TICKET| ).
           WRITE: / 'Created user in HANA:', <ls_abap_user>.
         CATCH cx_sql_exception INTO o_exception.
           WRITE: |Error checking Users exists IN HANA: |, o_exception->get_text( ).
           EXIT.
       ENDTRY.
     ENDIF.
   ENDLOOP.

   LOOP AT lt_hana_users ASSIGNING FIELD-SYMBOL(<ls_user>).
     WRITE: / |DEACTIVATE user: { <ls_user> }|.
     TRY.
         lo_conn->execute_query( |alter user { <ls_user> } DEACTIVATE| ).
       CATCH cx_sql_exception INTO o_exception.
         WRITE: |Error checking Users exists IN HANA: |, o_exception->get_text( ).
         EXIT.
     ENDTRY.
   ENDLOOP.


   LOOP AT lt_check_permissions ASSIGNING <ls_abap_user>.
     WRITE: / |Check permissions for user: { <ls_abap_user> }|.
 *   Update permissions based on specifically assigned roles in ERP

   ENDLOOP.

[MattHarding]

FYI - Just found this post which has provided a solution:

[MattHarding]

Adding to this, I'm referring to a side car implementation of HANA Live, which as I understand it, direct OData calls in S4 will go via CDS views and hence having direct user-based access to HANA will no longer be a requirement. This is possibly why this problem doesn't appear to be solved.  Solution will be to implement my own script it seems so I can get the non-CDS version of SAP Smart Business to work in the mean time.