Definition

Purpose

The user_in_role model contains user-in-role records. It provides user role metadata for organisations including employment relationships and job classifications.

Information

This master table captures clinician and staff role assignments within healthcare organisations. Each record represents a single employment relationship with associated roles and responsibilities, enabling permissions modelling and role-based access control.

• emis_userinrole_guid (PK): The unique identifier for the user in role
• emis_employer_organisation_guid (FK): Links to the organisation that employs this user

Examples

Get all users in role for an organisation

SELECT *
FROM hive.explorer_recruit.user_in_role
WHERE organisation = 'CDB-0001'
LIMIT 100;

Find users by job category

SELECT
  emis_userinrole_guid,
  emis_employer_organisation_guid,
  job_category_code,
  job_category_name
FROM hive.explorer_recruit.user_in_role
WHERE job_category_code = 'N10'
LIMIT 100;

Find all roles for a specific employer organisation

SELECT
  emis_userinrole_guid,
  job_category_code,
  job_category_name
FROM hive.explorer_recruit.user_in_role
WHERE emis_employer_organisation_guid = 'ORG-GUID-123'
LIMIT 100;