Users who have accessed a topic in a protected category

data & reporting
1

I could use some help. I don’t know my head from my butt with SQL.

I am admining a board for my labor union. I’m an elected rep, but I’m not in leadership. The reason I’m admining it is because I have experience with discourse already, I set it up to modernize how we communicate internally (team collaboration), and I’m the only one at the moment (of 20 of us) who really has any clue how to do anything more advanced in a tech related behind the scenes capacity greater than using a web builder to build a website. Point is our group is end user tech savy, but not “behind the scenes.”

As this is a work space for all of us, including leadership, I’ve setup a couple categories for them to work in which I shouldn’t be able to snoop in. (call it above my pay grade).

Since there’s no real way to hide content from an admin, I created a theme component with CSS to:

  • hide messages buttons in user profiles
  • hide impersonate button in admin profiles
  • hide topic preview of categories on categories page.
  • hide the topic list within the categories from everyone.
    • Then add visibility to the topic lists to members a (primary) group.

Bottom line, I’m creating a series of log and audit trails to be available should they wish to check that I’m not snooping.

  1. they can look and see in site logs if i ever touch that theme component (edit/disable) without them being aware, they can ask why and see detail
  2. if I ever used the impersonate or view message feature, that would be in the logs as well.

Basically, If I ever tamper with the CSS I wrote to hide this stuff they can see and ask me to explain myself.

  1. if I added myself to their group so the content is visible to me, in the group logs it shows me adding/removing myself in the logs.they can ask me to explain myself

What I need help with:
if I ever view the page with the css style-sheets disabled, I can see the URLs to the topics. Then obviously I can open the topics directly. What I want is a data explorer query they (executive team who all get admin access) can run from time to time that shows topics viewed within a category by any user not a member of the “executive” group.
They have no idea this can be done, but I want every base covered. I want to future proof this beyond me. As best as I can with the tools available.

So again, I need a data explorer query to do:

  1. if user is not part of group "executive"
  2. then display topics viewed by user(s) in category “EB” (or category id: 34)

This combined with the CSS white out I’ve applied creates enough of a paper trail that says if someone (me or anyone not on the executive team who may be given admin access in the future), has viewed topics within that category and they are not part of the executive team, they really have had to go out of their way and it’s intentional snooping.

Can anyone help with this?

2

I think you should open new topic about this…

But, you may try (and learn SQL) :wink:

Point 1:

-- [params]
-- string :gname = executive
-- string :uname = SidV
SELECT u.username, g.id as "group id", g.name
FROM group_users gu, users u, groups g
WHERE g.id = gu.group_id
AND u.id = gu.user_id
AND g.name ILIKE :gname
AND u.username ILIKE :uname

Point 2

I can’t understand your point… what do you need?

Check the query list and try to adapt some query for your need.

3 Likes
3

Basically write a query to display any topics read in category 1 or 2 by any user not assigned to group A

4

Users who have accessed a topic in a protected category

This query will return all topic_users (users who have viewed or posted in a topic) who are not members of the group that is given for the query’s :group_name, for topics in categories that have the group in their security settings. This can be used to find admins who have viewed a topic in a category that has group permissions added to it.

--[params]
-- string :group_name

WITH allowed_users AS (
SELECT user_id
FROM group_users gu
JOIN groups g
ON g.id = gu.group_id
WHERE g.name = :group_name
),
group_categories AS (
SELECT category_id
FROM category_groups cg
JOIN groups g
ON g.id = cg.group_id
WHERE g.name = :group_name
)

SELECT
tu.user_id,
tu.topic_id,
tu.last_visited_at::date
FROM topic_users tu
JOIN topics t
ON t.id = tu.topic_id
WHERE t.category_id IN (SELECT category_id FROM group_categories)
AND tu.user_id NOT IN (SELECT user_id FROM allowed_users)
ORDER BY t.category_id
4 Likes