6 ms·

What people often don't realize is that in a big business system a user may have no permission to raw data of some table, but may have permission to report whic

by adontz 1y ago

What people often don't realize is that in a big business system a user may have no permission to raw data of some table, but may have permission to report which includes aggregated data of the same table, so report permissions cannot be deducted from base CRUD permissions. If such SIAAS - Checks that query is SELECT query (can be tricky with CTE, requires proper SQL parser) - Allows editing said query by superuser only - Can be parametrized, including implicit $current_user_id$ parameter - Has it's own permissions and users can run the query if they have permissions It's safe enough. I've seen and applied such "Edit raw SQL in HTML form" many times. It's super flexible, especially combined with some CSV-to-HTML, CSV-to-PDF, or CSV-to-XLS rendering engine.

leprechaun1066 1y ago
Most applications backed kdb+ do just this. It comes with its own parser and you can query tables using something like an ast. For example the user might ask for data with the constraint where TradingDesk=`Eq, AvgPx>500.0 which kdb+ parses into ((=;`TradingDesk;(),`Eq);(>;`AvgPx;500.0)) As a dev on the system I can then have a function which takes in this constraint and a list of clients that I want to restrict the result to. That list of clients could come from another function related to the entitlements of the user who made the request: applyClientRestriction:{[constraint;clients] constraint,enlist(in;`Client;enlist clients)} Which results in an extension of the constraint like this for two clients A and B: q)applyClientRestriction[((=;`TradingDesk;(),`Eq);(>;`AvgPx;500.0));`ClientA`ClientB] ((=;`TradingDesk;enlist`Eq);(>;`AvgPx;500.0);(in;`Client;enlist`ClientA`ClientB)) Then that gets passed to the function which executes the query on the table (kdb+ supports querying tables in a functional manner as well as with a structured query language) and the result has the restrictions applied. It's really nice because, once parsed, it's list processing like in a lisp and not string processing which is a pain.
indigo945 1y ago
> - Checks that query is SELECT query (can be tricky with CTE, requires proper SQL parser) Not only is this difficult parsing-wise, there's also no reason to assume that a select query is read-only even when no CTE or subqueries are involved. Function calls in the select clause can also write data. > - Has it's own permissions and users can run the query if they have permissions This is the important one. If the role the query runs as doesn't have write permissions on any table, then the user can't write data, period. Note that this is often not as easy to implement as it seems. For example, in PostgreSQL, neither set role nor set session authorization actually prevent the user from doing malicious things, because the user can just reset role or reset session authorization in the query. For PostgreSQL to properly respect a role's permissions, the SIAAS needs to actually connect to the database as that role. Common GUC-based row level security approaches are also incompatible with this idea.
- kogus 1y ago
  I'm not sure what database platform they used, but in SQL Server, functions cannot have side-effects. https://learn.microsoft.com/en-us/sql/relational-databases/user-defined-functions/user-defined-functions?view=sql-server-ver17 https://learn.microsoft.com/en-us/sql/relational-databases/u...
  - Tostino 1y ago
    In Postgres they absolutely can. They are all just happening inside the same transaction scope unlike stored procedures.
    - flysand7 1y ago
      Speaking of postgres, you don't even need a function, you can just use RETURNING clause of a modifying query to provide data source for the select: select * from ( delete from users returning id )
  - chasil 1y ago
    Dear SQL Server user, welcome to the world of SQL/PSM. https://en.wikipedia.org/wiki/SQL/PSM https://en.wikipedia.org/wiki/SQL/PSM Within this ADA-esque world, packages, procedures, and functions may initiate DML. Assuming these objects are in the default "definer rights" context, the DML runs with the full privilege of the owner of the code (this can be adjusted to "invoker rights" with a pragma). Perhaps this is why Microsoft ignores it (as Sybase did before it).
    - kogus 1y ago
      I am not an expert on SQL/PSM, but I have worked in an Oracle shop before, and used PL/SQL extensively. In SQL Server, the equivalent is T-SQL. T-SQL procedures can do pretty much anything (assuming it is executed by a user with sufficient privileges), including creating and altering tables, creating and executing other procedures, running dynamic sql, as well as ordinary CRUD style operations. The "no side effect" limitation applies specifically to SQL functions.
