MySQL security top wish list

August 29, 2013

Security seems to have no boundaries. I've been tightening our database security lately, and it seems like this could go on forever: from app to console to privileges to server, there are so many aspects to managing database security. Unfortunately, this is a field where MySQL is in particular weak, and with very little work done in the many years I've been working with MySQL.

My very own top-wanted security features for MySQL follows. Surely this is but a small subset, your mileage may vary.

Autherntication-only SSL

By default, MySQL client API is unencrypted and passwords are sent in cleartext. MySQL supports SSL, but it an "all or nothing" deal: if you want to use SSL, then everything goes by SSL: any query, SELECT, DDL and whatnot.

[UPDATE]: Thanks to Davi & Jan for correcting me on this: passwords are not sent via cleartext. I'm not sure by which constellation I saw cleartext passwords being sent -- but obviously that was long time ago. Just verified via tcpdump, got "mysql_native_password" message and no cleartext password. Lesson learned!

Roles

Need I elaborate? This is a fundamental construct in a database grant system. The effort of maintaining multiple accounts with similar/identical privileges is overwhelming. (PS I haven't used Securich to date)

Host aggregation

In MySQL the combination of user+host makes for a distinct account. Thus, 'gromit'@'192.168.%' is a completely different account than 'gromit'@'10.10.%'. I get the idea: you can have more privileges to, say, gromit@localhost than for gromit@'192.%'. In practice, this only makes a headache. In all my years, I have never encountered nor designed a privilege set where two accounts of the same user had different set of privileges. Never ever ever. It is confusing and pointless: if an account has a different set of roles, just call it by another name!

Had we had roles, that would be less painful; but my opinion is that an account should be identified by user only, not by user+host. The 'host' part should just indicate the whitelist of machines from which the user is allowed to connect.

Host blacklist

Speaking of whitelist, it would be great to have a host blacklist. If I wanted to grant access to 'gromit'@'192.168.%' except for '192.168.10.%' -- well, I would have to whitelist all the possible subnets. I can't exclude a set of hosts.

Catalogues

Another common construct not available in MySQL: a level above "schema" and below "server". The need for catalogues is in particular obvious when you want to grant some user SELECT access to all your schemas. Ahem, excluding, of course, the mysql schema... If I could create a "user catalogue", as opposed to "system catalogue", then I would have been able to GRANT SELECT ON CATALOGUE user.* TO my_user@localhost, and this would apply to all databases in that catalogue.

Privileges auditing

I've spent the last week or so restricting privileges to all accounts. This is hard work, because you want to make sure you're not revoking privileges which are required by the system (in which case I would either choose not to revoke, or create a new dedicated account with requested set of privileges). It would be so much fun if I could turn a flag on, like "SET GLOBAL audit_privileges := 1", and have a ++counter for every time a privilege check is made per account.

I guess we could go on... On a brighter note, I've been using the audit plugin interface by writing a login audit plugin with very good results (= good auditing & important insights); the (simple) code will be released shortly as open source; I'll write more on this at a later stage.

tags:
posted in MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

30 Comments to "MySQL security top wish list"

  1. Davi Arnaut wrote:

    > By default, MySQL client API is unencrypted and passwords are sent in cleartext.

    Are you sure about this? There is a cleartext authentication plugin, but it does not seem to be enabled by default.

  2. Jan Kneschke wrote:

    tl;dr MySQL does *not* send password in cleartext by default at auth-time.

    "mysql_native_password" is the default since MySQL 4.1 and it is a challenge/response protocol with double SHA1() to allow both sides to agree that the client knows the right password.

    "mysql_old_password" was used before, uses the same style, but a weak hashing function.

    MySQL 5.6 adds "sha256_password".

    Only if you explicitly tell MySQL to use "mysql_cleartext" (which is used for the PAM auth for example) the client will send the password in cleartext.

  3. shlomi wrote:

    @Davi, @Jan,

    I stand corrected, thank you!

  4. Peter Laursen wrote:

    "I have never encountered nor designed a privilege set where two accounts of the same user had different set of privileges. Never ever ever".

    I have! it is quite common on 'shared hosting' that a customer account only has access to use a specific username. Or rather it was. I have not used traditional 'shared hosting' for 5 years. It is largely becoming obsolete because of the developements in the Cloud. But what about RDS etc.?

  5. shlomi wrote:

    @Peter,

    "a customer account only has access to use a specific username" -- this does not impose a case where the customer would have multiple "host" versions with different set of privileges per such entry.

    I'm speaking of a case where you have gromit@198.% with X privileges, and gromit@10.0.% with Y privileges.

  6. Peter Laursen wrote:

    Actually I am speaking about exactly the same.

    I used some 'shared hosting' 6-8 years ago. I was given a (read: ONE and ONLY ONE) username.

    From a web-based 'control panel' I could allow access to MySQL from a number of remote hosts and define different privileges when connecting from different hosts.

    Even though what actually happened was tranparent to users/customers (as any direct access to the `mysql database` was denied), this means that more entries was added to the `user` table (with same 'user' but with different 'host').

    @Shlomi .. did you ever use 'shared hosting'? I think not, because I know you would hate not to have access to ececute anything at all from the command line and only have access to do what a web-based Control Panel application would let you do!

  7. Peter Laursen wrote:

    Try to create a number of cheap 'shared hosting' accounts from this list:
    http://www.whoishostingthis.com/compare/

    .. and you will find similar implementations (to the extend that they allow for connections from remote hosts at all, what they not all do).

  8. shlomi wrote:

    @Peter,

    This very site runs on shared hosting :) Thankfully I'm given SSH access, and I do (occasionally) command line my way into the database.

    The scenario you describe sounds terrible, I confess.
    I don't know that any remotely serious database should run on shared hosting.

  9. Joro wrote:

    Thanks for voicing this !

    Care to file a bug on deprecating the host name ? :) I'd be highly interested on how popular will it get to be.
    IMHO it's one of these charming mysqlisms that will probably never go away :)

    BTW, what you're asking for is a subset of what's currently implemented. Just never specify host names and it will work as you expect it to :)

  10. Joro wrote:

    Another question on authentication only SSL.

    What's the point ?

    It's common wisdom that the slowest part in an SSL connection is the connection establishment. Once this is done it's just some casual symetric hash calculation that should be a problem on modern hardware.

  11. shlomi wrote:

    @Joro,
    - I have no assumption that this (hostnames) will ever change... I realize no point here in filing a bug.

    - I'm not asking for a subset, since I don't want a gromit@% account. I want gromit@(192.168.10.%|10.0.0.%|12.34.56.78) -- so I don want explicit hostnames.

    - You can safely ignore the first section, about SSL. I kept it just for reference -- that I was wrong on this point.

  12. Joro wrote:

    @Shlomi.

    OK, so you had a separation of "user account" (a bearer of privileges) and a "login" (a user name + optional host combination) separation and allowing 1 user account to many logins.

    IMHO MySQL has that, but it needs some work :)

    How about the following scheme :
    1. You define all of your common privileges into "gromit@%"
    2. You alter "gromit@%" so that no one can't connect using this account (e.g. use a bad plugin)
    3. You define a number of "logins", e.g. gromit@192.168.%, gromit@10.10.%, etc that users can login with.

    And you end up with a white-list and a single set of privileges to maintain ;)

  13. shlomi wrote:

    @Joro,

    That's funny: upon reading your solution the word "roles" jumps to my mind ;)

    More seriously -- your newly created logins, i.e. gromit@192.168.%, gromit@10.10.% will not inherit any of "gromit@%" privileges -- since they're their own accounts -- so how does that work other than re-defining the set of privileges to each of these accounts?

  14. Joro wrote:

    Sure, roles is better. But on the scheme of mine : wanna bet that they'll get inherited ? :)

  15. shlomi wrote:

    Checking right away ... :)

  16. shlomi wrote:

    I think I should have taken the bet:

    mysql> grant process,create,replication client on *.* to 'shushu'@'%' identified by 'abcd';

    mysql> select user,host,password from mysql.user where user='shushu';
    +--------+------+-------------------------------------------+
    | user | host | password |
    +--------+------+-------------------------------------------+
    | shushu | % | *A154C52565E9E7F94BFC08A1FE702624ED8EFFDA |
    +--------+------+-------------------------------------------+

    mysql> -- blocking the user:
    mysql> set password for 'shushu'@'%'='A154C52565E9E7F94BFC08A1FE702624ED8EFFDA*';

    mysql> -- let's be on the safe side:
    mysql> flush privileges;
    mysql> create user 'shushu'@'127.0.0.%' identified by '12345';

    Now, from another console (using mysqlsandbox):

    bash$ ./use -h 127.0.0.1 -P 5532 -ushushu -p12345
    mysql> select current_user();
    +------------------+
    | current_user() |
    +------------------+
    | shushu@127.0.0.% |
    +------------------+

    mysql> show grants for current_user();
    +---------------------------------------------------------------------------------------------------------------+
    | Grants for shushu@127.0.0.% |
    +---------------------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'shushu'@'127.0.0.%' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9' |
    +---------------------------------------------------------------------------------------------------------------+

    mysql> show slave status;
    ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER,REPLICATION CLIENT privilege(s) for this operation

  17. Joro wrote:

    It doesn't work only for global privileges (see below). I guess we owe each other a drink ;)

    mysql> create user gromit@'%';
    --------------
    create user gromit@'%'
    --------------

    Query OK, 0 rows affected (0.00 sec)

    mysql> create database secret;
    --------------
    create database secret
    --------------

    Query OK, 1 row affected (0.00 sec)

    mysql> create user gromit@localhost;
    --------------
    create user gromit@localhost
    --------------

    Query OK, 0 rows affected (0.00 sec)

    mysql> exit;
    Bye

    d:\mysql\work\mysql-5.6\bld\mysql-test>mtcli -u gromit -h localhost
    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 3
    Server version: 5.6.15-debug-log Source distribution

    Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    mysql> create table secret.t1(a int);
    --------------
    create table secret.t1(a int)
    --------------

    ERROR 1142 (42000): CREATE command denied to user 'gromit'@'localhost' for table 't1'

    mysql> exit;
    Bye

    d:\mysql\work\mysql-5.6\bld\mysql-test>mtcli -u root
    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 4
    Server version: 5.6.15-debug-log Source distribution

    Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    mysql> grant all privileges on secret.* to gromit@'%';
    --------------
    grant all privileges on secret.* to gromit@'%'
    --------------

    Query OK, 0 rows affected (0.00 sec)

    mysql> exit;
    Bye

    d:\mysql\work\mysql-5.6\bld\mysql-test>mtcli -u gromit -h localhost
    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 5
    Server version: 5.6.15-debug-log Source distribution

    Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    mysql> create table secret.t1(a int);
    --------------
    create table secret.t1(a int)
    --------------

    Query OK, 0 rows affected (0.05 sec)

    mysql> show grants;
    --------------
    show grants
    --------------

    +--------------------------------------------+
    | Grants for gromit@localhost |
    +--------------------------------------------+
    | GRANT USAGE ON *.* TO 'gromit'@'localhost' |
    +--------------------------------------------+
    1 row in set (0.00 sec)

    mysql> select version();
    --------------
    select version()
    --------------

    +------------------+
    | version() |
    +------------------+
    | 5.6.15-debug-log |
    +------------------+
    1 row in set (0.00 sec)

    mysql> select user(), current_user();
    --------------
    select user(), current_user()
    --------------

    +------------------+------------------+
    | user() | current_user() |
    +------------------+------------------+
    | gromit@localhost | gromit@localhost |
    +------------------+------------------+
    1 row in set (0.00 sec)

    mysql>

  18. shlomi wrote:

    I certainly need a drink after this!

  19. James Day wrote:

    Shlomi, you probably use one of the most popular sites in the world that has different permissions for the same account: Wikipedia.

    There are two different systems there. The dominant single signon version has one account that is exclusive across all wikis. Permissions for the account are different on each wiki. You can be an ordinary user on one, administrator or various types of superuser on another. All from one login.

    The older system has completely different logins for each wiki even if the user name is the same. This allows a larger virtual namespace, so even though I could bar anyone else on any other wiki from using the same user name as me by switching to single signon, I choose not to.

    I agree with you partially: it would be good to have a list of login locations that a single account can use. That is probably the dominant reason for having more than one name/host pair at the moment. I also agree about roles, they would make life a lot easier.

    I don't fully agree that it is a bad idea to change permissions based on location. That's a convenience for the user, so they can use one user name/password and get the right permissions for their location. But it would be good to reduce this to only the cases where it is desired by making it easier to administer multiple locations with the same user name/password. I think but not particularly strongly that it is somewhat desirable to have the potential to use the same user name with different password for different locations/permissions, but think it'll mostly be undesired by most places.

    Views are my own, for an official Oracle opinion consult a PR person. But pay good attention to Joro: he's likely to be lead on any work involving permissions. :)

    James Day, MySQL Senior Principal Support Engineer, Oracle

  20. shlomi wrote:

    James,
    Thanks for this interesting use case.
    I guess I'm much more oriented to letting web apps manage control over database objects rather than mapping app users directly to database accounts.

    Most of your description relates to different privileges on different schemata. But on "so they can use one user name/password and get the right permissions for their location" I confess I still don't get it. It's not single sign on for different systems: this is a single sign on depending on whether I'm at the office, home, local datacenter or nearby subnet. And you get different privileges.

    I am happy to acknowledge I'm not familiar with all use cases. I can see some benefit in having more privileges connecting from local host as opposed to remote servers - for the DBA perhaps - and I don't see that databases should be too much topology oriented.

    My views are my own, too :)

  21. » Implementing a host blacklist with MySQL privileges Todd's MySQL Blog wrote:

    […] I saw Shlomi’s recent post which asked (in part) for blacklist support in MySQL, I started thinking about ways in which this […]

  22. Todd Farmer wrote:

    Hi Shlomi,

    FYI, one can implement host blacklists today:

    http://mysqlblog.fivefarmers.com/2013/08/30/implementing-a-host-blacklist-with-mysql-privileges/

    Be warned, though: it relies on the behavior that treats the same user as different accounts when the matching host differs. :)

    Todd

  23. James Day wrote:

    In theory we're all supposed to have an only my views disclaimer of similar form to the one I use, that references the correct place to go to for an official view. But it's possible that I'm the only one of us who has both read the rule and tries to follow it a bit. :)

    I probably wouldn't usually want to manage most users to user-specific database accounts and that isn't how Wikipedia does it. But I would want to split read, write and higher permissions by database account, so only the portions of the application code that need the permissions are vulnerable to attacks that might abuse the higher permissions.

    In general I agree about topology and I think that most places would be happy with one user name and multiple locations and would choose not to have the same user name being a different person or password when coming from different locations. But I can see that restriction being irritating in a multiple office large environment where it could end up blocking people from using preferred names because of naming conflicts with those in other offices. Probably not enough DBAs for it to matter in most businesses.

    I think you're doing a good job of describing the most common cases and wants. An interesting challenge for us is making that easy while also not blocking the others.

  24. shlomi wrote:

    Todd,

    This is very interesting! I'll need to test this with some authentication plugin (or write my own "reject plugin"). If this turns out to work well - it's a good enough solution for me!

    Thanks

  25. James Day wrote:

    Re "Unfortunately, this is a field where MySQL is in particular weak, and with very little work done in the many years I've been working with MySQL".

    I think we made that claim obsolete in 5.6. :) You might find it useful to read these things:

    http://www.mysqlperformanceblog.com/2013/08/17/mysql-5-6-security-vs-ease-of-use/

    Randomly assigned root password instead of blank, then forced to change that before you can use the account. Seems like quite an improvement.

    How about the password validation plugin, described at http://dev.mysql.com/doc/refman/5.6/en/validate-password-plugin.html ?

    Or perhaps the SHA-256 authentication plugin for more secure password hashing: http://dev.mysql.com/doc/refman/5.6/en/sha256-authentication-plugin.html ?

    And maybe the removal of passwords from logging: http://dev.mysql.com/doc/refman/5.6/en/password-logging.html ?

    There's more, of course. What we're trying to do is get it more secure by default.

    The greater focus on security mostly started to become visible in 5.6 though of course that means the work started a couple of years earlier. Easy enough to miss it if you didn't look at the security changes in 5.6.

    We do still have an education challenge, of course. One part of which is helping people like you to know what we're doing so you can pass the word on and get more people using the improved security capabilities.

    There's still more to do and Todd has been coming up with some really good more secured by default ideas that I hope to see in 5.7.

  26. shlomi wrote:

    @James & @Todd,

    Well done on moving things in security! I will humbly maintain my position that MySQL is at the moment still weak about security, without invalidating your hard & appreciated work (and I agree on dropping the "very little work done... part"). It's a thing that has been neglected for years, I think there's a lot yet to cover.
    Great that you are making progress: enforecement of (strong & strongly hashed) passwords is excellent. I will publicly cheer you up on further progress!

  27. James Day wrote:

    Thanks. I agree that there's still a lot to cover.

    More Todd and Joro's area than mine, though I have some input on various aspects of it and always keep my eyes open for issues or opportunities. And I try to inform and clarify.

    One good question to ponder from time to time is:"how can we protect the uninformed or unlucky from attacks or issues by default, without also making life unduly hard?"

  28. Peter (Stig) Edwards wrote:

    Hello Shlomi,
    If you didn't know already, Vicențiu Ciorbaru is working on a project to implement roles for MariaDB as part of GSoC 2013
    https://mariadb.atlassian.net/browse/MDEV-4397
    Mark Callaghan added a comment in MDEC-4397 pointing to https://code.google.com/p/google-mysql-tools/wiki/MysqlRoles
    http://cvicentiu.wordpress.com/
    https://code.launchpad.net/~cvicentiu/maria/gsoc

  29. shlomi wrote:

    Peter,
    I did not know - thanks for this info! Will take a look

  30. Todd Farmer wrote:

    In thinking about roles, I remembered that Workbench has some concept of "roles". Although it's managed outside the database rather than inside, I think it could prove useful for managing common permissions across many user accounts. In order to do so, though, WB needs to allow users to create custom "role" definitions:

    http://bugs.mysql.com/bug.php?id=70227

    If you think that would be useful to you, be sure to let the WB team know.

Leave Your Comment

 
Powered by Wordpress and MySQL. Theme by openark.org