137 lines
4.4 KiB
MySQL
137 lines
4.4 KiB
MySQL
|
--------------------------------------------------------
|
||
|
|
-- DDL for Function FNC_INTERNAL_PROFILE
|
||
|
|
--------------------------------------------------------
|
||
|
|
|
||
|
|
CREATE OR REPLACE EDITIONABLE FUNCTION "ENVX_OPER"."FNC_INTERNAL_PROFILE" (
|
||
|
|
pi_username IN VARCHAR2,
|
||
|
|
pi_email IN VARCHAR2,
|
||
|
|
pi_group_list IN CLOB
|
||
|
|
) RETURN CLOB AS
|
||
|
|
v_input_string VARCHAR2(4000) := pi_group_list;
|
||
|
|
v_output_json CLOB := '';
|
||
|
|
v_json_aux json;
|
||
|
|
v_domain_table sys.odcivarchar2list;
|
||
|
|
v_domain_name VARCHAR2(100) := '';
|
||
|
|
v_group_name VARCHAR2(100) := '';
|
||
|
|
v_hash_aux VARCHAR2(100);
|
||
|
|
v_user_id NUMBER := 0;
|
||
|
|
v_user VARCHAR2(500) := '';
|
||
|
|
v_context VARCHAR2(500) := '';
|
||
|
|
BEGIN
|
||
|
|
|
||
|
|
-- Documento JSON Principal de Usuario --
|
||
|
|
v_hash_aux := rawtohex(dbms_crypto.hash(
|
||
|
|
utl_raw.cast_to_raw(pi_username),
|
||
|
|
2
|
||
|
|
));
|
||
|
|
v_user_id := abs(TO_NUMBER(substr(
|
||
|
|
rawtohex(v_hash_aux),
|
||
|
|
1,
|
||
|
|
12
|
||
|
|
),
|
||
|
|
'XXXXXXXXXXXX'));
|
||
|
|
--v_user_id := v_user_id MOD 100000000;
|
||
|
|
|
||
|
|
v_user := regexp_substr(
|
||
|
|
pi_username,
|
||
|
|
'(.+)\s+(.+)?',
|
||
|
|
1,
|
||
|
|
1,
|
||
|
|
NULL,
|
||
|
|
1
|
||
|
|
);
|
||
|
|
v_context := regexp_substr(
|
||
|
|
pi_username,
|
||
|
|
'(.+)\s+(.+)?',
|
||
|
|
1,
|
||
|
|
1,
|
||
|
|
NULL,
|
||
|
|
2
|
||
|
|
);
|
||
|
|
v_output_json := '{"user":"'
|
||
|
|
|| v_user
|
||
|
|
|| '","userId":'
|
||
|
|
|| to_char(v_user_id)
|
||
|
|
|| ',"context":"'
|
||
|
|
|| v_context
|
||
|
|
|| '","email":"'
|
||
|
|
|| pi_email
|
||
|
|
|| '","idm":[';
|
||
|
|
-- Documento JSON Principal de Usuario --
|
||
|
|
|
||
|
|
-- Evalua que existan los grupos de LDAP --
|
||
|
|
IF pi_group_list IS NULL THEN
|
||
|
|
v_output_json := v_output_json || ']';
|
||
|
|
ELSE
|
||
|
|
v_domain_table := sys.odcivarchar2list();
|
||
|
|
v_domain_table.extend(regexp_count(
|
||
|
|
v_input_string,
|
||
|
|
','
|
||
|
|
) + 1);
|
||
|
|
|
||
|
|
-- Iteracion por los grupos del IDM --
|
||
|
|
FOR i IN 1..v_domain_table.count LOOP
|
||
|
|
v_domain_table(i) := regexp_substr(
|
||
|
|
v_input_string,
|
||
|
|
'[^,]+',
|
||
|
|
1,
|
||
|
|
i
|
||
|
|
);
|
||
|
|
v_domain_name := replace(
|
||
|
|
nvl(
|
||
|
|
regexp_substr(
|
||
|
|
v_domain_table(i),
|
||
|
|
'(.+)(\/)',
|
||
|
|
1,
|
||
|
|
1
|
||
|
|
),
|
||
|
|
'others'
|
||
|
|
),
|
||
|
|
'/',
|
||
|
|
''
|
||
|
|
);
|
||
|
|
v_group_name := regexp_substr(
|
||
|
|
v_domain_table(i),
|
||
|
|
'(.+\/)?(.+)',
|
||
|
|
1,
|
||
|
|
1,
|
||
|
|
NULL,
|
||
|
|
2
|
||
|
|
);
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
v_output_json := v_output_json
|
||
|
|
|| (
|
||
|
|
CASE
|
||
|
|
WHEN i != 1 THEN
|
||
|
|
','
|
||
|
|
ELSE ''
|
||
|
|
END
|
||
|
|
)
|
||
|
|
|| '{"domain":"'
|
||
|
|
|| v_domain_name
|
||
|
|
|| '","group":"'
|
||
|
|
|| v_group_name
|
||
|
|
|| '"}';
|
||
|
|
|
||
|
|
END LOOP;
|
||
|
|
v_output_json := v_output_json || ']';
|
||
|
|
-- Iteracion por los grupos del IDM --
|
||
|
|
|
||
|
|
END IF;
|
||
|
|
v_output_json := v_output_json || '}';
|
||
|
|
-- Evalua que existan los grupos de LDAP --
|
||
|
|
|
||
|
|
P_MANAGE_USERS(v_output_json);
|
||
|
|
|
||
|
|
RETURN v_output_json;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN OTHERS THEN
|
||
|
|
RETURN 'ERR - '
|
||
|
|
|| sqlerrm
|
||
|
|
|| v_output_json;
|
||
|
|
END;
|
||
|
|
|
||
|
|
/
|