ako 1y ago
So you build a view on top of the tables, allows users access to the aggregating views, but not to the underlying tables?

by adontz 1y ago

leprechaun1066 1y ago

Most applications backed kdb+ do just this. It comes with its own parser and you can query tables using something like an ast. For example the user might ask for data with the constraint where TradingDesk=`Eq, AvgPx>500.0 which kdb+ parses into ((=;`TradingDesk;(),`Eq);(>;`AvgPx;500.0)) As a dev on the system I can then have a function which takes in this constraint and a list of clients that I want to restrict the result to. That list of clients could come from another function related to the entitlements of the user who made the request: applyClientRestriction:{[constraint;clients] constraint,enlist(in;`Client;enlist clients)} Which results in an extension of the constraint like this for two clients A and B: q)applyClientRestriction[((=;`TradingDesk;(),`Eq);(>;`AvgPx;500.0));`ClientA`ClientB] ((=;`TradingDesk;enlist`Eq);(>;`AvgPx;500.0);(in;`Client;enlist`ClientA`ClientB)) Then that gets passed to the function which executes the query on the table (kdb+ supports querying tables in a functional manner as well as with a structured query language) and the result has the restrictions applied. It's really nice because, once parsed, it's list processing like in a lisp and not string processing which is a pain.

indigo945 1y ago

> - Checks that query is SELECT query (can be tricky with CTE, requires proper SQL parser) Not only is this difficult parsing-wise, there's also no reason to assume that a select query is read-only even when no CTE or subqueries are involved. Function calls in the select clause can also write data. > - Has it's own permissions and users can run the query if they have permissions This is the important one. If the role the query runs as doesn't have write permissions on any table, then the user can't write data, period. Note that this is often not as easy to implement as it seems. For example, in PostgreSQL, neither set role nor set session authorization actually prevent the user from doing malicious things, because the user can just reset role or reset session authorization in the query. For PostgreSQL to properly respect a role's permissions, the SIAAS needs to actually connect to the database as that role. Common GUC-based row level security approaches are also incompatible with this idea.

kogus 1y ago
I'm not sure what database platform they used, but in SQL Server, functions cannot have side-effects. https://learn.microsoft.com/en-us/sql/relational-databases/user-defined-functions/user-defined-functions?view=sql-server-ver17 https://learn.microsoft.com/en-us/sql/relational-databases/u...
- Tostino 1y ago
  In Postgres they absolutely can. They are all just happening inside the same transaction scope unlike stored procedures.
  - flysand7 1y ago
    Speaking of postgres, you don't even need a function, you can just use RETURNING clause of a modifying query to provide data source for the select: select * from ( delete from users returning id )
- chasil 1y ago
  Dear SQL Server user, welcome to the world of SQL/PSM. https://en.wikipedia.org/wiki/SQL/PSM https://en.wikipedia.org/wiki/SQL/PSM Within this ADA-esque world, packages, procedures, and functions may initiate DML. Assuming these objects are in the default "definer rights" context, the DML runs with the full privilege of the owner of the code (this can be adjusted to "invoker rights" with a pragma). Perhaps this is why Microsoft ignores it (as Sybase did before it).
  - kogus 1y ago
    I am not an expert on SQL/PSM, but I have worked in an Oracle shop before, and used PL/SQL extensively. In SQL Server, the equivalent is T-SQL. T-SQL procedures can do pretty much anything (assuming it is executed by a user with sufficient privileges), including creating and altering tables, creating and executing other procedures, running dynamic sql, as well as ordinary CRUD style operations. The "no side effect" limitation applies specifically to SQL functions.

ako 1y ago

So you build a view on top of the tables, allows users access to the aggregating views, but not to the underlying tables?