How to easily list all shared files and access/permissions users/groups have to the files?

kimsyversen commented 7 years ago

Issue

As an admin, How can you easily list all shared files and what access users/groups have to the files (or vice versa)?

Background and other information

First I would like to thank the community and all the developers for their work on Nextcloud. I think Nextcloud is very good. However I find it difficult to audit accesses to files/folders and this is a very important feature that Nextcloud should have.

All private users and enterprises should have control of their data in Nextcloud and be able to audit accesses to files/folders in a reasonable amount of time. Especially enterprises need to protect their files and verify that all given accesses are correct. Assume there are 20 groups and 200 users with direct and indirect (access by inheritance) read/write access to different folders/files. How do you audit accesses and permissions? It might be hundreds of files and folders.

I have tried to find possible ways to solve this, but I have not found any good solution (yet). First, please correct me if i'm wrong. Based on what I know, there is not implemented any functionality that can easily show this mix of directly/indirectly shared folders/files. I know it is possible to impersonate users, but this is not a feasible solution if you have hundreds of users.

Does it exist a solution for this? It does not need to be implemented with UI - one could run a script locally on the server to e.g. create a csv file containing all the information.

The oc_share table in the database seem to show files/folders and what users/groups they are directly shared with (including permissions), but are missing important information explained in the following example.

Example

I installed Nextcloud 12 and added multiple users and the two groups ParentGroup and ChildGroup. Then I added the following files and folders as the admin user.

admin/files
└── ParentFolder
    ├── ChildFolder
    │   ├── Screen Shot 2017-06-30 at 16.10.45.png
    │   └── wizard.php
    └── Important Picture.png

I have applied the following sharings:

ParentFolder is shared with ParentGroup. ParentGroup have one member (kim)
Important Picture.png is shared with ParentGroup (easy to see in the picture)
ChildFolder is directly shared with ChildGroup. ChildGroup have two members arne and bjarne (difficult to see in the picture). ChildFolder is also directly shared with the users halfdan and katrine (easy to see in the picture)

Then I compared the shared files/folders against the oc_share table:

The oc_share table in the database seem to show files/folders and what users/groups they are directly shared with (including permissions). I found out that the permission is changed to 17 (reshare), 21 (reshare, change), 23 (reshare, create, change), and 31 (reshare, create, change and delete).

However, if files are indirectly shared via groups (by inheritance), it is difficult to see it. In this case you see that Important Picture.png is shared with ParentGroup only because it is directly shared. I have to manually share each file by clicking on the share icon and selecting ParentGroup. Since ParentFolder is shared with ParentGroup, I should not need to manually share files.

It should be possible to easily see that wizard.php and the screenshot is shared, and who they are shared with, without having to share them directly (assume you have hundreds of file and you cannot check all files manually). If it was possible to see files shared by inheritance, I could just read from the table to get the users. For groups I could match group names against the oc_group_user table to find its users.

And, you can have different folder paths containing folders and/or files with the same name. This would be confusing if you use the oc_share table when trying to audit accesses. The file_source column might be useful to see if this is a folder at the same path, and the table oc_activity contain the folder paths for the files. Might be able to combine these tables and the file_source id. I'm not sure if this is a good solution.

MorrisJobke commented 7 years ago

cc @nextcloud/sharing

kimsyversen commented 7 years ago

I have made a (simple and horribly bad coded) POC to illustrate better and that might be useful for others. Assumptions:

You must run the script on the server
It assumes that you share directly with users and don't use groups
You cannot see shared files by inheritance

The script will output a csv file that can be analyzed in excel etc:

path,shared_by,shared_to,perm_id,permissions
files/X,admin@localhost.local,kim@localhost.local,31,CURD + Reshare
files/Y,admin@localhost.local,kim@localhost.local,31,CURD + Reshare
files/Y/a.pdf,admin@localhost.local,kim@localhost.local,19,UR + Reshare

How to run

sudo python audit.py --host=<DB IP> --user=<DB USER> 
--passw=<DB PASS> --dbname=<DB NAME> | tee ~/$(date +"%Y-%m-%dT%H-%M-%SZ")_audit.csv

audit.py

#!/usr/bin/python
# -*- coding: utf-8 -*-
import pymysql.cursors
import argparse
import json

parser = argparse.ArgumentParser()
parser.add_argument('--host', action='store', dest='host',
                    help='')
parser.add_argument('--user', action='store', dest='user',
                    help='')
parser.add_argument('--passw', action='store', dest='passw',
                    help='')
parser.add_argument('--dbname', action='store', dest='dbname',
                    help='')
args = parser.parse_args()

