Error code 1410 mysql

Just my 2 cents on the subject. I was having the exact same issue with trying to connect from MySQL Workbench. I’m running a bitnami-mysql virtual machine to set up a local sandbox for development.

Bitnami’s tutorial said to run the ‘Grant All Privileges’ command:

/opt/bitnami/mysql/bin/mysql -u root -p -e "grant all privileges on *.* to 'root'@'%' identified by 'PASSWORD' with grant option";

This was clearly not working, I finally got it to work using Mike Lischke’s answer.

What I think happened was that the root@% user had the wrong credentials associated to it. So if you’ve tried to modify the user’s privileges and with no luck try:

1. Dropping the user.
2. Create the user again.
3. Make sure you have the correct binding on your MySQL config file.
   In my case I’ve commented the line out since it’s just for a sandbox environment.

1. Dropping the user.

From Mysql Console:

List Users (helpful to see all your users):

select user, host from mysql.user;

Drop Desired User:

drop user '{{ username }}'@'%';

2. Create the user again.

Create User and Grant Permissions:

CREATE USER '{{ username }}'@'%' IDENTIFIED BY '{{ password }}';
GRANT ALL PRIVILEGES ON *.* TO '{{ username }}'@'%' WITH GRANT OPTION;

Run this command:

FLUSH PRIVILEGES;

3. Make sure you have the correct binding on your MySQL config file.

Locate your MySQL config file (additional notes at the end). If you want to have MySQL listen for connections on more than one network find the following line on the config file:

bind-address=127.0.0.1

and comment it using a ‘#’:

#bind-address=127.0.0.1

For production environments you might want to use limit the network access (additional notes at the end).

Then restart your MySQL service.

Hope this helps someone having the same issue!

Binding: If you want to know more about this I suggest looking at the following
solution How to bind MySQL server to more than one IP address. It
basically says you can leave MySQL open and limit connections by using
a firewall, or natively if you have MySQL version 8.0.13 and above.

MySQL Config File The file could have different locations depending on your
Linux distribution and installation. On my system it was located at
'/etc/my.cnf'. Here are other suggested locations:

• /etc/mysql/mysql.conf.d
• /etc/mysql/my.cnf

You can also search for the config locations as shown in this website:
How to find locations of MySQL config files.

I was trying to install wordpress on ubuntu using the link and struck at https://ubuntu.com/tutorials/install-and-configure-wordpress#4-configure-database

When I run this command,I get the error-

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER
    -> ON wordpress.*
    -> TO wordpress@localhost
    -> IDENTIFIED BY 'root';


ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'root'' at line 1

I tried almost every syntax by changing the wordpress@localhost to 'wordpress'@'localhost'

I tried removing and adding the APOSTROPHE on 'root' in IDENTIFIED BY 'root',

then after searching I tried this command as well — GRANT ALL PRIVILEGES ON *.* TO 'wordpress'@'localhost' IDENTIFIED BY 'root‘ and got

Error — ERROR 1410 (42000): You are not allowed to create a user with GRANT

Nothing found working for me. Please help where am doing mistake. Thanks

P.S. Database is already there —

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wordpress          |
+--------------------+
5 rows in set (0.01 sec)

It seems, that this is a question that regularly shows up in forums or stackoverflow.

To start, let’s highlight the fact that in MySQL 8.0 it’s not any more possible to create a user directly from the GRANT command (ERROR 1410 (42000): You are not allowed to create a user with GRANT).

This means that to grant some privileges, the user must be created first.

Let’s create a user ‘user1‘ with ‘ChangeMe‘ as password that the user will have to change:

mysql> create user 'user1' identified by 'ChangeMe' password expire;
Query OK, 0 rows affected (1.35 sec)

Let’s try to connect to MySQL using that new created user:

 $ mysql -u user1 -pChangeMe -h localhost
 mysql: [Warning] Using a password on the command line interface can be insecure.
 Welcome to the MySQL monitor.  Commands end with ; or g.
 Your MySQL connection id is 347
 Server version: 8.0.13
 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 Oracle is a registered trademark of Oracle Corporation and/or its
 affiliates. Other names may be trademarks of their respective
 owners.
 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
 mysql>

Nothing special, we are connected as expected… but didn’t I explicitly expired the password ?

Yes I did, let’s try any statement:

 mysql> select now();
 ERROR 1820 (HY000): You must reset your password using ALTER USER
 statement before executing this statement.

