Re: Does information_schema show too much information?
Posted by: Roland Bouman
Date: January 20, 2006 07:45AM

Interesting point. I don't think it's a bug, because the standard (iso-9075 2003) dictates that
1) the information schema is available to every user
2) the information schema provides information about all objects accessible to the current user
3) the information schema has a VIEWS view with a VIEW_DEFINITION column.

On the Other hand, when you look at the definition oof the views view according to the standard, there's something interesting in how the view definition column is defined:

CREATE VIEW VIEWS AS
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,
CASE
WHEN EXISTS
( SELECT *
FROM DEFINITION_SCHEMA.SCHEMATA AS S
WHERE ( TABLE_CATALOG, TABLE_SCHEMA )
= ( S.CATALOG_NAME, S.SCHEMA_NAME )
AND
( SCHEMA_OWNER = CURRENT_USER
OR
SCHEMA_OWNER IN
( SELECT ROLE_NAME
FROM_ENABLED_ROLES ) ) )
THEN VIEW_DEFINITION
ELSE NULL
END AS VIEW_DEFINITION,
CHECK_OPTION, IS_UPDATABLE, IS_INSERTABLE_INTO
FROM DEFINITION_SCHEMA.VIEWS
WHERE ( TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME ) IN
( SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM TABLES )
AND
TABLE_CATALOG
= ( SELECT CATALOG_NAME
FROM INFORMATION_SCHEMA_CATALOG_NAME );
GRANT SELECT ON TABLE VIEWS
TO PUBLIC WITH GRANT OPTION;

See, the view definition column shows the code only if the current user is owner of the view or if his role is in the enabled_roles view

I'm not 100% sure, but I think the intention of the standard is indeed to hide the code from unauthorized users.

So maybe you should report it as a bug. Will you post back the ID here? thanks! If you dont plan on reporting this as a bug, post back too. I will.

P.S, i checked using the 1999 and the 2003 specs here.
PS2 I also looked for some option to mysqld, like --skip-information-schema, but did not find one.





Edited 1 time(s). Last edit at 01/20/2006 07:49AM by Roland Bouman.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Does information_schema show too much information?
4323
January 20, 2006 07:45AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.