MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

MySQL security risk?

with 15 comments

Somebody asked me why you must provide the IDENTIFIED BY password clause on a grant after you’ve already created the user. This is a very good question, I wondered it myself a while back. This blog provides the what, why, and how of it.

The reason you typically must provide the IDENTIFIED BY password clause is that typically you’ve enabled the NO_AUTO_CREATE_USER value in your sql_mode. That parameter is there by default from MySQL 5.0.2 forward. It’s what requires you to use the IDENTIFIED BY password clause.

The parameter enforces a match against user, host, and password columns in the user table of the mysql database. If you disable it, the match is on user and host columns in the user table. If you make a grant to a user without specifying a host and a wildcard (%) host doesn’t exist for that user, the grant will insert a duplicate user. The same user name with a wildcard host trumps the one with a localhost host value for access privileges, which means that user no longer needs a password to gain entry to the database.

The following steps will show and explain what happens in MySQL’s security model.

1. Sign on as the root user, then create a new myuser user:

mysql> CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'myuser'@'localhost';

If you attempt to grant permissions to the myuser without a password value, like this

mysql> GRANT ALL ON sampledb.* TO 'myuser'@'localhost';

You should see an outcome like the following. At least, you will when the NO_AUTO_CREATE_USER value is set in your sql_mode variable.

ERROR 1133 (42000): Can't find any matching row in the user table

If it let you set it, then the NO_AUTO_CREATE_USER value isn’t set. You can check what’s set with this query:

mysql> SELECT @@sql_mode;

You should see this:

+----------------------------------------------------------------+
| @@sql_mode                                                     |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+

2. Reset the sql_mode to disable the NO_AUTO_CREATE_USER value, like this (presuming you’ve got the generic MySQL 5.1 settings):

mysql> SET sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

3. After disabling the restriction, you can grant privileges on a sampledb to the myuser user without the IDENTIFIED BY password clause. You’d use the following syntax:

mysql> GRANT ALL ON sampledb.* TO 'myuser'@'localhost';

Before you sign off and sign on to test your new user, you should now query the USER table. Here’s the query to focus on the three important columns:

mysql> SELECT USER, host, password
    -> FROM USER
    -> WHERE USER = 'myuser';

If you return more than one row, you made an error and most likely left off the @'localhost' or used @'%'. If you made that mistake, you’ll find that disabling the NO_AUTO_CREATE_USER value, can yield multiple entries in the user table for the same user. Worse yet, the user no longer can sign in with their password.

+--------+-----------+-------------------------------------------+
| USER   | host      | password                                  |
+--------+-----------+-------------------------------------------+
| myuser | %         |                                           |
| myuser | localhost | *CBA73BBE5D9AF59311C3F4D7E8C20AA847F7B188 |
+--------+-----------+-------------------------------------------+

If you were to sign off and attempt to sign on with the myuser account password, you’d get the following error message:

ERROR 1045 (28000): Access denied FOR USER 'myuser'@'localhost' (USING password: YES)

You can now sign on as the myuser user only without a password. Fixing it is simple, just drop the user for any host:

mysql> DROP USER 'myuser'@'%';

While disabling the sql_mode parameters makes your job of granting privileges easier, it risks compromising your database. Therefore, avoid it or be careful. If I missed something or got it wrong, let me know.

Written by maclochlainn

June 18th, 2009 at 5:47 pm

Posted in LAMP,MAMP,MySQL,sql

15 Responses to 'MySQL security risk?'

