How serious are companies and the people who support their databases about security? Not serious enough it seems.
How many databases are running with users with excessive privileges, OS privileges elevated?
What about securing via encryption your network traffic both inside your network and to external networks?
Over the next series of posts rather than give you a list of security tips, I am going to detail how to make your database more secure in detail.
These are not tips that your read with your morning coffee and then forget about, rather tasks. Tasks which you add to your project or task list
and work through until they are completed, signed off if necessary.
Task 1: Fix database users with wildcard hosts.
Database users with wildcard hosts who can potentially connect from any host from anywhere.
The SQL to check:
select user,host from mysql.user where host = '%';
- Users outside our datacentre (or cloud) network such as corporate headquarters or via home/vpn connections need access to the database.
- It is too hard to determine which user ip ranges or ip subnets will connect to our database.
- Monitoring software needs to connect and its ip address could change.
- If we change from wildcards, “things” could break.
- We don’t need to worry about wildcard hosts as our firewall will save us/stop any problems.
Company X has grown from startup hacked up over a few weekends to a global tech VC darling with a huge, or rather HUUGGEE valuation.
The companies application and database originally ran on a pc under the founders desk, but over time has migrated to the cloud to become an Agile, Resilient and Scalable Enterprise.
You, the humble, meek, and lowly DBA have been hired and after onboarding, are tasked amongst other things with securing the database.
You get your credentials for the host and database sorted out and connect to the db and run the following SQL.
MariaDB [(none)]> select user,host from mysql.user where host = '%'; +-------------+------+ | user | host | +-------------+------+ | hackmeplz | % | | replication | % | +-------------+------+ 2 rows in set (0.00 sec)
Change all users with wildcard host to a specific ip address or a specific ip subnet.
If all application traffic comes from your datacentre or cloud network for example from ip subnet 10.0.0.X then in our example,
‘hackmeplz’@’%’ becomes ‘hackmeplz’@’10.0.0.%’
If all application traffic comes via a Enterprise Load Balancer (ELB) or haproxy or Virtual IP (VIP) such as 10.0.0.3 then in our example,
‘hackmeplz’@’%’ becomes ‘hackmeplz’@’10.0.0.3’
The How to do the fix and not get fired for being a cowboy:
- Prepare a security audit report/review.
- Present or email to stakeholders (developers, reporting users, managers)
- Prepare a maintenance plan and gain approval to change.
- Organize maintenance window with specific time and date and gain approval for change.
- Follow the maintenance plan which will be something like this
– Announce start of maintenance window in your chat/communication channel.
– create a new user with the host part that contains a ip subnet
– drop old user with wildcard host.
– Get developers or report users to test to make sure their application works
– Confirm good.
– Announce the end of the maintenance window in your chat/communication channel.
- Go home and bask in the feeling that you have proactively and pre-emptively prevented a more than probable private pilllager
from causing your company a publicly painful punishment.