Появилась задача провести аудит персонального облачного хранилища Seafile. К сожалению, в системе, из веб интерфейса нет возможности выгрузить список пользователей, репозиториев и т.п. в удобном режиме, и одновременно. Из-за чего было принято решение попробовать забрать всё что нужно напрямую из баз данных, благо структура там простая, и таблиц не так много. Наверное, можно было бы воспользоваться и встроенным API, однако мне показалось, что в данном случае, мне показалось, что проще будет сделать, так, как сделано.

Собственно, SQL скрипты:

lusers.sql

Получаем список локальных пользователей системы.

use ccnet-db;
select 'EmailUser' AS 'table',e.id,e.email,e.is_staff,e.is_active,FROM_UNIXTIME(ctime / 1000000) as ctime,l.last_login from EmailUser e LEFT JOIN `seahub-db`.base_userlastlogin l ON e.email=l.username;

Особо интересное поле тут – is_staff, если значение не 0, то пользователь – администратор.

adusers.sql

Получаем список пользователей системы подключенных из LDAP (например из ActiveDirectory).

use ccnet-db;
select 'LDAPUsers' AS 'table',e.id,e.email,e.is_staff,e.is_active,l.last_login from LDAPUsers e LEFT JOIN `seahub-db`.base_userlastlogin l ON e.email=l.username;

В целом, всё аналогично предыдущему запросу.

repos.sql

Запрос, который даёт информацию обо всех репозиториях (обычных, публичных и виртуальных), а также о том, кому на данные репозитории выданы права, и в каком объеме. Наверное, самый полезный запрос для аудита.

USE seafile-db;
SELECT
  r.repo_id,
  COALESCE(i.name, origin_info.name) AS repo_name,
  virt.path AS virtual_path,
  o.owner_id AS owner_id,
  s.size AS repo_size_MB,
  f.file_count,
  CASE WHEN virt.origin_repo IS NOT NULL THEN 'true' ELSE 'false' END AS is_virtual,
  CASE WHEN pub.permission IS NOT NULL THEN 'true' ELSE 'false' END AS is_public,
  sr.to_email AS shared_with_user,
  sr.permission AS user_permission,
  cg.group_name AS shared_with_group,
  rg.permission AS group_permission
FROM
  Repo r
LEFT JOIN RepoOwner o ON r.repo_id = o.repo_id
LEFT JOIN RepoSize s ON r.repo_id = s.repo_id
LEFT JOIN RepoFileCount f ON r.repo_id = f.repo_id
LEFT JOIN RepoInfo i ON r.repo_id = i.repo_id
LEFT JOIN VirtualRepo virt ON r.repo_id = virt.repo_id
LEFT JOIN RepoInfo origin_info ON virt.origin_repo = origin_info.repo_id

-- Доступ пользователям
LEFT JOIN SharedRepo sr ON r.repo_id = sr.repo_id

-- Доступ группам
LEFT JOIN RepoGroup rg ON r.repo_id = rg.repo_id
LEFT JOIN `ccnet-db`.Group cg ON rg.group_id = cg.group_id

-- Публичные репозитории
LEFT JOIN InnerPubRepo pub ON r.repo_id = pub.repo_id

ORDER BY repo_name;

groups.sql

Информация о группах, и членах этих групп

USE ccnet-db;
SELECT g.user_name,grp.group_name,grp.creator_name
FROM `GroupUser` g
LEFT JOIN `Group` grp ON g.group_id = grp.group_id
ORDER BY grp.group_name;

fileshares.sql

Информация об опубликованных ссылках для доступа к файлам и папкам, может полезно, для предотвращения утечек информации.

use seahub-db;
select 
s.username,
s.repo_id,
COALESCE(i.name, origin_info.name) AS repo_name,
s.path,
s.ctime,
s.view_cnt,
s_type,
CASE WHEN s.password IS NOT NULL THEN 'true' ELSE 'false' END AS password,
s.expire_date,
s.permission
from share_fileshare s 
LEFT JOIN `seafile-db`.RepoInfo i ON s.repo_id = i.repo_id
LEFT JOIN `seafile-db`.VirtualRepo virt ON s.repo_id = virt.repo_id
LEFT JOIN `seafile-db`.RepoInfo origin_info ON virt.origin_repo = origin_info.repo_id;

uploadshares.sql

Аналогично предыдущему, но перечисляются опубликованные ссылки для загрузки информации, в идеале, как по мне, такие ссылки, как из предыдущего так и из этого примера, лучше избегать, ну или как минимум делать из запароленными.

use seahub-db;
select 
s.username,
s.repo_id,
COALESCE(i.name, origin_info.name) AS repo_name,
s.path,
s.ctime,
s.view_cnt,
CASE WHEN s.password IS NOT NULL THEN 'true' ELSE 'false' END AS password,
s.expire_date
from share_uploadlinkshare s 
LEFT JOIN `seafile-db`.RepoInfo i ON s.repo_id = i.repo_id
LEFT JOIN `seafile-db`.VirtualRepo virt ON s.repo_id = virt.repo_id
LEFT JOIN `seafile-db`.RepoInfo origin_info ON virt.origin_repo = origin_info.repo_id;

devices.sql

Выводит список устройств, которые подключены к системе, есть смысл периодически проверять и удалять неактуальные.

use seahub-db;
select user,platform,device_name,platform_version,client_version,last_accessed,last_login_ip,created_at from api2_tokenv2 order by last_accessed;

Выполнение

Можно данные скрипты запускать вручную, например, командами вида:

mysql -u root -p < devices.sql > devices.csv

Но проще загнать всё в один небольшой скрипт:

#!/bin/bash
mkdir -p result
read -s -p "Enter MySQL password: " MYSQL_PWD
echo

for sql_file in *.sql; do
    base_name="${sql_file%.sql}"
    output_file="${base_name}.txt"

    echo "Executing $sql_file -> $output_file"
    mysql -u root -p"$MYSQL_PWD" < "$sql_file"  > ./"result/$output_file" 2>&1
done

rm -rf audit-result.tar.gz 
tar -czvf audit-result.tar.gz ./result

В результате, после выполнения получим список CSV файлов, запакованных в архив, который можно забрать с сервера с помощью scp, и дальше обрабатывать удобными инструментами.

PS всё описанное тестировалось на довольно старой версии Seafile. Возможно в актуальных версиях что-либо может не сработать, или потребуется дополнение/обновление. Просьба написать в комментариях, если будут каки-то проблемы.

В результате, после выполнения получим список CSV файлов, запакованных в архив, который можно забрать с сервера с помощью scp, и дальше обрабатывать удобными инструментами.

PS всё описанное тестировалось на довольно старой версии Seafile. Возможно в актуальных версиях что-либо может не сработать, или потребуется дополнение/обновление. Просьба написать в комментариях, если будут каки-то проблемы.

PPS скрипты разместил в github.