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.