Re: GRANT/REVOKE: Allow column-level privileges

"kevin brintnall" <kbrint(at)rufus(dot)net>
...
> (SQL99, 10.5 <privileges>, General Rules, 15-18)
>
> 15) SELECT with neither <privilege column list> nor <privilege method
> list> specifies the SELECT privilege on all columns of T including any
> ^^^^^^^^^^^^^
> columns subsequently added to T and implies a table privilege
> descriptor
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

That is exactly what I wanted to point out.
We should record in some place that the grantee will have SELECT priviledge
on any newly created columns. e.g.
GRANT SELECT ON tab TO grantee;
can be represented as
<tab, grantee, table priviledge SELECT>
after REVOKE SELECT (c1) ON tab FROM grantee;
<tab, grantee, pseduo table priviledge SELECT>
<tab/c2, grantee, column priviledge SELECT>
<tab/c3, grantee, column priviledge SELECT>
when ALTER TABLE tab ADD COLUMN c4 CHAR(8);
we can use
<tab, grantee, pseduo table priviledge SELECT>
to deduce:
<tab/c4, grantee, column priviledge SELECT>

> and one or more column privilege descriptors. If T is a table of a
> structured type TY, then SELECT also specifies the SELECT privilege on
> all
> methods of the type TY, including any methods subsequently added to the
> type TY, and implies one or more table/method privilege descriptors.
>
> Aside from checking the column acl first, I'm not sure how we can conform
> to the spec. Does anyone have a better way to handle this internally,
> while still producing correct results?
>
> GRANT SELECT ON tab TO grantee;
> REVOKE SELECT (c1) ON tab FROM grantee;
>
> It's possible I'm just mis-understanding SQL99 ... ?