I’ve just setup percona cluster 5.7 in k8s, using the percona operator.
Everything seems to be fine, except that, although I can use the root user to create new users, I can not grant them privileges.
I’ve setup only 1 replica, so this is not a replication issue.
Im doing this from a dummy webpage:
[host] => dummy-app-cluster-haproxy
[user] => root
[pass] => root_password
[sql] => GRANT ALL PRIVILEGES ON test.* TO 'server'@'%';
Where ‘server’ is the new user that is already created.
Hi @hgraca - welcome to the Percona Forums!
Can you show us the error that is returned when you run the GRANT statement?
I just checked on my PXC Operator v1.7 environment (PXC 8.0.21) and it shows that my root user has the GRANT option, meaning root can assign privs to other users. If you can show us the output of show grants when logged in as root that would help us diagnose this further.
mysql> show grants\G
*************************** 1. row ***************************
Grants for root@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 `root`@`localhost` WITH GRANT OPTION
mysql> select @@version;
+-------------+
| @@version |
+-------------+
| 8.0.21-12.1 |
+-------------+
1 row in set (0.00 sec)
There is no error that I can see, not returned by the call and not in the logs.
Im using a dummy web UI, but I will try instantiating a MySQL client pod in minikube.
Show grants give me this:
Grants for root@%
GRANT ALL PRIVILEGES ON . TO ‘root’@‘%’ WITH GRANT OPTION
mysql> show grants\G
*************************** 1. row ***************************
Grants for root@%: GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION
1 row in set (0.00 sec)
mysql> select @@version;
+--------------+
| @@version |
+--------------+
| 5.7.32-35-57 |
+--------------+
1 row in set (0.00 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.01 sec)
mysql> GRANT ALL PRIVILEGES ON test.* TO 'server'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> select * from mysql.user WHERE User = 'server' OR User = 'root';
+-----------+--------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked |
+-----------+--------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| localhost | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *2AF14BD74D8A4FDB580D3208C0092BE75EE95B75 | N | 2021-02-18 11:41:17 | NULL | N |
| % | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *2AF14BD74D8A4FDB580D3208C0092BE75EE95B75 | N | 2021-02-18 11:41:17 | NULL | N |
| localhost | server | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *C83ECE2124388A3DFF0268CCA8A3798C608F97A6 | N | 2021-02-18 12:10:54 | NULL | N |
| % | server | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | mysql_native_password | | N | 2021-02-18 12:11:13 | NULL | N |
+-----------+--------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
4 rows in set (0.00 sec)
mysql> exit
Bye
root@mysql-client:/# mysql -h dummy-app-cluster-pxc-0.dummy-app-cluster-pxc.dummy-app.svc.cluster.local -u server -pdummy-pass-user
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'server'@'172.17.0.9' (using password: YES)
