MySQL User Privileges

MySQL & MariaDB
1

Hi,
I did drop root user. After new create root user, but the user not yet privileges.
Example it isn’t not create new user. How can ı this error?

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES)
mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'passwpord';
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
mysql> GRANT GRANT OPTION ON *.* TO 'root'@'%';
ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES)
mysql> FLUSH PRIVILEGES;
ERROR 1227 (42000): Access denied; you need (at least one of) the RELOAD privilege(s) for this operation
mysql>
2

Hi @parttime .

Could you share the MySQL version, and how the user was dropped?

Also, is this a new MySQL installation, do you have other users created in there?

Finally, using this guide might help you (It’s for resetting a root password, which is not directly your case, but due to the errors it might help: https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html).

Please try it, should it not work, share more details about the current situation.

Best,
Mauricio.

3

This error. How we can resolve the stitation?

mysql> GRANT ALL PRIVILEGES ON . TO ‘user’@‘%’ WITH GRANT OPTION;
ERROR 1045 (28000): Access denied for user ‘root’@‘%’ (using password: YES)

5

Hello @parttime

Ref: https://dev.mysql.com/doc/refman/8.0/en/grant.html
To grant a privilege with GRANT, you must have the GRANT OPTION privilege, and you must have the privileges that you are granting. (Alternatively, if you have the UPDATE privilege for the grant tables in the mysql system schema, you can grant any account any privilege.) When the read_only system variable is enabled, GRANT additionally requires the CONNECTION_ADMIN privilege (or the deprecated SUPER privilege).

Example:

master [localhost] {msandbox} ((none)) > select user, host,Grant_priv, Super_priv from mysql.user;
+------------------+-----------+------------+------------+
| user             | host      | Grant_priv | Super_priv |
+------------------+-----------+------------+------------+
| msandbox         | 127.%     | N          | Y          |
| msandbox_ro      | 127.%     | N          | N          |
| msandbox_rw      | 127.%     | N          | N          |
| rsandbox         | 127.%     | N          | N          |
| msandbox         | localhost | N          | Y          |
| msandbox_ro      | localhost | N          | N          |
| msandbox_rw      | localhost | N          | N          |
| mysql.infoschema | localhost | N          | N          |
| mysql.session    | localhost | N          | Y          |
| mysql.sys        | localhost | N          | N          |
| root             | localhost | Y          | Y          |
+------------------+-----------+------------+------------+
11 rows in set (0.00 sec)


master [localhost] {msandbox} ((none)) > select user();
+--------------------+
| user()             |
+--------------------+
| msandbox@localhost |
+--------------------+
1 row in set (0.00 sec)

master [localhost] {msandbox} ((none)) > show Grants\G
*************************** 1. row ***************************
Grants for msandbox@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `msandbox`@`localhost`
*************************** 2. row ***************************
Grants for msandbox@localhost: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `msandbox`@`localhost`
2 rows in set (0.00 sec)

master [localhost] {msandbox} ((none)) > create user u1@'%' identified by 'Hello@123';
Query OK, 0 rows affected (0.03 sec)


master [localhost] {msandbox} ((none)) > grant ALL on *.* to u1@'%';
ERROR 1045 (28000): Access denied for user 'msandbox'@'localhost' (using password: YES)
master [localhost] {msandbox} ((none)) > grant ALL on *.* to u1@'%' with grant option;
ERROR 1045 (28000): Access denied for user 'msandbox'@'localhost' (using password: YES)
master [localhost] {msandbox} ((none)) >

Check if you have any user with Grant_priv, Super_priv

select user, Grant_priv, Super_priv from mysql.user;

If it’s a new setup without any data, I would suggest Re-Initializing MySQL and creating a required user with ALL, SUPER, WITH GRANT Option privileges before deleting the root user.