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