CODEWITHSUNDEEP
mysql
Bruce P
Bruce P

Reputation: 20759

Adding new host entries to existing mysql users

I have a fairly well defined list of 50 or so users in a mysql database. All their entires in the user table look something like this:

mysql> select user,host,password from user where host = '1.2.3.4';
+--------+---------+----------+
| user   | host    | password |
+--------+---------+----------+
| user1  | 1.2.3.4 | *XXX...  |
| user2  | 1.2.3.4 | *YYY...  |
| user3  | 1.2.3.4 | *ZZZ...  |
etc.

What I would like to do is add a second entry for each user so that they can also connect from 10.% using the same password and granting full access from 10.%, but leaving the existing grants in place.

Is there a way I could do this all from within mysql (for automation purposes) or would I have to write a script that dumps the users & password hashes out then issues new grant statements for the 10.% host?

Upvotes: 10

Views: 19448

Answers (2)

Ahsan.Amin
Ahsan.Amin

Reputation: 784

If you already know the credentials of the user you can also do this.

CREATE USER 'some_user_name'@`some_new_host_or_ip` IDENTIFIED BY 'existing_password';

FLUSH PRIVILEGES;

By the way, as mentioned above post, I couldn't find the password field, not sure if it is relevant to the MySQL version. in my case, it was like below. So just if this thing can help others

insert into mysql.user (user,host,authentication_string)
    select user,'some_new_host_or_ip',authentication_string 
    from mysql.user where user = 'some_user_name' limit 1

FLUSH PRIVILEGES;

Upvotes: 0

Linger
Linger

Reputation: 15058

INSERT INTO user (user, host, password)
SELECT U.user, '10.x' AS NHost, U.password FROM user AS U

Upvotes: 7

Related Questions