2013-03-18 10:00:00

Fine-grained permissions in Zumero

All right, which one of you people added John C. Dvorak to my list of RSS feeds?

eric$ ./sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT zumero_sync(
    'main',
    'https://zinst393e9343b87.s.zumero.net',
    'all_feeds'
    );
0;0;0;3584;0;476;213;217

sqlite> SELECT * FROM feeds;
2|https://ericsink.com/rss.xml
1|http://feeds.hanselman.com/ScottHanselman
3|http://rssnewsapps.ziffdavis.com/PCMAG_dvorak.xml

Yes, I left the permissions wide open, specifically so that you could add to the list.

But... Dvorak?

Really?

Mobile offline RSS reader, Part 2

In part 1, I got started with a single 'feeds' table that contained nothing but the URL. Now I want to add a place to store the title of the feed, and a place to remember the last time I retrieved the XML.

CREATE VIRTUAL TABLE IF NOT EXISTS about USING zumero(
    feedid INTEGER UNIQUE NOT NULL REFERENCES feeds (feedid),
    title TEXT NOT NULL
    );

CREATE VIRTUAL TABLE IF NOT EXISTS last_update USING zumero(
    feedid INTEGER UNIQUE NOT NULL REFERENCES feeds (feedid),
    when_unix_time INTEGER NOT NULL
    );

Note that unlike the feeds table, these two tables are protected from changes by unauthenticated users. Let's take a closer look at the SQL statements from part 1 where I configured the Access Control List (ACL).

First, I use a convenience function to create the ACL table itself, which, by convention, is named "z_acl".

SELECT zumero_define_acl_table('main');

Then it is time to insert several ACL entries, each of which is just a row in the z_acl table. Here is the first one:

INSERT INTO z_acl (scheme,who,tbl,op,result) VALUES (
    '',
    zumero_named_constant('acl_who_anyone'),
    '',
    '*',
    zumero_named_constant('acl_result_deny')
    );

This entry establishes a default. Basically it says, "don't let anybody do anything".

The 'who' column specifies which users this will match at permission-checking time. An entry that specifies 'anyone' will match, well, anyone at all, whether they are authenticated or not.
The meaning of the 'scheme' column depends on the value of the 'who' column. In this case, 'scheme' is not needed, so it is an empty string.
The 'tbl' column is also not used in this entry, so it is an empty string as well.
The 'op' column specifies which operations this will match at permission-checking time. The asterisk here is interpreted as a wildcard.
Finally, the 'result' column is used to provide a result for the permission-check when this entry has matched, which it always will.

When checking for permissions, entries are checked in order from most-specific to least-specific. The ACL entry above will always match, but if something else matches first, it will not apply. The following entries are the exceptions, the cases where we want to actually allow something.

The following entry says, "people who are members of the admin group are allowed to do anything they want":

INSERT INTO z_acl (scheme,who,tbl,op,result) VALUES (
    zumero_internal_auth_scheme('zumero_users_admin'),
    zumero_named_constant('acl_who_any_authenticated_user'),
    '',
    '*',
    zumero_named_constant('acl_result_allow')
    );

For many situations, I would just stop here and add no further entries to the ACL. Only the admins can do anything.

But in this situation, I specifically wanted to allow unauthenticated users to have a couple of permissions. This one allows 'read' access ('pull') to the entire dbfile:

INSERT INTO z_acl (scheme,who,tbl,op,result) VALUES (
    '',
    zumero_named_constant('acl_who_anyone'),
    '',
    zumero_named_constant('acl_op_pull'),
    zumero_named_constant('acl_result_allow')
    );

And the following ACL entry is the one that isn't looking so wise in the hindsight of the moment:

INSERT INTO z_acl (scheme,who,tbl,op,result) VALUES (
    '',
    zumero_named_constant('acl_who_anyone'),
    'feeds',
    zumero_named_constant('acl_op_tbl_add_row'),
    zumero_named_constant('acl_result_allow')
    );

Note the value for the 'tbl' column in this final ACL entry. I've granted 'add_row' permission to 'anyone', but only for the 'feeds' table. This is why the two new tables I just created will remain safe from changes by non-admin users.

These permissions are enforced at the server not on the client. When performing operations on the local copy of the SQLite database, you can do anything you want. But when you sync, if you are attempting to push changes which include operations beyond your permissions, your push will fail with "zumero:permission_denied".

Stay tuned for part 3.