Table page should show everyone with permissions against that table

[simonw]

It shows groups and users in a grid right now, but it doesn't show a list of ALL users who can do things on that table including via their groups.

[simonw]

files-to-prompt datasette_acl/__init__.py | llm -m claude-3.5-sonnet --system 'SQL query for listing every user who can perform :action_name against :resource_id including both users with direct permission and users with permission via one of their groups. Results should include that information, so I can tell if the user has permission directly or which groups it is via - as a JSON array of groups'

https://gist.github.com/simonw/d4fb619a0b51e5ce1090d2e44061ea9d

I modified it to return null for direct ones:

WITH resource_id AS (
    SELECT id FROM acl_resources
    WHERE database = :database AND resource = :resource
),
action_id AS (
    SELECT id FROM acl_actions
    WHERE name = :action_name
),
direct_permissions AS (
    SELECT actor_id, NULL AS group_id
    FROM acl
    WHERE resource_id = (SELECT id FROM resource_id)
    AND action_id = (SELECT id FROM action_id)
    AND actor_id IS NOT NULL
),
group_permissions AS (
    SELECT ag.actor_id, a.group_id
    FROM acl a
    JOIN acl_actor_groups ag ON a.group_id = ag.group_id
    WHERE a.resource_id = (SELECT id FROM resource_id)
    AND a.action_id = (SELECT id FROM action_id)
    AND a.group_id IS NOT NULL
),
all_permissions AS (
    SELECT actor_id, group_id FROM direct_permissions
    UNION ALL
    SELECT actor_id, group_id FROM group_permissions
)
SELECT 
    ap.actor_id,
    CASE 
        WHEN ap.group_id IS NULL THEN NULL
        ELSE json_group_array(g.name)
    END AS permission_source
FROM all_permissions ap
LEFT JOIN acl_groups g ON ap.group_id = g.id
GROUP BY ap.actor_id
ORDER BY ap.actor_id;

[simonw]

Mucked around a bit and got to this query:

WITH direct_permissions AS (
    SELECT a.resource_id,
      a.actor_id, ac.name AS action_name, NULL AS group_id
    FROM acl a
    JOIN acl_actions ac ON a.action_id = ac.id
    WHERE a.actor_id IS NOT NULL
),
group_permissions AS (
    SELECT a.resource_id, 
      ag.actor_id, ac.name AS action_name, g.name AS group_name
    FROM acl a
    JOIN acl_actions ac ON a.action_id = ac.id
    JOIN acl_groups g ON a.group_id = g.id
    JOIN acl_actor_groups ag ON a.group_id = ag.group_id
    WHERE a.group_id IS NOT NULL
),
all_permissions AS (
    SELECT resource_id, actor_id, action_name, group_id AS group_name FROM direct_permissions
    UNION ALL
    SELECT resource_id, actor_id, action_name, group_name FROM group_permissions
)
SELECT 
    resource_id, 
    action_name,
    actor_id,
    json_group_array(
        distinct group_name
    ) AS permission_source
FROM all_permissions
GROUP BY resource_id, action_name, actor_id
ORDER BY resource_id, action_name, actor_id;

It appears to show actors across ALL resources and actions, with an array of either groups that give them that permission on null for if they have direct permission.

[simonw]

If I return "**direct**" as the string there (and ban that as a group name) I could use a view and standard Datasette JSON faceting to build a really neat interface here. I'd have to solve exposing views from the internal.db table first though.

[simonw]

llm -c 'Modify that query so it only takes resource_id as an argument, and returns all users who have any access on the table with columns action_name, actor_id and permission_source - that permission_source should always be an array, strings for group names and null if the user is a direct permission user for that thing'

I eventually got to this code:

# resource_id, action, actor_id, grant_sources
# 1, alter-table, simon, ["staff","dev", null]
# Would indicate simon has alter-table on resource 1
# thanks to a direct user grant (null) + through two groups
GRANT_SOURCES_CTE = """
with direct_permissions as (
    select
        acl.resource_id,
        acl.actor_id,
        acl_actions.name as action,
        null as group_name
    from acl
    join acl_actions on acl.action_id = acl_actions.id
    where acl.actor_id is not null
),
group_permissions as (
    select
        acl.resource_id,
        acl_actor_groups.actor_id,
        acl_actions.name as action,
        acl_groups.name as group_name
    from acl
    join acl_actions on acl.action_id = acl_actions.id
    join acl_groups on acl.group_id = acl_groups.id
    join acl_actor_groups on acl.group_id = acl_actor_groups.group_id
    where acl.group_id is not null
),
all_permissions as (
    select
        resource_id, actor_id, action, group_name
    from direct_permissions
    union all
    select
        resource_id, actor_id, action, group_name
    from group_permissions
),
grant_sources as (
    select
        resource_id,
        action,
        actor_id,
        json_group_array(
            distinct group_name
        ) as grant_source
    from all_permissions
    group by resource_id, action, actor_id
    order by resource_id, action, actor_id
)
"""


async def grant_sources(datasette, resource_id=None):
    sql = GRANT_SOURCES_CTE
    sql += "\nselect * from grant_sources"
    args = {}
    if resource_id is not None:
        sql += " where resource_id = :resource_id"
        args["resource_id"] = resource_id
    db = datasette.get_internal_database()
    result = await db.execute(sql, args)
    return [dict(r, grant_source=json.loads(r["grant_source"])) for r in result.rows]

Which returns results like this:

[
  {
    "action": "delete-row",
    "actor_id": "simon",
    "grant_source": [
      "staff"
    ],
    "resource_id": 2
  },
  {
    "action": "drop-table",
    "actor_id": "simon",
    "grant_source": [
      null
    ],
    "resource_id": 2
  },
  {
    "action": "insert-row",
    "actor_id": "peanut",
    "grant_source": [
      null
    ],
    "resource_id": 2
  },
  {
    "action": "insert-row",
    "actor_id": "roo",
    "grant_source": [
      null
    ],
    "resource_id": 2
  },
  {
    "action": "insert-row",
    "actor_id": "simon",
    "grant_source": [
      null,
      "staff"
    ],
    "resource_id": 2
  },
  {
    "action": "update-row",
    "actor_id": "simon",
    "grant_source": [
      "staff"
    ],
    "resource_id": 2
  },
  {
    "action": "update-row",
    "actor_id": "tanko",
    "grant_source": [
      "sales"
    ],
    "resource_id": 2
  },
  {
    "action": "update-row",
    "actor_id": "terry",
    "grant_source": [
      "sales"
    ],
    "resource_id": 2
  },
  {
    "action": "update-row",
    "actor_id": "tom",
    "grant_source": [
      "sales"
    ],
    "resource_id": 2
  }
]

For this table:

But I've decided to go a different direction for the logic to display things on this page.

[simonw]

The question to answer here is "what is the full list of users who are allowed to do thing X, including users who gained that ability through a group"

Some options for how to display that:

• List of groups, full list of users, no indication of which users came from which group
• List of groups, for each group a list of users, then a list of direct users. This could show users multiple times but does at least express ALL of the information

I'm leaning to the second option. Even if we have teams with hundreds of groups with thousands of members I think that will still be readable enough, and I expect most Datasette instances will have less than 100 members total.

[simonw]

One other option here: if it turns out the list of users for insert-row and update-row and delete-row are exactly the same (which I imagine will happen a lot) I could render that as:

insert-row, update-row, delete-row

• Groups:
  • sales: sam, tom, terry
  • dev: barry, simon
• Users:
  • simon

[simonw]

Should that format show the combined users in a single list? Not unless someone asks for it, I think we're OK without that.