[plug] MySQL for PostgreSQL users

[Tim Bowden]

Thanks Daniel, that's clarified things for me.

Regards,
Tim
On Wed, 2008-09-17 at 13:50 +1000, Daniel Pittman wrote:
> 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
> _______________________________________________
> PLUG discussion list: plug at plug.org.au
> http://www.plug.org.au/mailman/listinfo/plug
> Committee e-mail: committee at plug.linux.org.au
-- 
Experience is that marvelous thing that enables you recognize a mistake
when you make it again.