Roles, Groups, and Permissions

Roles, Groups, and Permissions#

Znuny’s access control system is built on three layers: groups define what can be accessed, roles bundle group permissions together, and users (agents) are granted access either directly to groups or via roles. Customers have their own parallel path through group_customer and group_customer_user.

Understanding this model lets you answer questions like “which agents can work tickets in queue X?”, “what groups does this user have?”, or “which roles grant access to this group?”

The Permission Model#

Agent permissions — two paths to a group:

users ──► group_user ──────────────────────────────► permission_groups
users ──► role_user ──► roles ──► group_role ──────► permission_groups

Customer permissions:

customer_company  ──► group_customer      ──────────► permission_groups
customer_user     ──► group_customer_user ──────────► permission_groups

Permission Keys#

Every row in the assignment tables carries a permission_key that specifies the level of access being granted.

Key	What it allows
`ro`	Read-only access — view tickets and articles in the group’s queues.
`move_into`	Move tickets into queues that belong to the group.
`create`	Create new tickets in the group’s queues.
`note`	Add notes to tickets in the group’s queues.
`owner`	Become the owner of tickets in the group’s queues.
`priority`	Change the priority of tickets in the group’s queues.
`rw`	Full read-write access. When `rw` is granted, all other keys are implied. The system stores only `rw` and removes the individual keys.

For group_role the presence of a permission is controlled by permission_value: 1 = enabled, 0 = disabled (explicitly withdrawn).

The Tables#

`permission_groups` — group definitions#

Column	Type	Description
`id`	INTEGER	Primary key. Referenced by all assignment tables as `group_id`.
`name`	VARCHAR(200)	Group name (e.g. `users`, `stats`, `admin`). Unique.
`comments`	VARCHAR(250)	Optional description.
`valid_id`	SMALLINT	`1` = active. Joins to `valid.id`.

`roles` — role definitions#

Column	Type	Description
`id`	INTEGER	Primary key. Referenced by `role_user.role_id` and `group_role.role_id`.
`name`	VARCHAR(200)	Role name. Unique.
`comments`	VARCHAR(250)	Optional description.
`valid_id`	SMALLINT	`1` = active.

`group_user` — direct agent → group assignment#

Agents assigned directly to a group (without a role). One row per user/group/permission_key combination.

Column	Type	Description
`user_id`	INTEGER	Joins to `users.id`.
`group_id`	INTEGER	Joins to `permission_groups.id`.
`permission_key`	VARCHAR(20)	The permission level: `ro`, `move_into`, `create`, `note`, `owner`, `priority`, or `rw`.

`role_user` — agent → role membership#

Associates agents with roles. Contains no permission key — the permissions come from group_role.

Column	Type	Description
`user_id`	INTEGER	Joins to `users.id`.
`role_id`	INTEGER	Joins to `roles.id`.

`group_role` — role → group permission assignment#

Associates roles with groups and permission keys. One row per role/group/permission_key combination.

Column	Type	Description
`role_id`	INTEGER	Joins to `roles.id`.
`group_id`	INTEGER	Joins to `permission_groups.id`.
`permission_key`	VARCHAR(20)	The permission type (see keys above).
`permission_value`	SMALLINT	`1` = permission granted, `0` = permission explicitly withdrawn.

`group_customer` — customer company → group#

Grants a customer company (organization) access to a group.

Column	Type	Description
`customer_id`	VARCHAR(150)	Joins to `customer_company.customer_id`.
`group_id`	INTEGER	Joins to `permission_groups.id`.
`permission_key`	VARCHAR(20)	Permission type.
`permission_value`	SMALLINT	`1` = granted, `0` = withdrawn.
`permission_context`	VARCHAR(100)	Context for the permission (e.g. `Customer::Self` or `Customer::Other`).

`group_customer_user` — customer contact → group#

Grants an individual customer user access to a group. user_id here is the customer login (a string), not an integer.

Column	Type	Description
`user_id`	VARCHAR(100)	Joins to `customer_user.login`.
`group_id`	INTEGER	Joins to `permission_groups.id`.
`permission_key`	VARCHAR(20)	Permission type.
`permission_value`	SMALLINT	`1` = granted, `0` = withdrawn.

Permission Queries#

All groups an agent belongs to (direct + via role):

-- Direct group memberships
SELECT
    u.login,
    CONCAT(u.first_name, ' ', u.last_name)  AS agent,
    pg.name                                  AS group_name,
    gu.permission_key,
    'direct'                                 AS source
