Re: BUG #1937: Parts of information_schema only accessible to owner

> -----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".

Tony Marston

http://www.tonymarston.net