We must change the password as expected. Let’s change it to ‘MySQL8isGreat‘:

 mysql> set password='MySQL8isGreat';
 Query OK, 0 rows affected (0.34 sec)

And now we can use MySQL and run any statement we are allowed to do (that we have the privileges for).

 mysql> select now();
 +---------------------+
 | now()               |
 +---------------------+
 | 2019-01-10 14:36:05 |
 +---------------------+
 1 row in set (0.00 sec)
 mysql> show databases;
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 +--------------------+
 1 row in set (0.20 sec)

It seems I don’t have access to many databases…

The default privilege is very limited:

 mysql> show grants;
 +-----------------------------------+
 | Grants for user1@%                |
 +-----------------------------------+
 | GRANT USAGE ON . TO user1@% |
 +-----------------------------------+
 1 row in set (0.00 sec)

It’s now time to grant more privileges to our user… but which privileges are available ?

In 8.0.13, they are currently 46 privileges !

To list them all, just run:

 mysql> show privileges; 
 +----------------------------+---------------------------------------+-------------------------------------------------------+
 | Privilege                  | Context                               | Comment                                               |
 +----------------------------+---------------------------------------+-------------------------------------------------------+
 | Alter                      | Tables                                | To alter the table                                    |
 | Alter routine              | Functions,Procedures                  | To alter or drop stored functions/procedures          |
 | Create                     | Databases,Tables,Indexes              | To create new databases and tables                    |
 | Create routine             | Databases                             | To use CREATE FUNCTION/PROCEDURE                      |
 | Create role                | Server Admin                          | To create new roles                                   |
 | Create temporary tables    | Databases                             | To use CREATE TEMPORARY TABLE                         |
 | Create view                | Tables                                | To create new views                                   |
 | Create user                | Server Admin                          | To create new users                                   |
 | Delete                     | Tables                                | To delete existing rows                               |
 | Drop                       | Databases,Tables                      | To drop databases, tables, and views                  |
 | Drop role                  | Server Admin                          | To drop roles                                         |
 | Event                      | Server Admin                          | To create, alter, drop and execute events             |
 | Execute                    | Functions,Procedures                  | To execute stored routines                            |
 | File                       | File access on server                 | To read and write files on the server                 |
 | Grant option               | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess   |
 | Index                      | Tables                                | To create or drop indexes                             |
 | Insert                     | Tables                                | To insert data into tables                            |
 | Lock tables                | Databases                             | To use LOCK TABLES (together with SELECT privilege)   |
 | Process                    | Server Admin                          | To view the plain text of currently executing queries |
 | Proxy                      | Server Admin                          | To make proxy user possible                           |
 | References                 | Databases,Tables                      | To have references on tables                          |
 | Reload                     | Server Admin                          | To reload or refresh tables, logs and privileges      |
 | Replication client         | Server Admin                          | To ask where the slave or master servers are          |
 | Replication slave          | Server Admin                          | To read binary log events from the master             |
 | Select                     | Tables                                | To retrieve rows from table                           |
 | Show databases             | Server Admin                          | To see all databases with SHOW DATABASES              |
 | Show view                  | Tables                                | To see views with SHOW CREATE VIEW                    |
 | Shutdown                   | Server Admin                          | To shut down the server                               |
 | Super                      | Server Admin                          | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.   |
 | Trigger                    | Tables                                | To use triggers                                       |
 | Create tablespace          | Server Admin                          | To create/alter/drop tablespaces                      |
 | Update                     | Tables                                | To update existing rows                               |
 | Usage                      | Server Admin                          | No privileges - allow connect only                    |
 | XA_RECOVER_ADMIN           | Server Admin                          |                                                       |
 | SET_USER_ID                | Server Admin                          |                                                       |
 | ROLE_ADMIN                 | Server Admin                          |                                                       |
 | RESOURCE_GROUP_USER        | Server Admin                          |                                                       |
 | RESOURCE_GROUP_ADMIN       | Server Admin                          |                                                       |
 | BINLOG_ADMIN               | Server Admin                          |                                                       |
 | SYSTEM_VARIABLES_ADMIN     | Server Admin                          |                                                       |
 | GROUP_REPLICATION_ADMIN    | Server Admin                          |                                                       |
 | CONNECTION_ADMIN           | Server Admin                          |                                                       |
 | REPLICATION_SLAVE_ADMIN    | Server Admin                          |                                                       |
 | ENCRYPTION_KEY_ADMIN       | Server Admin                          |                                                       |
 | BACKUP_ADMIN               | Server Admin                          |                                                       |
 | PERSIST_RO_VARIABLES_ADMIN | Server Admin                          |                                                       |
 +----------------------------+---------------------------------------+-------------------------------------------------------+
 46 rows in set (0.00 sec)

