Export share rights into CSV

Seafile Server
1

Hi,
is it possible to export the access to my shared libraries into a CSV or something similar?

Like
Librarie1;User1(R/W);User2(.R.);User3(Admin)
Librarie2;… you get it.

We have 203 Libraries at this point and had an audit which user has access to which files. This is a litle pain in the a**.

We have 1 “Master” Account which manages all Libraries for our company.

bye Andre

2

Hi Andre,

just do an SQL query like this:

select name, from_email, to_email, permission from SharedRepo join RepoInfo repo_id on SharedRepo.repo_id;

You will get a list like this:

name                    from_email           to_email             permission
Meine Bibliothek        xmuster@xyz.de       zmuster@xyz.de       rw
...

If you use the command line you can redirect the result into a file:

mysql -u seafile -p seafile-db --password=secret -e "select name, from_email, to_email, permission from SharedRepo join RepoInfo repo_id on SharedRepo.repo_id" > shares.txt

Dirk

1 Like
3

i give this a try next week. Thank you very much!

4

just 4 month later and i gave it the first try, yea i know, i’m not the fastest one.

The results are mixed …

There are two very strange things. First i see libraries which are non existent and never were. And the Seccond thing is, that i see the shares multiple times.

I added an screenshot, this is 1 librarie, shared with our file server. This is for almost every librarie and every user.

Anything i can do about it? I can’t be the only one who gets an data protection audit and who needs this kind of export.

seafile-export
seafile-export906×1084 232 KB

5

Hi, that is because the sql is not correct.
I am having the same challenge in my company.
Came up with something like this, but cannot get the table where all the groups are listet, so I get only their IDs.

SELECT 
    RepoOwner.owner_id AS 'Repo Owner', 
    Repo.repo_id, 
    RepoInfo.name AS 'Repo Name', 
    ROUND(RepoSize.size / 1024 / 1024 / 1024, 2) AS 'Repo Size (GB)', 
    ROUND(UserQuota.quota / 1024 / 1024 / 1024, 2) AS 'User Quota (GB)',
    SharedRepo.to_email AS 'Shared With User', 
    SharedRepo.permission AS 'User Permission',
    RepoGroup.group_id AS 'Shared With Group ID', 
    RepoGroup.permission AS 'Group Permission'
FROM 
    Repo
JOIN RepoInfo ON Repo.repo_id = RepoInfo.repo_id
JOIN RepoOwner ON Repo.repo_id = RepoOwner.repo_id
JOIN RepoSize ON Repo.repo_id = RepoSize.repo_id
JOIN UserQuota ON RepoOwner.owner_id = UserQuota.user
LEFT JOIN SharedRepo ON Repo.repo_id = SharedRepo.repo_id
LEFT JOIN RepoGroup ON Repo.repo_id = RepoGroup.repo_id
LIMIT 10;

so far…

6

oh the thread is 4 years old…sorry…

7

Anyway, I found the solution for me and post it in case someone else needs it.



SELECT 
    RepoOwner.owner_id AS 'Benutzer', 
    RepoInfo.name AS 'Bibliothek',
    ROUND(RepoSize.size / 1024 / 1024) AS 'Bibliothek Größe (MB)', 
    ROUND(UserQuota.quota / 1024 / 1024) AS 'Benutzer Quota (MB)',
    SharedRepo.to_email AS 'Geteilt mit Benutzer', 
    SharedRepo.permission AS 'Berechtigung des Benutzers',
    `Group`.group_name AS 'Geteilt mit Gruppe',
    RepoGroup.permission AS 'Berechtigung der Gruppe',
    (SELECT GROUP_CONCAT(GroupUser.user_name SEPARATOR ', ')
     FROM ccnet_db.GroupUser 
     WHERE GroupUser.group_id = `Group`.group_id) AS 'Gruppenmitglieder'
FROM 
    seafile_db.Repo
JOIN seafile_db.RepoInfo ON Repo.repo_id = RepoInfo.repo_id
JOIN seafile_db.RepoOwner ON Repo.repo_id = RepoOwner.repo_id
JOIN seafile_db.RepoSize ON Repo.repo_id = RepoSize.repo_id
JOIN seafile_db.UserQuota ON RepoOwner.owner_id = UserQuota.user
LEFT JOIN seafile_db.SharedRepo ON Repo.repo_id = SharedRepo.repo_id
LEFT JOIN seafile_db.RepoGroup ON Repo.repo_id = RepoGroup.repo_id
LEFT JOIN ccnet_db.`Group` ON RepoGroup.group_id = `Group`.group_id
LIMIT 10;
1 Like
8

Even after four years, MANY THANKS. I can still make very good use of it today. We have to submit a rights report regularly and this will help me a lot!

9

Hi, Since I don’t know that much about SQL, why do you limit the output to 10 lines?

have a nice day,
Andre