Access denied; you need (at least one of) the SUPER privilege(s) for this DEFINER operation

Just a MacOS extra update for hjpotter92 answer.

To make sed recognize the pattern in MacOS, you'll have to lớn add a backslash before the = sign, lượt thích this:

sed -i old 's/\DEFINER\=`[^`]*`@`[^`]*`//g' tệp tin.sql

Problem: You're trying to lớn import data (using mysqldump file) to lớn your mysql database ,but it seems you don't have permission to lớn perform that operation.

Solution: Assuming you data is migrated ,seeded and updated in your mysql database, take snapshot using mysqldump and export it to lớn file

mysqldump -u [username] -p [databaseName] --set-gtid-purged=OFF > [filename].sql

From mysql documentation:

GTID - A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (master). This identifier is unique not only to lớn the server on which it originated, but is unique across all servers in a given replication setup. There is a 1-to-1 mapping between all transactions and all GTIDs.

--set-gtid-purged=OFF SET GLOBAL.gtid_purged is not added to lớn the output, and SET SESSION.sql_log_bin=0 is not added to lớn the output. For a server where GTIDs are not in use, use this option or AUTO. Only use this option for a server where GTIDs are in use if you are sure that the required GTID phối is already present in gtid_purged on the target server and should not be changed, or if you plan to lớn identify and add any missing GTIDs manually.

Afterwards connect to lớn your mysql with user root ,give permissions , flush them ,and verify that your user privileges were updated correctly.

mysql -u root -p
UPDATE mysql.user SET Super_Priv='Y' WHERE user='johnDoe' AND host='%';
FLUSH PRIVILEGES;
mysql> SHOW GRANTS FOR 'johnDoe';
+------------------------------------------------------------------+
| Grants for johnDoe                                               |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `johnDoe`                                  |
| GRANT ALL PRIVILEGES ON `db1`.* TO `johnDoe`                     |
+------------------------------------------------------------------+

now reload the data and the operation should be permitted.

mysql -h [host] -u [user] -p[pass] [db_name] < [mysql_dump_name].sql

Full Solution

All the above solutions are fine. And here I'm gonna combine all the solutions sánh that it should work for all the situations.

1. Fixed DEFINER

For Linux and Mac

sed -i old 's/\DEFINER\=`[^`]*`@`[^`]*`//g' tệp tin.sql

For Windows
download atom or notepad++, open your dump sql tệp tin with atom or notepad++, press Ctrl+F
search the word DEFINER, and remove the line DEFINER=admin@% (or may be little different for you) from everywhere and save the tệp tin.
As for example
before removing that line: CREATE DEFINER=admin@% PROCEDURE MyProcedure
After removing that line: CREATE PROCEDURE MyProcedure

1. Remove the 3 lines Remove all these 3 lines from the dump tệp tin. You can use sed command or open the tệp tin in Atom editor and tìm kiếm for each line and then remove the line.
   Example: Open Dump2020.sql in Atom, Press ctrl+F, tìm kiếm SET SESSION.SQL_LOG_BIN= 0, remove that line.

SET  SESSION.SQL_LOG_BIN= 0;
SET  GLOBAL.GTID_PURGED=/*!80000 '+'*/ '';
SET  SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

2. There an issue with your generated file You might face some issue if your generated dump.sql tệp tin is not proper. But here, I'm not gonna explain how to lớn generate a dump tệp tin. But you can ask bủ (_)

Issue

Below statement or line your Dump tệp tin creating issue

