Gwendal. I understand all that. It's also good that you've confirmed how SQLITE_READ is actually queried by the authorizer callback interface. I was wondering about that. Reading your earlier post, one might get the impression that the SQLITE_READ authorizer action was not queried by the engine for aggregate table reads for some reason. Presumably that would be a bug.
My question about your solution is illustrated by looking at the existing defines for orthogonal operations. Consider how SELECT, INSERT, and UPDATE are currently defined as below. #define SQLITE_INSERT 18 /* Table Name NULL */ #define SQLITE_SELECT 21 /* NULL NULL */ #define SQLITE_UPDATE 23 /* Table Name Column Name */ If this interface is logically missing SQLITE_READ_TABLE then shouldn't all the orthogonal authorizer action codes in that same dimension also be implemented? Thus, why not also add authorizer action codes for SQLITE_WRITE_TABLE, SQLITE_READ_COLUMN, SQLITE_WRITE_COLUMN, SQLITE_READ_SCHEMA, and SQLITE_WRITE_SCHEMA? Why only just SQLITE_READ_TABLE? If SQLITE_READ_TABLE is missing why aren't the others also missing? Who is the author of the Authorizer Action Code source? Does anyone know? Does the author have an opinion? Are these new SQLITE_READ_X and SQLITE_WRITE_X authorizer codes truly missing from the intended design? If so, are they on the development road map? Or, was the presumption that practical applications would handle access control by denying everything except operations within an application defined view and trigger layer? [See idea about the 6th parameter of the callback, view or trigger name, I mentioned in the previous post.] Why is this forum so silent on this question? Usually there are half a dozen responses on the "correct way" to do it. This time, crickets. On Mon, May 8, 2017 at 8:04 AM, Gwendal Roué <[email protected]> wrote: > Hello Peter, > > It's the generally the responsability of the callback implementor to test > or not each authorization, depending on her needs. See > https://sqlite.org/c3ref/set_authorizer.html > > -- Allow user to run select statements, and read col1 of t1: > -- SQLITE_SELECT > -- SQLITE_READ t1 col1 main > SELECT col1 FROM t1; > > -- Allow user to run select statements, read col1 of t1, and insert in > t2: > -- SQLITE_INSERT t2 main > -- SQLITE_SELECT > -- SQLITE_READ t1 col1 main > INSERT INTO t2 SELECT col1 FROM t1; > > There are also authorization callbacks for functions: > > -- Allow user to run select statements, read col1 of t1, execute count > function: > -- SQLITE_SELECT > -- SQLITE_FUNCTION max > -- SQLITE_READ t1 col1 main > SELECT MAX(col1) FROM t1 > > But here is why I'm suggesting a new code SQLITE_READ_TABLE: > > -- Allow user to run select statements, and execute count function: > -- SQLITE_SELECT > -- SQLITE_FUNCTION count > SELECT COUNT(*) FROM t1 > > In the previous query, no one knows that the table t1 is about to be used. > > The authorizer callback can not be extended so that it tells everything > about a function arguments. That's because a function arguments can be too > complex to fit in the callback arguments: > > -- SQLITE_SELECT > -- SQLITE_FUNCTION count > SELECT COUNT(*) FROM t1, t2, t3, t4, t5 > > -- SQLITE_SELECT > -- SQLITE_FUNCTION count > -- SQLITE_READ t1 col1 main > -- SQLITE_READ t2 col1 main > -- SQLITE_READ t3 col1 main > SELECT COUNT(DISTINCT t1.col1 + t2.col1 + t3.col1) FROM t1, t2, t3 > > With the newly introduced SQLITE_READ_TABLE code, we have instead: > > -- SQLITE_SELECT > -- SQLITE_READ t1 main > -- SQLITE_FUNCTION count > SELECT COUNT(*) FROM t1 > > And now the client knows that the table t1 is used, and can forbid this > access. > > Gwendal Roué > > > Gwendal. Your proposal last month for adding column names to the > callback parameters seemed more sensible. > > > > The first question that comes to mind when new callback modes are to > being proposed is what else would be missing if the same standard were > applied to every possible operation? > > > > My thought. A cursory read of the relevant code comments (see below) > suggests the author had in mind only precise security control of views and > triggers - the ubiquitous 6th parameter mentioned in the comment. If > that's the idea, then one presumably denies everything by default and then > handles requests only to a purpose built secure view and trigger layer. > > > > It would be nice to hear from the author about what they actually had in > mind for those who need total iron clad security of every row or aggregate > query of any table. For example, if SQLITE_READ authorization is not being > tested, why not? Is it tested later? Perhaps the architecture of the > authorizer is not self explanatory from the names of the #defines or is > described elsewhere. > > > > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
