18

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.

Improve this question
2
  • 2
    You have an intermediary; those procs/views on the web DB. As long as your perms are set correctly the extra database seems like unnecessary abstraction with no security implications at all.
    – Zelda
    Commented Feb 7, 2012 at 17:20
  • @BenBrocka That's what I was thinking, but I wanted to double check before I removed it entirely
    – Rachel
    Commented Feb 7, 2012 at 17:33

2 Answers 2

Reset to default
7
+50

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

Improve this answer
8
  • Thank you. There are a few reasons the web data is in a separate database, but the biggest one was that there are actually multiple private databases, and the web one is responsible for going to the correct private database to obtain data. My main concern was finding out if the intermediary db actually adds anything to the security of the site because I would like to remove it. So far it is sounding like it doesn't add anything other than an extra layer of complexity.
    – Rachel
    Commented Feb 8, 2012 at 14:31
  • @Rachel: the "multiple private databases" bit is quite relevant to any answer, especially this one... I'd have said something different if this was known
    – gbn
    Commented Feb 8, 2012 at 14:54
  • @Rachel: why you don't mentioned the "multiple PrivateDB" on the question? That changes everything... ;-\
    – Fabricio Araujo
    Commented Feb 8, 2012 at 17:21
  • @FabricioAraujo I edited my question 2 hours ago to include that information. I didn't think it mattered at the time since I was asking about the security of the database arrangement, not the design of it.
    – Rachel
    Commented Feb 8, 2012 at 17:24
  • 1
    @FabricioAraujo: the requisites defines the design, so a design must be correct before being secure. A secured incorrect design is worthless - a insecure correct design can be made secure anytime.
    – Fabricio Araujo
    Commented Feb 8, 2012 at 17:28
4

The answer is: it depends. If the private database is not accessed from outside internal LAN (or only through an VPN), this scheme adds security as someone using the credentials of the WebSite would have just limited access to that private DB Server (and will have some more work to get into your internal network - time that can be handy in discovering the intrusion and closing the hole).

If not, I don't think it adds anything except complexity.

EDIT:

Seems I've misread your question, so let's see if I understood correctly now: the IntermDb is a empty database just to connect to PrivateDB OR it is an DB which have views/procedure of its' own which connects to the PrivateDB to retrieve data?

In the first case, WTF? Rip it off. It's worthless, unless someone wants to audit it to profile access to the PrivateDB in a lazier way (and making WebApp developers work harder). SQL Profiler can filter using DB_ID...

In the second case, I sustain my previous answer.

EDIT: "I still don't see how this is more secure than connecting the private db directly to the public db since all 3 databases on on the same SQL instance and the login used for all 3 databases is the same, and can only access specific views and stored procedures in the private db anyways".

Having the intermediary means that invader would have to dig in your code know the existance of an IntermDB - and it have to dig your code on it to know that exists an PrivateDB.

If you put your PrivateDB on another server internal to your organization LAN/WAN (if possible), it can give enough time to the admin detect and block the attempt of invasion. If you use synonyms, this move is smooth as all you have is rebuild them to the existing code go to the right place.

Also you gain other advantages: since all code have to pass through IntermDB to access data in PrivateDB, no developer would be tempted to try bypass it - and that attempt can be easily spotted on SQL Server Profiler as the DB_ID of the request of PrivateDb data would be WebAppDb.

Improve this answer
2
  • Wouldn't the security be the same as if you had the public db on one server, and the private one on another? What does adding a 3rd database to that scheme do to increase security? Although in my situation, all 3 databases are on the same SQL server instance (added this info to my question too)
    – Rachel
    Commented Feb 7, 2012 at 18:20
  • In response to your edit, the middle db contains it's own views and stored procedures which returns data from the private db. I still don't see how this is more secure than connecting the private db directly to the public db since all 3 databases on on the same SQL instance and the login used for all 3 databases is the same, and can only access specific views and stored procedures in the private db anyways
    – Rachel
    Commented Feb 7, 2012 at 20:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.