How would you architect a “standard” web application to protect the database?
OK imagine a "standard" web application. Some web server, some language like PHP, Ruby, whatever, some database Postgres, SQL server, MySQL, whatever.
How would you architect this to protect the database?
Would you have the database on a separate server?
Would you allow direct connections to the database from your web application code?
How would you defend your database in case hackers crack the web application server?
17 comments
[ 0.15 ms ] story [ 38.5 ms ] threadIf you are new to web application security, OWASP is a good starting point: https://www.owasp.org/index.php/Main_Page
I know that isn't the answer you wanted, but it's the truth, so: don't lose your web server.
In certain regulated industries you'll be encouraged to do some theatre like separating the database from the server on a different network, putting a firewall between them, yadda yadda. Which is all great, but the web server has to be able to talk to the database in some fashion, and that's game over if the web server can say the right things. If you assume the ability of the attacker to coerce the web server into saying any of the range of things the web server is authorized to say, that's usually "pretty bad news" with regards to what you have in your DB.
Concrete example: suppose I have the web server connect to the DB with a relatively limited privileged user, which is one thing you should certainly do. That user only gets access to the proper database on the DB server (which after all might have many of them), which prevents e.g. someone from hopping from a WordPress compromise directly into the main application database... sorta. You'll probably also want to limit the actions the webserver can take to avoid e.g. creating users or changing schemas.
That still leaves the web server able to do CRUD operations, which is easily enough to do a lot of damage to your business and yourself. Rails can trivially execute "select * from patients;" That single line, or one isomorphic to it, is a catastrophe if you're in healthcare.
(Yeah yeah row-level ACLs, I know, I know. Aside from "Not widely supported in actual web application stacks" they're probably not sufficiently robust in the face of a server compromise because the server should be able to authenticate as every user somehow and at that point you're just a for-loop away from total compromise.)
i also do not think missing just one step in security will do it(although it can), unless you miss a big step, most places i have seen exploited were doing a number of things incorrectly.
So not sure how you can protect against this.
Categorise the app's access to each DB table into one of the following types: read-insert-update-delete, read-insert-update, read-insert, insert-only and read-only. I've never had to worry about delete-only or update-only, but YMMV.
For example, the table COUNTRY is probably read-only, whilst ORDER is read-insert-update. Document this stuff and add schema comments to make code reviews easier.
Then I have DB roles like dbname_owner, dbname_admin and dbname_app. The owner can create/drop/alter etc, the admin can read and write to all tables but not change the schema, the app user has per-table permissions. Schema updates do require the owner role but I rarely automate them.
Essentially the application is given no permission to write to tables at all, but just to call various stored procedures which enforce access permissions and implement auditing.
This works particularly well if the database is append-only (writing change records instead of mutating, rather like double entry book-keeping).
If the stored procs are enforcing checks, then it isn't good enough to just say 'I am user bbcbasic'. It should ask for an authentication token or password, or against your current DB login.
While it's possible to have a login stored proc, this is not strictly necessary. If you have e.g. an OAuth2 server which is separate from your application, you can use those tokens for authentication with your database.
If the application is completely compromised (e.g. someone has root on server), then a malicious party would be able to act as any logged in user by dumping tokens.
However, what they would not be able to do is arbitrarily dump your database, bypass your auditing, or mess up your application invariants.
Put the DB on a separate server. Don't access DB directly from the web app; access it through services. Set up a robust authentication system for your services (OAuth?)
Set up routing such that the microservice cannot talk to any machine that isn't on the same subnet as the front end (perhaps some static route work will help)
Design the app so that no transaction state is kept in the web server, pass idempotent statements through the API with transactional identifiers rather than keep state.
Create logs using a separate service that can watch all traffic between the front end and the micro service by snooping the network between them. Make the only way to login to the logging server from a locally resident console terminal, have it send the logs to your network accessible system but internally provide triggering on suspicious activities.
Never allow "remote hands" service on any machine in a data center you don't own, install intrusion detection on every machine.
That is probably a start.
Let me see if I understand what your saying:
1) no transaction state is kept in the web server
So if I'm a bank app, the data to populate the account history table and the form post data to transfer money are not held on to by the web server? You'd have to go out of your way to hold onto that data, unless you had some output caching block on the account history.
Maybe my app is more complicated, and I have some type of long-running job, so I create a job class with all the job parameters, and stick it in Session, Redis, or plain App Memory Cache, while the client polls to see if the job is complete. You're saying don't hold onto the Job class, but just an identifier for the Job.
2) pass idempotent statements through the API with transactional identifiers rather than keep state
How is this done? If the user wants to move $200 from account_a to account_b, twice, how is that handled? Which service generates the transaction identifiers?
If your system uses plugins like Wordpress (where you can't guarantee anti-injection measures), then you seriously need to use real per-user database authentication (killing connection pooling, boo-hoo).
Your secondary threat is hacker getting root on web server. Web server must not connect to db as superuser. Connect with minimal privileges. Need network monitoring to look for bulk selects, or unwanted sequences of selects. You will lose some data, but you don't have to lose all of it.
1. db server is on own box in own network zone w firewall
2. db server has two network cards. Own network where only web server can connect, and management network. Management network is internal-only or VPN'd on a different IP/domain
3. management network allows for network backup, SNMP, syslog, ssh
4. db text logs go to remote syslog server which is monitored for security events
5. snort or bro NIDS between web server and db server. Preferably in promiscuous/no ip address mode
6. your user authn system allows no direct table access. web server can only connect thru sprocs, which return single records.