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

Question

So I try to import sql file into rds (1G MEM, 1 CPU). The sql file is like 1.4G

mysql -h xxxx.rds.amazonaws.com -u user -ppass --max-allowed-packet=33554432 db < db.sql

It got stuck at:

ERROR 1227 (42000) at line 374: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

The actual sql content is:

/*!50003 CREATE*/ /*!50017 DEFINER=`another_user`@`1.2.3.4`*/ /*!50003 TRIGGER `change_log_BINS` BEFORE INSERT ON `change_log` FOR EACH ROW
IF (NEW.created_at IS NULL OR NEW.created_at = '00-00-00 00:00:00' OR NEW.created_at = '') THEN
        SET NEW.created_at = NOW();
END IF */;;

another_user is not existed in rds, so I do:

GRANT ALL PRIVILEGES ON db.* TO another_user@'localhost';

Still no luck.

hjpotter92 · Accepted Answer · 2018-02-27T12:17:22.540

351

Either remove the DEFINER=.. statement from your sqldump file, or replace the user values with CURRENT_USER.

The MySQL server provided by RDS does not allow a DEFINER syntax for another user (in my experience).

You can use a sed script to remove them from the file:

sed 's/\sDEFINER=`[^`]*`@`[^`]*`//g' -i oldfile.sql

edited Feb 27 '18 at 12:17

answered May 17 '17 at 04:51

hjpotter92

78,589
36
144
183

4

