C4f1151075b447779af31e99d6cf70e2c6eb47ac
By:
newbie

change mysql user & pass

June 7, 2017 680 views
MySQL Ubuntu 16.04

i have a remote database setup.
and my database is connected with my web server through private network.

so i created 2 users with following command

GRANT ALL ON wordpress.* TO 'wordpressuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON wordpress.* TO 'wordpressuser'@'Private_IP' IDENTIFIED BY 'password';

now i need to change password, does the following command is enough to change? or i need to take any extra step to ensure previous access is revoked.

SET PASSWORD FOR 'wordpressuser'@'localhost' = PASSWORD('new-password');
SET PASSWORD FOR 'wordpressuser'@'Private_IP' = PASSWORD('new-password');
3 Answers

@newbie

That is enough, but this query will give you a warning and if you read it you see that you do not need to use the PASSWPRD function and instead use plain text password, like this:

SET PASSWORD FOR 'wordpressuser'@'Private_IP' = 'new-password';

  • @Mohsen47 It depends on the MySQL/MariaDB version. The latest versions actually recommends this instead:

    ALTER USER 'wordpressuser'@'Private_IP' IDENTIFIED BY 'new-password';
    

Hi @newbie
Since it's WordPress, meaning PHP, it doesn't have an idle connection kept alive, so every time it shows a page, it actually does a connection to the database and authenticates every time.
So that means you're all good - no reason to restart the database or anything like that.
To do a test, you can simply change the MySQL password to something different and WordPress will give you a database error instantly on reload of a page.

  • @Mohsen47 @hansen
    thanks both of you for helping me out.

    i need to know another thing, when i setup my site, i granted full permission to my mysql user that connect through private ip using

    GRANT ALL PRIVILEGES ON wordpress.* TO 'wordpressuser'@'web_server_ip';
    

    but after setting it up, i gave selective privilege to same user using

    GRANT SELECT,DELETE,INSERT,UPDATE ON wordpress.* TO 'wordpressuser'@'web_server_ip';
    

    but the thing is, from wordfence, i found following thing from diagnostic

    MySQL Status
    Checking if MySQL user has DELETE privilege OK
    Checking if MySQL user has INSERT privilege OK
    Checking if MySQL user has UPDATE privilege OK
    Checking if MySQL user has SELECT privilege OK
    Checking if MySQL user has CREATE TABLE privilege OK
    Checking if MySQL user has ALTER TABLE privilege OK
    Checking if MySQL user has DROP privilege OK
    Checking if MySQL user has TRUNCATE privilege OK

    so DROP, TRUNCATE etc privileges are still in effect. but im not sure wordfence is referring those permission for localhost user or remote user from webserverip

    do you guy's have any idea on this?

    thanks in advance.

    • @newbie

      You need to revoke privileges, or only create the user with these:

      GRANT SELECT,DELETE,INSERT,UPDATE ON wordpress.* TO 'wordpressuser'@'web_server_ip';
      

      And I think you need to reload privileges after revoking - FLUSH PRIVILEGES;.
      Meaning, don't use GRANT ALL PRIVILEGES ... if you only want the user to have certain privileges.

      If the webserver is one droplet and the database is another, then WordFence is using the database connection configured in WordPress, so it would never use the "localhost", but the "webserverip".

      • @newbie

        You can also check the current privileges granted to a user using this query:

        SHOW GRANTS FOR 'user'@'host';

        to make sure what are the privileges assigned to the user.

        • @Mohsen47 @hansen
          thanks both of you. you are correct. my user has both permission.

          mysql> SHOW GRANTS FOR 'user'@'Private_IP';
          +-------------------------------------------------------------------------------------+
          | Grants for user@Private_IP                                                 |
          +-------------------------------------------------------------------------------------+
          | GRANT USAGE ON *.* TO 'user'@'Private_IP'                                  |
          | GRANT ALL PRIVILEGES ON `db1`.* TO 'user'@'Private_IP'                 |
          | GRANT ALL PRIVILEGES ON `db2`.* TO 'user'@'Private_IP'                   |
          | GRANT SELECT, INSERT, UPDATE, DELETE ON `db3`.* TO 'user'@'Private_IP' |
          +-------------------------------------------------------------------------------------+
          4 rows in set (0.00 sec)
          

          how can i revoke GRANT ALL PRIVILEGES from db1 & db2?

          also i did tried with following command, but nothing changes.

          GRANT SELECT,DELETE,INSERT,UPDATE ON db1.* TO 'user'@'Private_IP';
          

          thanks in advance.

          • Can't remember how to revoke without revoking everything:

            REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'Private_IP';
            

            And then create the GRANT you want.

          • @hansen is right.

            You might need FLUSH PRIVILEGES; after that.

@Mohsen47 @hansen

thank you guy's

that worked but it would be very nice if i could revoke ALL PRIVILEGES from a single database.

  • @newbie

    You can revoke all privileges from a single database, use this query:

    REVOKE ALL PRIVILEGES ON 'databasename'.* FROM 'user'@'host';

Have another answer? Share your knowledge.