Security user roles allow you to restrict access to data for different user groups. Each role describes the entities and fields to which access is allowed according to the given restrictions.
Within ozma.io at the system level, restrictions are implemented by adding conditions to the user's SQL request in a WHERE block
The role is created in the public.roles table.
| Column | Description |
|---|---|
schema_id |
The schema to which this role is bound. |
name |
Role name |
allow_broken |
Flag that allowing to save the idle role |
Roles can be inherited from other roles -- inherited permissions are cumulative.
Each role must be inherited from funapp.observer - a system role that gives the user access to the tables that are necessary to work with the ozma.io interface.
The public.role_entities table contains the restrictions set for the given role and entity.
select), insertion (insert), update (update), checks for validity of changes (check) and deletion (delete) operations with this entity.true.| Column | Description |
|---|---|
role_id |
The role for which the constraint is defined. |
entity_id |
The entity for which the constraint is defined. |
insert |
Permission to insert records. |
select |
SELECT constraint as an expression in FunQL. |
update |
UPDATE constraint as an expression in FunQL. Additionally, restrictions are imposed from select for the entity and affected fields. |
check |
Check for the admissibility of user changes. Conducted after INSERT and UPDATE queries. Required for INSERT and UPDATE. |
delete |
DELETE constraint as an expression in FunQL. Additionally, restrictions are imposed from select for the entity and all its fields. |
Manager (role) maintains Tasks (entity) in the system. Manager sees only his tasks and tasks of his department, manager can add and edit only his tasks and does not have access to delete.
| Field | Value | Explanation |
|---|---|---|
| role_id | user.manager |
User role |
| entity_id | pm.tasks |
Entity to which constraints are set |
| select | responsible = ANY((SELECT id FROM base.people WHERE department = (SELECT department FROM base.people WHERE user = $$user_id LIMIT 1))) |
A user with the user.manager role only sees pm.tasks entries where the person in responsible fieild has the same department as the user. $$user_id - unique user id from the public.users table |
| insert | responsible = (SELECT id FROM base.people WHERE user = $$user_id LIMIT 1) |
A user with the user.manager role can add new tasks (pm.tasks) only if he is set as responsible in the created record |
| update | responsible = (SELECT id FROM base.people WHERE user = $$user_id LIMIT 1) |
A user with the user.manager role can edit tasks (pm.tasks) only if he is set as responsible in the edited entry |
| check | true |
A user with the user.manager role can change pm.tasks entries |
| delete | false |
Complete prohibition of deleting pm.tasks entries for users with the user.manager role |
For entities in the same hierarchy, accesses are added as "AND" from the root of the hierarchy.
For example, there is a hierarchy of entities - Organizations and People inside Contacts. Restrictions are set for these three entities:
| Entity | Parent entity | Restriction |
|---|---|---|
base.contacts |
null | (c) |
base.people |
base.contacts |
(p) |
base.organizations |
base.contacts |
(o) |
Then, when selecting from entities, the restrictions will look like this:
| Query | Restriction |
|---|---|
SELECT ... FROM base.contacts |
WHERE (c) AND ((sub_entity INHERITED FROM people AND (p)) OR (sub_entity INHERITED FROM organizations AND (o))) |
SELECT ... FROM base.people |
WHERE (c) AND (p) |
SELECT ... FROM base.organizations |
WHERE (c) AND (p) |
The public.role_column_fields table contains the constraints set for the given role and entity field.
select), insertion (insert), update (update) and checks for the validity of changes (check) operations.true.| Column | Description |
|---|---|
role_entity_id |
The entity constraint for which the field constraint is defined. |
column_name |
The field for which the constraint is being defined. |
select |
SELECT constraint as an expression in FunQL. |
insert |
Permission to insert records. |
update |
Restriction on UPDATE fields as an expression in FunQL language. |
check |
Check for the admissibility of user changes. Conducted after INSERT and UPDATE queries. Required for INSERT and UPDATE. |
If the user does not have read access to any field specified in the userview, then the entire userview will be unavailable for this user.
In the future, we plan to add the ability to show data that the user does not have access to, replacing text, numbers, dates with asterisks (****).
This will allow the user to see the structure of the data, but not the data itself. It will also reduce the time for the solution administrator to configure and debug user views.
Manager (role) maintains Tasks (entity) in the system. Deny access to editing the "Director assessment" field for any tasks, and also prohibit reading this field for tasks that are maintained by people from the user's department
| Column | Value | |
|---|---|---|
| role_entity_id | user.manager.pm.tasks |
Entity for which the constraint is defined |
| column_name | assessment |
The "Director assessment" field, for which the restriction is defined |
| select | responsible = (SELECT id FROM base.people WHERE user = $$user_id LIMIT 1) |
The user sees the field only if he is in the "Responsible" |
| insert | false |
User can't fill in the field when inserting a new record |
| update | false |
The user cannot change the value of the field |
| check | false |
The user cannot change the value of the field |
admin schema for configuring rolesThe admin-schema contains a set of user views for setting up roles, as well as a procedure that allows you to generate a role template.
admin.roles_tableadmin.role_form with a list of parent roles, a list of users and a list of restrictions on entities for this roleadmin.role_entity_form with restrictions on actions on the record and a list of restrictions on entity fieldsadmin.generate_role_template allows you to generate a role template with default values for selected schemas or individual entities