#1
  1. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,030
    Rep Power
    535

    Understanding MySQL usernames and privileges


    I've never really understood MySQL usernames and privileges, and figure it is time to learn.

    Okay, looks like I added 7 users. Most if not all were added when I copied line for line some tutorial, and never understood the implications.

    So, here a couple of questions that come to mind:
    1. Why do some have a host, yet not others? What does this mean?
    2. Why are some of the passwords fairly short, while two of them are much longer?
    3. I take it *.* is database.table, true?
    4. For a web application, should I typically just allow a user access to the database which is needed (i.e. not *.* but thisDatabase.*)?
    5. What if an application needed access to two databases? Will something like GRANT ALL PRIVILEGES ON db1.*, db2.*... work?
    6. Is GRANT USAGE ON *.* for beatbox redundant since I later add GRANT ALL PRIVILEGES?
    7. Should I typically grant less privileges?
    8. What other questions should I be asking?


    Thank you!

    Code:
    mysql> select Host,User from mysql.user;
    +-----------+-------------+
    | Host      | User        |
    +-----------+-------------+
    |           | beermaster  |
    |           | bluebox     |
    |           | remote      |
    |           | wordpress   |
    | localhost | beatbox     |
    | localhost | redmine     |
    | localhost | root        |
    +-----------+-------------+
    7 rows in set (0.00 sec)
    
    mysql> show grants for 'beermaster'@'';
    +------------------------------------------------------------------------------------------------------------+
    | Grants for beermaster@                                                                                     |
    +------------------------------------------------------------------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'beermaster'@'' IDENTIFIED BY PASSWORD '3c253fca5f9e1018' WITH GRANT OPTION |
    +------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> show grants for 'bluebox'@'';
    +------------------------------------------------------------------------------------------------------------+
    | Grants for bluebox@                                                                                     |
    +------------------------------------------------------------------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'bluebox'@'' IDENTIFIED BY PASSWORD '3c253fca5f9e1018' WITH GRANT OPTION |
    +------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> show grants for 'remote'@'';
    +--------------------------------------------------------------------------------------------------------+
    | Grants for remote@                                                                                     |
    +--------------------------------------------------------------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'remote'@'' IDENTIFIED BY PASSWORD '3c253fca5f9e1018' WITH GRANT OPTION |
    +--------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> show grants for 'wordpress'@'';
    +-----------------------------------------------------------------------------------------------------------+
    | Grants for wordpress@                                                                                     |
    +-----------------------------------------------------------------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'wordpress'@'' IDENTIFIED BY PASSWORD '3c253fca5f9e1018' WITH GRANT OPTION |
    +-----------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> show grants for 'beatbox'@'localhost';
    +--------------------------------------------------------------------------------------------------------------------+
    | Grants for beatbox@localhost                                                                                   |
    +--------------------------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'beatbox'@'localhost' IDENTIFIED BY PASSWORD '*7997HGK8E2F7F53037C6E6C1797249EDC8866E9B' |
    | GRANT ALL PRIVILEGES ON `beatbox`.* TO 'beatbox'@'localhost'                                               |
    +--------------------------------------------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)
    
    mysql> show grants for 'redmine'@'localhost';
    +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Grants for redmine@localhost                                                                                                                                                                                               |
    +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'redmine'@'localhost' IDENTIFIED BY PASSWORD '3c253fca5f9e1018' |
    | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `redmine`.* TO 'redmine'@'localhost' |
    +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)
    
    mysql> show grants for 'root'@'localhost';
    +----------------------------------------------------------------------------------------------------------------------------------------+
    | Grants for root@localhost                                                                                                              |
    +----------------------------------------------------------------------------------------------------------------------------------------+
    | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*7997HGK8E2F7F53037C6E6C1797249EDC8866E9B' WITH GRANT OPTION |
    +----------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql>
  2. #2
  3. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    1. I believe it means there are multiple hosts defined for those users, so they are stored in a separate table.
    2. I assume they are using different hashing algorithms.
    3. Yes
    4. Yes
    5. I don't know whether you can specify two DBs in a single grant statement, but you can run multiple grant statements to give the user permission to multiple DBs.
    6. No, because your grant all privileges command is only for one DB
    7. Grant the permissions you need
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,030
    Rep Power
    535
    Thanks E-Oreo!

    Me: Why do some have a host, yet not others? What does this mean?
    You: I believe it means there are multiple hosts defined for those users, so they are stored in a separate table.
    Me: Hosts being the server where MySQL is running? Where is this specified?

    Me: Why are some of the passwords fairly short, while two of them are much longer?
    You: I assume they are using different hashing algorithms.
    Me: I take it the application hashes the password?

    Me: What other questions should I be asking?
    You: NULL
    Me: What, you can't figure out what I want to ask?
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    you'll probably find this old-fashioned, and I guess it's no longer common amongst developers today, but you can answer pretty much every question yourself with a quick look into the official MySQL manual. That's where the MySQL people explain the inner workings of their database system.

    "What does the 'host' mean? Why do some users have a host and others not?"
    http://dev.mysql.com/doc/refman/5.6/...on-access.html

    "Are the passwords being hashed?"
    http://dev.mysql.com/doc/refman/5.6/en/create-user.html

    "Why do the hashes have different lengths?"
    http://dev.mysql.com/doc/refman/5.6/...d-hashing.html

    "Does xy work?"
    Try it!

    "Should I only grant the necessary privileges?"
    Well, if you wanna make your life more adventurous with interesting debugging sessions and attacks, you should grant the users all kinds of privileges. The more, the better. In any other case, you should probably keep all privileges at the minimum. Just like you do with your operating system, your web applications and any other software (hopefully).

    I understand that sometimes it's necessary to ask people who have more experience. But this kind of question can be answered with a 30 seconds Google search and some reading. At least that's what I did, and I'm no genius or something.
    Last edited by Jacques1; June 22nd, 2013 at 09:26 PM.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".

IMN logo majestic logo threadwatch logo seochat tools logo