[cap-talk] capabilities in relational databases

[Rob Meijer]

On Sat, April 12, 2008 15:51, Sam Mason wrote:
> 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;

To me it would appear that something like the following would be much more
in line with the capabilities paradigm:

CREATE VIEW a7b3npqs87g3nchly682 AS SELECT * FROM t WHERE n < 10;
GRANT SELECT ON a7b3npqs87g3nchly682 TO ALL

By using a strong name for the view and NOT limiting access based on
user or role, the view name would be usable as a simple password capability,
separate from identity or role based access controls.

Rob