Working with grant-based roles

library(activityinfo)
library(dplyr)
library(tidyr)
library(purrr)

Introduction to user roles

This tutorial will provide a quick introduction on how to work with grant-based roles and permissions using the ActivityInfo R Package and secure your database and ensure user's can access exactly what they need. For a quick introduction, see "Understanding grant-based roles".

Note: in order to fully follow this tutorial you must have an ActivityInfo user account or a trial account with the permission to add a new database. Setup a free trial here: https://www.activityinfo.org/signUp

Exploring permissions, grants and operations

Each database has a single database owner. This user can view and change all elements of the database. If you do not invite any users, or make any forms public, than the database owner is the only person who can access a database.

To add a new database for this tutorial use addDatabase(label).


newDb <- addDatabase("A new ActivityInfo tutorial database!")

The database tree contains all the information we need about our database, including the owner information.


# fetch database information from the server 
dbTree <- getDatabaseTree(databaseId = newDb$databaseId)

as_tibble(dbTree$ownerRef)

# A tibble: 1 × 3

id	name	email

123456789	me@example.com{.email}	me@example.com{.email}

A rapid way to take a look at the default system roles is with the getDatabaseRoles(database) function that can take a database tree or database id as its parameter.


# extract roles from the tree as a data frame  
roles <- getDatabaseRoles(dbTree)

roles

# A tibble: 3 × 8

id	label	permissions	parameters	filters	grants	version	grantBased

dataentry	Data Entry	<list [0]>	<list [0]>	<list [0]>	<list [1]>	0	TRUE
readonly	Read only	<list [0]>	<list [0]>	<list [0]>	<list [1]>	0	TRUE
admin	Administrator	<list [3]>	<list [0]>	<list [0]>	<list [1]>	0	TRUE

Administrative permissions

In this table, special database-level permissions are provided in the "permissions" columns and the Administrator has all three administrative permissions:

manage users,
manage roles, and
manage automations.

A list of all administrative permissions can be expanded for users with those roles by using the tidyverse `tidyr` package.

library(tidyr)

roles |> 
  tidyr::unnest_longer(permissions) |> 
  tidyr::unnest_wider(permissions) |> 
  select(id, label, operation)

# A tibble: 3 × 3

id	label	operation

admin	Administrator	MANAGE_USERS
admin	Administrator	MANAGE_ROLES
admin	Administrator	MANAGE_AUTOMATIONS

Grants

Since 2024, all new roles are grant-based. Grants define the operations that are allowed for a specific resource. A resource can be any of the following:

a database,
a folder, or
a form.

One can list all resources that are granted by expanding the grants column as follows.


roles |> 
  select(id, label, grants) |> 
  tidyr::unnest_longer(grants) |> 
  tidyr::unnest_wider(grants)

# A tibble: 3 × 5

id	label	resourceId	optional	operations

dataentry	Data Entry	c2mhs8mm3632xsb2	FALSE	<list [6]>
readonly	Read only	c2mhs8mm3632xsb2	FALSE	<list [2]>
admin	Administrator	c2mhs8mm3632xsb2	FALSE	<list [16]>

In this case, all three roles have a single resource: the entire database. Each grant defines the resource in the "resourceId" column and a list with a number of "operations" allowed on the database.

Operations

To expand the grants and see the specific operations available for each resource within the role, do the following:


roles |> 
  select(id, label, grants) |> 
  # expand grants
  tidyr::unnest_longer(grants) |> 
  tidyr::unnest_wider(grants) |>
  # expand operations
  tidyr::unnest_longer(operations) |> 
  tidyr::unnest_wider(operations) |>
  
  select(id, label, resourceId, operation)

# A tibble: 24 × 4

id	label	resourceId	operation

dataentry	Data Entry	c2mhs8mm3632xsb2	VIEW
dataentry	Data Entry	c2mhs8mm3632xsb2	DISCOVER
dataentry	Data Entry	c2mhs8mm3632xsb2	EDIT_RECORD
dataentry	Data Entry	c2mhs8mm3632xsb2	ADD_RECORD
dataentry	Data Entry	c2mhs8mm3632xsb2	DELETE_RECORD
...	...	...	...

