Here is my requirement for a SAP HANA 2.0 system. 1) Identify users with not having SAML flag enabled from "SYS."USERS" table. Fields that I need from this table are USER_NAME,EXTERNAL_IDENTITY,IS_SAML_ENABLED. Sample data looks like this.
USER_NAME EXTERNAL_IDENTITY IS_SAML_ENABLED.
JDOE JDOE@aol.com TRUE
JDOE1 JDOE1@mail.com FALSE
JDOE2 JDOE@aol.com FALSE
2) If SAML is not enabled, I have to run these two SQL statements on each user ID in a loop until all those users identified in step 1 are updated and then close the loop. alter user JDOE enable SAML; alter user JDOE add identity 'JDOE' for SAML provider ;
SAML_PROVIDER variable comes from a table SAML_PROVIDERS that has multiple entries and and so on.
3) Another constraint is to map SAML to user based on their EXTERNAL_IDENTITY that has multiple values. This field is used for Kerberos authentication. So if first user has @aol.com then assign 'SAML_PROVIDER1' and if @mail.com is there then assign
4) I am looking to have this in a stored procedure because I would like to schedule this as a background job in HANA XS.
Here is the code that I tried to come up with after spending considerable amount of time and looking at information available online. Please note that I still not added the second SQL statement to the code. I am open to other suggestions that does not involve loop as long as I can do these tasks through a background job.
PROCEDURE ""."" ( )
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
i INTEGER;
row_count INTEGER;
loop_current_SQL NVARCHAR(200);
valid_SAML NVARCHAR(5);
BEGIN
it_no_saml_users = SELECT DISTINCT
A."USER_NAME",A."IS_SAML_ENABLED",A."CREATOR"
FROM "SYS"."USERS" A
LEFT OUTER JOIN "SYS"."SAML_USER_MAPPINGS" B
ON (A."USER_NAME" = B."USER_NAME")
WHERE B."USER_NAME" IS NULL and A."CREATOR"='ADM' and
A."IS_SAML_ENABLED"='FALSE';
SELECT COUNT("USER_NAME") into row_count FROM :it_no_saml_users;
FOR i IN 0 .. :row_count -1 DO
SELECT "IS_SAML_ENABLED"
into valid_SAML FROM :it_no_saml_users
LIMIT 1 OFFSET :i;
IF :valid_SAML IS NULL THEN
SELECT 'ALTER USER' || "USER_NAME" || 'ENABLE SAML'
INTO loop_current_SQL
FROM :it_no_saml_users;
EXEC(:loop_current_SQL);
END IF;
END FOR;
END
When this procedure is ran, I do not get any error but number of rows affected is shown as 0.
I am new to SQL and please ignore if my procedure does not make sense.
Thank you.
Asked by RMR
(11 rep)
Aug 2, 2018, 02:14 PM
Last activity: Aug 6, 2018, 01:46 PM
Last activity: Aug 6, 2018, 01:46 PM