You're correct. The reason it doesn't work is that specifying another user as `DEFINER` when the logged-in user does not have the `SUPER` privilege (which itself is not allowed in RDS) would allow arbitrary privilege escalation -- stored programs run with the credentials and privileges of their `DEFINER` (as opposed to those of the calling user -- their `INVOKER`), by default. Also [at Server Fault](https://serverfault.com/a/555983/153161). – Michael - sqlbot May 17 '17 at 11:28
2

Man you are a life saver. Had my hosting company tell me corrupt database when exported and nothing that could be done to restore. Perfect solution. – Woody Oct 05 '17 at 01:24
7

For some reason I had to use * instead of +: ````sed 's/\sDEFINER=`[^`]*`@`[^`]*`//' -i oldfile.sql```` – Berend de Boer Nov 21 '17 at 21:50
The `+` is not valid in standard sed. `sed -E` also works, but I don't see any harm in changing it to `*` anyway. – FauxFaux Feb 27 '18 at 12:06
1

is there any faster option ? sed is kind of slow if the sql file is very big – WonderLand Mar 19 '18 at 06:33
1

@WonderLand You can try `awk` which might be a little faster than `sed` – hjpotter92 Mar 19 '18 at 06:41
if it easy/fast for you ... do you mind improve your answer adding an `awk` example too ? – WonderLand Mar 19 '18 at 14:57
@WonderLand I haven't used `awk` much. I just read online [here](https://unix.stackexchange.com/a/311546) that sed is slower than awk. – hjpotter92 Mar 19 '18 at 15:36
This solution worked perfectly for me. Another potential solution from aws doc: https://aws.amazon.com/premiumsupport/knowledge-center/error-1227-mysqldump/ – Md. Jun 28 '19 at 15:51
The RDS export I had had double quotes instead of backticks. `/s\sDEFINER="[^`]*"@"[^`]*"//g` works on this type. – Elijah Lynn Aug 21 '19 at 01:27
1

@trainoasis osx uses a different version of sed/grep etc. tools. Please check the man pages for relevant commands in osx `sed`. – hjpotter92 Mar 02 '20 at 15:10
For some reason the sed.exe that comes with Git Bash tools doesn't seem to like character classes - to get around this I used this regex instead: `sed 's/ DEFINER=\`\w*\`@\`\w*\`//g' -i oldfile.sql` – Steve Chambers Mar 28 '21 at 20:42

Jeremy Jones · Answer 2 · 2023-02-22T21:21:57.433

284

Remove the 3 lines below if they're there, or comment them out with -- :

At the start:

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

At the end:

-- SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

Note that the comment characters are "dash dash space" including the space.

A better solution is to stop these lines from being written to the dump file at all by including the option --set-gtid-purged=OFF on your mysqldump command.

edited Feb 22 '23 at 21:21

answered Dec 23 '19 at 11:38

Jeremy Jones

4,561
3
16
26

71

You can prevent these by adding `--set-gtid-purged=OFF` to your `mysqldump` command. Found here: https://stackoverflow.com/a/56251925 – Illya Moskvin Dec 27 '19 at 07:13
2

@IllyaMoskvin this is what worked for me. Upon Date Export in my sql workbench --> Advanced Options --> set-gtid-purged - Add 'SET @@GLOBAL.GTID_PURGED' to the output --> set to "OFF" – Llama Feb 10 '21 at 20:26
1

Worked for me as well. I had mariadb version of mysql. – Sanjeev Kulkarni N Feb 12 '21 at 14:43
3

This worked for me on RDS. – Srikanth Reddy Mar 02 '22 at 01:56
1

Worked for me, heroku based SQL (JAWSDB) – Mathias Bradiceanu Jun 19 '23 at 11:35
1

Worked, thanks. – Roelof Briers Jul 31 '23 at 21:21

score 71 · Answer 3 · edited Oct 26 '21 at 11:59

71

Another useful trick is to invoke mysqldump with the option --set-gtid-purged=OFF which does not write the following lines to the output file:

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

not sure about the DEFINER one.

edited Oct 26 '21 at 11:59

Dherik

17,757
11
115
164

answered Mar 10 '20 at 16:19

quimm2003

871
6
8

4

Thanks! It helped me in case of RDS – Victor Mar 11 '20 at 23:34
1

Thank you. In my case I ran `SET @@GLOBAL.GTID_MODE = OFF;` in MySql Workbench in the exporting side from source database – Byron Wong May 07 '20 at 13:52
I have deleted these two lines from the dump file then it works for me --SET @@SESSION.SQL_LOG_BIN= 0; SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ ''; – Omkar Singh Jun 02 '21 at 09:10
2

thank you. I solved it by specifying `--set-gtid-purged=OFF` in MySQL Workbench. – Day Break Nov 01 '21 at 14:59

score 25 · Answer 4 · answered Feb 26 '21 at 07:28

When we create a new RDS DB instance, the default master user is not the root user. But only gets certain privileges for that DB instance. This permission does not include SET permission. Now if your default master user tries to execute mysql SET commands, then you will face this error: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation

Solution 1

Comment out or remove these lines

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

Solution 2

You can also ignore the errors by using the -f option to load the rest of the dump file.

mysql -f <REPLACE_DB_NAME> -u <REPLACE_DB_USER> -h <DB_HOST_HERE> -p < dumpfile.sql

Solution 2 works well if you are piping a mysqldump to a new database — andrew lorien, Feb 24 '22 at 05:27

score 21 · Answer 5 · answered Nov 12 '19 at 00:36

21

Just a MacOS extra update for hjpotter92 answer.

To make sed recognize the pattern in MacOS, you'll have to add a backslash before the = sign, like this:

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

answered Nov 12 '19 at 00:36

marcel

266
2
5

works as of 2020, using MariaDB 10.4 on macOS Catalina – Wayne Jun 15 '20 at 06:48
This worked for me also on MacOS. Thank you so much! – Romeo Onisim Jun 01 '21 at 09:03

score 14 · Answer 6 · answered Mar 26 '20 at 13:21

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

Solution: Assuming you data is migrated ,seeded and updated in your mysql database, take snapshot using mysqldump and export it to 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 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 the output, and SET @@SESSION.sql_log_bin=0 is not added to 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 set is already present in gtid_purged on the target server and should not be changed, or if you plan to identify and add any missing GTIDs manually.

Afterwards connect to 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

score 9 · Answer 7 · answered Oct 02 '20 at 08:54

Full Solution

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

Fixed DEFINER

For Linux and Mac

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

For Windows
download atom or notepad++, open your dump sql file 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 file.
As for example
before removing that line: CREATE DEFINER=admin@% PROCEDURE MyProcedure
After removing that line: CREATE PROCEDURE MyProcedure

Remove the 3 lines Remove all these 3 lines from the dump file. You can use sed command or open the file in Atom editor and search for each line and then remove the line.
Example: Open Dump2020.sql in Atom, Press ctrl+F, search 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;

There an issue with your generated file You might face some issue if your generated dump.sql file is not proper. But here, I'm not gonna explain how to generate a dump file. But you can ask me (_)

Harsh Manvar · Answer 8 · 2022-02-19T11:18:56.930

Issue

Below statement or line your Dump file creating issue

DEFINER=username@`%

Simple Solution

The solution that you can workaround is to remove all the entries from SQL dump file and import data from the GCP console.

 cat DUMP_FILE_NAME.sql |  sed -e 's/DEFINER=`<username>`@`%`//g' > NEW-CLEANED-DUMP.sql

above command will help to remove all those lines from the dump file and create the new fresh dump file without Definer.

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

If you are on AWS RDS

You might see face issue, if your dump file is larger you can check the first 20 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

score 3 · Answer 9 · answered Jun 10 '20 at 17:08

For importing database file 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

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

mysqldump -u user -p old_db | gzip -9 > path_to_db_exported.sql.gz;
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

score 3 · Answer 10 · answered Sep 29 '20 at 08:11

3

When you restore backup, Make sure to try with the same username for the old one and the new one.

answered Sep 29 '20 at 08:11

Mafei

3,063
2
15
37

score 2 · Answer 11 · answered Dec 09 '19 at 06:53

2

I commented all the lines start with SET in the *.sql file and it worked.

answered Dec 09 '19 at 06:53

Tengerye

1,796
1
23
46

6

nice man, really descriptive. youre doing a great job – Llama Mar 31 '21 at 03:04

score 1 · Answer 12 · answered Mar 03 '21 at 11:27

If it helps, when I tried to 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 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 do the restore.

score 1 · Answer 13 · answered Mar 31 '21 at 10:48

1

None of the above solutions worked for me. I had to do the following:

Use the following flags with mysqldump:

mysqldump --databases <db1> <db2> --master-data=1 --single-transaction --order- 
by-primary --foce -r all.sql -h<host> -u<user> -p<password>

Remove the line that looks like:
```
CHANGE MASTER TO MASTER_LOG_FILE='binlog.....
```
- In my file, that was line #22, so I ran: sed -i '22d' all.sql

Import the data to your RDS:

mysql -h<host> -u<user> -p<password>
$ source all.sql

answered Mar 31 '21 at 10:48

Maroun

94,125
30
188
241

Did your dump still contain the three SET lines that all the other solutions say we have to delete? I can't see how these flags will make a difference – andrew lorien Feb 24 '22 at 05:06

score 1 · Answer 14 · answered Jun 26 '22 at 09:29

In my case (trying to execute a SQL file into AWS RDS) the beginning of my SQL statement looked like this:

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

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

So the new statement looks like this:

CREATE VIEW 'something_view' ...

score 0 · Answer 15 · answered Apr 29 '20 at 15:00

* Answer may only be applicable to MacOS *

When trying to import a .sql file 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! :)

score 0 · Answer 16 · edited Feb 11 '22 at 17:32

0

Issue in dump.

Please try to 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 import by following way:

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

edited Feb 11 '22 at 17:32

Automate This

30,726
11
60
82

answered Mar 08 '21 at 12:07

Alkesh Sanghadiya

1
1

Thanawat · Answer 17 · 2023-06-09T12:18:54.167

My Problem: I want to dump the database from A provider to 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.

Backup database with additional option - Remove DEFINER

Assign GRANT permission

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

Restore database with sql dump file, you will found this error

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

Go to sql dump file, comment this line and save it

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

Restore database to destination server again

score 0 · Answer 18 · answered Aug 25 '23 at 09:04

0

Some SET statements might require privileges,so you can try removing them:

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

answered Aug 25 '23 at 09:04

Vaibhav Jain

305
3
8

score -1 · Answer 19 · answered Oct 02 '20 at 21:00

-1

Need to set "on" server parameter "log_bin_trust_function_creators" on server side. This one you can easily find on left side blade if it is azure maria db.

answered Oct 02 '20 at 21:00

karthik varada

1

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

19 Answers19

Solution 1

Solution 2

Full Solution

Linked