Retrieve and examine a single role

A single role can be retrieved from a database by id using getDatabaseRole(database, roleId) where the database can be the database tree or the database id.


# extract roles in a dataframe
readOnlyRole <- getDatabaseRoles(dbTree, "readonly")

str(readOnlyRole)

List of 8
 $ id         : chr "readonly"
 $ label      : chr "Read only"
 $ permissions: list()
 $ parameters : list()
 $ filters    : list()
 $ grants     :List of 1
  ..$ :List of 3
  .. ..$ resourceId: chr "c2mhs8mm3632xsb2"
  .. ..$ optional  : logi FALSE
  .. ..$ operations:List of 2
  .. .. ..$ :List of 3
  .. .. .. ..$ operation         : chr "VIEW"
  .. .. .. ..$ filter            : NULL
  .. .. .. ..$ securityCategories: list()
  .. .. ..$ :List of 3
  .. .. .. ..$ operation         : chr "DISCOVER"
  .. .. .. ..$ filter            : NULL
  .. .. .. ..$ securityCategories: list()
 $ version    : int 0
 $ grantBased : logi TRUE

Inspect invited user role assignments

We can also inspect which roles have been assigned to each user. This will not list the database owner.


dbUserRoles <- getDatabaseUsers(dbTree$databaseId) |> unnest_wider(role, names_sep = "_")

Creating roles

There are a number of helper functions that make the creation of user roles straightforward.

There are few important principles:

The role object is first created and then added to the database
There are separate functions for adding a new role addRole() and for updating an existing role updateRole()
When granting access to a resource, a resource id must be provided. Note that resource ids are not validated during role creation.

Resource-level access

A simple role that gives resource-level access can be quickly created.

This example grants access to a single data entry form. It does so by defining a grant to the form and the operations available to edit and view the form. The operations are defined with the resourcePermissions() function. In R use ??resourcePermissions to see the function definition.


dataEntryFormId <- "c12fsdkjla89" # replace with id of a form in your database

# create a role definition
dataEntryNoDeleteRole <- role(
  id = "entrynodelete",
  label = "Data entry without delete",
  grants = 
    list(
      grant(
        resourceId = dataEntryFormId, 
        permissions = resourcePermissions(
          view = TRUE,
          add_record = TRUE,
          edit_record = TRUE,
          delete_record = FALSE,
          export_records = TRUE),
          optional = FALSE
        )
      )
  )

# upload role to our database
addRole(dbTree$databaseId, dataEntryNoDeleteRole)

This second role is an admin with access to the entire database but without the "automation" permission.


# create a role definition
adminRoleNoAutomation <- role(
  id = "adminnoautomation",
  label = "Admin without automation",
  grants = 
    list(
      grant(
        resourceId = dbTree$databaseId, 
        permissions = resourcePermissions(
          view = TRUE,
          add_record = TRUE,
          edit_record = TRUE,
          delete_record = TRUE,
          export_records = TRUE,
          lock_records = TRUE,
          add_resource = TRUE,
          edit_resource = TRUE,
          delete_resource = TRUE,
          bulk_delete = TRUE,
          manage_collection_links = TRUE,
          manage_users = TRUE,
          manage_roles = TRUE,
          manage_reference_data = TRUE,
          manage_translations = TRUE,
          audit = TRUE,
          share_reports = TRUE,
          publish_reports = TRUE,
          reviewer_only = TRUE,
          discover = TRUE),
        optional = FALSE
        )
      ),
  permissions = 
    databasePermissions(
      manage_automations = FALSE,
      manage_users = TRUE,
      manage_roles = TRUE
    )
  )


# upload role to our database
addRole(dbTree$databaseId, adminRoleNoAutomation)

Update role with optional access to another form

