Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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?

share|improve this question

migrated from stackoverflow.com Jun 6 at 0:14

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 4 down vote accepted

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.

share|improve this answer
 
oic. If you want fixed privileges go with definer. –  djechlin Jun 5 at 20:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.