REST and role-based CRUD operations

This posting will demonstrate the use of the REST interface to perform updates, insertions and deletions in a database, where the URL structure, access permissions and navigation are determined by the roles in the database, rather than by application defined routing tables or configuration files. The default Pyrrho REST service from version 4.5 uses Windows authentication, and the server administrator has the option to use a secure channel for the http transport. The features apply in both the standard and Open Source version of Pyrrho (OSP).
We will use the following simple REST client (which is not Pyrrho-specific):

This screenshot shows the database as Library, the role as Library, and the table as BOOK (the REST interface is case-sensitive, and the default case in SQL is upper case).
Let us use PUT to provide better author names. With the permissions set up in previous postings, any user granted the Library or LIBRARIAN roles can do this. The roles are different as we will see below. We can use XML or SQL format for the data we supply. First let’s use SQL. The SQL output format for http://localhost:8180/Library/Library/AUTHOR/NAME gives (('Dickens')(‘Conrad’)), so we use the same format, naturally, to update the names:

If we use XML, things look a little different. Let’s look at BOOK with XML

Here the librarian has changed the names of some columns for the LIBRARIAN role, and marked book as an entity and id and aid as attributes. We can post a new book:

Notice that we don’t need to supply all of the fields (in this case it would be incorrect to supply values for the generated Author column). Pyrrho is forgiving about whether columns are supplied as attributes or children. But only one record can be POSTed at a time.
POST does not return data.
Checking, we see the new book has gone in with a generated key value of 1 (we could have given our own value).

Recall that this table looks very different in other roles:

Finally DELETE works. Provided the current role has the right permissions, the records that would be returned by GET are instead deleted.
Role names do not need to be meaningful, and using a hard-t-guess name for a role with extra privileges can be a useful extra layer of security.