Can't grant privileges to user

[hgraca]

Hi,

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.

Any ideas on what the problem might be?

[Michael_Coburn]

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)

[hgraca]

Hi @Michael_Coburn , tkx for your prompt reply.

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

And the version:
@@version
5.7.32-35-57

[hgraca]

Here what I got from the terminal:

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)

[Michael_Coburn]

Hi @hgraca
You have no authentication_string defined for user server@'%', you should do this as the root user:

ALTER USER server@'%' IDENTIFIED BY 'dummy-pass-user';

[hgraca]

HAH!!

It’s working now!

Tkx a lot for your quick help!!