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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
