[cap-talk] capabilities in relational databases

[Sam Mason]

On Sat, Apr 12, 2008 at 01:27:00PM +0800, John McCabe-Dansted wrote:
> On Sat, Apr 12, 2008 at 12:07 PM, John Carlson wrote:
> > No one wants to try SQL as capabilities?  I'm game.  What else do we
> > have to add to SQL to make them capabilities?
>
> Well SQL Views, Tables, and pre-compiled Queries are all objects that
> could be OO-caps just like any other.

I'm having trouble understanding what this thread is discussing, but
I think PostgreSQL[1] provides a set of tools that allows something
similar to caps.  Most databases seem to be identity/role based, but PG
(I'm not familiar with other RDBMSs) allows views and stored procedures
can be run under the authority of the defining role.  This does allow
attenuation of authority, although it's somewhat coarse.

For example the database administrator Alice, whose code is prefixed
with "a:", can create a new user:

  a: CREATE ROLE bob;

then create a new table and put some data in:

  a: CREATE TABLE t ( n INTEGER PRIMARY KEY );
  a: INSERT INTO t (n) VALUES (1),(2),(11),(12);

Bob ("b:" prefix) doesn't get any authority over this table though:

  b: SELECT * FROM t;
  ERROR:  permission denied for relation t

Alice then creates a view to access some subset of the table and grants
Bob permission to get data from this view:

  a: CREATE VIEW vt AS SELECT * FROM t WHERE n < 10;
  a: GRANT SELECT ON vt TO bob;

Bob can now see the subset of "t" where "n" is less than ten.

  b: SELECT * FROM vt;
   n 
  ---
   1
   2
  (2 rows)

But note that Bob can only see this subset, he has no authority to
see the whole of table "t".  Rules can be set up that allow similar
attenuation of INSERT, UPDATE, and DELETE statements.

Is that something like what was being talked about or have I
misunderstood completely?


Thanks,
  Sam

 [1] http://www.postgresql.org/