The Pyrrho Database Management System

Malcolm Crowe, University of the West of Scotland

 

Roles

In Pyrrho a Role can have a description in addition to a name, and user privileges must be acquired through roles. The role to use for a database can be specified when the connection is established, and modified later using the SET ROLE= syntax. If no role is specified, the default database role has the same name as the database: if the logged in user does not have permission to use this, the guest role is used instead, which can access objects that have been granted to PUBLIC.

For example, suppose a small sporting club (such as squash or tennis) wishes to allow members to record their matches for ranking purposes:

Played: (id int primary key, winner int references members, loser int references members, agreed boolean)

For simplicity we give everyone select access to both these tables.

Grant select on members to public

Grant select on played to public

Although Pyrrho records which user makes changes, it will save time if users are not allowed to make arbitrary changes to the Played table. Instead we will have procedure Claim(won,beat) and Agree(id), so that the Agree procedure is effective only when executed by the loser. With some simple assumptions on user names, the two procedures could be as simple as:

Create procedure claim(won int,beat int)

insert into played(winner,loser) values(claim.won,claim.beat)

Create procedure agree(p int)

  update played set agreed=true

     where id=agree.p and

loser in (select m.id from members m where current_user like ('%'||firstname))

These two procedures have insert and update access to the Played table respectively.

Grant insert on played to procedure claim(int,int)

Grant update on played to procedure agree(int)

We want all members of the club to be able to execute these procedures. We could simply grant execute on these procedures to public, or by means of a role, and Pyrrho supports such usage. However, it is better practice to grant these permissions instead to an authority (say, membergames) and allow any member to use this authority:

Create authority membergames 'Matches between members for ranking purposes'

Grant execute on procedure claim(int,int) to authority membergames

Grant execute on procedure agree(int) to authority membergames

Grant authority membergames to public

This example could be extended by considering the actual use made of the Played table in calculating the current rankings, etc.

In addition to the above uses for roles, Pyrrho allows roles to rename and add other metadata to objects for which they have admin permission. This metadata is just for the role, but will also be seen by other grantees unless they have their own metadata for the object.