3 ms·

It's easy in most modern multiuser databases to restrict a database user to SELECT privileges only, and to set a query timeout of something like 5 seconds (or 3

It's easy in most modern multiuser databases to restrict a database user to SELECT privileges only, and to set a query timeout of something like 5 seconds (or 30 or whatever is appropriate), right? Then the queries can be run only using that database user, and there's no risk of INSERT/UPDATE/DELETE. Injection isn't possible. And you can limit access to only the relevant tables as well, so you can't accidentally leak PII.

drysart 1y ago
Most ancient databases can do it too. It's been a standard feature of databases for decades. It's a little horrifying seeing the hoops people are jumping through to solve a problem that's more quickly, more easily, and more fully solved with your database of choice's equivalent of a CREATE USER statement; and maybe creating some views on tables if you have a need to hide individual columns from users.
chasil 1y ago
No, a user that is only granted SELECT may write to the database. The trivial example is sorting. A more nuanced example, in the world of Oracle, is block cleanout. A block may be encountered that has been modified by a past transaction where the commit status is not immediately known; the query session is responsible for either updating the (confirmed) commit status, or rolling back the block from the UNDO/rollback disk image. So readers always write, likely in every database to some degree.
- crazygringo 1y ago
  I don't know what you're talking about. Sorting doesn't write to a database. You can check yourself by looking at last-modified-by timestamps on database files. They don't change. Gigantic sorts may need to write to a temp file but the temp file isn't part of the database, it's a temp file. And I don't know how Oracle works under the hood, but the entire point of transactional multiuser databases is that one user can read while another writes a transaction. A read neither commits nor rolls back a separate transaction currently in progress. It reads from the pre-transaction state.
  - chasil 1y ago
    In Oracle, a user has a default tablespace, and a temporary tablespace. When a sort exceeds SORT AREA SIZE, then it writes into a sort segment in their registered temporary tablespace. Any or all of the datafiles behind this tablespace will show updated mtime. All users share a single sort segment within a tablespace. And users running SELECT will write to the database if a delayed block cleanout needs to occur. https://asktom.oracle.com/ords/asktom.search?tag=delayed-block-cleanout https://asktom.oracle.com/ords/asktom.search?tag=delayed-blo...
    - crazygringo 1y ago
      All of that sounds like implementation details to do with files. No logical database writing occurs. The relational data in the database cannot be changed by a SELECT command, which is the point of user permissions. Even if temp disk space is used or stale data gets cleaned up in the process, that is irrelevant in the context of preventing inserts/updates/deletes. So not sure why you're bringing it up?
      - chasil 1y ago
        Logical? If the transaction committed, then the block metadata must change, but the row content remains the same. If the transaction is not active and did not commit, then the uncommitted data must be discarded, and the saved rollback must be restored. That does not fit within what I assume to be your distinction between a physical and a logical write. It does preserve acid.

drysart 1y ago

Most ancient databases can do it too. It's been a standard feature of databases for decades. It's a little horrifying seeing the hoops people are jumping through to solve a problem that's more quickly, more easily, and more fully solved with your database of choice's equivalent of a CREATE USER statement; and maybe creating some views on tables if you have a need to hide individual columns from users.

chasil 1y ago

No, a user that is only granted SELECT may write to the database. The trivial example is sorting. A more nuanced example, in the world of Oracle, is block cleanout. A block may be encountered that has been modified by a past transaction where the commit status is not immediately known; the query session is responsible for either updating the (confirmed) commit status, or rolling back the block from the UNDO/rollback disk image. So readers always write, likely in every database to some degree.

crazygringo 1y ago
I don't know what you're talking about. Sorting doesn't write to a database. You can check yourself by looking at last-modified-by timestamps on database files. They don't change. Gigantic sorts may need to write to a temp file but the temp file isn't part of the database, it's a temp file. And I don't know how Oracle works under the hood, but the entire point of transactional multiuser databases is that one user can read while another writes a transaction. A read neither commits nor rolls back a separate transaction currently in progress. It reads from the pre-transaction state.
- chasil 1y ago
  In Oracle, a user has a default tablespace, and a temporary tablespace. When a sort exceeds SORT AREA SIZE, then it writes into a sort segment in their registered temporary tablespace. Any or all of the datafiles behind this tablespace will show updated mtime. All users share a single sort segment within a tablespace. And users running SELECT will write to the database if a delayed block cleanout needs to occur. https://asktom.oracle.com/ords/asktom.search?tag=delayed-block-cleanout https://asktom.oracle.com/ords/asktom.search?tag=delayed-blo...
  - crazygringo 1y ago
    All of that sounds like implementation details to do with files. No logical database writing occurs. The relational data in the database cannot be changed by a SELECT command, which is the point of user permissions. Even if temp disk space is used or stale data gets cleaned up in the process, that is irrelevant in the context of preventing inserts/updates/deletes. So not sure why you're bringing it up?
    - chasil 1y ago
      Logical? If the transaction committed, then the block metadata must change, but the row content remains the same. If the transaction is not active and did not commit, then the uncommitted data must be discarded, and the saved rollback must be restored. That does not fit within what I assume to be your distinction between a physical and a logical write. It does preserve acid.