How would you architect a “standard” web application to protect the database?

[hoodoof]

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?

[patio11]

In most cases you assume that if hackers get root on the web application server you lose everything else in the data center, including the database. There are over a hundred things you have to do right to prevent this, and if you miss one, you lose.

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.)

[eonw → patio11]

"everything else in the data center" is a little missleading, and quite frankly an exaggeration in my experience. one compromised server will not bring down rackspace, for instance. or even our little three row DC.

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.

[PerfectElement → patio11]

What if your sensitive data is encrypted at rest and the application is responsible for decrypting it?

[jtchang → PerfectElement]

The webapp server still has the decryption keys.

[elchief]

Your primary threat is SQL Injection. Use parameterization to avoid this.

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.

[ChuckMcM]

Well set up an api to talk to the data base service via a microservice (no sense putting straight SQL out there)

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.

[euroclydon → ChuckMcM]

> 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.

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?

[codepeach]

Having the database on a seperate server doesn't imply it's safe, if the web application has access to the database.

If you are new to web application security, OWASP is a good starting point: https://www.owasp.org/index.php/Main_Page

[hoodoof → codepeach]

Sorry I should have asked for specific ideas rather than references to OWASP which seems to be "the answer to all security architecture questions".

[bbcbasic]

If it is a standard web application it probably connects to the DB as one very powerful user that can at least read/modify data in the DB, and may have DDL permissions to create tables etc.

So not sure how you can protect against this.

[andymurd → bbcbasic]

I often use the following pattern:

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.

[xyzzy123 → andymurd]

For high-value custom applications, you can go a step further and enforce a stored procedure interface to the database.

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).

[bbcbasic → xyzzy123]

Does the database then deal with user management? E.g. someone logs in, gets an authentication token, etc. Or do app users map to DB users?

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.

[xyzzy123 → bbcbasic]

Absolutely correct, yes, the authentication process needs to be handled outside the app.

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.

[alansmitheebk]

Use prepared statements to pretect against SQLI!!!!

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?)

[tumikosha]

java+spring+swagger+hibernate