1

Background

I have inherited a system that has several hundred databases.

The primary "Clients" Database represents all clients with several tables like:
WebUsers = username | password | server_id
Servers = server_id | ip_address |

The remaining "ClientInfo" databases are intended for one client. Tables like: Customers = id | first_name | last_name

I am not a DBA. I'm a web developer who inherited a rough built system. So I apologize ahead of tie if this question seems silly.

The process

The client visit a login server: login.example.com. This server has access to the "Clients" database. They login to their account which takes them to the correct web application server, sass1.example.com, that has credentials found in the "Servers" database and grants them a secure session.

I think this is a pretty basic SASS web application setup. Think sales-force, get-satisfaction, that kind of thing.

My Problem

All databases are managed under a single database login. So the login server technically has access to all the client databases. And each application server has access to all databases. The only limiter being a single string in the session that identifies the user.

From a security standpoint it is my understanding that these databases should each have their own credentials. So that if one is breached via code injection remaining databases are secure.

For example my code logs into the client db using environment variables. Then selects the database name that is stored in the secure session. A code injection could easily submit a query that changes the active database.

What is the secure way to manage this system?

My rough plan was to have the login server point to the "Clients" database through a unique db user that has access to only that database. Then I would store the db user/password for each clients database in a "ClientList" table.

Then on login the client specific database credentials would be pulled from an encrypted value in the "server" table. So the session would check if it's in a valid login state then instead of the string pointing to a database name it has an id that points to the "ClientList" table that has encrypted login credentials to just their database.

But this doesn't seem much different than just managing all db's on the same login since the clients database still has all the passwords. A code injection would have access to the decrypt algorithms and just log in with different credentials. What is the right (secure) way to manage this system?

  • When you say all the logins are saved in at able, are these webapp logins used to log into your own web app back end, or is it actually a log in for SQL Server users which then are granted access to the DB directly? – Ali Razeghi Jul 1 '16 at 19:07
  • @AliRazeghi I added some details to the question. The web servers all use one set of credentials to access the database engine and those credentials have access to all databases on that engine. The web user logs into the web server with a unique set of credentials for each person. Does that clear up your question? – danielson317 Jul 1 '16 at 19:17
  • Thank you. So to ensure I got it right, we have a single user in the connection string that the webapp uses to connect to the SQL Server back end. That single user is the DBO or has access of all the databases in the server used to power this web app. That handles the IIS to SQL Auth. This is the issue here. As far as the webapp users go, they are saved somewhere else and are not a consideration for the question you posed so we can effectively ignore that for now. Correct? – Ali Razeghi Jul 1 '16 at 19:21
  • Sounds about right. A little behind on Database lingo but I think I understood the question :). But it is worth mentioning that the security hole I'm concerned with is a web user getting code injection access and deleting another clients database. – danielson317 Jul 1 '16 at 19:22
  • Which DBMS are you talking about? – a_horse_with_no_name Jul 5 '16 at 13:19
0

It seems like a limiting factor here might be that we don't want to make a connection string for each client and separate the user used to connect to the DB back end that way. As a result we are using a SQL Database user that has full access to all the databases, with them really only needing access to 1 DB that being their own. In the current security model, if a SQL injection attack were to occur, they would be be able to have access to all databases, instead of just the 1 user database they need due to the connection string.

To resolve this issue you're proposing that you create a database to hold customer information such as login ID and password to their SQL Server database user which has access to read/write/execute, and want to know if it provides any benefits over the current model.

You could do as you proposed by having a user that can only accept sanitized and paramterized stored procedure queries with being granted only execute permissions on the master users/login table. Once you get that value based on your uniqifier (be it ID, company name, etc.), you are mostly protected from a SQL injection attack. An extra level of defense would be to encrypt the value within the DB and have the app decrypt the username and password before passing it in. This would fully protect you from someone getting your username/password from SQL Injection even if they did manage to pull out the raw value from the DB engine. They would have to also get the decrypting private key off the web or key server before they could read the credentials needed to access the databases.

By doing this you are at least protecting your company from having someone make a change on every single DB in the enterprise if they were to break in, and they would be limited to just messing up 1 customer; unless of course they got your private keys or the decrypted password list some other way.

This is of course just a quick review of what you proposed. There are tons of different ways to do security and this might not be the most ideal in your scenario, I don't know what would be best from the limited info, but it would work to answer your question and give you that extra line of protection of encryption, multiple places they'd have to break in, and separate users owning each DB which is always nice.

SQL 2016 has some great new security enhancements you could utilize too if you want such as always encrypted data and saving your keys elsewhere if you wish to take this to the next step.

  • So thanks for summarizing my question but I don't see any new info in this answer. – danielson317 Jul 5 '16 at 12:44
  • It addressed your question of "But this doesn't seem much different than just managing all db's on the same login. What is the right way to do this?" Then went through how the security you're working for could be addressed across the whole stack by introducing encryption to your scenario, grant rights only on executing stored procs which removes any need to 'read/write' access. This answer was written for SQL Server, Postgres/MySQL will have different methods but similar end result. – Ali Razeghi Jul 5 '16 at 17:08

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.