We can modify our role so that we can optionally give access to another form. To modify a role, we need to change the definition and then use the updateRole() function to change the role in our database.


optionalFormId <- "c45fadgfla72" # the optional grant we will add

# create a role definition
dataEntryNoDeleteRole <- role(
  id = "entrynodelete",
  label = "Data entry without delete",
  grants = 
    list(
      grant(
        resourceId = dataEntryFormId, 
        permissions = resourcePermissions(
          view = TRUE,
          add_record = TRUE,
          edit_record = TRUE,
          delete_record = FALSE,
          export_records = TRUE),
          optional = FALSE
        ),
      grant(
        resourceId = optionalFormId, 
        permissions = resourcePermissions(
          view = TRUE,
          add_record = TRUE,
          edit_record = TRUE,
          delete_record = FALSE,
          export_records = TRUE),
          optional = TRUE
        )
      )
  )

# upload role to our database
updateRole(dbTree$databaseId, dataEntryNoDeleteRole)

See the documentation for role(), grant(), resourcePermissions() for the full description of these functions.

Record-level access control using parameters

Sometimes, it is necessary to be able to specify exactly which operations are available on a row by row basis. For example, a reporting partner may be able to use a form to submit reports and they may also have the ability to view and edit their own reports. However, they should not be able to view or edit reports of other partners.

Role creation in a single database with record-level permission filters is easiest within the ActivityInfo user-interface. See "Add your first parameter to a role".

Create partner form and reporting forms

We will create a list all reporting partners that we will use to assign a partner to users. We will also create a form for submitting reports.


## create a partner reference form
partnerForm <- formSchema(
  databaseId = dbTree$databaseId, 
  label = "Reporting Partners") |>
  addFormField(
    textFieldSchema(
      code = "name",
      label = "Partner name", 
      required = TRUE))

# upload form
addForm(partnerForm)
  
# create data frame with the name column for partners
partnerTbl <- tibble(name = c("Partner A", "Partner B", "Partner C"))
  
# import partner records into the form table
importRecords(partnerForm$id, data = partnerTbl)

We can examine the form records with getRecords(partnerForm):

# Form (id):          Reporting Partners (cktgbvem37c2xvj3)
# Total form records: 3
# Table fields types: Text
# Table filter:       NULL
# Table sort:         NULL
# Table Window:       No offset or limit
  `_id`              `_lastEditTime` `Partner name`
  <chr>                        <dbl> <chr>         
1 cx51safm37c3f5icog      1730985535 Partner A     
2 crv6qs1m37c3f5icoh      1730985535 Partner B     
3 c6lra77m37c3f5icoi      1730985535 Partner C

Next we create our reporting form. We will add a Partner field which references the partner form and fill it with the partner ids. Next we have to get the partner ids that were generated in the partner form so we can fill the reference field.

# create a reporting table with a reporting partner field
reportingForm <- formSchema(
  databaseId = dbTree$databaseId, 
  label = "Partner reports") |>
  addFormField(
    referenceFieldSchema(
      referencedFormId = partnerForm$id, 
      code = "rp", 
      label = "Partner", 
      required = TRUE)) |>
  addFormField(
    textFieldSchema(
      label = "Report", 
      required = TRUE))

addForm(reportingForm)

# get list of partner ids
partnerTbl <- getRecords(partnerForm) |>
  collect()
partnerIds <- partnerTbl[["_id"]]

partnerReports <- paste0("This is a report from ", partnerTbl[["Partner name"]], ".")

# create a reports table
reportingTbl <- tibble(
  Partner = partnerIds, 
  Report = partnerReports
  )

# import reports
importRecords(reportingForm$id, data = reportingTbl)

We can examine the form table using getRecords(reportingForm):

# Form (id):          Partner reports (cl0g9vkm37c3llt5)
# Total form records: 3
# Table fields types: c(Partner = "Reference", Report = "Text")
# Table filter:       NULL
# Table sort:         NULL
# Table Window:       No offset or limit
  `_id`              `_lastEditTime` Partner            Report                          
  <chr>                        <dbl> <chr>              <chr>                           
