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 
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
Use case supported by the password expiry feature.
For more information, checkout this blog article: Passbolt’s New Automation of Shared Passwords Expiry