3 ms·

My assumption (with modern applications!) is that nothing but the role directly owning the data will access the data. The development and DBA teams will likely

My assumption (with modern applications!) is that nothing but the role directly owning the data will access the data. The development and DBA teams will likely have a role they can assume after performing a carefully-audited breakglass procedure to use in an emergency (rare) or to fulfill audit tasks. At least in my org this is a well-known problem with legacy applications sharing databases. Limit access to the database to a single role, used by a single application, and you absolve so many issues.

jbmsf 3y ago
100%.
chasil 3y ago
When your data has outlived several generations of front-end technologies (mine started with Powerbuilder), you will find that ownership of the data does not control the evolution of how it will be used with what replaces past clients. One particularly interesting Oracle problem is: ORA-00060: deadlock detected while waiting for resource Tom Kyte's book, Expert One-on-One Oracle, describes the primary culprit: "Oracle considers deadlocks to be so rare, so unusual, that it creates a trace file on the server each and every time one does occur... The number one cause of deadlocks in the Oracle database, in my experience, is un-indexed foreign keys." For another perspective, add to this a default setting in every SQLite database: $ sqlite3 verynew.db SQLite version 3.34.1 2021-01-20 14:10:07 Enter ".help" for usage hints. sqlite> .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; COMMIT; Foreign keys can cause interesting problems, and SQLite specifically prefers to avoid them.
pixl97 3y ago
>Limit access to the database to a single role, used by a single application, and you absolve so many issues. This kinda sounds like "Get rid of 90% of the usefulness of having a database" Of course, maybe you mean make the same data the DB has available via API, or make other users of the data read only.
- arrowleaf 3y ago
  Yes, if you have downstream services that need to make use of your data, you would provide an API. That way you can manage the number of connections, how the queries are formed, any sort of encoding/decoding, authorization, caching, etc. Definitely not limited to read-only.

jbmsf 3y ago

100%.

chasil 3y ago

When your data has outlived several generations of front-end technologies (mine started with Powerbuilder), you will find that ownership of the data does not control the evolution of how it will be used with what replaces past clients. One particularly interesting Oracle problem is: ORA-00060: deadlock detected while waiting for resource Tom Kyte's book, Expert One-on-One Oracle, describes the primary culprit: "Oracle considers deadlocks to be so rare, so unusual, that it creates a trace file on the server each and every time one does occur... The number one cause of deadlocks in the Oracle database, in my experience, is un-indexed foreign keys." For another perspective, add to this a default setting in every SQLite database: $ sqlite3 verynew.db SQLite version 3.34.1 2021-01-20 14:10:07 Enter ".help" for usage hints. sqlite> .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; COMMIT; Foreign keys can cause interesting problems, and SQLite specifically prefers to avoid them.

pixl97 3y ago

>Limit access to the database to a single role, used by a single application, and you absolve so many issues. This kinda sounds like "Get rid of 90% of the usefulness of having a database" Of course, maybe you mean make the same data the DB has available via API, or make other users of the data read only.

arrowleaf 3y ago
Yes, if you have downstream services that need to make use of your data, you would provide an API. That way you can manage the number of connections, how the queries are formed, any sort of encoding/decoding, authorization, caching, etc. Definitely not limited to read-only.