authen.public

Parameters

Name	Type	Mode
v_group_id	integer	IN
v_prop_id	integer	IN
v_codes	bpchar[]	IN
v_grant_all	boolean	IN (DEFAULT false)

Definition

DECLARE
    v_is_standard boolean;
    v_app text;
    v_desired character(5)[];
BEGIN
    SELECT (prop_id IS NULL), COALESCE(app_code, 'pms') INTO v_is_standard, v_app
    FROM public.user_group WHERE id = v_group_id;

IF v_grant_all THEN
        SELECT COALESCE(array_agg(p.code), ARRAY[]::character(5)[])
        FROM public.permission p WHERE p.app_code = v_app
        INTO v_desired;
    ELSE
        v_desired := COALESCE(v_codes, ARRAY[]::character(5)[]);
    END IF;

IF v_is_standard THEN
        DELETE FROM public.group_permission_override
        WHERE group_id = v_group_id AND prop_id = v_prop_id;

-- allow = desired ที่ไม่มีใน base
        INSERT INTO public.group_permission_override(group_id, prop_id, code, perm_id, action_type)
        SELECT v_group_id, v_prop_id, p.code, p.id, 'allow'
        FROM public.permission p
        WHERE p.code = ANY(v_desired)
          AND p.app_code = v_app
          AND NOT EXISTS (SELECT 1 FROM public.group_permission gp WHERE gp.group_id = v_group_id AND gp.perm_id = p.id);

-- deny = base ที่ไม่มีใน desired
        INSERT INTO public.group_permission_override(group_id, prop_id, code, perm_id, action_type)
        SELECT v_group_id, v_prop_id, gp.code, gp.perm_id, 'deny'
        FROM public.group_permission gp
        WHERE gp.group_id = v_group_id
          AND NOT (gp.code = ANY(v_desired));
    ELSE
        PERFORM public.fn_set_group_permissions(v_group_id, v_desired, false);
    END IF;

PERFORM public.sp_reset_user_permissions(ur.user_id, ur.prop_id)
    FROM public.user_roles ur
    WHERE ur.group_id = v_group_id AND ur.prop_id = v_prop_id AND ur.active;
END

fn_role_set_permissions

Parameters

Definition