Here at XTIVIA we have provided professional services many times to clients running into errors while restoring a MySQL database from a backup created with mysqldump. One specific error on import of a backup, Error 1227, reports “Access denied; you need (at least one of) the SUPER privilege(s) for this operation” and provides a line number in the sql dump file. When reviewing the information at that line, it is often found that a create view statement is to be run but errors out. The problem is, the user who is restoring the backup does not have the same database privileges as the user defined in the view.
What is a view?
Queries can be stored as virtual tables within MySQL. The virtual table is called a view and will provide a result set of the query when invoked. Views can be created to use numerous different types of select statements, stored and invoked in a simpler fashion as a view than by providing the full query or queries.
How is a view created?
Creating a view involves providing a user or “definer” for the view, providing the query and defining the SQL Security for the view as the definer or an “invoker”. The default SQL Security option is “definer” and a definer is the user who created the view or a user who is labeled as the definer at the time of the view creation. An invoker is any user invoking the view by running a statement which references the view after it is created. These different users have defined privileges within the database, as all users do. Defining privileges to the view makes sense because a user without access to a certain schema or certain table should not be able to query that data via invoking views or other methods. By creating a view as a definer with specific privileges, only those with the minimum permissions of the definer are allowed to view the underlying data. The view has set permissions of the user defined as the definer or as the invoker.
Why is Error 1227 so commonly encountered when importing a view from a logical backup?
The reason MySQL throws error 1227 during a restore at a create view statement is because the definer of the view differs from the user restoring the database. Super privilege is required to create the view which was defined by a user other than the user importing the data. This is a security measure in case the definer of the view has privileges to access certain data that the user who is running the restore does not.
What can be done to resolve Error 1227?
There are a few workarounds to get all data and views imported. Understanding the risks involved with each is recommended prior to using these options.
- Restore the database as a user with super privilege. Following the import, alter the views to set the definers back to their original users.
- Restore the database as the definer user if possible. It is likely that there are many views with different definers having different permissions so this may not be feasible. Following the import, alter the views to set the definers back to their original users.
- Edit the backup file by either removing the DEFINER= statement from the backup file, or replace the definer values with CURRENT_USER.
For example use sed or perl to modify the file. Following the import, alter the views to set the definers back to their original users.