[HACKERS] Role syntax (or, SQL99 versus sanity)
I'm looking at the problem Stephen Frost noted of not being able to duplicate the SQL99-specified syntax for GRANT/REVOKE with roles. The SQL99 spec has for GRANT (REVOKE has the identical issue): ::= GRANT TO [ { }... ] [ WITH HIERARCHY OPTION ] [ WITH GRANT OPTION ] [ GRANTED BY ] ::= GRANT [ { }... ] TO [ { }... ] [ WITH ADMIN OPTION ] [ GRANTED BY ] Barring the appearance of one of the OPTION clauses, it is actually impossible to tell which kind of statement you are dealing with, other than by noticing whether the words appearing between GRANT and TO all look like known privilege keywords. The bison conflicts Stephen was seeing come from the fact that we treat most of the privilege keywords as unreserved words, and so the ambiguity is fatal as far as bison is concerned. 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.) 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; treating WITH GRANT OPTION and WITH ADMIN OPTION as interchangeable spellings of the same thing (which they very nearly are anyway). 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. (Possibly we could alleviate this by adding a HINT.) These considerations also suggest that it'd be a good idea to disallow the privilege names (select insert etc) as role names. On the whole, the SQL99 committee should have followed Stephen's idea and made the syntax be "GRANT ROLE rolenames" ... Thoughts anyone? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Role syntax (or, SQL99 versus sanity)
* Tom Lane ([EMAIL PROTECTED]) wrote: > The SQL99 spec has for GRANT (REVOKE has the identical issue): > > ::= > GRANT > TO [ { }... ] > [ WITH HIERARCHY OPTION ] > [ WITH GRANT OPTION ] > [ GRANTED BY ] SQL2003 seems to have the same issue. The only possible additional bit is (in SQL2003 at least, I imagine 99 is the same): ::= ON 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 signature.asc Description: Digital signature
Re: [HACKERS] Role syntax (or, SQL99 versus sanity)
Stephen Frost <[EMAIL PROTECTED]> writes: > Is there some way we could use that 'ON' is required for the > 'privileges' grant? Well, the difficulty is that we can't see the ON until we've scanned the list of privilege or role names. Now that I've calmed down a bit, the solution is fairly obvious: the name list has to be left as strings during the grammar. We'll check the privilege names for validity at execution. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Role syntax (or, SQL99 versus sanity)
Tom Lane wrote: I'm looking at the problem Stephen Frost noted of not being able to duplicate the SQL99-specified syntax for GRANT/REVOKE with roles. The SQL99 spec has for GRANT (REVOKE has the identical issue): ::= GRANT TO [ { }... ] [ WITH HIERARCHY OPTION ] [ WITH GRANT OPTION ] [ GRANTED BY ] ::= GRANT [ { }... ] TO [ { }... ] [ WITH ADMIN OPTION ] [ GRANTED BY ] Barring the appearance of one of the OPTION clauses, it is actually impossible to tell which kind of statement you are dealing with, other than by noticing whether the words appearing between GRANT and TO all look like known privilege keywords. The bison conflicts Stephen was seeing come from the fact that we treat most of the privilege keywords as unreserved words, and so the ambiguity is fatal as far as bison is concerned. 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.) 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; treating WITH GRANT OPTION and WITH ADMIN OPTION as interchangeable spellings of the same thing (which they very nearly are anyway). 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. (Possibly we could alleviate this by adding a HINT.) These considerations also suggest that it'd be a good idea to disallow the privilege names (select insert etc) as role names. On the whole, the SQL99 committee should have followed Stephen's idea and made the syntax be "GRANT ROLE rolenames" ... Thoughts anyone? going backwards ... . getting to SQL99 syntax might be a pain but we should do it, however unwise they were in choice of syntax. . excluding named privileges from use as role names seems highly desireable . thought on resolution - not sure if we can play games with %prec - I suspect we can't - could we get there if we declare the named privs as "reserved just for this purpose"? That would probably imply partitioning the unreserved keywords list. cheers andrew ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Role syntax (or, SQL99 versus sanity)
On Tue, Jun 28, 2005 at 12:29:22PM -0400, Tom Lane wrote: > 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; This alone makes me want to ditch the SQL99 syntax... IMHO there should be a definative way to differentiate between a role grant and a privilege grant. But I tend to agree that supporting SQL99 is a good thing, so... How horrid would it be to support both SQL99 and the suggested GRANT ROLE syntax, possible with a means to turn off the SQL99 syntax. This would allow catching typo'd GRANT privilege statements. Another possibility is to support GRANT, GRANT ROLE, and GRANT PRIVILEGE, and strongly suggest that users use the latter 2 and not the first one. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]