12 ms·

I may be wrong about the history of security work on databases (feel free to correct me!) , but I think the security model was built mostly for an age where a s

by pm90 6y ago

I may be wrong about the history of security work on databases (feel free to correct me!) , but I think the security model was built mostly for an age where a single DB would be used by many apps, thus access control at the DB level seemed natural to the DB admins. Today, with managed/containerized DBs and Microservices and share nothing architecture, I’ve seen most apps use their own database instance, in which case the access control stuff seems to be more of an obstacle than useful. e.g. just the other day, I ran into issues because the user used for schema migration did not have access over some tables in my apps database in Postgres since it has table level access control.

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.
bavell 6y ago
I'm curious to hear what others think about this as well. I run some WordPress sites in containers and I give each one it's own DB instance. I just create the root account with a strong password, give it to WordPress and call it a day. Makes ops much simpler although it's probably not the best use of resources (e.g. memory). Any big downsides to doing things this way? I'm no DBA so I appreciate any insights.
- PudgePacket 6y ago
  It may be more cost effective to use the same db instance, but that has it's own issues, like 1 site dragging everyone down, all sites need to handle the same db version and upgrade together.. I guess it's also just more instances to manage in total, instead of 1 + n it's 2n. You _could_ run the dbs on the same instance but not using the same DB process, via containerisation perhaps. That way you could reduce your instance count while still keeping (somewhat) operational separation..
- neop1x 6y ago
  Scaling, durability and backup. Your single small container DB may not be able to serve high requests per second. It depends on the architecture and caching, though. If it goes down, your app goes down, no failover or replicas. And it is difficult to backup many isolated DB instances.

by pm90 6y ago

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.

bavell 6y ago

I'm curious to hear what others think about this as well. I run some WordPress sites in containers and I give each one it's own DB instance. I just create the root account with a strong password, give it to WordPress and call it a day. Makes ops much simpler although it's probably not the best use of resources (e.g. memory). Any big downsides to doing things this way? I'm no DBA so I appreciate any insights.

PudgePacket 6y ago
It may be more cost effective to use the same db instance, but that has it's own issues, like 1 site dragging everyone down, all sites need to handle the same db version and upgrade together.. I guess it's also just more instances to manage in total, instead of 1 + n it's 2n. You _could_ run the dbs on the same instance but not using the same DB process, via containerisation perhaps. That way you could reduce your instance count while still keeping (somewhat) operational separation..
neop1x 6y ago
Scaling, durability and backup. Your single small container DB may not be able to serve high requests per second. It depends on the architecture and caching, though. If it goes down, your app goes down, no failover or replicas. And it is difficult to backup many isolated DB instances.