How can "oc_bruteforce_attempts" table in SQLite database be cleared?

Support intro

Sorry to hear you’re facing problems :slightly_frowning_face:

help.nextcloud.com is for home/non-enterprise users. If you’re running a business, paid support can be accessed via portal.nextcloud.com where we can ensure your business keeps running smoothly.

In order to help you as quickly as possible, before clicking Create Topic please provide as much of the below as you can. Feel free to use a pastebin service for logs, otherwise either indent short log examples with four spaces:

example

Or for longer, use three backticks above and below the code snippet:

longer
example
here

Some or all of the below information will be requested if it isn’t supplied; for fastest response please provide as much as you can :heart:

Nextcloud version (14.0.1):
Database version (SQLite):

Logging-in with my Android devices results in time-outs because the “brute force” security feature creates a 30 second delay.

I recently changed my nextcloud access password. I had to try logging-in repeatedly with the new password before it eventually ‘stuck’. This apparently invoked the “brute force” feature. I found instructions to clear the applicable tables from a MySQL database. However, I’m still running with a SQLite database. Can somebody please walk me through the steps necessary to remotely clear my current IP address from the table?

Thanks in advance!

*Being able to do this with a quick virtual press of a GUI button would be a very helpful feature in a compendium of similarly useful functions in aMaintenanceapp of some sort!

I found my own answer (albeit not one with SQLite instructions). Perhaps somebody with a similar problem may find the solution to be useful:

Simply enable the “Brute-Force Settings” app: https://apps.nextcloud.com/apps/bruteforcesettings

Nevertheless, it would still be nice to be able to clean-out the entries rather than only suppress them.

Use those two commands to delete all rows from the table oc_bruteforce_attempts:

sqlite3 /path/to/nextcloud/data/owncloud.db "DELETE FROM oc_bruteforce_attempts;"
sqlite3 /path/to/nextcloud/data/owncloud.db "VACUUM;"

The second command is to shrink the database file (and free the unused space), which is not done automatically.
Also note, in difference to other SQL databases, there is no TRUNCATE TABLE command in SQLite3.

Thank-you, Bernie. I seem to be having an ownership issues because after entering the first of the above commands, I get:

Error: unable to open database "/var/www/html/nextcloud/data/owncloud.db": unable to open database file

I first tried as a regular user and then also as sudo. In spite of the file being owned by apache (my server is running Fedora), wouldn’t sudo provide ultimate authority?
I can’t remember defining the database name when I set up the server. Nevertheless, even trying to verify the proper database name by doing;

sqlite3 /var/www/html/nextcloud/data/ .databases

resulted in:

Error: unable to open database "/var/www/html/nextcloud/data/": unable to open database file

Any further hand-holding would be appreciated very much.

You need to locate your SQLite3 database file. Its name is “owncloud.db” and it is located in the data folder of your nextcloud installation. Look for the value of ‘datadirectory’ in the config.php of your installation to find the correct directory.
Change to that directory with:
cd /path/to/datadirectory
Then issue the commands as your webserver user (in my case this is www-data):

sudo -u www-data sqlite3 owncloud.db "DELETE FROM oc_bruteforce_attempts;"
sudo -u www-data sqlite3 owncloud.db "VACUUM;"

Thanks for your continued support, Bernie. I now understand which commands I must issue but, I still have a very basic problem: I cannot access the /data directory to issue them. For example:

cd /var/www/html/nextcloud/data/
-bash: cd: /var/www/html/nextcloud/data/: Permission denied

If you can throw me a bone and show me how I can overcome this block, I’d appreciate this as well. Thanks in advance.

Enter sudo su as very first command and enter your password. Then changing the directory should work.

That did the trick! However…

root@heviiMTLServer heviiguy# cd /var/www/html/nextcloud/data/
root@heviiMTLServer data# sqlite3 /owncloud.db “DELETE FROM oc_bruteforce_attempts;”
Error: no such table: oc_bruteforce_attempts

So close yet so far :wink:

Your prefix for the tables might be different than oc_
Run this command and see the prefix (again with sudo su beforehand):
grep prefix cd /var/www/html/nextcloud/config/config.php
Then change the prefix accordingly in the command.