Should I be thinking about security at the database row/document level?

Question

Suppose I have a web application that is a social network. In order for two people to have access to eachother's content they must become friends.

There is logic in the application to ensure both have improved eachother and once that happens, an update is made in each user's "friends" array.

I am wondering though; suppose some hacker found a way to append their user_id to another user's friends array. Now he can view the other user's content unauthorized. For simplicity, assume that this is the only security flaw in the system.

How does one protect against this sort of possibility? Are there features in databases that handle this?

P.S. The database I happen to be using is MongoDB, but also curious to hear about others

Answer 1

Generally, what you are describing is the responsibility of the application layer and not the database layer. Your application needs to make sure that only mutually accepted friends are added to the list, and that proper authorization is required to access data.

This responsibility can not really be moved over to the database in any simple way, as Pascal explains. In the end, you will always need some mechanism by which the app tells the database that two people are friends. If the app can be tricked to to "lie" to the database, you have problems no matter what.

What you could do is block the app from updating tables, and instead rely on procedures that enforces your security contraints. But now you have just moved the problem to a new place. And probably to a programming language and development environment that is worse to work in. Trying to put application logic in the database isn't a hit.

So the application layer is the relevant one here. Focus your energy at securing that.

Answer 2

Are there features in databases that handle this?

Let's look at MySQL:

The database runs as its own process (just like MongoDB). You connect to the db process with specific user credentials. Authorization is determined based on which user is connected.

In a web app, the web app usually has its own user credentials to connect to the db, but the whole webapp uses the same db connection and therefore is bound by the same access rights.

So you can't solve your problem by restricting access rights to some db rows or columns or even tables. The users in your app don't correspond to database users, so you have to implement authorization for your app users in the app. You can't defer it to the database.