Project

General

Profile

Tâche #29872

Scénario #30411: Traitement express MEN (28-35)

EOLE 2.6 : accès à la base mysql sans mot de passe

Added by Yoni Baude 6 months ago. Updated 20 days ago.

Status:
Fermé
Priority:
Normal
Assigned To:
Start date:
04/08/2020
Due date:
% Done:

100%

Estimated time:
0.00 h
Remaining (hours):
0.0

Description

Bonjour,

Après une migration d'un horus 2.5 vers 2.6, l'upgrade de la base mysql (version 5.5 vers la version 5.7) active le plugin mysql_native_password pour le compte root@localhost. Un mot de passe est nécessaire pour la connexion à la base mysql.

   mysql> SELECT user, host, plugin, authentication_string FROM mysql.user;
+------------------+-----------+-----------------------+-------------------------------------------+
| user             | host      | plugin                | authentication_string                     |
+------------------+-----------+-----------------------+-------------------------------------------+
| root             | localhost | mysql_native_password | ***************************************** |

Lors d'une installation neuve horus 2.6, la base mysql est activé avec le plugin auth_socket pour le compte root@localhost.

mysql> SELECT user, host, plugin, authentication_string FROM mysql.user;
+------------------+-----------+-----------------------+-------------------------------------------+
| user             | host      | plugin                | authentication_string                     |
+------------------+-----------+-----------------------+-------------------------------------------+
| root             | localhost | auth_socket           | ***************************************** |

Plus de mot passe nécessaire pour une connexion root sur mysql, le compte système est utilisé.

Requête pour modifier le plugin mysql:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'le_mot_de_pass_choisi';

Bien à vous,
Yoni


Related issues

Related to eole-mysql - Scénario #21688: Gérer les problèmes de connexion à phpMyAdmin avec le compte root Terminé (Sprint) 02/16/2018 03/09/2018

Associated revisions

Revision 31aed926 (diff)
Added by Gérald Schwartzmann about 1 month ago

Skip column names to pass into the condition ref #25519

  • add param to mysql command

Cherry-picked from eole-mysql:1907578f for EOLE 2.6.2

Ref: #29872

History

#1 Updated by Yoni Baude 5 months ago

Le plugin mysql auth_socket pour le compte root@localhost ne permet pas l'installation du serveur GFC windows.

#2 Updated by Joël Cuissinat 2 months ago

  • Parent task set to #30411

#3 Updated by Joël Cuissinat about 1 month ago

  • Related to Scénario #21688: Gérer les problèmes de connexion à phpMyAdmin avec le compte root added

#4 Updated by Joël Cuissinat about 1 month ago

Normalement, le script /usr/share/eole/postservice/00-mysql-native-password modifie bien le plugin d'authentification pour l'utilisateur root.

Il est également possible d'utiliser le compte de maintenance "debian-sys-maint" :

mysql --defaults-file=/etc/mysql/debian.cnf

Du coup, si je comprends bien, le problème concerne les serveurs sur lesquels on souhaite restaurer GFC avant instance ?

#5 Updated by Joël Cuissinat about 1 month ago

  • 2.6.2 (daily)
    mysql> SELECT user, host, plugin, authentication_string FROM mysql.user;
    +------------------+-----------+-----------------------+-------------------------------------------+
    | user             | host      | plugin                | authentication_string                     |
    +------------------+-----------+-----------------------+-------------------------------------------+
    | root             | localhost | auth_socket           |                                           |
    | mysql.session    | localhost | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
    | mysql.sys        | localhost | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
    | debian-sys-maint | localhost | mysql_native_password | *051311F2BB28ED3631A9367B5414D909B8FC6CA6 |
    +------------------+-----------+-----------------------+-------------------------------------------+
    
  • 2.6.2 (instance-default)
    +------------------+-----------+-----------------------+-------------------------------------------+
    | user             | host      | plugin                | authentication_string                     |
    +------------------+-----------+-----------------------+-------------------------------------------+
    | root             | localhost | auth_socket           | *E11ED96D04E93A8D57E36EE630F5BBEFDD2D5AC6 |
    | mysql.session    | localhost | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
    | mysql.sys        | localhost | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
    | debian-sys-maint | localhost | mysql_native_password | *051311F2BB28ED3631A9367B5414D909B8FC6CA6 |
    +------------------+-----------+-----------------------+-------------------------------------------+
    

