[plug] MySQL for PostgreSQL users

[Daniel Pittman]

Tim Bowden <tim.bowden at westnet.com.au> writes:

> I've got to use MySQL instead of my preferred PostgreSQL (wordpress
> dependency.  Why oh why can't open source projects be database agnostic.
> We have an SQL standard for a reason), but I'm not quite understanding
> the MySQL user/security model yet.  I'm not looking to become a MySQL
> master (I'd rather play in the PostgreSQL sandpit) but could someone
> please take pity and check my steps or give me a succinct set of steps
> to create a user and db that can be accessed only from localhost that
> has no privileges beyond those required to manipulate the given db.
>
> So far:
> CREATE USER 'username'@'localhost' IDENTIFIED BY 'mysecret';
> CREATE DATABASE mydb;
> GRANT ALL ON mydb.* TO 'usrname'@'localhost';
>
> Q.  How does MySQL treat 'username' as opposed to 'username'@'localhost'
> or 'username'@'somehost'.  Are they all the same user just with
> different rights depending on where they connect from (which is what I
> think is happening), or are they completely different users?  What I've
> read has been a bit ambiguous here.

Those statements are, essentially, 'insert ... into mysql.users', where
the "primary key" of the users table is username and host.

So, user at localhost, user@% and user at somehost can all occupy different
rows in the users table.

When a connection happens MySQL searches for the "best" match, which is
'user at whatever', then 'user at wildcard', more or less.

Each row contains the rights that the individual user has, so each
record can contain different values.

Poke around in the 'mysql' database, which contains these tables, and
the sense should become a little more clear.

Regards,
        Daniel