Subscribe to comments with RSS or TrackBack to 'MySQL security risk?'.

  1. Hi ,
    I have a single user as this :

    +---------+-----------+----------+
    | USER    | Host      | password |
    +---------+-----------+----------+
    | prakash | localhost |          |
    +---------+-----------+----------+

    So when I am trying to login thorough the user ‘prakash’ without giving pasword, it showing this error

    ERROR 1045 (28000): Access denied for user ‘prakash’@’localhost’ (using password: YES)

    what does it mean .

    Prakash mohanty

    8 Apr 10 at 4:01 am

  2. It means that a password is required for the user. When you want to be prompted for your password and avoid disclosing it. You should use the following syntax:

    mysql -usomename -p

    maclochlainn

    8 Apr 10 at 9:40 pm

  3. I think you’ll find the example you’re looking for in this other blog post of mine.

    maclochlainn

    8 Apr 10 at 9:42 pm

  4. But question is that when I created the user I havn’t assign any password. Is it necessary to assign a password first ?? .

    My Actual problem is that , I want to login MySQL from OS user authnetication inside my script. That means it will take OS login name (Both in windows and Linux) and pass that user to mysql without password asking.

    Prakash mohanty

    8 Apr 10 at 11:51 pm

  5. Can I access MySQL db from the OS credentials. I mean by taking OS user name and without assigning any password.

    Thanks in advance

    Prakash mohanty

    12 Apr 10 at 4:21 am

  6. How I will login to mysql in root account without giving password ?

    Prakash mohanty

    12 Apr 10 at 6:02 am

  7. Yes, you can do that. It’s not the most secure way to perform tasks because there isn’t any way to synchronize between MySQL and the Operating System unless you write something as an extension.

    Here’s one way to do what you’d like, but there’s a security risk to do this:

    1. As the root user, create a user that has the same name as an operating system user but restrict access as localhost (at least).

    mysql> CREATE USER 'hacker'@'localhost';
    mysql> GRANT SELECT ON sampledb.* TO 'hacker'@'localhost';

    2. You can now connect without a password or user name by leveraging the environment variable. Here’s the Windows example:

    C:\> mysql -u%USERNAME%

    You can also create the anonymous account and try to restrict it through procedures that validate the result of the user() function. The current_user() function returns a ''@'localhost', while the would return the 'username'@'localhost' when the real username isn’t defined. The following shows that:

    mysql> SELECT CURRENT_USER(), USER();
    +----------------+---------------------------+
    | CURRENT_USER() | USER()                    |
    +----------------+---------------------------+
    | @localhost     | McLaughlin-7x64@localhost |
    +----------------+---------------------------+

    As information, I’m not sure it’s harmful to explain but I’m quite sure it’s ill advised. Do this at your own risk of compromising your database’s security.

    maclochlainn

    12 Apr 10 at 8:07 pm

  8. Hi I am doing this for my local server moniter, So no harm on this. But I am still finding the error. I tried this :
    mysql> create user ‘C5136731I’@’10.52.6.138′ ;
    Query OK, 0 rows affected (0.00 sec)
    mysql> grant select on mysql.* to ‘C5136731I’@’10.52.6.138′;
    Query OK, 0 rows affected (0.00 sec).

    C:\Documents and Settings\C5136731>mysql -u%USERNAME%;
    ERROR 1045 (28000): Access denied for user ‘C5136731;’@’localhost’ (using password: YES).

    The same error also for local host .

    Prakash mohanty

    12 Apr 10 at 11:58 pm

  9. I’ve a hunch that you’ve not set up your hosts file. It’s found in the following location on Windows:

    C:\Windows\System32\Drivers\etc\hosts

    You probably have the first line, but you’ll need to add the second line. The .com changes on your domain resolution. Then, you need to explicitly grant to 'C5136731l'@'localhost'. The recognized values are '', 'localhost', and '%'.

    127.0.0.1      localhost
    172.16.153.87  C5136731l C5136731l.domain.com

    Hope this helps.

    maclochlainn

    13 Apr 10 at 9:32 pm

  10. Hi, As above (mysql -u%USERNAME%;) I tried its working from command line, If I am trying inside my perl script its not working.
    My script looks like this :
    my $dbh = DBI->connect(“DBI:mysql:mysql:localhost”,”$login_user”);

    where as $login_user contains the value of OS user.

    I am getting error like this :
    DBI connect(‘mysql:localhost’,’c5136731′,…) failed: Access denied for user ‘c5136731’@’localhost’ (using password: NO)

    Prakash mohanty

    15 Apr 10 at 2:49 am

  11. I’ll try to post something for you this weekend.

    maclochlainn

    15 Apr 10 at 7:17 am

  12. I am giving some brief idea what I am going to do with mysql.

    I am developing a script (perl module), to moniter MySQL database. What the info. I want to grap.

    get the login credentials from OS authnetication (OS user will be able to access the DB, without password.). execute mysql and mysqladmin client commands through that user and collect info, Like :
    1) number of users
    2) datafile status , log file status. and all

    Thanks in advance

    Prakash mohanty

    16 Apr 10 at 5:31 am

  13. Prakash,

    I’ve no problem running this from the command-line, with the following grants. If you’re calling it through Apache, change the localhost to %.

    CREATE USER 'hacker1'@'localhost';
    GRANT SELECT ON sampledb.* TO 'hacker1'@'localhost';

    Perl Program is:

    #!/strawberry/perl/bin -w
    use strict;
    use DBI;
     
    # Create a connection or report failure.
    my $db = DBI->connect("DBI:mysql:sampledb:localhost","hacker1","")
       or die "Could not connect to database: ". DBI->;errstr;
     
    # Create a SQL statement variable.   
    my $sql = "SELECT current_user, item_rating FROM item";
     
    # Prepare the SQL statement.
    my $stmt = $db->prepare($sql);
     
    # Execute the statement.
    $stmt->execute();
     
    # Print while data is found.
    while (my($title, $rating) = $stmt->fetchrow_array())
      { print "$title".", "."$rating\n"; }

    maclochlainn

    18 Apr 10 at 9:30 am

  14. Hi macl,

    Thanks for the help. I have one question that : after logging in as root when I am changing to mysql db, its showing error, as below:

    C:\prakash\playstation\java> mysql -uroot -p
    Enter password: *******
    mysql> \u mysql ;
    ERROR 1044 (42000): Access denied FOR USER 'root'@'localhost' TO DATABASE 'mysql'

    Prakash mohanty

    20 Apr 10 at 12:58 am

  15. Hi Mac,

    I have small doubt about MySQL log file. I want to get information about the log-file. I mean, like how large is the log file, is it auto extensible or not?? Can you help me please.

    prakash mohanty

    12 May 10 at 11:45 pm

Leave a Reply