=> Ce n'est pas le résultat attendu !

#6 Updated by Joël Cuissinat about 1 month ago

Visiblement, il y a un saut de ligne qui fait échouer le test [ "${plugin##plugin}" = "auth_socket" ] !

root@horus:~# plugin=$(mysql --batch -e 'use mysql;select plugin from user where user="root" and plugin="auth_socket";' 2>/dev/null)
root@horus:~# echo "\"${plugin}\"" 
"plugin
auth_socket" 
root@horus:~# echo "\"${plugin##plugin}\"" 
" 
auth_socket" 

Erf, ça passait sans les double quotes à gauche...

root@horus:~# [ ${plugin##plugin} = "auth_socket" ] && echo "OK" 
OK
root@horus:~# [ "${plugin##plugin}" = "auth_socket" ] && echo "OK" 
root@horus:~#

Les double quotes ont été ajoutés suite à un autre problème : #26805 sauf qu'en 2.7, l'option --skip-column-names (#25294) a également été ajoutée mais pas en 2.6.2 !

~/git/eole-mysql$ git branch -r --contains 1907578f 
  origin/2.7.0/master
  origin/2.7.1/28203-fromhost_vs_hostname
  origin/2.7.1/master
  origin/2.7.10/master
  origin/2.7.2/master
  origin/HEAD -> origin/master
  origin/dist/eole/2.7.0/master
  origin/dist/eole/2.7.1/master
  origin/dist/eole/2.7.10/master
  origin/dist/eole/2.7.2/master
  origin/dist/eole/2.8.0/master
  origin/master

#7 Updated by Joël Cuissinat about 1 month ago

  • Status changed from Nouveau to En cours
  • Assigned To set to Joël Cuissinat

#8 Updated by Joël Cuissinat about 1 month ago

=> eole-mysql 2.6.2-5

#9 Updated by Joël Cuissinat about 1 month ago

  • Status changed from En cours to Résolu
  • % Done changed from 0 to 100

#10 Updated by Fabrice Barconnière about 1 month ago

OK :
  • Avant MàJ :
    root@horus:~# mysql                                                                                      
    Welcome to the MySQL monitor.  Commands end with ; or \g.                                                
    Your MySQL connection id is 11                                                                                                                                                                                     
    Server version: 5.7.31-0ubuntu0.16.04.1 (Ubuntu)                                                                                                                                                                   
    
    Copyright (c) 2000, 2020, 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> SELECT user, host, plugin, authentication_string FROM mysql.user;                                                                                                                                           
    +------------------+-----------+-----------------------+-------------------------------------------+                                                                                                               
    | user             | host      | plugin                | authentication_string                     |                                                                                                               
    +------------------+-----------+-----------------------+-------------------------------------------+                                                                                                               
    | root             | localhost | auth_socket           | *E11ED96D04E93A8D57E36EE630F5BBEFDD2D5AC6 |
    
  • Après MàJ + reconfigure :
    root@horus:~# mysql
    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
    
    root@horus:~# mysql --user=root --password
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 8
    Server version: 5.7.31-0ubuntu0.16.04.1 (Ubuntu)
    
    Copyright (c) 2000, 2020, 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> SELECT user, host, plugin, authentication_string FROM mysql.user;
    +------------------+-----------+-----------------------+-------------------------------------------+
    | user             | host      | plugin                | authentication_string                     |
    +------------------+-----------+-----------------------+-------------------------------------------+
    | root             | localhost | mysql_native_password | *C483A839ADC9722C3AE5D93FC708787684F28473 |
    

#11 Updated by Fabrice Barconnière about 1 month ago

  • Project changed from eole-mysql to Distribution EOLE
  • Status changed from Résolu to Fermé
  • Remaining (hours) set to 0.0

#12 Updated by Joël Cuissinat 20 days ago

  • Estimated time set to 0.00 h

Yoni a répondu

Also available in: Atom PDF