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?
Best Answer
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.