Re: BUG #1937: Parts of information_schema only accessible

On Sat, 8 Oct 2005, Tony Marston wrote:

>
>
>
>
> > -----Original Message-----
> > From: Stephan Szabo [mailto:sszabo(at)megazone(dot)bigpanda(dot)com]
> > Sent: 08 October 2005 16:44
> > To: Tony Marston
> > Subject: RE: [BUGS] BUG #1937: Parts of information_schema
> > only accessible to owner
> >
> >
> > On Sat, 8 Oct 2005, Tony Marston wrote:
> >
> > > I have searched through the SQL 2003 standard and can find no such
> > > restriction. In the volume titled "Information and
> > Definition Schemas
> > > (SQL/Schemata)" in section 5.20 (INORMATON_SCHEMA.COLUMNS view) it
> > > states the following under the heading "Function":
> > >
> > > "Identify the columns of tables defined in this catalog that are
> > > accessible to a given user or role."
> > >
> > > Note there that it does not say that the user must be the
> > owner, but
> > > that the user is allowed to access the table (i.e. has access
> > > privileges).
> > >
> > > I take this to mean (as any reasonable person would) that if a user
> > > has been granted the privilges to access an object then
> > that same user
> > > can view all the information on that object which is defined within
> > > the information schema.
> > >
> > > Unless you can provide a direct quote from the SQL standard which
> > > contradicts this I strongly suggest that you revise your opinion.
> >
> > What I gave was *directly* part of the definition of the view from the
> > standard:
> >
> > > > CASE WHEN EXISTS ( SELECT *
> > > > FROM DEFINITION_SCHEMA.SCHEMATA AS S
> > > > WHERE ( TABLE_CATALOG, TABLE_SCHEMA )
> > > > = (S.CATALOG_NAME, S.SCHEMA_NAME )
> > > > AND SCHEMA_OWNER = USER )
> > > > THEN COLUMN_DEFAULT
> > > > ELSE NULL
> > > > END AS COLUMN_DEFAULT,
> >
> > I think any "reasonable person" would read the definition
> > portion above from that view and interpret that as give the
> > column default if the table the the column is in came from a
> > schema that is owned by USER otherwise give NULL.
> >
>
> I disagree. The function description in the SQL 1999 standard says "Identify
> the columns of tables defined in this catalog that are accessible to a given
> user." It is clear that the actual code sample given does not conform to
> this description, so I would argue that the code is wrong and the
> description is right. Any reasonable person would assume that the code
> sample would conform to the description. After all, the description does not
> say "except for those items where the user must also be the owner".

If there's two items:
"Function" with a description and "Definition" with a definition, I think
it's fairly ignorant to read the former as overriding the latter. The
latter *is* the definition.