6

Is it secure to login to a website by using database usernames, for each user? Is it a bad practice? What can be the issues?

The idea is to create the users and permissions at database level.

EDIT: Any user accessing through my website would have INSERT, DELETE, UPDATE an SELECT permissions in specific tables.

At code level I would block any login to specific users with higher permission levels (like admin).

Only my company staff will be accessing my website. So, there will be no account management through the website, nor creation of new users. I would do it locally.

EDIT2: I will be using PostgreSQL, and my users should be able to access the site over the internet (not the database).

One of the reasons I ask this is because I saw a similar comercial desktop application, which does just that: you login inside the application, with a database username. I know that type of software is intended to be run inside a local network, but can easily be configured to work outside.

  • Could you add more information to your question? For example, what kind of users do you have? Is it just one or two admins, your sales-staff, or your complete customer base? What access are these users allowed to have? How are your database rights set up? But generally, no, this is not a good idea (see eg a somewhat similar question here). – tim Mar 3 '16 at 13:34
  • @tim I made an edit addressing your questions, but I guess you already answered me. Thank you – ivanc Mar 3 '16 at 13:54
  • What database are you using, and will the users be accessing the website on a local intranet, or over the internet? – TTT Mar 4 '16 at 4:12
2

Edit: I made a lot of assumptions in this answer that were not necessarily true. I just found out that the DB in question is Postgres. My answer is more tailored towards SQL Server, which until version 2016 did not have Row-Level Security. I'll leave my answer here for information purposes.

What you are suggesting is certainly not best practice, for many reasons. That doesn't mean you can't do it, but you must be willing to concede all of these points if you don't:

With your model, any user can access the database directly without using the website at all. (Edit: this is assuming you are in a local intranet and your users can access the DB directly using another tool outside of the website).

Because of this:

  1. Any user can add/edit/view/delete information for all users, not just their own information. (Note: this is not necessarily true if your DB supports Row-Level Security, and if you use it.)
  2. If any one user's credentials are compromised, all of the data is potentially at risk. (Note: this is not necessarily true if your DB supports Row-Level Security, and if you use it.)
  3. A user accessing the DB directly could incidentally run queries that do something they didn't intend. They can also unknowingly lock up the DB and make it unusable for others.

There are other reasons to not do it, but let's talk about the "norm" instead. Typically you manage your users from the website, and then give the website access to the DB with a single user that has the minimum set of permissions needed. If you ever suspect the DB password has been compromised, you can simply change that one password, and then let the website know about the new password (usually in an encrypted file of some sort that is not web accessible). This also makes it easier to provide different levels of permissions to different users, simply by controlling their access in the website code. You can also get much more granular, for example, you could allow certain users to only update a particular column of a particular table, if you wanted to.

Fortunately, there are many free out-of-the-box solutions that manage users and roles on virtually every popular web platform. I can't really think of good reason to venture away from the norm for connecting to a database via a website.

Edit: even though it may be the case that Postgres best practices differ from my suggestions in this answer, I still am not convinced that providing direct access to the DB is better than my suggestions. Without knowing more about Postgres, I would still prefer this model. But that could simply be because this is what I'm used to.

  • 1. not true. 2. not true. 3 true but can be handled w throttling on the web server. You are assuming that db would be accessible directly to the internet, and this is not necessary – Neil McGuigan Mar 4 '16 at 0:04
  • @NeilMcGuigan - I was under the assumption that this was a local intranet. – TTT Mar 4 '16 at 3:59
  • 1
    @NeilMcGuigan Even if they can not access the part of the network that the database is on, do you want that to be your only line of defense? Would you hand out username and passwords to your database to collegues or strangers just because it is not directly accesible to the internet? – Anders Mar 4 '16 at 14:47
  • @NeilMcGuigan - I edited my answer to address your statements. I agree with you that my statements were too broad, as they didn't consider Row-Level security. However, I think I still believe that given the two options, I would prefer this method over yours. But I won't go as far as saying which is better. – TTT Mar 4 '16 at 14:53
  • In what way does your method not violate the principal of least privilege? – Neil McGuigan May 28 '16 at 18:59
0

There are actually two questions here: how to authenticate a user against a web server, and which user to use to connect to the db as.

The real question is, should database security be up to the DBA or the web programmer?

Database servers are long-lived and are often shared by multiple applications.

Ideally, you would authenticate your (LAN) webapp users against an LDAP (aka ActiveDirectory) server, via Kerberos + SPNEGO. This gives you centralized authentication and single sign-on across many apps.

You could also authenticate webapp users directly against db users, example here . If that's where all your user authn info is, and you don't use LDAP/Kerberos, might as well use that.

The next question is which db user to use when connecting from the web server:

  1. Connect as a power user and add "real" userid info to your SQL statements (using parameters of course!)
  2. Connect as the real db user (this kills connection pooling)
  3. Connect as a limited user and impersonate the real user using set role

ONE is the most common, not because it's a good idea, but because people wanted to use connection pooling in the past and didn't understand impersonation. This kicks the Principle of Least Privilege directly in the crotch.

There are incredibly common, career-ending vulnerabilities made more likely with option 1: SQL Injection and Insecure Object References:

Web server connects to db as powerUser, Malice logs in to website as malice and finds a SQL injection and drops your payments table, as powerUser has that authority.

Your web developers append the userid to the sql for the /bank-accounts URL, but are stupid and forget to append the userid for individual bank account URLs (like /bank-accounts/999). Malice logs in as malice and views bobs bank balance.

These risks can be minimized by using impersonation and database row security (which essentially requires impersonation). PG 9.5 supports row security. You can mimic it in earlier versions with security_barrier check option views and triggers.

It also makes data auditing easier, as you have the real username right in your db as current_user

I would say a database like Postgres has better security than most web security frameworks, as it does Row Security, Column Security and has a nice hierarchical role system.

I've seen web security frameworks that select all rows for a given query, then discard everything that doesn't belong to user X. This totally screws up pagination and is terrible for performance. I've also never seen one that does Column/Field security right.

  • 1
    If you have 1 million users for your website, you would prefer to have 1 million DB user accounts rather than storing them in a users table in your DB? Don't you need a users table anyway to store user info beyond login credentials? – TTT Mar 4 '16 at 4:36
  • @TTT how is managing 1M db users harder than managing 1M application users? Read my links: I made tens of thousands of pg users in a second. Also, op is asking about dozens. You would need a table to store user info outside of their username and password, yes – Neil McGuigan Mar 4 '16 at 5:45
  • 1
    I'm not saying one is harder than the other. I come from the MS world and we typically don't do it that way. It sounds like postgres and SQL Server have different sets of best practices. Also, I don't think the speed of creating users is relevant because with most large applications I've worked on the user's create their own account, or they are authenticated using a Windows AD and their access is controlled by Domain groups. Also, many of the projects I work have multiple web apps connecting to a single database, so I find it easier to let the apps manage their own security. YMMV. – TTT Mar 4 '16 at 14:37
  • @TTT MS does a lot of things wrong. Your method sounds like you don't care about the principal of least privilege. Centralized security is cheaper and safer than de-centralized. – Neil McGuigan May 28 '16 at 18:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.