SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Member
    Join Date
    Sep 2013
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to create an account in MySQL?

    Good afternoon, ladies and gentlemen!


    My problem is that I can not to create an account in MySQL. I use to create an account the following code:
    CREATE USER [username] IDENTIFIED BY PASSWORD 'password';,
    but MySQL shows the following: ERROR 1372 (HY000); Password hash should be 41-digit hexadecimal number.
    Please, help me!

  2. #2
    Barefoot on the Moon! silver trophy Force Flow's Avatar
    Join Date
    Jul 2003
    Location
    Northeastern USA
    Posts
    4,606
    Mentioned
    56 Post(s)
    Tagged
    1 Thread(s)
    If you use IDENTIFIED BY PASSWORD 'password', that signifies that you will be using a hashed hexidecimal value as the password.

    If you use IDENTIFIED BY 'password', that signifies that you will be using a plain text password.

    http://dev.mysql.com/doc/refman/5.1/en/create-user.html
    Visit The Blog | Follow On Twitter
    301tool 1.1.5 - URL redirector & shortener (PHP/MySQL)
    Can be hosted on and utilize your own domain

  3. #3
    SitePoint Member
    Join Date
    Sep 2013
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Good aftetnoon, Mr Force Flow!

    I read the article from your link, but I would like to ask you something else. I understand that the option "IDENTIFIED BY PASSWORD 'password'" is better because it is more secure than option "IDENTIFIED BY 'password'" - is this correct? Still I have some questions: 1) I should point out to the host when creating an account, for example, "localhost" or not? 2) I should specify the privileges of the user using the operator "GRANT" or not? If Yes, what should be specifically specify to fully use MySQL: edit and much more (have the right to full administration site)? Thanks in advance for your help!

  4. #4
    SitePoint Member
    Join Date
    Sep 2013
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Please help me someone to understand my previous questions on this topic! Thanks in advance for your help!

  5. #5
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,014
    Mentioned
    187 Post(s)
    Tagged
    2 Thread(s)
    Sorry, it's a Holiday and I'm not quite on the same page yet. Maybe this will help
    CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']
    [, user [IDENTIFIED BY [PASSWORD] 'password']] ...
    ......
    The CREATE USER statement was added in MySQL 5.0.2. This statement creates new MySQL accounts. To use it, you must have the global CREATE USER privilege or the INSERT privilege for the mysql database.
    ......
    The account can be given a password with the optional IDENTIFIED BY clause. The user value and the password are given the same way as for the GRANT statement. In particular, to specify the password in plain text, omit the PASSWORD keyword. To specify the password as the hashed value as returned by the PASSWORD() function, include the PASSWORD keyword.
    So first questionn, you have the global CREATE USER privilege?

  6. #6
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,058
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Daria Alexeeva View Post
    Good aftetnoon, Mr Force Flow!

    I read the article from your link, but I would like to ask you something else. I understand that the option "IDENTIFIED BY PASSWORD 'password'" is better because it is more secure than option "IDENTIFIED BY 'password'" - is this correct?
    Yes, BUT (and I haven't fully tested this, so please notify me if it errors), you should be able to do this:
    Code MySQL:
    CREATE USER [username] IDENTIFIED BY PASSWORD PASSWORD('password');

    Worse case, you just need to perform it in two or more steps.
    Code MySQL:
    SELECT PASSWORD('password');

    Copy the output of the above and then use that hash in your CREATE USER statement
    Code MySQL:
    CREATE USER [username] IDENTIFIED BY PASSWORD 'password';

    Granted, both are stored securely within MySQL the only concern is if mysql_history is going to log the query and thus have the password in plain text. If you have your new user change their password immediately, then this becomes a moot point as the history will no longer refer to the new updated password.

    Quote Originally Posted by Daria Alexeeva View Post
    Still I have some questions: 1) I should point out to the host when creating an account, for example, "localhost" or not?
    No, they shouldn't care. If they gave you the privileges for creating an account, then you have the right to use them. If you were denied those privileges and needed them, then you'd want to contact your host.

    Quote Originally Posted by Daria Alexeeva View Post
    2) I should specify the privileges of the user using the operator "GRANT" or not? If Yes, what should be specifically specify to fully use MySQL: edit and much more (have the right to full administration site)? Thanks in advance for your help!
    If the user is to have Full Admin rights, then you'd GRANT ALL PRIVILEGES, however, if the user does not need full administration, only grant what they need.
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  7. #7
    SitePoint Member
    Join Date
    Sep 2013
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you very much, Mitti neague and cpradio for your help!

    But I would like something clarified. You see I am a beginner programmer. I have created a website using HTML and CSS. I installed a local server, MySQL and PHP. PHP and MySQL I need to create a registration system for users and baskets buyers on my website. I have a book on MySQL, but lacking the precision of instructions how to create a full account and full administrator rights with all privileges. I connect to MySQL while only using the [mysql-u root], so I do not have the global CREATE USER privilege, and the like. I will appreciate if You tell me step-by-step instructions on creating a proper and full account in MySQL.

  8. #8
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,058
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    root should have full privileges (including create user).

    So if you want to create a new account with similar privileges, run your CREATE USER statement, then grant the privileges:
    Code MySQL:
    GRANT ALL ON database.table TO 'username'@'connection_name';

    Okay, so the above seems confusing, and it is on some level, which is why I typically recommend using phpMyAdmin. That makes it much easier as it is GUI based. However, it isn't too difficult to figure out.

    So the first part, "database.table": You can specify which database and a specific table to grant permissions to for a given account. You can also specify ALL tables within a database using database.*, or you can specify ALL databases and ALL tables using *.*

    The latter part, "'username'@'connection_name'". You can specify which user is getting these privileges and what privileges they get based on how they connected to the server. The username is self explanatory, put the new account name there. The connection name is a bit trickier, but you can think of it this way: There are a variety of connection strings you can use to connect to MySQL, you can use "localhost", or 127.0.0.1, or the physical IP of your server 124.35.43.12, or even the host name of the server hosting your MySQL mysql.mydomain.com, or % to denote all possible ways of connecting.

    You can grant specific privileges based on which of those connection strings the user uses. So for example, if you are hosting your own web application and MySQL and Apache are installed on the same server, I'd personally start with "localhost", so you'd grant the permission to username@Localhost ; however, future proofing would incline me to tell you to setup the DNS entry for mysql.mydomain.com and use that. This way, if you ever move MySQL to a different server, your code doesn't change (as it is already using that connection string).

    So why does MySQL let you assign privileges based on a connection? I'm not sure this is the correct answer, but I imagine it is from a security perspective. In short, I can give greater privileges to those using that account within my network, as I can define a local IP or localhost and thus detect they are working inside my network. I can then give out less privileges to those using mysql.mydomain.com or the external IP 124.35.43.12 so if the user gets compromised, it can't take down the database/server.

    Personally, I think this is a crappy implementation. I would never personally use the same username/credentials for an external application and an internal application. They would either 1) go through a RESTful service or 2) use different accounts. I just don't see the usefulness in MySQL working this way with its privileges...

    I know a lot of that probably went over your head (I apologize for that -- so feel free to ask questions if I just made you concerned with anything else), but hopefully this gave you some insight to the privilege system of MySQL.
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  9. #9
    SitePoint Member
    Join Date
    Sep 2013
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Tell me, please, I correctly described the following 4 steps to create an account in MySQL or not:
    1) Create the CREATE USER 'user'@'localhost'; 2) Create a database; 3) Create a table of data; 4) then create privileges using GRANT ALL ON *.* 'user'@'localhost'.

    At the moment I have no personal account information databases and data tables in MySQL.

    Thanks in advance.

  10. #10
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,058
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Yes, those are the steps. Be careful of you fourth step as you missed the keyword TO between *.* and 'user'@'localhost'
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  11. #11
    SitePoint Member
    Join Date
    Sep 2013
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Please explain how to record the password in the hexadecimal system during account creation using the CREATE USER. Or the system issues a password, which then must be entered to log in? And if You were told how to actually work with passwords in MySQL.


    Thanks in advance.
    Last edited by cpradio; Dec 26, 2013 at 09:36.

  12. #12
    SitePoint Member
    Join Date
    Sep 2013
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry for the fingerprint in the word "explain" in the previous post .

  13. #13
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,058
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Daria Alexeeva View Post
    Please explain how to record the password in the hexadecimal system during account creation using the CREATE USER.
    I'd use the following approach:
    Code MySQL:
    SELECT PASSWORD('password i want to use');

    That should produce an output showing the hexadecimal value, take that output, and use it in your CREATE USER statement.

    Quote Originally Posted by Daria Alexeeva View Post
    Or the system issues a password, which then must be entered to log in? And if You were told how to actually work with passwords in MySQL.
    I'm not sure you can have the system generate a password, and this is just how I'd do it. Wasn't taught one way or another, but in all honesty, you are putting to much emphasis on this. Sure using the hexadecimal is more secure, however, for it to be a "real" issue, your server must be compromised to where the attacker has access to your logs. That means they have full (or at least significant) access to the machine and that is a much bigger issue than your MySQL password.
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  14. #14
    SitePoint Member
    Join Date
    Sep 2013
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for reply!

    So, to create your account should be written down so: CREATE USER 'user'@'localhost' without IDENTIFIED BY 'password' and then after that write SELECT PASSWORD ('password i want to use')? Between these two statements CREATE USER and SELECT PASSWORD need to press the ENTER button or not? I correctly written summary to create an account? Correct me please if I don't understand! And how to properly solve the issue of security in MySQL? So the password is not necessary to use the hexadecimal system?

    Thanks in advance.

  15. #15
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,058
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Daria Alexeeva View Post
    So, to create your account should be written down so: CREATE USER 'user'@'localhost' without IDENTIFIED BY 'password' and then after that write SELECT PASSWORD ('password i want to use')? Between these two statements CREATE USER and SELECT PASSWORD need to press the ENTER button or not? I correctly written summary to create an account?
    No. If you want to use the hexadecimal format, run the SELECT statement first. Yes, press enter, as you need it to execute. Copy the output (should give you a hexadecimal value).

    Next write the CREATE USER statement with the IDENTIFIED BY PASSWORD 'paste hexadecimal value from the select statement here', press enter to execute it, then run a GRANT ALL ... for your new user.

    Quote Originally Posted by Daria Alexeeva View Post
    And how to properly solve the issue of security in MySQL?
    Don't let your server get compromised. The issue is, if someone has access to the logs, they may be able to view the password created for that user. Prevent the server from being breached and limit who has access to the physical server and the password is secure (without using the hexadecimal system).

    Quote Originally Posted by Daria Alexeeva View Post
    So the password is not necessary to use the hexadecimal system?
    Correct. You technically don't need to use the hexadecimal version for creating a user. The whole issue is the password gets stored in the mysql history log. If you keep your server secure, or wipe out that file after creating your user, you have nothing to worry about. Quite frankly, if you server is breached, if you have worse things to worry about than a mysql password.
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •