Re: BUG #1937: Parts of information_schema only accessible

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.