Missing privilege after 18.04 > 20.04 upgrade

Discussion in 'General' started by Redwarp, Jan 9, 2022.

  1. Redwarp

    Redwarp Member

    Hi
    Can you help me to NOT take a sledgehammer to crack this nut please ?

    Just updated server from 18.04 ubuntu to 20.04 and majority is running fine.

    In ONE or TWO places only, in ispconfig3 control panel I am getting this error:
    Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation
    I am also getting same from within SSH shell trying to access mysql from cli etc.
    1) Do I need to reset my root mysql password ? (probably, as I will need to change it anyway at some point) but.....mysql is running, my databases are accessible etc....just cannot access root user directly.
    2) If I reset the root password, seems more likely than not that much more stuff will get knocked out so....can I just correct the permission/grant that seems to be the actual error without forcing a password change for the time being ?
    3) Can I "fish out" the root mysql password from one or more of the ispconfig system files ? to save myself all the aggro ?
     
  2. ahrasis

    ahrasis Well-Known Member HowtoForge Supporter

    Have you follow the PST for Ubuntu 20.04 after you finished upgrading?
     
  3. Redwarp

    Redwarp Member

    Hi....no, can you link me to that please ?
     
  4. ahrasis

    ahrasis Well-Known Member HowtoForge Supporter

  5. Redwarp

    Redwarp Member

  6. Redwarp

    Redwarp Member

    OK cool, almost directly there.....now managed to get a connection to mysql (root) so, just (hopefully) need to grant missing priveledge denoted by this error:
    Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation.
    Do you have a recommended technique for that please ?
     
  7. ahrasis

    ahrasis Well-Known Member HowtoForge Supporter

  8. Redwarp

    Redwarp Member

    Hi again
    Please can you point me to a quick fix on this ?
    I can not control/run my ispconfig control panel in many places I get the (error as per originally stated above....lacking a priviledge) So in places where ispconfig tries to assert access control over databases.....I get this error, so I can do nothing useful with the control panel admin and Im stuck.
    Is there a succinct way of updating the grants such that ispconfig will get back in control of the necessary database(s) please ? ftp users are all locked out, I cannot adinister websites or ftp and Im sure it is only a small issue (maybe not)
    What I have tried:
    GRANT ALL PRIVILEGES ON phpmyadmin.* TO 'ispconfig'@'localhost'; same for dbispconfig.*
    flushed etc, no joy
    Please help
     
  9. till

    till Super Moderator Staff Member ISPConfig Developer

    Try to run in mysql as mysql root user:

    Code:
    GRANT SUPER ON *.* TO [email protected];
    GRANT SUPER ON *.* TO [email protected];
    FLUSH PRIVILEGES;
    The MySQL root password is in the file /usr/local/ispconfig/server/lib/mysql_clientdb.conf
     
  10. Redwarp

    Redwarp Member

    Hi...I already fished out the root password so havent had to change that thankfully.
    I get a warning (doesnt tell me what it is) when I try:
    GRANT SUPER ON *.* TO [email protected]; or
    GRANT SUPER ON *.* TO 'ispconfig'@'localhost';
    maybe this is getting to the nub of the issue:
    mysql> GRANT SUPER ON *.* TO 'ispconfig'@'localhost';
    Query OK, 0 rows affected, 1 warning (0.00 sec)
     
  11. till

    till Super Moderator Staff Member ISPConfig Developer

    You must grant super privilege to ispconfig user for localhost and 127.0.0.1, granting it to localhost only is not enough. And single quotes are not required for strings that do not contain whitespace, but you may apply them, it makes no difference.
     
  12. Redwarp

    Redwarp Member

    Additionally, seems no record available here at all:
    mysql> GRANT SUPER ON *.* TO 'ispconfig'@'127.0.0.1';
    ERROR 1410 (42000): You are not allowed to create a user with GRANT
     
  13. Redwarp

    Redwarp Member

    So I need to create a user record, I can see how to do that, except (trying to avoid down the line issues) I want to create it with same (old password) for ispconfig user......is it going to be the same as for mysql roor user ? Or is there an ispconfig CONFIG file where I can fish that out of as well please.....Im sure we are close and grteful for your fast responses
     
  14. till

    till Super Moderator Staff Member ISPConfig Developer

    You can find the password of the ispconfig user in the file /usr/local/ispconfig/server/lib/config.inc.php
     
  15. Redwarp

    Redwarp Member

    Great...making progress thanks.
    Now have ispconfig CP playing the game.
    Now need to backtrack on FTP users.......again, feel this can be quickly fixed by changing the relevant ispconfig authentication technique back to msql_native_password and duplicating the password back to users table.....so I have a hashed password (not plain as I thought it might be) from theconfig.inc.php file. Can I just update the user record to bring those two fields back in line ?
     
  16. till

    till Super Moderator Staff Member ISPConfig Developer

    It's the plaintext password, not a hashed password.
     
  17. Redwarp

    Redwarp Member

    Hmm...I can only see this section regarding DB/password:
    $conf['db_database'] = 'dbispconfig';
    $conf['db_user'] = 'ispconfig';
    $conf['db_password'] = '59e95XXXXXXXXXXXXXXXX582fd'; (hidden obviously) , so that is hashed at that point.....can I find it elsewhere in plain ?
    (this is likely relevant.....
    define('ISPC_APP_VERSION', '3.1.15p3');
    )
    Many thanks
     
  18. till

    till Super Moderator Staff Member ISPConfig Developer

    That's the plain text password, yes. You can test it easily:

    mysql -h localhost -u ispconfig -p

    then enter the password and you are logged in.
     
  19. till

    till Super Moderator Staff Member ISPConfig Developer

    You run a really old and outdated ISPConfig version. It seems as if you missed updating ISPConfig to the latest version, which might explain your issues. ISPConfig must be updated with reconfigure services = yes when you upgrade the OS as the config files and settings must be adapted to the newly installed software versions. So you should do an ISPConfig update now by running:

    ispconfig_update.sh
     
  20. Redwarp

    Redwarp Member

    OK, yes you are correct, my mistake. So I can use cli to connect as ispconfig user, good. However, the user I needed to create on 127.0.0.1 has created with auth type caching_sha2_password
    1) Do I need to knock that back down to mysql_native_password ?
    2) Oddly....I created the required user, and the GRANTS appeared by themselves without me having to do it, that I found strange.
    3) Currently, I can see listed in ISP control panel, all my sites and all ftp users.....but none of the ftp users can login.....is this to do with the auth type being out of step per above ?
    OH...also....the user I created....I used the plain text password for root, so I am sure that is wrong. So maybe I have the ispconfig/localhost record "correct" with original password and ISPconfig logging in & listing but my NEW 'ispconig/127.0.0.1' record needs the password fixing ? (back as it should be to agree with the ispconfig.php file as above ?

    Thanks again
     

Share This Page