MySQL Bugs: #40640: Default SQL security: definer: safe?

Bug #40640	Default SQL security: definer: safe?
Submitted:	11 Nov 2008 15:24	Modified:	21 Oct 2009 17:45
Reporter:	Olaf van der Spek (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	qc

Description:
http://dev.mysql.com/doc/refman/5.0/en/create-view.html

> The default SQL SECURITY value is DEFINER.

Is this a good idea?
If I create a view as root and forget to set SQL SECURITY INVOKER, any user is granted all privileges on the table used in the view.

How to repeat:
-

Thank you for the report.

This was done to follow SQL standard. So I close the report as "Not a Bug"

Really?
Your documentation says standard SQL does the exact opposite.

> The DEFINER and SQL SECURITY  clauses for views are extensions to standard SQL. In standard SQL, views are handled using the rules for SQL SECURITY INVOKER.

Thank you for the feedback.

My comment was due to not public worklog for this feature implementation. I'll consult with our SQL standard experts and most likely documentation would be corrected.

In short: SQL SECURITY is MySQL extension to the standard. Without having this extension it is logical to don't care about INVOKER rights for underlying tables, but for her rights on the VIEW and for DEFINER's rights for underlying tables.

I don't really understand what you're saying.
Ok, so SQL SECURITY is an extension. The SQL standard acts like SECURITY INVOKER. MySQL defaults to SECURITY DEFINER.
Why?
Isn't it better to default to how the standard acts?

Olaf,

> Ok, so SQL SECURITY is an extension. The SQL standard acts like
SECURITY INVOKER. MySQL defaults to SECURITY DEFINER.

Not.

SQL standard acts like SECURITY DEFINER. There is error in our documentation.

I just didn't set it as verified documentation bug, because I want a note from our SQL standard experts before doing it.

Ah, ok, in that case it makes sense.

I got the answer.

"Standard SQL has no DEFINER or SQL SECURITY clauses in
CREATE VIEW.

The standard says that the definer of the view, which is the same as
the owner of the view's schema, gets applicable privileges on the view
(for example SELECT) and may grant them. Unfortunately MySQL has no
concept of a schema "owner". So we must add a clause to identify the
definer. So the DEFINER clause is an "extension" but the intent is to have
what the standard has, that is, a permanent record of who defined."

Regarding to the quote from the user manual.

This can be applicable to functions in view. For example, like CREATE VIEW v AS SELECT f(); "The idea here is that function f() will be invoked with SQL SECURITY INVOKER, in standard SQL."

So category changed to "Documentation" with request to the Documentation team: please clarify what you meant by "In standard SQL, views are handled using the rules for SQL SECURITY INVOKER."

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Correct manual so indicate that the standard treats views with DEFINER security. Also added additional information from previous comment about the purpose of the DEFINER clause.