1 ctnab0jm37ccyvj20s      1730985980 cx51safm37c3f5icog This is a report from Partner A.
2 copxctim37ccyvj20t      1730985980 crv6qs1m37c3f5icoh This is a report from Partner B.
3 cclowium37ccyvj20u      1730985980 c6lra77m37c3f5icoi This is a report from Partner C.

Defining a reporting partner role

Using ActivityInfo formulas, we can specify row-level access rules. We assign each user a partner parameter in the role definition in order to do this. This parameter will be available in formulas using @user.partner.


partnerParameter <- parameter(id = "partner", label = "Partner", range = partnerForm$id)

The "Reporting Partner" role will also have access to view the partner reference form so that they can choose their own organization when creating a report.

They will have restricted row-level access to the reporting form for submitting and editing only their own reports. We'll use a formula to restrict access to the rows where the reporting partner is correct (rp == @user.partner).

We will also define an optional partner form grant, so the administrator can decide if they should have full access to the partner form and manage the full list of partners.

roleId <- "rp"
roleLabel <- "Reporting Partner"

partnerParameter <- parameter(id = "partner", label = "Partner", range = partnerForm$id)

# view entire database
databaseGrant <- grant(resourceId = dbTree$databaseId, 
               permissions = resourcePermissions(
                 view = TRUE,
                 edit_record = FALSE
               ))

# record level access: edit only records where @user.partner equals the partner form id
reportingFormGrant = grant(resourceId = reportingForm$id,
               permissions = resourcePermissions(
                 view = sprintf("%s == @user.partner", partnerForm$id),
                 edit_record = sprintf("%s == @user.partner", partnerForm$id),
                 discover = TRUE,
                 export_records = TRUE))

# optionally grant the user the right to edit records (but not delete records) in the partner table
partnerFormGrant = grant(resourceId = partnerForm$id,
               permissions = resourcePermissions(
                 view = TRUE,
                 discover = TRUE,
                 edit_record = TRUE), 
               optional = TRUE)

# define the partner role
reportingPartnerRole <- 
  role(
    id = roleId,
    label = roleLabel,
    parameters = list(
      partnerParameter
      ),
    grants = list(
      databaseGrant,
      reportingFormGrant,
      partnerFormGrant
      )
   ) 

# upload role to our database
addRole(dbTree$databaseId, reportingPartnerRole)

Assigning users a reporting partner role

We can assign new users to the database and give them reporting partner roles:

User A: Assign to Reporting Partner A so they can view and edit Partner A reports only
User B: Assign to Reporting Partner B so they can view and edit Partner B reports only. In addition, we will provide editing rights for the Reporting Partner table via the optional grant.



# fetch the partner id in order to set the parameter for each user role
partnerAId <- partnerTbl |> filter(`Partner name` == "Partner A") |> pull(`_id`)
partnerBId <- partnerTbl |> filter(`Partner name` == "Partner B") |> pull(`_id`)

# user A has access to Reporting Partner A reports only
userA <- addDatabaseUser(
    databaseId = dbTree$databaseId, 
    email = "user.a@example.com", 
    name = "User A", 
    locale = "en", 
    roleId = "rp", 
    roleParameters = list(partner = partnerAId), 
  )

# user B has access to Partner B reports only and has been provided the optional access to the partnerForm using the partner form id and the roleResources parameter
userB <- addDatabaseUser(
  databaseId = dbTree$databaseId,
    email = "user.b@example.com", 
    name = "User B", 
    locale = "en", 
    roleId = "rp", 
    roleParameters = list(partner = partnerBId), 
    roleResources = list(partnerForm$id)
  )

Add users and assign roles

Adding users and assigning a role can be quickly achieved using addDatabaseUser().


addDatabaseUser(
  databaseId = dbTree$databaseId, 
  name = "Tutorial database user", 
  email = "activityinfo1@example.com", 
  locale = "en", 
  roleId = "readonly"
  )
