
How would you architect a “standard” web application to protect the database? - hoodoof
OK imagine a &quot;standard&quot; web application.  Some web server, some language like PHP, Ruby, whatever, some database Postgres, SQL server, MySQL, whatever.<p>How would you architect this to protect the database?<p>Would you have the database on a separate server?<p>Would you allow direct connections to the database from your web application code?<p>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.)

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

~~~
jtchang
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
> 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](https://www.owasp.org/index.php/Main_Page)

~~~
hoodoof
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
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
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
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
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

