Re: BUG #1937: Parts of information_schema only accessible

Thread added to the TODO list.

---------------------------------------------------------------------------

Stephan Szabo wrote:
> On Tue, 4 Oct 2005, Tony Marston wrote:
>
> > Description:        Parts of information_schema only accessible to owner
> > Details:
> >
> > I have been trying to access parts of the information_schema as an ordinary
> > user, not as owner, and I am encountering instances where I cannot retrieve
> > any rows at all, or where some of the columns are empty when they should not
> > be.
> >
> > This sounds like a faulty implementation to me, with too many restrictions.
> > As far as I am concerned if I have access privileges on an object then I
> > should be able to see ALL information_schema details regarding that object.
>
> Complain to the SQL committee then. ;) Many portions of information_schema
> are defined in the spec with limitations based on the user.
>
> > As an example, in the view "information_schema.columns" I can only see the
> > entry in COLUMN_DEFAULT if I am the owner. Why is this? What is the logic
> > behind this decision?
>
> Because of this piece of the definition:
>
>                 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,
>
> It looks like we're using table owner rather than schema owner since we
> allow mixed ownership of contents of a schema, but the general principle
> is the same.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

--
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +