1

I have an application that needs to know if it is connected to the original database that it was installed with or if the connection is to a copy of that database. Is there any known method to know if the database has been cloned and the application is no longer connected to the original? I am specifically interested in MS SQL Server and Oracle.

I was kicking ideas around for a stored procedure but that most likely doesn't have access to the hardware to confirm unique hardware information that would somewhat guarantee that the database is the one that it was originally connected to during installation.

I'm trying to prevent/detect cloning of a database so that there is only 1 "true location of truth".

John Gardeniers
  • 27,262
  • 12
  • 53
  • 108
millebi
  • 61
  • 5
  • Why though? What purpose does doing this serve? – MDMarra Dec 11 '12 at 22:11
  • You mean one "single point of failure," right? I can't think of any good reason to do this. – Michael Hampton Dec 11 '12 at 22:22
  • Why don't you trust your DBAs? – mfinni Dec 12 '12 at 03:55
  • I can see the rationale behind it. It's not a matter of trusting DBA's. Say you sell a product that makes use of a database. It is important to you (for, say, warranty purposes) to know that the database you installed the product with, is the very same database that is being used once the client calls your customer support line. Might sound a *bit* strange, but still plausible. – Isaac Dec 12 '12 at 05:03
  • @Isaac, but if it's a clone, then it shouldn't matter. And if it's not a clone, support should be able to see that immediately. – MDMarra Dec 12 '12 at 16:27
  • I'm working on an Audit system that has to ensure that someone (including the customer) doesn't switch a copy of the database in, then "do bad things" and then switch it back. Essentially the next step beyond FssAgg (I'm a belt, suspenders and glue kind of person). It looks like I'm probably going to have to try something "sneaky" in a stored procedure to create a value that can be recalculated to do the detection. – millebi Dec 12 '12 at 21:29
  • There's nothing that you can do that a customer can't work around. Look at everytime a DRM scheme is cracked. If you don't trust what someone else might do with the data, don't let the data leave your site. That way, you can actually audit changes! – mfinni Dec 12 '12 at 23:55

3 Answers3

3

I have cloned Oracle databases for two purposes. To the best of my knowledge only one of these will meet your requirement.

  • I have cloned database to provide an additional instance (sometimes read-only) for another environment or use. In this case I change the database name. These clones can be detected by checking the database name.
  • I have cloned the database for recovery or roll-back purposes. Usually, these replace the original database. I know of no way of identifying this as a cloned database.

There is data that may indicate the database was cloned, or moved. Things like filenames for tablespaces are likely to change when a database is cloned, but they can change even if the database is not cloned. I believe you can retrieve the hostname or IP address on which the database is running. Again, this may be indicative that the database is cloned, but can't be used to verify that it is a clone.

While it is possible to end up connected to the wrong clone, it is generally somewhat difficult to get in that situation. Spend some time understanding how your connections to the database are routed.

BillThor
  • 27,354
  • 3
  • 35
  • 69
  • :) I need to do the detection from the db client end and not on the db server itself. Also someone running the DB in a VM could easily clone it including the server it's on. I suspect I may be in one of those infinite impossibility problems :( – millebi Dec 12 '12 at 21:36
  • @millebi The information I referred to can all be check from the client. With the connection methods I use, most problems would involve DNS changes and/or ARP poisoning. – BillThor Dec 13 '12 at 00:44
1

My take on it is this: if it is made of bits, it can be cloned in a way that you will never be able to tell the difference between the original and the clone. Your only chance would be if there was some sort of a calculated value that each database instance returns, so -

  1. Each database instance returns a different value.
  2. The value returned by each database instance is consistent across invocations.

Condition number (2) is your villain here. If the value returned by each instance is consistent across invocations, then the value must rely on some sort of a stored value, somewhere. That stored value can be cloned, too...

Q.E.D

Isaac
  • 256
  • 1
  • 5
0

'show slave status;' you know which server you are connected to...the show slave status will tell you which server is the master...if they are on different servers. You may be able to use the global variable server_id to figure out the difference on the same server...I have not played with it.

jim_m_somewhere
  • 168
  • 6