authen.public

Parameters

Name	Type	Mode
prop_id	integer	IN
user_id	integer	IN
category_id	text	IN (DEFAULT NULL)
code	character	OUT
name	text	OUT
category	text	OUT
subcategory	text	OUT
parent	character	OUT
is_granted	boolean	OUT
prop_id	integer	IN
user_id	integer	IN
category_id	text	IN
app	text	IN
code	character	OUT
name	text	OUT
category	text	OUT
subcategory	text	OUT
parent	character	OUT
is_granted	boolean	OUT

Definition

WITH requested_permissions AS (
      SELECT base_perm.id, p.code, p.name, p.category, p.subcategory, p.parent
      FROM public.fn_get_permission_codes($3, $4) p
      JOIN public.permission base_perm ON base_perm.code = p.code
  ),
  active_roles AS (
      SELECT ur.group_id
      FROM public.user_roles ur
      WHERE ur.user_id = $2
        AND ur.prop_id = $1
        AND ur.active
      UNION
      SELECT u.user_group_id
      FROM public.users u
      JOIN public.user_property up
        ON up.user_id = u.id
       AND up.prop_id = $1
      WHERE u.id = $2
        AND u.user_group_id IS NOT NULL
        AND NOT EXISTS (
            SELECT 1
            FROM public.user_roles ur
            WHERE ur.user_id = $2
              AND ur.prop_id = $1
              AND ur.active
        )
  ),
  role_allow AS (
      SELECT ep AS perm_id
      FROM active_roles ar
      CROSS JOIN LATERAL public.fn_role_effective_perm_ids(ar.group_id, $1) ep
      UNION
      SELECT uo.perm_id
      FROM public.user_permission_override uo
      JOIN public.permission p ON p.id = uo.perm_id
      WHERE uo.user_id = $2
        AND uo.prop_id = $1
        AND uo.action_type = 'allow'
        AND p.app_code = COALESCE(NULLIF($4, ''), current_setting('current.app', true), 'pms')
  ),
  override_deny AS (
      SELECT uo.perm_id
      FROM public.user_permission_override uo
      JOIN public.permission p ON p.id = uo.perm_id
      WHERE uo.user_id = $2
        AND uo.prop_id = $1
        AND uo.action_type = 'deny'
        AND p.app_code = COALESCE(NULLIF($4, ''), current_setting('current.app', true), 'pms')
  ),
  granted AS (
      SELECT perm_id FROM role_allow
      EXCEPT
      SELECT perm_id FROM override_deny
  )
  SELECT rp.code,
         rp.name,
         rp.category,
         rp.subcategory,
         rp.parent,
         g.perm_id IS NOT NULL AS is_granted
  FROM requested_permissions rp
  LEFT JOIN granted g ON g.perm_id = rp.id
  ORDER BY rp.code

fn_get_user_permissions

Parameters

Definition