Hi,
As admin, I’m looking for an option to verify what folders are shared by a link (which are usally shared with external users, that are not members in my nextcloud).
So far, I only can check what folders I have shared. But is there an option to monitor / log and administrate the folders that are shared as a link by ALL user?
I would like to have a view what folders are shared with who for all users and all folders.
You can do an sql query to get all the links. You can also add this app: https://apps.nextcloud.com/apps/sharelisting and do a script via bash to check and list shares.
If you want to see what happens with all the public links you can either write an sql query or use this: https://apps.nextcloud.com/apps/files_trackdownloads to see downloads of public links.
Thx, I’ll try that. I was hoping there would be an easier / build in option, log-file or app for that. Since it would be nice, if an Admin could see (for security reasons) all the links/folders that are shared by the users - especially with external parties.
the sharelisting is mostly what you want since it is an app that you can call from the cli.
I installed the app, but I can not find neither a dashboard/view nor a link to a specific command line.
Could you please give me a hint how to use the app?
sudo -u $WWWUSER /path/to/nextcloud/occ sharing:list
It has some options as well to check for listings by a user and so on as well
thx a lot - that helped! 
I will try to test other commands from the readme as well. Too bad, nothing similar is already integrated in the admin dashboard.
Hello Everyone,
The solution provided by SmallOne was nearly perfect.
Unfortunately the sharing:list does not offer the ability to export the Expiry Date of a link.
We like to review Shares Created by users so that we can tell them to delete it when it is no longer needed (especially on Shares by Link that do not require password to be used this is very important)
Is there any alternative way to get a list of shares (with expiry date included)?
I even checked the MySql underlying tables but somehow I could not see the column where this information is saved.
Thank you and sorry for reviving this old post, but I thought having the info in one place may be useful for others that need the same.
I sorted this out running below query in MySQL.
Thank you anyway.
select
sh.id,
(case
when share_type = 0 then ‘USER’
when share_type = 1 then ‘GROUP’
when share_type = 3 then ‘LINK’
when share_type = 4 then ‘EMAIL’
when share_type = 6 then ‘REMOTE’
when share_type = 7 then ‘CIRCLE’
when share_type = 8 then ‘GUEST’
when share_type = 9 then ‘REMOTE_GROUP’
when share_type = 10 then ‘ROOM’
else ‘Unknown’
end) as ShareTypeDescription,
JSON_UNQUOTE(JSON_EXTRACT(ow.data, “$.displayname.value”)) ownerName,
owldap.ldap_dn OwnerNameLDAP,
JSON_UNQUOTE(JSON_EXTRACT(ini.data, “$.displayname.value”)) initiatorName,
inildap.ldap_dn initiatorNameLDAP,
(case
#USER
when share_type = 0 then
(select JSON_UNQUOTE(JSON_EXTRACT(ash.data, “$.displayname.value”)) ownerName from oc_accounts ash where ash.uid = sh.share_with)
#GROUP
when share_type = 1 then
sh.share_with
#LINK
when share_type = 3 then
‘PUBLIC LINK’
#EMAIL
when share_type = 4 then
sh.share_with
#REMOTE
when share_type = 6 then
sh.share_with
#CIRCLE
when share_type = 7 then
sh.share_with
#GUEST
when share_type = 8 then
sh.share_with
#REMOTE_GROUP
when share_type = 9 then
sh.share_with
#ROOM
when share_type = 10 then
sh.share_with
else ‘Unknown’
end) as ShareWithDescription,
(case
when sh.permissions = 31 then ‘ALL’
when sh.permissions = 0 then ‘NONE’
else MAKE_SET(sh.permissions, ‘READ’, ‘UPDATE’,‘CREATE’, ‘DELETE’, ‘SHARE’, ‘ALL’)
end) PermissionDescription,
sh.password, sh.parent, sh.item_type, sh.item_source, sh.item_target,
sh.file_source, sh.file_target, FROM_UNIXTIME(sh.stime) linkDate, sh.expiration,
CONCAT(‘https://nextcloud.mysite.com/s/’, token) shareLink,
(
Case
when
(
select min(emnt.mount_point)
from
oc_mounts mnt,
oc_external_mounts emnt
where mnt.mount_id = emnt.mount_id
and mnt.user_id = sh.uid_owner
and mnt.storage_id = fc.storage
) is not null
then
CONCAT(
(
select min(emnt.mount_point)
from
oc_mounts mnt,
oc_external_mounts emnt
where mnt.mount_id = emnt.mount_id
and mnt.user_id = sh.uid_owner
and mnt.storage_id = fc.storage
), ‘/’, fc.path)
else
CONCAT(’/Home/’, fc.path)
end) fullPath
from oc_share sh
left outer join oc_accounts ow on sh.uid_owner = ow.uid
left outer join oc_ldap_user_mapping owldap on sh.uid_owner = owldap.directory_uuid
left outer join oc_accounts ini on sh.uid_initiator = ini.uid
left outer join oc_ldap_user_mapping inildap on sh.uid_initiator = inildap.directory_uuid,
oc_filecache fc, oc_storages s
where
sh.file_source = fc.fileid and fc.storage = s.numeric_id
order by ini.data, sh.id
I think the most users in this thread have not understand the functions of Nextcloud. I think is is not the assignment of the admin to check hat the users share their or the company data correctly. Then you also must control in your company every e-mail attachment.
I think it is better to teach your users in self-controlling the shares.
Hello Devnull,
You may be right in a normal or home environment but if you have to comply with SOX regulatory and Internal / External audits you can’t really avoid having to monitor what links are left / created by user.
Take a user that has left a Company but has created a Public Link hooked into a company shared drive containing sensitive data before leaving.
Without having an Admin to monitor (and eventually remove those shares) you would basically allow unauthorized users still accessing your information indefinitely.
Remember public shares do not disappear when a user is deactivated in Nextcloud.
Thank you
Yes. And in the audit you control email attachments? Or is email no problem because it is 60 years old and cloud is only a hype?
The difference between an Email and Nextcloud is that once we deactivate his account the user loses the ability to access new content (new emails)
With the shares by link unless you control what’s been shared, access isn’t revoked (even if you deactivate the user in Nextcloud) and users continue to be able to access shared drives even AFTER they are gone .
When used in combination with SMB Shares leaving access open to old users will definitely fail your Audits (or you could be exposed to loss of critical information such as quotes, contracts etc.)
Not trying to convince you (plus I have a solution now) but for us, it is very important to prevent access to shared drives when users go away (and yes, we also deactivate user Email access)
After user leaves the company you should delete all shares.
Correct, I agree with you and without this report (to monitor shares on a global level) there’s no way to prove this was done. This is precisely why this is needed.
Auditors will ask for this prove. IT Departments will need it to check or even build reminders.
We do the same with in other areas (our Active Directory for example).
We interfaced AD with our HR system (SAP) so that IT gets an alert when an Employee is about to leave the company and if IT has forgotten to deactivate the user.
You also need it because not all shares may need to be deleted.
Some may need some share to have their ownership transferred (shares that give access to folders to other users but they are not public links).
Yes this is all correct. But with deleting the user in Nextcloud you solve a lot of this problems.
Hey @alessandrodimarco ,
i strongly agree with you. Devnull is of course right to first educate the users to make their and your life easier. but if the case like you describes happens and data is “stolen” after a person left already the company it is a bad thing. shareholders could sue the management of the company if no security measures are in place like you added.
Btw. how did you solve this? I’m looking also for a solution here. could you share it?
thx
Hi,
@alessandrodimarco just found your code and tried to run it on NC 20.0.9 MariaDB 10.3.27 with no luck (error: "ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LINK’
when share_type = 4 then
sh.share_with
when share_type = 6 then
sh…’ at line 28
")
Could you help please?
how to indentify username of $WWWUSER on my server?
@angaros. The issue is the typographic quotes. Here is a fixed version of @alessandrodimarco snippet (works for on NC 22):
select
sh.id,
(case
when share_type = 0 then 'USER'
when share_type = 1 then 'GROUP'
when share_type = 3 then 'LINK'
when share_type = 4 then 'EMAIL'
when share_type = 6 then 'REMOTE'
when share_type = 7 then 'CIRCLE'
when share_type = 8 then 'GUEST'
when share_type = 9 then 'REMOTE_GROUP'
when share_type = 10 then 'ROOM'
else 'Unknown'
end) as ShareTypeDescription,
JSON_UNQUOTE(JSON_EXTRACT(ow.data, "$.displayname.value")) ownerName,
owldap.ldap_dn OwnerNameLDAP,
JSON_UNQUOTE(JSON_EXTRACT(ini.data, "$.displayname.value")) initiatorName,
inildap.ldap_dn initiatorNameLDAP,
(case
when share_type = 0 then
(select JSON_UNQUOTE(JSON_EXTRACT(ash.data, "$.displayname.value")) ownerName from oc_accounts ash where ash.uid = sh.share_with)
when share_type = 1 then
sh.share_with
when share_type = 3 then
'PUBLIC LINK'
when share_type = 4 then
sh.share_with
when share_type = 6 then
sh.share_with
when share_type = 7 then
sh.share_with
when share_type = 8 then
sh.share_with
when share_type = 9 then
sh.share_with
when share_type = 10 then
sh.share_with
else 'Unknown'
end) as ShareWithDescription,
(case
when sh.permissions = 31 then 'ALL'
when sh.permissions = 0 then 'NONE'
else MAKE_SET(sh.permissions, 'LIRE', 'MODIFIER','CRÉER', 'SUPPRIMER', 'REPARTAGER', 'TOUT')
end) PermissionDescription,
sh.password, sh.parent, sh.item_type, sh.item_source, sh.item_target,
sh.file_source, sh.file_target, FROM_UNIXTIME(sh.stime) linkDate, sh.expiration,
CONCAT('https://nextcloud.mysite.com/s/ 5', token) shareLink,
(
Case
when
(
select min(emnt.mount_point)
from
oc_mounts mnt,
oc_external_mounts emnt
where mnt.mount_id = emnt.mount_id
and mnt.user_id = sh.uid_owner
and mnt.storage_id = fc.storage
) is not null
then
CONCAT(
(
select min(emnt.mount_point)
from
oc_mounts mnt,
oc_external_mounts emnt
where mnt.mount_id = emnt.mount_id
and mnt.user_id = sh.uid_owner
and mnt.storage_id = fc.storage
), '/', fc.path)
else
CONCAT('/Home/', fc.path)
end) fullPath
from oc_share sh
left outer join oc_accounts ow on sh.uid_owner = ow.uid
left outer join oc_ldap_user_mapping owldap on sh.uid_owner = owldap.directory_uuid
left outer join oc_accounts ini on sh.uid_initiator = ini.uid
left outer join oc_ldap_user_mapping inildap on sh.uid_initiator = inildap.directory_uuid,
oc_filecache fc, oc_storages s
where
sh.file_source = fc.fileid and fc.storage = s.numeric_id
order by ini.data, sh.id