4 ms·

Not every database application is a public facing web application. Think about, for instance, a company's internal customer database (including billing, etc),

by RNCTX 6y ago

Not every database application is a public facing web application. Think about, for instance, a company's internal customer database (including billing, etc), accessed with internal tools. CSR/Support people need to be able to see billing status but not credit card numbers, for example. Different levels of management need to be able to generate reports, but again not get credit card numbers. For these use cases the opposite is true. The further you take auth and permissions away from the database/devops admins, the more times you're going to need to reinvent auth and permissions boilerplate for every internal tool under the sun. Which is a pointless exercise if none of the tools in question are ever going to be accessible outside of the company's VPN anyway. Active Directory / LDAP are not abandoned technologies, after all.

bavell 6y ago
What are the pros/cons of using entirely different (siloed) databases with uniform permissions instead of a single database supporting granular permissions? Off the top of my head, I'd say security might be improved but ease of use may suffer. Interested in hearing other's thoughts on the matter.
- carlps 6y ago
  The cons are inumerable. We've been down that road before and the whole world of business intelligence and enterprise data warehousing came out of it. When every functional unit has their own db, how do you answer questions of the business as a whole? How do you deal with data that multiple functional areas need? On top of that, managing a lot of small silos requires a lot more work and resources then the alternative. Eventually you're going to want to bring that data all together and now you have to have data architects and engineers developing etl solutions and then your resulting data warehouse is going to be that singular database you were trying to avoid from the start, with all the overhead of building it and maintaining it on top of all the maintenance of the source silos. See https://en.wikipedia.org/wiki/Data_warehouse https://en.wikipedia.org/wiki/Data_warehouse
  - pm90 6y ago
    One thing to note is that the singular database used for answering business questions is often not customer facing, and thus does not have as strong reliability guarantees (In my experience). Most of the business analytics happens asynchronously (etl pipelines everywhere). I'm not sure how to avoid this. The need to answer business question favors a single, queryable DB whereas the need to keep applications siloed and abstracted away favors multiple application data stores.
danenania 6y ago
Access control gets complicated though. Maybe a column should be accessible to a user, but only under x, y, z conditions that require hundreds of lines of logic to figure out. Do you really want all that inside the db?
- RNCTX 6y ago
  It certainly does. I've been at a company that had an improper permission result in a CSR getting elevated access and deleting the whole customer database, including every single credit card number, by mistake (admittedly this was back in like 2002). Better still, the devops guy who wrote the backup scripts had recently quit, and they were broken, the backups weren't being done. The company-saving backup from the previous day wound up being on an internal tools developer's laptop. I wasn't a database admin there or responsible for the permissions, but it was quite a wakeup call for everyone to double check their stuff when the rumors of what happened got out of the meetings the next day. The counter argument is, would it better to have 10, 20, 100 such possible situations mulling around the building every day, or just one? Maybe if there's just one, you put enough effort and people into the one to get it right. That's the pitch for AD / LDAP being used for all auth and permissions, and I think a compelling one at that.

by RNCTX 6y ago

bavell 6y ago

What are the pros/cons of using entirely different (siloed) databases with uniform permissions instead of a single database supporting granular permissions? Off the top of my head, I'd say security might be improved but ease of use may suffer. Interested in hearing other's thoughts on the matter.

carlps 6y ago
The cons are inumerable. We've been down that road before and the whole world of business intelligence and enterprise data warehousing came out of it. When every functional unit has their own db, how do you answer questions of the business as a whole? How do you deal with data that multiple functional areas need? On top of that, managing a lot of small silos requires a lot more work and resources then the alternative. Eventually you're going to want to bring that data all together and now you have to have data architects and engineers developing etl solutions and then your resulting data warehouse is going to be that singular database you were trying to avoid from the start, with all the overhead of building it and maintaining it on top of all the maintenance of the source silos. See https://en.wikipedia.org/wiki/Data_warehouse https://en.wikipedia.org/wiki/Data_warehouse
- pm90 6y ago
  One thing to note is that the singular database used for answering business questions is often not customer facing, and thus does not have as strong reliability guarantees (In my experience). Most of the business analytics happens asynchronously (etl pipelines everywhere). I'm not sure how to avoid this. The need to answer business question favors a single, queryable DB whereas the need to keep applications siloed and abstracted away favors multiple application data stores.

danenania 6y ago

Access control gets complicated though. Maybe a column should be accessible to a user, but only under x, y, z conditions that require hundreds of lines of logic to figure out. Do you really want all that inside the db?

RNCTX 6y ago
It certainly does. I've been at a company that had an improper permission result in a CSR getting elevated access and deleting the whole customer database, including every single credit card number, by mistake (admittedly this was back in like 2002). Better still, the devops guy who wrote the backup scripts had recently quit, and they were broken, the backups weren't being done. The company-saving backup from the previous day wound up being on an internal tools developer's laptop. I wasn't a database admin there or responsible for the permissions, but it was quite a wakeup call for everyone to double check their stuff when the rumors of what happened got out of the meetings the next day. The counter argument is, would it better to have 10, 20, 100 such possible situations mulling around the building every day, or just one? Maybe if there's just one, you put enough effort and people into the one to get it right. That's the pitch for AD / LDAP being used for all auth and permissions, and I think a compelling one at that.