# Requires pip install pymysql

# CURD = Create, Update, Read, Delete
# CUR = Create, Update, Read
# CR = Create, Read
# R = Read
permissions = {
'1'  : 'R', 
'3'  : 'UR',
'5'  : 'CR', 
'7'  : 'CUR', 
'9'  : 'RD',
'11' : 'URD',
'13' : 'CRD',
'15' : 'CURD',
'17' : 'R + Reshare',
'19' : 'UR + Reshare',
'21' : 'CR + Reshare',
'23' : 'CUR + Reshare',
'25' : 'RD + Reshare',
'27' : 'URD + Reshare',
'29' : 'CRD + Reshare',
'31' : 'CURD + Reshare'}

def get_email(raw_string):
    json_string = json.loads(raw_string)
    return json_string['email']['value']

# Connect to the database
connection = pymysql.connect(host=args.host,
                             user=args.user,
                             password=args.passw,
                             db=args.dbname,
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
try:
    cursor = connection.cursor()
    cursor2 = connection.cursor()
    cursor3 = connection.cursor()
    cursor4 = connection.cursor()
    cursor5 = connection.cursor()

    # Get a unique list of all parent IDs
    parents = "SELECT DISTINCT(parent) FROM oc_filecache WHERE path LIKE 'files%';"
    cursor.execute(parents)
    parents = cursor.fetchone()

    #Print headers
    print "path,shared_by,shared_to,perm_id,permissions"

    while parents is not None:
        # Get all childs per parent
        childrens = "SELECT * FROM oc_filecache WHERE parent = %s;" % (parents['parent'])
        cursor2.execute(childrens)
        childrens = cursor2.fetchone()

        while childrens is not None:
            # Find owner for each child
            shared_with = "SELECT * FROM oc_share WHERE file_source = %s" % (childrens['fileid'])
            cursor3.execute(shared_with)
            shared_with = cursor3.fetchone()

            while shared_with is not None:
                user_i_nfo = "SELECT * FROM oc_accounts WHERE uid = '%s'" % (shared_with['uid_initiator'])
                cursor4.execute(user_i_nfo)
                user_i_nfo = cursor4.fetchone()
                uid_initiator_email = get_email(user_i_nfo['data'])

                user_s_nfo = "SELECT * FROM oc_accounts WHERE uid = '%s'" % (shared_with['share_with'])
                cursor5.execute(user_s_nfo)
                user_s_nfo = cursor5.fetchone()
                uid_shared_by_email = get_email(user_s_nfo['data'])

                print "%s,%s,%s,%s,%s" % (childrens['path'], uid_initiator_email, uid_shared_by_email, shared_with['permissions'], permissions[str(shared_with['permissions'])])

                shared_with = cursor3.fetchone()

            childrens = cursor2.fetchone()
        parents = cursor.fetchone()
finally:
    connection.close()

hede5562 commented 6 years ago

shared_from_teams_psql.tar.gz

I've modified the script from kimsyversen to use JOINS instead of nested SELECT statements. This way it's much faster. And we do use Postgres instead of MySQL. And it's much fancier – with the help of jquery-1.5.2.min.js and jquery.tablesorter.min.js plus some gif files it's even possible to sort the resulting table. (These common files are not included here - you'll find them with your favorite internet search engine)

Still it's a proof of concept and not a real solution. And we are not using it any longer in favor of some GUI-integrated solution, patching the nextcloud sources:

nextcloud.specialadminshare.patch.txt

This patch adds a status line to the shared dialogue right inside the Nextcloud GUI if a file owned by some special user got shared by some other user. We use it to show shares owned by the user "admin" to all users belonging to the group "alle" (where german "alle" means english "all", but it's indeed some limited user group).

Say if you are a user in group "alle" and some other user (independent of it's group) has reshared a file owned by Admin or any file inside a folder owned by admin, you can see those informations (who shared it to whom) within the Nextcloud Web App. Just like if you shared it by your own.

Both names are hard coded for now. Thus this patch also is not production ready, it's also just a proof of concept. But it's working better than expected. Maybe I develop it further not to be hard coded but user configurable. But for now it's ok to be configurable only inside the code. I don't think it will get integrated into Nextcloud anyway, it probably doesn't fit into their usage concept.

PS: I've modified the patch with some editor (text replace) to not include some internal data; hopefully it's still working like the unmodified patch. And it's for Nextcloud 13. Tell me if it doesn't work like expected.

kimsyversen commented 6 years ago

Hi, this is awesome - great work!

This is important functionality that in my opinion should be integrated into Nextcloud. There are so many organisations using Nextcloud today and it should be easy for them to perform internal control and have the ability to detect and react to incorrectly shared files. Incorrectly shared files might be personal/customer data that is shared with unintended persons. Depending on the data, this might be in violation to GDPR and there might also be a requirement to report the incident.

I have tested the GUI integrated solution and I’m not able to make it work.

I had to modify the paths in the patch as my Nextcloud files are stored in a different location, and change “alle” to “users” (this is my alle-group). For the test I created a new Docker-instance using 13.0.5 and applied the patch patch -p1 < nextcloud.specialadminshare.patch.txt. I log in to Nextcloud as admin but I cannot reshare the folders I create so this is the first hinder. Resharing is not allowed even if I'm admin and resharing is on.

Any tips?

hede5562 commented 6 years ago

Sorry, I've uploaded an old version for an older nextcloud version. Some Javascript has changed. The old one must have thrown errors while applying. Try this one:

nextcloud.13.0.3.specialadminshare.patch.txt

It should apply to the current 13.0.5 without any error, not even a hunk warning. ("patch -p1 < /path/to/patch" inside the nextcloud path; nothing changed in the 3 modified files between 13.0.3 and 13.0.5)

hede5562 commented 6 years ago

btw: I do not modify resharing itself. If resharing is not working, the problem should be independent of my patch. And users of your "users" groups should see all shares by admin, not only the reshared ones. You do not have to reshare objects. Still they must be owned by admin (or any other user configured inside the code).

kimsyversen commented 6 years ago

Many thanks @hede5562 - it works.

This is really useful and I hope this will be developed further.

kimsyversen commented 6 years ago

For everyone else, this is how it looks in the UI. Admin created the folder Lots of new data and shared it with K and R. K reshared the folder with T.

skjnldsv commented 4 years ago

We now have the sharelisting app https://apps.nextcloud.com/apps/sharelisting

stylefieber commented 3 years ago

That's not really a good solution. You (or an admin without console knowledge) wants to check if everything is ok with the shares and get just a list in the GUI. Our boss for examples likes all the share feature but he is afraid that any person could accidentally share more files as he/she should and he wants to be able to check that from time to time.

The current "shared" view is almost that. It just lacks of the possibility to see ALL shares / all link shares, system wide.

arjenlentz commented 3 years ago

I'm with @stylefieber

It just lacks of the possibility to see ALL shares / all link shares, system wide.

For compliance and just sensible security reasons, I think it's important to be able to get that systemwide overview. Yes there can be an audit log, which is great, but that serves a different purpose. They each have their place.

pgassmann commented 2 years ago

@skjnldsv this issue should be reopened. A system wide overview would be very useful. We would like to check all shares with a public link.

@skjnldsv can those be listed and filtered with the sharelisting app? Where can I find what the permissions mean?

"permissions": 1, "permissions": 31, "permissions": 15,

EDIT: I found some information about the permissions: https://docs.nextcloud.com/server/20/developer_manual/client_apis/OCS/ocs-share-api.html#create-a-new-share

the permissions are the sum of

1 = read; 
2 = update; 
4 = create; 
8 = delete; 
16 = share;

matti-owl commented 1 year ago

I'm totally uncapable to code, but I have been waiting for this "total list of shares, users/groups permissions and expiry date" feature for NC admins. Would be a huge help to review if there are any possible security issues or leaks. My upvote for this, thanks!

Corinari commented 1 year ago

I'm also interested if it is possible to get a list of alle shared public links, their responding files and maybe the expiration date. Even a CLI script would be sufficent for me.

Reading the linked documentation, unfortunately i wasnt able to get responses showing shares.

michu-pl commented 9 months ago

I also need to create a list of shared resources to know who has access to what. I think this is a very important functionality for compliance and security reasons. @skjnldsv this issue should be reopened.

matti-owl commented 6 months ago

I'm totally uncapable to code, but I have been waiting for this "total list of shares, users/groups permissions and expiry date" feature for NC admins. Would be a huge help to review if there are any possible security issues or leaks. My upvote for this, thanks!

Talking to myself :D but any news on this, is this totally undoable thing?

skjnldsv commented 6 months ago

Hey folks! Thanks for the interest. The sharelisting was Nextcloud official response on the matter. While I understand this might not fit everyone's use-cases, we have no plans on changing this for now. Feel free to open tickets to improve the sharelisting app on the appropriate repository.

Regarding your custom request and especially seeing many are here for your businesses, if you wish to have this feature implemented by the Nextcloud GmbH there is the option for consulting work on top of your Nextcloud Enterprise subscription to get your features implemented.

nextcloud / server