Author Topic: MySQL root login  (Read 2273 times)

RJStock

  • Zen Apprentice
  • *
  • Posts: 6
  • Karma: +0/-0
    • View Profile
MySQL root login
« on: March 04, 2017, 07:43:30 pm »
I have Zentyal 5.07 operating for a month. I want to run a website that requires php and additional mysql databases.

I have located the root mysql password in /var/lib/zentyal/conf/zentyal-mysql.passwd file. I am unable to login on the command line as root using that password. The zentyal user is able to login from command line with that password.

I re-started mysql with the skip-grant-tables option and was able to see that the password in the authentication_string column for the zentyal user and root are the same, and do match to a "select password('password-from-file')" statement. I've also tried re-setting the root password, no luck. I've reset it back to original. There is no password column as with earlier mysql versions.

Does anyone have the solution?




RJStock

  • Zen Apprentice
  • *
  • Posts: 6
  • Karma: +0/-0
    • View Profile
Re: MySQL root login
« Reply #1 on: March 04, 2017, 08:42:06 pm »
Root user uses plugin auth_socket. I'm able to login without password using sudo mysql.

Now to figure out how to use phpmyadmin so that it can see more databases than just its own!

half_life

  • Bug Hunter
  • Zen Hero
  • *****
  • Posts: 862
  • Karma: +57/-0
    • View Profile
Re: MySQL root login
« Reply #2 on: March 06, 2017, 02:57:17 pm »
Please take a look at
Code: [Select]
/var/lib/zentyal/conf/zentyal-mysql.passwd

jclendineng

  • Zen Monk
  • **
  • Posts: 61
  • Karma: +2/-0
    • View Profile
Re: MySQL root login
« Reply #3 on: March 07, 2017, 05:37:16 pm »
He stated that in the post actually, didn't seem to work. Mysql is crazy on here, I've had issues myself. It works, though you need to disable root for security and add a admin user specifically for Mysql and grant all permissions. That's what I did. Make sure you set a good password and that the user is hardened sufficiently. The way Zentyal handles databases isnt great but trial and error makes it all work. I see you tried to login safe mode, that works usually, you DO need to reboot or at least reload the pertinent modules before you try as it fails to start in safe mode if you do not. I'm working on another system at the moment but when I get home I will get on and write a short tutorial if you're still having issues.

half_life

  • Bug Hunter
  • Zen Hero
  • *****
  • Posts: 862
  • Karma: +57/-0
    • View Profile
Re: MySQL root login
« Reply #4 on: March 08, 2017, 03:41:38 pm »
That password is for the zentyal user.  Specifying -u zentyal in the command yields the correct results

Code: [Select]
mysql -u zentyal -p
Once in  using

Code: [Select]
select * from mysql.user;
will give you a list of users with access to the database.

jclendineng

  • Zen Monk
  • **
  • Posts: 61
  • Karma: +2/-0
    • View Profile
Re: MySQL root login
« Reply #5 on: March 08, 2017, 04:12:18 pm »
Well the issue is user zentyal has no perms, we know what users have access, root, the issue is that the root pass listed is not correct.

OP: Create a new user for mysql, and as su grant all perms (in mysql), so it acts like root.  Dont change anything else as zentyal relies on some stuff for its databases.  Root pass does not work, even when you change the pass, the hash is still the same, so somewhere along the line zentyal forces the orig pass, though that pass does not seem to work.  Very strange, but all part of the fun.

Edit 2: Start mysql in safe mode to do all this, that works, though ymmv since it is super wonky on this build.  BUT once set up it works very well, with only a few issues I can see in the logs.  phpmyadmin works, the whole 9 yards, you just have to set up a user that has root privileges.  Id also suggest setting an admin group and restricting users from using su or acting as root just for hardening purposes, so the user you create doesnt become a liability.
« Last Edit: March 08, 2017, 04:21:36 pm by jclendineng »

half_life

  • Bug Hunter
  • Zen Hero
  • *****
  • Posts: 862
  • Karma: +57/-0
    • View Profile
Re: MySQL root login
« Reply #6 on: March 09, 2017, 05:34:08 am »
If hardening is your purpose, wouldn't it be better if said user could not access the database except from localhost and that the user not be able to login except via ssh keys?  Changing the root user from auth_socket is straightforward. 

Code: [Select]
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mynewpassword';
I had not noticed this change in version 5 but it is not really an issue as long as root has been provisioned in this way.  I dare say you have bigger problems if someone were to compromise the root user.