Usuários que acessaram um tópico em uma categoria protegida

Comunidade Dados e relatórios
1

Preciso de uma ajuda. Não faço a menor ideia do que estou fazendo com SQL.

Sou administrador de um fórum do meu sindicato. Sou um representante eleito, mas não estou na liderança. O motivo de eu administrar o fórum é porque já tenho experiência com o Discourse; configurei-o para modernizar nossa comunicação interna (colaboração em equipe) e sou o único, entre nós 20, que realmente entende como fazer algo mais avançado em termos técnicos “nos bastidores”, além de usar um construtor de sites. O ponto é que nosso grupo é leigo em tecnologia, mas apenas como usuários finais, não nos bastidores.

Como este é um espaço de trabalho para todos nós, incluindo a liderança, criei algumas categorias para que trabalhem nelas, nas quais eu não deveria ter acesso para espionar. (Digamos que isso esteja acima do meu nível hierárquico).

Como não há uma maneira real de esconder conteúdo de um administrador, criei um componente de tema com CSS para:

  • ocultar os botões de mensagens nos perfis de usuário;
  • ocultar o botão de “impersonar” nos perfis de administrador;
  • ocultar a prévia dos tópicos das categorias na página de categorias;
  • ocultar a lista de tópicos dentro das categorias de todos.
    • Em seguida, conceder visibilidade das listas de tópicos apenas aos membros de um grupo (primário).

Resumindo, estou criando uma série de registros e trilhas de auditoria para estarem disponíveis caso queiram verificar se não estou espionando.

  1. Eles podem verificar nos logs do site se eu algum dia alterar (editar/desativar) esse componente de tema sem que eles saibam; podem perguntar o motivo e ver os detalhes.
  2. Se eu algum dia usar o recurso de “impersonar” ou visualizar mensagens, isso também aparecerá nos logs.

Basicamente, se eu algum dia alterar o CSS que escrevi para ocultar essas coisas, eles poderão ver e me pedir explicações.

  1. Se eu me adicionar ao grupo deles para que o conteúdo fique visível para mim, nos logs do grupo aparecerá que eu adicionei/removi a mim mesmo. Eles podem me pedir explicações.

O que preciso de ajuda:
Se eu algum vez visualizar a página com as folhas de estilo CSS desativadas, consigo ver os URLs dos tópicos. Obviamente, posso abrir os tópicos diretamente. O que eu quero é uma consulta do Data Explorer que eles (a equipe executiva, que todos têm acesso de administrador) possam executar de tempos em tempos, mostrando os tópicos visualizados dentro de uma categoria por qualquer usuário que não seja membro do grupo “executive”.

Eles não têm ideia de que isso pode ser feito, mas quero cobrir todas as bases. Quero tornar isso à prova de futuro, além de mim. O melhor que posso fazer com as ferramentas disponíveis.

Então, novamente, preciso de uma consulta do Data Explorer para:

  1. Se o usuário não fizer parte do grupo “executive”
  2. Então exibir os tópicos visualizados pelo(s) usuário(s) na categoria “EB” (ou ID da categoria: 34)

Isso, combinado com o “branqueamento” via CSS que apliquei, cria uma trilha de papel suficiente que indica que, se alguém (eu ou qualquer pessoa que não esteja na equipe executiva e que possa receber acesso de administrador no futuro) visualizou tópicos dentro dessa categoria e não faz parte da equipe executiva, essa pessoa realmente teve que se esforçar muito e foi uma espionagem intencional.

Alguém pode me ajudar com isso?

2

Acho que você deveria abrir um novo tópico sobre isso…

Mas, você pode tentar (e aprender SQL) :wink:

Ponto 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

Ponto 2

Não entendi seu ponto… o que você precisa?

Confira a lista de consultas e tente adaptar alguma consulta para sua necessidade.

3

Basicamente, escreva uma consulta para exibir qualquer tópico lido na categoria 1 ou 2 por qualquer usuário não atribuído ao grupo A.

4

Usuários que acessaram um tópico em uma categoria protegida

Esta consulta retornará todos os topic_users (usuários que visualizaram ou postaram em um tópico) que não são membros do grupo especificado no parâmetro :group_name da consulta, para tópicos em categorias que possuem esse grupo em suas configurações de segurança. Isso pode ser usado para identificar administradores que visualizaram um tópico em uma categoria à qual foram adicionadas permissões de grupo.

--[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