4

How do I create a user and specify a password in one line:

I thought CREATE USER 'foo'@'2.2.2.2' IDENTIFIED BY 'bar'; would do it but apparently it doesn't.

Any thoughts? I'm on version 5.1.49 of MySQL.

1
  • I am curious what you mean "apparently it doesn't"...your command should create the user (with only the ability to log on from 2.2.2.2 and no other privileges)...so why do you say it doesn't create it? Jan 7, 2013 at 22:22

2 Answers 2

1

Your syntax is the good one :

[root@localhost] [(none)] mysql> CREATE USER 'foo'@'2.2.2.2' IDENTIFIED BY 'bar';
Query OK, 0 rows affected (0.00 sec)

You can see if your user is created by using :

[root@localhost] [(none)] mysql> show grants for 'foo'@'2.2.2.2';
+----------------------------------------------------------------------------------------------------------+
| Grants for [email protected]                                                                                   |
+----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'foo'@'2.2.2.2' IDENTIFIED BY PASSWORD '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB' |
+----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

But it'll work only if you connect from 2.2.2.2.

Max.

10
  • 1
    you ... really didn't answer the question.
    – jcolebrand
    Jan 5, 2013 at 5:27
  • 1
    Oh really? Sorry about that! He asked for how to create a user with a password, i did. Next i gave my opinion on his 2.2.2.2 and finaly i asked him his exact problem... Jan 5, 2013 at 9:04
  • 2
    Ok, let's try again. The question states that he tried that, and it didn't let him use the password that he provided. Therefore, he's asking why it didn't work. It's entirely possible that he actually supplied 2.2.2.2, but I bet that's shorthand-example, just as foo and bar are. Maybe what you meant to do was to leave a comment on the question asking what the exact error is?
    – jcolebrand
    Jan 5, 2013 at 9:16
  • 1
    Looking at this, I can say you are pretty close. However, there is another method. Your command works but is functionally incomplete. The user can login after the user's creation but that's all. Your SHOW GRANTS reveals that. Since mysql's default sql_mode allows non-ANSI stuff, you could pull it off the GRANT command. That will do three things: 1) create the user, 2) set the password, 3) set the privs IN ONE COMMAND. Check the MySQL Doc dev.mysql.com/doc/refman/5.1/en/grant.html . It says if NO_AUTO_CREATE_USER is set, two commands needed. Otherwise, the one GRANT can do it. Jan 6, 2013 at 5:02
  • 2
    I am going to give you a +1 for what you contributed because doing this in one command is not widely known unless one is fully aware of MySQL's non-ANSI shortcuts and caveats. As written, you answer does answer the question as posed in its context. Jan 6, 2013 at 5:07
1

For the change is applied immidiately, you must use flush privileges to flush all memory cache of user table. Finally, you should make sure your client is 2.2.2.2

2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.