«

Dec 01

Solved – AWS RDS MySQL ERROR 1227 (42000) at line : Access denied

When your are trying to import your data into RDS MySQL, it may prompt with following error message. “ERROR 1227 (42000) at line xxx: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

 

Error!

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

This can be fixed by removing the DEFINER from MySQL dump. You can use following simple command to fix this issue.

 

there are alternative solution which provided by AWS premium support knowledge base , But that does not work for me. You can try that out if I above mentioned work around does not work.

Let’s look at why there is limited permission on RDS MySQL .

As you might be aware, AWS RDS (Relational Database Service) is a managed serviceĀ  and hence in order to guarantee the stability of RDS instance, the permissions of master user (root user in RDS) are not same as root user in native mysql.

RDS Master user has the following permission:
SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* WITH GRANT OPTION, REPLICATION SLAVE (Only For Amazon RDS MySQL versions 5.6 and 5.7, Amazon RDS MariaDB)

So if you need more permission other than above, you have to request / inform it to AWS support team. they will do the necessary arrangement.

If you have any questions please do comment below. :)

have nice time !!

Leave Your Thought Here