We have the following setup:
- Multiple production database containing private data which is used by desktop software
- A web database for a public website which needs some data from the private databases
- An intermediary database which contains a few views and stored procedures that pull data from the private databases
Currently the website logs in to web database, and the web database connects to intermediary database to pull data or execute stored procedures on the production databases. All the databases are on the same SQL instance, and the entire process uses the same user account.
The user account has full access to the web database and the intermediary database, but can only access specific views and stored procedures of and private database
Is this really more secure than just making the public database connect directly to the private ones?
It seems like the intermediary database is only there to complicates things since the same login is used to access data in all the databases, and it is already limited to just the views / SPs it needs in the private databases. I am hoping to remove it.