As an admin want to see/rotate all the passwords that were provided to leaving user

How to see all passwords currently and previously shared to dedicated user and accessed by the user? When someone leaves company I as an admin want to change all the passwords was provided to leaving user by any user in Passbolt

See. As a company owner, I can retrieve any secret stored in the company passbolt instance

See. As an admin, retain an escrow copy of users' private keys

thanks Remy, but my question is different

How to see all passwords currently and previously shared to dedicated user and accessed by the user? When someone leaves company I as an admin want to change all the passwords was provided to leaving user by any user in Passbolt?

Hello @shorkin,

Fair enough. At the moment it is not possible (unless you create a custom report in SQL). What you describe will be included as part of a second round of improvements to audit logs.

I’m putting this issue in the backlog as it’s not explicitly included in the other ticket, even though there is an overlap.

Hi,
this is not a necrobump :slight_smile:
I have the exact same need, and I would like to share the SQL custom query I created.

It may benefit others, until it may become available in the product.

Here it comes, it may probably be improved.

You will notice NOT IN ('some-resources-id-to-ignore') and username NOT IN ('an_old_crappy_name_diring_tests') … that allow to reject some resources and usernames fro mthe query if need be. Otherwise they can be removed.


SELECT 
   *
FROM
   (SELECT 
       left_r.r_name r_name,
           left_r.r_username r_username,
           MAX(modified) secret_update_date,
           left_r.sa_created last_access,
           left_r.accessing_user
   FROM
       secrets s
   JOIN (SELECT 
       sa.created sa_created,
           u.username accessing_user,
           r.name r_name,
           r.username r_username,
           r.id r_id
   FROM
       secret_accesses sa
   LEFT JOIN users u ON sa.user_id = u.id
   LEFT JOIN resources r ON r.id = sa.resource_id
       AND r.id NOT IN ('some-resources-id-to-ignore')
   WHERE
       u.id IN (SELECT 
               uu.id
           FROM
               passbolt.users uu
           WHERE
               uu.username NOT IN ('an_old_crappy_name_during_tests')
                   AND uu.deleted = 1
                   AND uu.active = 1
                   AND uu.username NOT IN (SELECT 
                       uuu.username
                   FROM
                       passbolt.users uuu
                   WHERE
                       uuu.deleted = 0 AND uu.active = 1))
   GROUP BY r.id) AS left_r ON left_r.r_id = s.resource_id
   WHERE
       s.modified > s.created
   GROUP BY s.resource_id UNION SELECT 
       left_r.r_name r_name,
           left_r.r_username r_username,
           MIN(s.created) secret_update_date,
           left_r.sa_created last_access,
           left_r.accessing_user
   FROM
       secrets s
   JOIN (SELECT 
       sa.created sa_created,
           u.username accessing_user,
           r.name r_name,
           r.username r_username,
           r.id r_id
   FROM
       secret_accesses sa
   LEFT JOIN users u ON sa.user_id = u.id
   LEFT JOIN resources r ON r.id = sa.resource_id
       AND r.id NOT IN ('some-resources-id-to-ignore')
   WHERE
       u.id IN (SELECT 
               uu.id
           FROM
               passbolt.users uu
           WHERE
               uu.username NOT IN ('an_old_crappy_name_during_tests')
                   AND uu.deleted = 1
                   AND uu.active = 1
                   AND uu.username NOT IN (SELECT 
                       uuu.username
                   FROM
                       passbolt.users uuu
                   WHERE
                       uuu.deleted = 0 AND uu.active = 1))
   GROUP BY r.id) AS left_r ON left_r.r_id = s.resource_id
   WHERE
       s.modified = s.created
           AND s.resource_id NOT IN (SELECT 
               s.resource_id
           FROM
               secrets s
           WHERE
               s.modified > s.created)
   GROUP BY s.resource_id) AS lgrq
WHERE
   lgrq.secret_update_date < lgrq.last_access 
UNION SELECT 
   r.name r_name,
   r.username r_username,
   s.modified s_modified,
   NULL last_access,
   uss.username accessing_user
FROM
   permissions AS p
       JOIN
   resources AS r ON p.aco_foreign_key = r.id
       JOIN
   secrets s ON r.id = s.resource_id
       JOIN
   users uc ON uc.id = r.created_by
       JOIN
   users uss ON uss.id = s.user_id
       JOIN
   users um ON um.id = r.modified_by
       JOIN
   groups gs ON gs.id = p.aro_foreign_key
       LEFT JOIN
   users us ON us.id = p.aro_foreign_key
WHERE
   (r.modified = s.modified
       AND s.user_id = r.created_by)
       AND p.aco = 'Resource'
       AND r.id IN (SELECT 
           rr.id
       FROM
           resources rr
               LEFT JOIN
           users u ON rr.created_by = u.id
               LEFT JOIN
           secrets s ON s.resource_id = rr.id
       WHERE
           u.id IN (SELECT 
                   id
               FROM
                   passbolt.users
               WHERE
                   username NOT IN ('an_old_crappy_name_diring_tests')
                       AND deleted = 1
                       AND active = 1
                       AND username NOT IN (SELECT 
                           username
                       FROM
                           passbolt.users
                       WHERE
                           deleted = 0 AND active = 1))
               AND r.id NOT IN ('some-resources-id-to-ignore')
       GROUP BY rr.id)
GROUP BY p.aco_foreign_key , s.user_id;

1 Like