You can see that a new user doesn’t have access to the test database anymore:
mysql> use test;
ERROR 1044 (42000): Access denied for user ‘user1’@’%’ to database ‘test’

Let’s allow our user to create tables in the database users1 that we created for him and also allow him to perform the following actions:

• Alter
• Create
• Delete
• Drop
• Index
• Insert
• Select
• Update
• Trigger
• Alter routine
• Create routine
• Execute
• Create temporary tables

 mysql> grant alter,create,delete,drop,index,insert,select,update,trigger,alter routine,
create routine, execute, create temporary tables on user1.* to 'user1';
 Query OK, 0 rows affected (0.23 sec)

NO NEED TO RUN FLUSH PRIVILEGES !

And in the open session for user1, we can check the granted privileges:

 mysql> show grantsG
 ******************** 1. row ********************
 Grants for user1@%: GRANT USAGE ON . TO user1@%
******************** 2. row ********************
Grants for user1@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP,
INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE ROUTINE, ALTER
ROUTINE, TRIGGER ON user1.* TO user1@%
2 rows in set (0.00 sec)

Now let’s imagine we want to have multiple users that will have access to the same database (mydatabase), instead of specifying all the grants for each users, let’s use a common role for all of them. We will call it ‘developer_user‘:

mysql> create ROLE developer_users;
mysql> grant alter,create,delete,drop,index,insert,select,update,trigger,alter
 routine,create routine, execute, create temporary tables 
on mydatabase.* to 'developer_user';
Query OK, 0 rows affected (0.12 sec)

Let’s grant the role to user1:

 mysql> grant 'developer_user' to 'user1';
 Query OK, 0 rows affected (0.16 sec)

Now back again in user1‘s session and let’s verify:

 mysql> SELECT CURRENT_ROLE();
 +----------------+
 | CURRENT_ROLE() |
 +----------------+
 | NONE           |
 +----------------+
 1 row in set (0.00 sec)
 mysql> set role   'developer_user';
 Query OK, 0 rows affected (0.00 sec)



  


                       
  



 mysql> SELECT CURRENT_ROLE();
 +----------------------+
 | CURRENT_ROLE()       |
 +----------------------+
 | developer_user@% |
+----------------------+
1 row in set (0.00 sec)
mysql> show grantsG
******************** 1. row ********************
Grants for user1@%: GRANT USAGE ON . TO user1@%
******************** 2. row ********************
Grants for user1@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, 
DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, 
CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON mydatabase.* TO user1@%
******************** 3. row ********************
Grants for user1@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, 
DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, 
CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON user1.* TO user1@%
******************** 4. row ********************
Grants for user1@%: GRANT developer_user@% TO user1@%
4 rows in set (0.00 sec)

Now we would like that every time user1 logs into MySQL, his new role will be set:

 mysql> set default role 'developer_user' to 'user1';
 Query OK, 0 rows affected (0.22 sec)

Let’s also create a user2 having the default role:

 mysql> create user 'user2' identified by 'DontChangeMe' default role 'developer_user';
 Query OK, 0 rows affected (0.18 sec)

And we can immediately test it:

 $ mysql -u user2 -pDontChangeMe -h localhost
 mysql: [Warning] Using a password on the command line interface can be insecure.
 Welcome to the MySQL monitor.  Commands end with ; or g.
 Your MySQL connection id is 352
 Server version: 8.0.13 MySQL Community Server - GPL
 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 Oracle is a registered trademark of Oracle Corporation and/or its
 affiliates. Other names may be trademarks of their respective
 owners.
 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
 mysql> show grantsG
 ******************** 1. row ********************
 Grants for user2@%: GRANT USAGE ON . TO user2@%
******************** 2. row ********************
Grants for user2@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, 
DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, 
CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON mydatabase.* TO user2@%
******************** 3. row ********************
Grants for user2@%: GRANT developer_user@% TO user2@%
3 rows in set (0.18 sec)

Summary

In summary, now in MySQL 8.0 you cannot create a user from GRANT, you don’t need to run FLUSH PRIVILEGES command (this is effective for a long time already, please forget about it !), you can use ROLES and you have more password management options.