CREATE OR REPLACE PROCEDURE add_user_to_apex_group (
p_workspace_name IN VARCHAR2, -- e.g. 'MY_WORKSPACE'
p_user_name IN VARCHAR2, -- e.g. 'AALAM'
p_group_name IN VARCHAR2 -- e.g. 'MANAGERS'
) IS
l_security_group_id NUMBER;
l_existing_groups VARCHAR2(32767);
l_group_table apex_t_varchar2 := apex_t_varchar2();
BEGIN
-- 1. Point APEX at the correct workspace (needed outside an APEX session)
l_security_group_id := apex_util.find_security_group_id(p_workspace => p_workspace_name);
apex_util.set_security_group_id(p_security_group_id => l_security_group_id);
-- 2. Create the group if it doesn't already exist
BEGIN
IF apex_util.get_group_id(p_group_name => p_group_name) IS NULL THEN
apex_util.create_user_group(p_group_name => p_group_name);
END IF;
EXCEPTION
WHEN OTHERS THEN
IF apex_util.get_group_id(p_group_name => p_group_name) IS NULL THEN
apex_util.create_user_group(p_group_name => p_group_name);
END IF;
END;
-- 3. Get the groups this user already belongs to (comma-space separated string)
l_existing_groups := apex_util.get_groups_user_belongs_to(p_username => p_user_name);
-- 4. Build the combined group list (existing + new), avoiding duplicates
IF l_existing_groups IS NOT NULL THEN
FOR i IN 1 .. REGEXP_COUNT(l_existing_groups, ',') + 1 LOOP
l_group_table.EXTEND;
l_group_table(l_group_table.COUNT) := TRIM(REGEXP_SUBSTR(l_existing_groups, '[^,]+', 1, i));
END LOOP;
END IF;
-- only add the new group if it isn't already in the list
IF NOT p_group_name MEMBER OF l_group_table THEN
l_group_table.EXTEND;
l_group_table(l_group_table.COUNT) := p_group_name;
END IF;
-- 5. Apply the full group list back to the user
apex_util.set_group_user_grants (
p_user_name => p_user_name,
p_granted_group_names => l_group_table
);
COMMIT;
END add_user_to_apex_group;
/
DECLARE
CURSOR C1 IS
select user_name from APEX_WORKSPACE_APEX_USERS;
BEGIN
FOR i IN C1
LOOP
add_user_to_apex_group (
p_workspace_name => 'SEAMAN',
p_user_name => i.user_name,
p_group_name => 'PLM'
);
END LOOP;
END;
/
0 comments:
Post a Comment