DEFINER=username@`%

Simple Solution

The solution that you can workaround is to lớn remove all the entries from SQL dump tệp tin and import data from the GCP console.

 cát DUMP_FILE_NAME.sql |  sed -e 's/DEFINER=``@`%`//g' > NEW-CLEANED-DUMP.sql

above command will help to lớn remove all those lines from the dump tệp tin and create the new fresh dump tệp tin without Definer.

Try importing new file(NEW-CLEANED-DUMP.sql).

If you are on AWS RDS

You might see face issue, if your dump tệp tin is larger you can kiểm tra the first trăng tròn lines using

head -30 filename

once you can see output look for line and line number

SET  SESSION.SQL_LOG_BIN= 0;
SET  GLOBAL.GTID_PURGED=/*!80000 '+'*/ '';
SET  SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN; 

we will remove these lines by line numbers for example 17,18,24 line number

sed -e '24d;17d;18d' file-name.sql > removed-line-file-name.sql

For importing database tệp tin in .sql.gz format, remove definer and import using below command

zcat path_to_db_to_import.sql.gz | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | mysql -u user -p new_db_name

1. Earlier, export database in .sql.gz format using below command.

   mysqldump -u user -p old_db | gzip -9 > path_to_db_exported.sql.gz;

2. Import that exported database and removing definer using below command,

   zcat path_to_db_exported.sql.gz | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | mysql -u user -p new_db

If it helps, when I tried to lớn restore a DB dump on my AWS MySQL RDS, I got this error:

ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER, 
SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation

I didn't have to lớn change the DEFINER or remove/comment out lines. I just did:

GRANT SESSION_VARIABLES_ADMIN ON *.* TO myuser@'myhost';
GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO myuser@'myhost';

And I was able to lớn tự the restore.

In my case (trying to lớn execute a SQL tệp tin into AWS RDS) the beginning of my SQL statement looked lượt thích this:

DROP VIEW IF EXISTS `something_view`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `something_view`...

All I had to lớn tự to lớn fix it was to lớn remove ALGORITHM=UNDEFINED DEFINER='root'@'%' SQL SECURITY DEFINER part of the above statement.

So the new statement looks lượt thích this:

CREATE VIEW 'something_view' ...

* Answer may only be applicable to lớn MacOS *

When trying to lớn import a .sql tệp tin into a docker container, I encountered the error message:

Access denied; you need (at least one of) the SUPER privilege(s) for this operation

Then while trying some of the other suggestions, I received the below error on my MacOS (osx)

sed: RE error: illegal byte sequence

Finally, the following command from this resource resolved my "Access Denied" issue.

LC_ALL=C sed -i old 's/\DEFINER\=`[^`]*`@`[^`]*`//g' fileName.sql

So I could import into the docker database with:

docker exec -i dockerContainerName mysql -uuser -ppassword table < importFile.sql

Hope this helps! :)

Issue in dump.

Please try to lớn get dump by following way:

mysqldump -h databasehost --user=databaseusername --password --single-transaction databasename  | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | gzip > /tmp/database.sql.gz

Then, try to lớn import by following way:

zcat /tmp/database.sql.gz | mysql -h database_host -u username -p databasename

My Problem: I want to lớn dump the database from A provider to lớn restore a database on the database cluster on B provider with MySQL version 8 both via DBeaver.

Solution: I follow this step was worked. I hope this solution will help you.

1. Backup database with additional option - Remove DEFINER

2. Assign GRANT permission

GRANT ALL PRIVILEGES ON [database_name].* TO '[database_user]'@'[database_host]';
FLUSH PRIVILEGES;

3. Restore database with sql dump tệp tin, you will found this error

Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation

4. Go to lớn sql dump tệp tin, comment this line and save it

SET  GLOBAL.GTID_PURGED=/*!80000 '+'*/;

5. Restore database to lớn destination server again

Some SET statements might require privileges,sánh you can try removing them:

sed -i '/^SET/d' dump.sql

Now you can grant SET_USER_ID to lớn the user which is running the import on a mysql RDS instance:
GRANT SET_USER_ID ON *.* TO 'import_user'@'%';

then you can run:
mysql -h xxxx.rds.amazonaws.com -u import_user -ppass db < db.sql

SET_USER_ID from mysql doc:

A user with this privilege can specify any trương mục as the DEFINER attribute of a view or stored program

In case someone is having a hard time trying to lớn lập cập Djando. I've found the inconvenience in seetings.py mysql connection

"OPTIONS": {
            'init_command': "SET sql_mode='STRICT_TRANS_TABLES', innodb_strict_mode=1"
}

Give the permissions for your MYSQL user, (i don't really know if it the correct solution but it works for bủ...)

GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' WITH GRANT OPTION;