Recently, I was queried on what would be the best way to retrieve data from a database within our enterprise, which is required by a vendor to consume. Basically, an external application looking to consume our data.
As obviously, suggested to expose as API (REST/SOAP) or spew out a CSV/TXT/sth. file which will be consumed by the external vendor. It seems that none of the above could work for the vendor, without writing some customized code. With this stage set, I thought of enumerating why one shouldn't give access to their database to someone whom we aren't in control. Let's begin !
(1) Accessing data through a client application/DB Connectivity API (Eg. JDBC)
NO-NO: Database patches/upgrades are normal and if we have to do any of those, we need to remind ourselves to check with the external vendor, whether it is all good, from their DB access API/client. This could have a roll-on effect on other databases installed in the same host. A very tight dependency, which DBAs will hate.
Additionally, there is no assurance of how good the database connectivity life-cycle is. Personally seen external applications screwing up enterprise database, by not closing DB connections or utilizing a high DB processes, which in turn affect other applications performance. The very hard part is debugging the issue, when all that one knows an external application "ABC" is accessing our database.
(2) Giving access as a database view, to prevent any misadventures from external vendor.
NO-NO: Consider a situation, where we would like to use the same DB view with some minor datatype or new field changes, we need to go to the vendor to check whether this is fine. If not, we will create another database view, very similar to the one used by the external vendor and the schema mess-up begins here.
(3) Vendor says, direct DB access is the only way
NO-NO: This is where the principles of integration within organization kick-in. Deviating from it, means we are getting into the "spaghetti" world of integration, which may not be a nightmare tonight, but will happen for sure in the near future.
As obviously, suggested to expose as API (REST/SOAP) or spew out a CSV/TXT/sth. file which will be consumed by the external vendor. It seems that none of the above could work for the vendor, without writing some customized code. With this stage set, I thought of enumerating why one shouldn't give access to their database to someone whom we aren't in control. Let's begin !
(1) Accessing data through a client application/DB Connectivity API (Eg. JDBC)
NO-NO: Database patches/upgrades are normal and if we have to do any of those, we need to remind ourselves to check with the external vendor, whether it is all good, from their DB access API/client. This could have a roll-on effect on other databases installed in the same host. A very tight dependency, which DBAs will hate.
Additionally, there is no assurance of how good the database connectivity life-cycle is. Personally seen external applications screwing up enterprise database, by not closing DB connections or utilizing a high DB processes, which in turn affect other applications performance. The very hard part is debugging the issue, when all that one knows an external application "ABC" is accessing our database.
(2) Giving access as a database view, to prevent any misadventures from external vendor.
NO-NO: Consider a situation, where we would like to use the same DB view with some minor datatype or new field changes, we need to go to the vendor to check whether this is fine. If not, we will create another database view, very similar to the one used by the external vendor and the schema mess-up begins here.
(3) Vendor says, direct DB access is the only way
NO-NO: This is where the principles of integration within organization kick-in. Deviating from it, means we are getting into the "spaghetti" world of integration, which may not be a nightmare tonight, but will happen for sure in the near future.
