This is a new one for me. Hopefully someone can help.
My client wants to allow their solution’s database to be hosted by whatever database their customer’s might have in their network. No problem there.
However they don’t want someone going into the database and mucking with some of the tables that shouldn’t be mucked with.
There are typically two ways to deal with a problem like this: Enforce in code or Enforce by license agreement.
This really isn’t that data-heavy of an application, so I was considering that I could allow the database to be created via script (tables and fields) but that the critical data was encrypted in my app before it was inserted, and decrypted when it was extracted. I have no idea if this is a terrible idea or if there is a standard way to handle it.
Not sure that stops any one from logging in to the DB and mucking with tables
About the only way you can make it so they cannot log in and see the tables or muck with them is to have the DB set up and administered by your app and only your app and the customer has no way to log in to their server and see the tables at all
And they dont have the administrator password for that database
But even then if its their server their DB admin could probably just reset that password and lock your app out
If your app encrypts the data before it puts it in the db you lose the value of a sql database since you cant use sql queries very well with encrypted data
you could create something like an md5 of each row
then you could tell if a row had been altered
whether you put that in the DB itself or not is a different consideration
It prevents them from extracting and reusing the data externally.
It prevents mucking by invalidating it and breaking the app, forcing good behavior.
The critical data needing to be encrypted isnt what is searched, but I take your point.
If its like ledger or something like that then you sometimes do MD4NewRow = MD5(some critical data from the row) + MD5PrevRow
That way you can validate if the critical data in each row has been altered and could evaluate if row has been removed in between. And its hard to just update the MD5 when trying to force change of row since that would affect the MD5 of rest of the ledger.
would it be possible to just NOT put this data in the users database at all ?
if you have the users data in their DB and this critical data in the app (sufficiently encrypted & obfuscated that its not easily extracted)
And then you can use an in memory DB to join the users data to this other data ?
[quote=484188:@Björn Eiríksson]If its like ledger or something like that then you sometimes do MD4NewRow = MD5(some critical data from the row) + MD5PrevRow
That way you can validate if the critical data in each row has been altered and could evaluate if row has been removed in between. And its hard to just update the MD5 when trying to force change of row since that would affect the MD5 of rest of the ledger.[/quote]
exactly - we had a sql database from a vendor that was the accounting data and this is basically how they insured that no single row could be modified without breaking the consistency and accuracy of the data
kind of like a block chain but this was back in the 2000’s before block chain became “the thing to solve all ills”
Yeah if you dont have control of that other database then using something local or in memory might make sense
You’ll have to manage things a bit differently since you wont be able to query your local data + user data in the remote server in one query (would be nice but …)
It’d be nifty IF SQLite’s “attach database” would allow you to attach a remote database but … we’re dreaming now
in some cases you could also hold the data in memory and make a search with OOP.
i made one invoice app that stores a complex structure as xml Object serialization in a single field. + one primary search field.
the other search was made with methods in classes.
Really depends on the DB engine and whether its managing those and automatically assigning them etc
When you define an integer primary key on SQLite is basically manages those more or less for you and I dont think it ever assigns a negative value. And I have NO idea what it would do with them If you manually inserted one (which you can still do)
right… I have an interesting use-case that supports that, but I’m going to stay hushed about it until I can think it all the way through to avoid looking stupid…
if you use xml Object serialization you can also save/exchange/import the data via file system.
with Object serialization you do not have id to id references.
you do not need this annoying sql query that have no standard.
I’m trying to get a sense of how large the largest database might be. I could be wrong, but I’m starting to believe that it’s a reasonable size to simply load into memory. Then I can very easily search with something as simple as a dictionary - this isn’t really complex relational stuff at all.
FWIW, we use negative ID’s for records that we add ourselves that must not be changed by a user. We use triggers to enforce that. Even developer would have to disable the trigger to be able to change that data.
Kem, that’s EXACTLY what I was thinking. My customer wants to add more defaults in an update but they don’t want IDs to conflict with ones that their users added.
then dont rely on the DB doing the right thing just by convention
design your db so users cannot insert ones that might conflict or be confused
and maybe dont rely on the row id as the primary key (which has issues in sqlite anyway when you vacuum)
design the db so you have well chosen primary keys
