Database vs application security design

Question

So I'm currently in the process of creating a small public website (where users can, for example, log in and change personal information), but I am wondering how the database security is actually designed for that type of thing, so I have several questions.

When an internet user accesses a page like stackoverflow, for example (without logging in on the site), which database user or role is he logged on in order to be able to see all the posted questions? Then, when the user logs in with his account, does his role in the database change (since he has more rights)?

If I setup my database to have a "Users" as well as a "Permissions" table, I can make sure that a user can only have access to his own data, at least application side. But how do I make sure the data is still protected database side since I suppose every public user has the same "database login" or "role"?

I know my question is probably a bit unclear, but don't hesitate to ask me for clarifications. I didn't really know where to begin.

Thanks.

P.S.: I'm currently using SQL Server

Answer 1

You've almost answered your own question.

Generally, your website will run under some user account, lets call this 'IWEB', this user account will be the same for everyone that hits your website, regardless of whether the user is logged into your website or not.

IWEB will have permissions to read data from a database. That's how the anonymous users work.

In your database you will have a user table, possibly a permissions table. IWEB will be given database permissions to create new users, update users. It possibly will be prevented from deleting users.

Your application will (though IWEB) use these tables to control who has access to your application and who can update what. A bug (e.g. SQL Injection) in your application could allow a malicious user to create admin accounts, change other users passwords etc...

In certain scenarios e.g. using Windows Authentication, you can pass through the user logged into your site, translate that into a database user and given them permissions. However you are unlikely to do that for a public facing website.

You could possibly do something where the tables are not directly accessed, but are controlled through stored procedures, denying access to the underlying tables. One of the parameters is the currently logged in user, that could then control access at the database layer (prevent a user updating another users profile). Unless of course your application has a bug that could allow an attacker to change their currently logged in user.

It all depends on your security requirements. In the main though, you will control access at your application.

Answer 2

To your first question: When a user first lands on a web page they have no 'role'. Your scripting will decide what the users can and cannot see.

For example you have a post that only certain user can see. That post will be hidden by default. Your script should then run a test to see if the user is currently logged in and if that user is in the permission group to view that post. If they are, then show the post.

Your database should not be accessible either way.

Answer 3

Database role doesn't change, but functions do. Users are authorized from the back end code, not from directly database. If you are trying to create logins for every single user for your database, that would not be a good aproach since there could be thousands or milions of users which somehow can have direct access to your database with some permissions.

Answer 4

What's wrong with having an Anonymous user in the Users table, having the lowest privileges in the Permissions table?