Forum on one server + MySQL on another

[frm]

Security is a major concern. Ideally your database server should be sitting behind a firewall with only the ports required to perform data access opened. Your web application should be connecting to the database server with a SQL account that has just enough rights for the application to function and no more. For example you should remove rights that permit dropping of objects and most certainly you shouldn't be connecting using accounts such as 'sa'.

Source: https://stackoverflow.com/questions...e-database-and-web-server-on-the-same-machine

This was found on a StackOverflow post.

Right now, I'm connecting with root to my MySQL server so it would only take 1 more step for someone to mess everything up as it's visible with username and password (though, it's not public SSH) in the config file.

Backups are done daily, but, I'd still prefer not to lose 1 day's worth of data as a lot or little can be added that day.

Is there a user that can be created for MySQL access that only can add data, but can't drop (drop whole tables) or delete (i.e. hard deletes)? This would prevent anyone from messing it up too badly as they can't drop tables or delete rows from tables (I presume that's what a hard delete does)? Possibly just create a bunch of rows in a loop if they really wanted to "mess" anything up (unless there's a timer for adding rows like 100 rows per 45 seconds or something on a user).

What settings for XenForo would this user be that's safer to use than root for accessing the database as it can be compromised and totally deleted with root?

@eva2000 might know?

 

[eva2000]

You should be creating individual MySQL users which only have access to your specific web app's database(s) and not all of them as you would with MySQL root user and then having frequent MySQL database backups locally + remotely.

 

[frm]

You should be creating individual MySQL users which only have access to your specific web app's database(s) and not all of them as you would with MySQL root user and then having frequent MySQL database backups locally + remotely.

How exactly would that work with XF's config.php file?

 

[frm]

Seems to me that a MySQL user can be created to never drop or delete and it'll just error out on a hard delete or removal of something, which I'm fine with because even my admin account can't hard delete (though if it were hacked, they'd just change the permission and delete on).

It's only if config.php was compromised and then connected through the internal network as it's blocked off elsewhere (1 roadblock).

 

[eva2000]

How exactly would that work with XF's config.php file?

same as mysql root user it's just mysql username and mysql password set as per https://xenforo.com/xf2-docs/manual/config/

 

[Sim]

Seems to me that a MySQL user can be created to never drop or delete

... which will break many forum upgrades or addon upgrades. Sometimes tables actually do need to be dropped for legitimate reasons and blocking your database user from doing so will leave your XF installation in an inconsistent state.

The DROP permission is also required for TRUNCATE_TABLE, which is a completely valid operation in some circumstances.

Just create a specific MySQL user account and give it permissions for only your XF database - worst case scenario is that your XF installation gets messed up by a hacker, at which point you simply create a new user and restore your XF database from backup (and invest time in working out how your server got hacked!) - they can't touch anything else on your MySQL server using your XF MySQL user credentials.

Have your MySQL server only respond to requests coming from the local machine. If you want to connect to it remotely for admin purposes, use an SSH tunnel so that you first authenticate to your machine using your SSH keys and then the connection to MySQL is local from your server - don't allow remote connections to your MySQL server.

If you want to have your MySQL server and your web server on different machines, use a local network connection to connect between them and firewall all other connections from the public internet. The only way someone can connect to your MySQL server is from your web server and any MySQL username and password only gets them access to a single database (do NOT use your MySQL root username/password to access MySQL from XF)

 

[frm]

and invest time in working out how your server got hacked!

I think this is the hardest part because if someone was smart, they'd sleep on it for a while as daily and weekly backups were taken and then lead the attack.

I have redundancy of all, but that doesn't mean someone didn't put a file there a long time ago that's not meant to be there.

 

[Sim]

You can always do a full rebuild from scratch - fresh install of XF, restore database from backup, copy attachments and thumbnails etc from backup.

Don't forget that the vast majority of hacks are not targeted at you specifically - your website typically has little or no value. Attacks are going to be simple and automated using known exploits by bots to gather credential databases that can be used in attacks against more important sites.

But even your credential database is full of passwords that everyone already knows

You have a responsibility to your users to take security seriously and make sure things are set up correctly - but unless you're dealing with "sensitive" personal information (ie you are a financial services company, a healthcare company, a govt agency, etc), then there's no need to go jumping at shadows.