Ask HN: Can users update data in your app's DBs with SQL?

5 pointsjames_anderson5y ago3 comments

Do you find yourself in the difficult position of having users who make updates to the database owned by your application?

In my experience this happens because the application has gaps in functionality or the process would be much faster to complete in SQL then it would in the app. This makes turning off access difficult as these processes are often business critical.

Problems caused: - Short-term - people make mistakes and corrupt/destroy data or cause downtime. - Long-term - data can't be trusted.

I'd be interested to hear people's war stories and the methods/tech used to escape.

My thoughts are: 1. Track scripting 2. Catalogue reasons for scripting 3. Address the functionality gap that will remove the most common reason for scripting 4. Remove access to tables users were updating via SQL before functionality gaps were filled 5. Continue 1-4 until done

3 comments

3 comments · 3 top-level

clusterhacks5y ago

No, but several small apps in my organization suffer from a similar problem. Gaps in the application cause users to either make mistakes or want to do "batch" type operations so users create some awful spreadsheet that gets sent the team's developers. Those developers then take those spreadsheets and do some series of one-off SQL scripts to pull those changes/updates into the db backends.

You could try to move to the mess we have - at least our updates are being done by app developers. These devs typically have a better shot at not corrupting data or breaking things.

Longer term (and I haven't been successful at this), I would try to convince the dev team that these types of use cases (users executing SQL) represent really fruitful feature requirements. In our case, I'd like to make app changes that allow the users to do these types of things via features in the app. But there are also trade-offs for dev time and whether or not these user-driven SQL hacks are actually features or more like "one-off" tasks . . .

quickthrower25y ago

No. Never for users to make updates. This seems like a terrible situation unless your app is merely a BI reporting tool and the customer owns the entire schema.

I have hated it when a customer has read access to their own DB to run reports on our app. Even then they can cause performance issues and create dependencies that means they break if we change the schema. In other words their IT adds to our support burden in arbitrary and unpredictable ways.

If you have to allow DB access and write access then use the DB views stored procedures and security features to lock it down. Maybe they think they have access to the User table but infact it’s a view and if they update it runs validations. This allows you to place some business logic and protection between the precious data and the customer.

tdeck5y ago

Back when applications were typically designed in this way, the best practice was to limit database access to running certain stored procedures on a per-client basis, and use those stored procedures as the client's "API" to the data.