How to list unshared records

Community Feedback
1

Hi,

If a user forget to share one of his password and lose its private key and/or his master password… the password is lost
Is it possible as an admin user to list these records (without the password) and so be able to remind user to share these records ?
Thank you

2

Hi @mdufourd this is an interesting question. If you are the admin in this scenario, are you restricted to a method internal to the app, or might your need be satisfied through some kind of a database query?

3

Hi @garrett
I am OK with a database query. I will put it in a cron and check the result with our nagios.

4

@mdufourd for example something like that?

Get the list of users with unshared passwords:

select username from users where id IN (select aro_foreign_key from permissions group by aco_foreign_key having count(aco_foreign_key) = 1);

Get the list of passwords that are not shared with anyone:

select name from resources where id IN (select aco_foreign_key from permissions group by aco_foreign_key having count(aco_foreign_key) = 1);
1 Like
5

Thanks

It works great. and Nagios raise the alarm accordingly.
Have a nice day
Marc

1 Like
6

Based on @remy’s response, I’ve upgraded the query to show both the resource and user names in the same query.

select resources.name, users.username
from permissions
    left join resources on permissions.aco_foreign_key = resources.id
    left join users on permissions.aro_foreign_key = users.id
group by permissions.aco_foreign_key
having count(permissions.aco_foreign_key) = 1
order by users.username;

The query will also list passwords only shared with a group, which may be a false positive - you can tell those because the username will be null.

EDIT: what follows is wrong. The where clause filters which rows get to the having, so the query really shows “which records are shared with a single user” totally ignoring shares with groups (ie, it will list a password that has a user AND a group). I leave it just for the record.

-- This query is wrong, don't use it!!
select resources.name, users.username
from permissions
    left join resources on permissions.aco_foreign_key = resources.id
    left join users on permissions.aro_foreign_key = users.id
where aro != "Group" -- <----- Here's THE mistake
group by permissions.aco_foreign_key
having count(permissions.aco_foreign_key) = 1
order by users.username;

It excludes resources shared with groups assuming that groups would have more than one user - which is not necessary true, but I didn’t want to further complicate the query.