Re: [sqlite] Proposition: introduce a new SQLITE_READ_TABLE Authorizer Action Code

[Gwendal Roué]

> Le 9 mai 2017 à 00:21, Simon Slavin  a écrit :
> 
> 
> On 8 May 2017, at 10:11pm, petern  wrote:
> 
>> Who is the author of the Authorizer Action Code source?
> 
> Although SQLite is in the public domain, development of it is not typical for 
> an open source project.  Almost everything you download when you download 
> SQLite was written by a development team of three or four people.  
> Contributions from outside that group are rarely (? ever ?) incorporated into 
> the project as source code supplied.  Instead the development group takes 
> suggestions submitted on this list and sometimes decides to write code to 
> implement them.

Thanks for the information: I didn't know that.

> Instead, SQLite provides many hooks and callback opportunities, and people 
> are encouraged to write their own extensions and host them themselves.

Forking SQLite is a very hard path, unfortunately :-)

Gwendal Roué

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposition: introduce a new SQLITE_READ_TABLE Authorizer Action Code

[Gwendal Roué]

> Le 8 mai 2017 à 23:11, petern  a écrit :
> 
> 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   /* NULLNULL
> */
> #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?

Because they are not missing: existing authorizer callbacks already provide a 
detailed information for all possible updates. We just miss information about 
selected tables.

> 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.

I did propose a patch as a way to show that my proposal doesn't come from thin 
air, but can be implemented.

Yes, I wish the core team would give at least an acknowledgement that something 
could be improved.

Gwendal Roué

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposition: introduce a new SQLITE_READ_TABLE Authorizer Action Code

[Simon Slavin]

On 8 May 2017, at 10:11pm, petern  wrote:

> Who is the author of the Authorizer Action Code source?

Although SQLite is in the public domain, development of it is not typical for 
an open source project.  Almost everything you download when you download 
SQLite was written by a development team of three or four people.  
Contributions from outside that group are rarely (? ever ?) incorporated into 
the project as source code supplied.  Instead the development group takes 
suggestions submitted on this list and sometimes decides to write code to 
implement them.

Instead, SQLite provides many hooks and callback opportunities, and people are 
encouraged to write their own extensions and host them themselves.

> 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.

Hardly anyone uses the authentication system, so far fewer people know the 
answers.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposition: introduce a new SQLITE_READ_TABLE Authorizer Action Code

[petern]

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   /* NULLNULL
*/
#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é  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.

Re: [sqlite] Fwd: SELECT Max(IndexedField) doesn't use partial index

[Paul van Helden]

On Mon, May 8, 2017 at 8:20 PM, Scott Robison 
wrote:

> On Mon, May 8, 2017 at 11:40 AM, Paul van Helden 
> wrote:
> > Hi,
> >
> > I use a lot of indexes on fields that typically contain lots of NULLs, so
> > the WHERE NOT NULL partial indexing seems very useful.
> >
> > However when I compare the "EXPLAIN QUERY PLAN" with a partial index vs.
> a
> > normal index, SQLite won't use the index to find Max(IndexedField) if it
> is
> > a partial index.
> >
> > Is this an optimization opportunity? I understand that other kinds of
> > partial indexes might exclude possible Min or Max values, but a NOT NULL
> > index would be fine for mins, maxes and most other things?
>
> This may be an optimization opportunity, but you can easily force the
> use of that index by stating WHERE NOT NULL in the select query
> itself. For example:
>
> CREATE TABLE a(b);
> CREATE INDEX ab on a(b) where b is not null;
>
> sqlite> explain query plan select max(b) from a;
> 0|0|0|SEARCH TABLE a
> sqlite> explain query plan select max(b) from a where b is not null;
> 0|0|0|SEARCH TABLE a USING COVERING INDEX ab
>
> >


Thanks Scott,

That works and fully solves my problem!

Regards,

Paul.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: SELECT Max(IndexedField) doesn't use partial index

[Scott Robison]

On Mon, May 8, 2017 at 11:40 AM, Paul van Helden  wrote:
> Hi,
>
> I use a lot of indexes on fields that typically contain lots of NULLs, so
> the WHERE NOT NULL partial indexing seems very useful.
>
> However when I compare the "EXPLAIN QUERY PLAN" with a partial index vs. a
> normal index, SQLite won't use the index to find Max(IndexedField) if it is
> a partial index.
>
> Is this an optimization opportunity? I understand that other kinds of
> partial indexes might exclude possible Min or Max values, but a NOT NULL
> index would be fine for mins, maxes and most other things?

This may be an optimization opportunity, but you can easily force the
use of that index by stating WHERE NOT NULL in the select query
itself. For example:

CREATE TABLE a(b);
CREATE INDEX ab on a(b) where b is not null;

sqlite> explain query plan select max(b) from a;
0|0|0|SEARCH TABLE a
sqlite> explain query plan select max(b) from a where b is not null;
0|0|0|SEARCH TABLE a USING COVERING INDEX ab

>
> Regards,
>
> Paul.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
Scott Robison
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Fwd: SELECT Max(IndexedField) doesn't use partial index

[Paul van Helden]

Hi,

I use a lot of indexes on fields that typically contain lots of NULLs, so
the WHERE NOT NULL partial indexing seems very useful.

However when I compare the "EXPLAIN QUERY PLAN" with a partial index vs. a
normal index, SQLite won't use the index to find Max(IndexedField) if it is
a partial index.

Is this an optimization opportunity? I understand that other kinds of
partial indexes might exclude possible Min or Max values, but a NOT NULL
index would be fine for mins, maxes and most other things?

Regards,

Paul.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposition: introduce a new SQLITE_READ_TABLE Authorizer Action Code

[Gwendal Roué]

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

Re: [sqlite] SQLiteDiskIOException: disk I/O error (code 1034)

[Rowan Worth]

On 8 May 2017 at 14:35, Clemens Ladisch  wrote:

> Rowan Worth wrote:
> > These days (the past 8 years?) at least there's the
> SQLITE_FCNTL_LAST_ERRNO
> > parameter to sqlite3_file_control() allowing the underlying cause to be
> > introspected, I just feel like it was a mistake to ever mask that cause.
>
> That error code is behind a FCNTL because it is not portable; it could
> never be a part of the public API.
>

Yeah, that's entirely fair. And platform-agnostic error code categories are
perhaps too much to expect, especially since this mechanism enables people
who care (eg. myself) to design said categories according to their needs
(at least if my java bindings actually covered sqlite3_file_control >_<).

Preserving the underlying cause is still possible to do portably; something
like sqlite3_os_errmsg() would provide an "opaque" string suitable for
user/developer consumption.

-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLiteDiskIOException: disk I/O error (code 1034)

[Clemens Ladisch]

Rowan Worth wrote:
> These days (the past 8 years?) at least there's the SQLITE_FCNTL_LAST_ERRNO
> parameter to sqlite3_file_control() allowing the underlying cause to be
> introspected, I just feel like it was a mistake to ever mask that cause.

That error code is behind a FCNTL because it is not portable; it could
never be a part of the public API.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users