CentOS 8.x Owncloud direct DB queries

Home > CentOS > CentOS 8.x > Desktop tools or applications > Owncloud > Direct DB queries

Connecting to database

For bitnami installations database settings can be checked at '/opt/owncloud-<version>/apps/owncloud/htdocs/config/config.php'. Note at least following values:

• 'dbname' => 'bitnami_owncloud',
• 'dbuser' => 'bn_owncloud',
• 'dbpassword' => '<secret>',

Then open connection to bitnami owncloud mysql database using:

/opt/owncloud-10.5.0-1/mysql/bin/mysql -u bn_owncloud -h 127.0.0.1 -p bitnami_owncloud

oc_filecache

This table has information about current files known to owncloud. Important column in this table is:

path

Stores path of file with respect to user folder (/opt/owncloud-<version>/apps/owncloud/data/<username>/)

path_hash

Md5 hash of path stored in path. This can be used to quickly search for a given path using indexes. See "show indexes from oc_filecache;" as it shows BTREE index on path_hash but no index on path.

permissions

Most likely related to filesystem permissions. I have seen value 31 when folder can be updated (owned by daemon:daemon) and 17 when it was read-only and owned by root.

Number of files in a given folder

We can check how many files/folders are there in any given path using queries such as:

select count(*) from oc_filecache where path like 'files/users/saurabh/%';

where path of file as per owncloud is 'users/saurabh/*'. We need to prepend paths with 'files/' while looking at this table.

Number of files of given type in a given folder

Similarly to find no. of pdf files within a given folder:

select count(*) from oc_filecache where path like 'files/users/saurabh/%.pdf';

oc_activity

This table has information of past 1 year of activity provided activity app ( https://marketplace.owncloud.com/apps/activity ) is installed and enabled. Once that is done we can see activity from web UI or through desktop client also. However, for complex queries across the system for specific time periods, it is easier to query the DB directly. This table has following important columns:

timestamp

Time when the activity was performed. To convert it to / from human readable formats refer Date

type

This is either 'file_created', 'file_changed' or 'file_deleted'

user

User who has performed given change

affecteduser

If a shared file is changed then this contains the name of other user affected by this change. If the file is shared by many users, many repeated entries with different usernames in affecteduser can be seen in the table

subject

This is 'changed_self' if the file is changed by same user. In this case you might see same values for user, affecteduser. If the file was changed by some other user you can here see 'changed_by'

file

Path of file. This is with respect to 'user' in user column as each user might have the same file at different paths

Number of files deleted by a user within given time periods under a specific folder

To query no. of files deleted by saurabh within timestamps inside a given folder use: Example values:

user

saurabh

after timestamp

1611513000 (2021-01-25 00:00:00)

before timestamp

1612290600 (2021-02-03 00:00:00)

path

/saurabh/workspace/

select count(*) from oc_activity where user='saurabh' and affecteduser='saurabh' and  timestamp > 1611513000 and type='file_deleted' and file like '/saurabh/workspace/%';

To see these files use:

select * from oc_activity where user='saurabh' and affecteduser='saurabh' and  timestamp > 1611513000 and type='file_deleted' and file like '/saurabh/workspace/%';

See Convert date to different format using date command for date/time conversion to epoch and back.

To query no. of files deleted by various users in a given folder

To query no. of files deleted by various users in a given folder in entire 1 year of database logs use:

select count(*),user from oc_activity where affecteduser='admin' and type='file_deleted' and file like '/projects/%' group by user;

here files were owned by admin user and shared with other users. The user deleting the file could be anyone else and is listed in the output under user table. The path of file as per admin user is /projects/*.

owncloud.log

Note that it is possible that a request for deleting a large file/folder is received via desktop client / web interface but that request takes too much time to complete (Delete large folder) and hence request times out or ends due to memory error, etc. In such cases file would get deleted at filesystem level but not at DB level. The mismatch issues between DB and filesystem can be solved using 'occ files:scan' Refer CentOS 7.x Owncloud file cache and sharing. But from audit purposes it wont be clear which user performed this action as the oc_activity table will not have corresponding file_deleted activities log. To find out about such actions we can have a look at '/opt/owncloud-<version>/apps/owncloud/data/owncloud.log'

Here in log file you can see parameters such as

method

PUT / GET / DELETE etc.

remoteAddr

Remote IP from which action was performed

user

User who performed this action

URL

URL or path for which action was performed.

Hence to search for files deleted under projects folder we can try:

grep DELETE owncloud.log | grep '%projects%' | less

and scroll till the timestamp when file could have been deleted. Then you might find logs such as:

{"reqId":"6423f2fb-3f66-4442-a6fa-0c79228ba113","level":3,"time":"2021-01-22T05:11:12+00:00","remoteAddr":"136.185.194.16","user":"user1@example.com", \
"app":"PHP","method":"DELETE","url":"\/owncloud\/remote.php\/dav\/files\/user1@example.com\/projects\/project1", \
"message":"Allowed memory size of 536870912 bytes exhausted (tried to allocate 4096 bytes) at \/opt\/owncloud-10.0.10-4\/apps\/owncloud\/htdocs\/lib\/composer\/doctrine\/dbal\/lib\/Doctrine\/DBAL\/Driver\/PDOStatement.php#105"} 

indicating user user1@example.com made request to delete folder projects/project1 at 2021-01-22 at 05:11:12 GST time (convert to local time zone appropriately) and the deletion failed due to memory exhaustion. In such cases folder projects/project1 gets deleted from filesystem but not at DB making it hard to audit / diagnose / troubleshoot.

Home > CentOS > CentOS 8.x > Desktop tools or applications > Owncloud > Direct DB queries