FROM   group_user        gu
JOIN   users             u  ON  u.id = gu.user_id
JOIN   permission_groups pg ON pg.id = gu.group_id
WHERE  u.login = 'agent.login'

UNION ALL

-- Via role
SELECT
    u.login,
    CONCAT(u.first_name, ' ', u.last_name)  AS agent,
    pg.name                                  AS group_name,
    gr.permission_key,
    CONCAT('role:', r.name)                  AS source
FROM   role_user         ru
JOIN   users             u  ON  u.id  = ru.user_id
JOIN   roles             r  ON  r.id  = ru.role_id
JOIN   group_role        gr ON gr.role_id = ru.role_id AND gr.permission_value = 1
JOIN   permission_groups pg ON pg.id = gr.group_id
WHERE  u.login = 'agent.login'

ORDER BY group_name, permission_key;

All agents who have rw access to a specific group:

SELECT DISTINCT
    u.login,
    CONCAT(u.first_name, ' ', u.last_name)  AS agent,
    src.source
FROM (
    -- Direct rw
    SELECT gu.user_id, 'direct' AS source
    FROM   group_user        gu
    JOIN   permission_groups pg ON pg.id = gu.group_id
    WHERE  pg.name            = 'admin'
    AND    gu.permission_key  = 'rw'

    UNION

    -- Via role rw
    SELECT ru.user_id, CONCAT('role:', r.name) AS source
    FROM   role_user         ru
    JOIN   roles             r  ON  r.id = ru.role_id
    JOIN   group_role        gr ON gr.role_id = ru.role_id
    JOIN   permission_groups pg ON pg.id = gr.group_id
    WHERE  pg.name            = 'admin'
    AND    gr.permission_key  = 'rw'
    AND    gr.permission_value = 1
) src
JOIN users u ON u.id = src.user_id
WHERE u.valid_id = 1
ORDER BY agent;

All roles and their group permissions (full permission matrix):

SELECT
    r.name                   AS role,
    pg.name                  AS group_name,
    gr.permission_key,
    gr.permission_value
FROM   group_role        gr
JOIN   roles             r  ON  r.id = gr.role_id
JOIN   permission_groups pg ON pg.id = gr.group_id
WHERE  r.valid_id = 1
ORDER BY r.name, pg.name, gr.permission_key;

All permissions for one agent (flat view — direct and role-based combined):

SELECT DISTINCT
    pg.name             AS group_name,
    perm.permission_key,
    perm.source
FROM (
    SELECT gu.group_id, gu.permission_key, 'direct' AS source
    FROM   group_user gu
    JOIN   users u ON u.id = gu.user_id
    WHERE  u.login = 'agent.login'

    UNION

    SELECT gr.group_id, gr.permission_key,
           CONCAT('role:', r.name) AS source
    FROM   role_user  ru
    JOIN   users      u  ON  u.id  = ru.user_id
    JOIN   roles      r  ON  r.id  = ru.role_id
    JOIN   group_role gr ON gr.role_id = ru.role_id
    WHERE  u.login            = 'agent.login'
    AND    gr.permission_value = 1
) perm
JOIN permission_groups pg ON pg.id = perm.group_id
ORDER BY pg.name, perm.permission_key;

Agents with no group or role assignments (orphaned users):

SELECT u.login, CONCAT(u.first_name, ' ', u.last_name) AS agent
FROM   users u
WHERE  u.valid_id = 1
AND    u.id NOT IN (SELECT user_id FROM group_user)
AND    u.id NOT IN (SELECT user_id FROM role_user)
ORDER BY u.last_name;

Customer company group access:

SELECT
    cc.name                AS company,
    pg.name                AS group_name,
    gc.permission_key,
    gc.permission_context
FROM   group_customer    gc
JOIN   customer_company  cc ON cc.customer_id = gc.customer_id
JOIN   permission_groups pg ON pg.id = gc.group_id
WHERE  gc.permission_value = 1
ORDER BY cc.name, pg.name;

Roles, Groups, and Permissions

Contents

Roles, Groups, and Permissions#

The Permission Model#

Permission Keys#

The Tables#

permission_groups — group definitions#

roles — role definitions#

group_user — direct agent → group assignment#

role_user — agent → role membership#

group_role — role → group permission assignment#

group_customer — customer company → group#

group_customer_user — customer contact → group#