Mysql – In creating view use SQL Security definer or invoker

MySQLSecurityview

CREATE VIEW syntax is

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

For SECURITY you can set DEFINER or INVOKER. When does it make sense to select "definer"? As far as I understand that means check the security permissions of me, the person who is creating the view. If I have full permissions across our database, then I just created a view that gives any invoker full access. That sounds wrong. Wouldn't you always want the security to be the person who is actually using the view?

Best Answer

Quotes taken from the documentation.

If you have the SUPER privilege, you can specify any syntactically legal account name. If the account does not actually exist, a warning is generated.

Being able to specify security accounts other than the view's creator is a key piece of using DEFINER, which you seem to have missed.

When a view has been referenced, privileges for objects accessed by the view are checked against the privileges held by the view DEFINER account...

This means that you can set the DEFINER to use a security account which has access to the underlying objects, but deny access to those objects to users at large, thus maintaining tighter security.

For example, say you have some table PrivateData, which users should not be able to generally access, but you want to create an internal report which tracks metadata on this table. You deny permissions on PrivateData to users, but create an account PrivateDataReader which has read-only access to the table. You can then create a view which presents the metadata information, specifying PrivateDataReader as the DEFINER.

If you always want to use the security of the user accessing the view, then you should specify INVOKER.

Related Question