Is it more secure to go through a 3rd database to connect two databases using the same login

database-designSecurity

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.

Best Answer

One thing jumps out here:

The entire process uses the same set of login credentials

Problem

So hypothetical userX (whether some meatsack using Excel, or IIS AppPool Identity) can see some views and code. It doesn't matter what database these views and code are in because userX is setup in 3 databases anyway.

However, you lose ownership chaining like this.

Let's say WebDB.dbo.SomeProc calls PrivateDB.dbo.SomeTable. UserX requires permissions on both objects. If this was OneDB.WebGUI.SomeProc using OneDB.dbo.SomeTable then only the OneDB.WebGUI.SomeProc needs permissions. Permissions on referenced objects with the same owner are not checked.

Note: I haven't looked too deeply at cross database ownership chaining. I only know plain old "ownership chaining" well

Now, as per comments you really have 2 databases that can be combined. Not 3 which was originally implied. However, the intermediate and web can be combined.

The other "private" databases can perhaps be combined, but that'll be a separate issue. See the bottom link for a fuller discussion of "one database or many"

Solution?

If the extra databases are code containers only, then schemas are a better idea.

This sounds like you've used "Database" where you should use "Schema" (in the SQL Server sense, not MySQL sense). I'd have a WebGUI schema, a Helper or Common schema (to replace Intermediate database) and Desktop schema. This way you separate permissions based on clients and just have one database

With one database (in addition to "ownership chaining") you can also start to consider indexed views, SCHEMABINDING (I use it always) and such that can't be done with seperate databases

For more on schemas, see these questions:

Finally, there appears no reason to have separate databases based on "transactional integrity not required". See this question to explain this: Decision criteria on when to use a non-dbo schema vs a new Database

Related Question