Re: Role syntax (or, SQL99 versus sanity)

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> The SQL99 spec has for GRANT (REVOKE has the identical issue):
>
> <grant privilege statement> ::=
> GRANT <privileges>
> TO <grantee> [ { <comma> <grantee> }... ]
> [ WITH HIERARCHY OPTION ]
> [ WITH GRANT OPTION ]
> [ GRANTED BY <grantor> ]

SQL2003 seems to have the same issue. The only possible additional bit
is (in SQL2003 at least, I imagine 99 is the same):
<privileges> ::= <object privileges> ON <object name>

Is there some way we could use that 'ON' is required for the
'privileges' grant?

> The only grammar-level solution I can see is to promote all of the
> following into some category of reserved word:
> INSERT UPDATE USAGE DELETE RULE TRIGGER EXECUTE TEMPORARY TEMP
> which is pretty annoying, even though SQL99 gives us license to do so
> for most of them. (But reserving RULE or TEMP would be contrary to
> spec.)

What about 'ON', from above? I don't suppose making that a reserved
word would maybe help (if it's not already, if it's allowed by the spec,
etc)? Sorry, just guessing really but it did seem like something you
didn't consider so I thought I'd mention it.

> Alternatively we might consider not distinguishing GRANT PRIVILEGE
> from GRANT ROLE at parse time, but sorting it out later. The most
> extreme form of this would be to actually allow both things in the
> same GRANT:
>
> GRANT INSERT, role1, UPDATE TO joe;

That would certainly be rather.. odd. It also doesn't really follow the
spec I don't think. Honestly, I'd think we'd want to error out if we
came across a situation here and assume the user misspelled a privilege
or something.

> treating WITH GRANT OPTION and WITH ADMIN OPTION as interchangeable
> spellings of the same thing (which they very nearly are anyway).

This I agree with, kind of silly to have them be named differently like
that.

> One objection to this is that misspelling a privilege keyword would
> give you a complaint about "unknown role", which might be a bit
> confusing; but I suspect we cannot avoid that anyway --- there is
> absolutely no basis on which we can say that
>
> GRANT INSIRT TO joe;
>
> isn't a GRANT ROLE operation, until we fail to find the role name.

Right, looks very much like a GRANT ROLE operation.

> (Possibly we could alleviate this by adding a HINT.)

Probably wouldn't hurt..

> These considerations also suggest that it'd be a good idea to disallow
> the privilege names (select insert etc) as role names.

If using the 'ON' requirement isn't possible then yes, I'd say we should
disallow the use of the privilege names as role names.

> On the whole, the SQL99 committee should have followed Stephen's
> idea and made the syntax be "GRANT ROLE rolenames" ...

I can't argue with that. :)

Thanks,

Stephen