addDatabaseUser(
  databaseId = dbTree$databaseId, 
  name = "Tutorial database user",
  email = "activityinfo2@example.com", 
  locale = "en", 
  roleId = "dataentry"
  )

# Retrieve a list of all the database users
dbUsers <- getDatabaseUsers(databaseId = newDb$databaseId)

dbUsers

For a fuller example using optional grants and parameters for row-level access, see the previous section on creating roles. There are also advanced tutorials for bulk adding users with grants and parameters.

List roles across all databases

It is also possible to compile a list of all roles across all databases at once.


# get a dataframe of all the databases
databases <- getDatabases()

# combine all the roles into a single table
allRoles <- 
  lapply(
    1:nrow(databases), 
    function(x) {
      getDatabaseRoles(databases[x,]$databaseId) |> 
        mutate(
          databaseId = databases[x,]$databaseId, 
          databaseLabel = databases[x,]$label
          )
      }
    ) |>
  bind_rows()

Using the tidyverse purrr package, this can be shortened further.

library(purrr)

allRoles <- purrr::pmap_dfr(databases, function(databaseId, label, ...) {
  getDatabaseRoles(databaseId) |>
    dplyr::mutate(
      databaseId = databaseId,
      databaseLabel = label
    )
})

List all users and their roles across all databases

To list all users and all roles across all databases to which you have access, we first need to make a function that uses getDatabaseUsers() to collect the list of users. It is setup to fail gracefully if one does not have permission to get the database users for any of the databases.


# get a list of database ids
dbIds = getDatabases()[["databaseId"]]

# define a function to try to get database users as a data frame and otherwise fail gracefully by returning NULL
getUsersGracefully <- function(databaseId) {
  try(
    return(getDatabaseUsers(databaseId))
  )
  return(NULL)
}

allUsers <- lapply(dbIds, getUsersGracefully) |>
  bind_rows() |> 
  as_tibble()


allUserRoles <- allUsers |> unnest_wider(role, names_sep = "_")

One can further filter all reporting partners across all databases assuming the reporting partner role has the same id in each database.


# filter all users by the role id
allUserRoles |> filter(role_id == "rp")

Additionally, one can filter all users by some specific operation, such as EDIT_RECORD. In order to do this, it is also important to use the allRoles data frame we defined in the previous section.


# filter all users by the role id
userRoleDefinitions <- 
  allUserRoles |> 
  left_join(allRoles, by = c("databaseId", role_id = "id")) |>
  select(databaseId, userId, name, role_id, grants)

# expand grants and operations per resource
userOperations <- userRoleDefinitions |>
  select(databaseId, userId, name, role_id, grants) |> 
  # expand grants
  tidyr::unnest_longer(grants) |> 
  tidyr::unnest_wider(grants) |>
  # expand operations
  tidyr::unnest_longer(operations) |> 
  tidyr::unnest_wider(operations)

# show users who have the EDIT_RECORD operation and on which resources
userOperations %>% filter(operation == "EDIT_RECORD")

Find users with legacy roles

Legacy roles are marked for deprecation in the near future. Before legacy roles are phased out, it is important to migrate users with legacy roles to grant-based roles. Thankfully, it is simple to find all users with legacy roles by doing the following with the allUserRoles and allRoles variables defined in the previous two sections.


usersWithLegacyRoles <- 
    allUserRoles |> 
    left_join(allRoles, by = c("databaseId", role_id = "id")) |>
    select(databaseId, userId, name, role_id, grants, grantBased) |>
    filter(grantBased == FALSE)

List all forms and database resources

It is possible to access the list of all the database resources with the function getDatabaseResources(databaseTree).


# Retrieve a list of all the resources (forms, sub-forms)
dbResources <- getDatabaseResources(databaseTree = dbTree)

dbResources

Next item

Add and manipulate databases and forms using R

Working with grant-based roles

Related videos