Hi,

I come from the Postgres world where one user has one password[1] and
pg_hba.conf decides which hosts that user may connect from.

[1] OK, it can get funky - but for the sake of argument, let's assume so...

I'm not a MySQL buff but I have inherited a few:

*Seems* to be the case that a password belongs to the pattern
"user@somewhere" rather than just "user".

Correct me if I'm wrong - but when I did some extra grants to allo a user to
connect from some extra client hosts, the new user was passwordless(!) (the
original user@somblah did require a password.

If I've got the right end of the stick, how do I grant access to a user to a
database (and tables etc etc) without having to specify a password - ie to
have the system use only one password?

I did RTFM but as I haven't found a tome "MySQL for PostgreSQL admins" I
haven't seen a really clear explanation of how MySQL authentication works.

Any comments received with thanks!

Cheers

Tim
--
Tim Watts

On 4/17/2011 3:24 PM, Tim Watts wrote:
You don't. If you want them to have to use a password, you need to
specify the password in the grant for that host.

MySQL is not PostGres and it's authentication works differently. And
DB2, Oracle and SQL Server each work differently, also.

Now, with that said, you *could* just insert the row into the
mysql.users table, taking the password from an existing row for that
user. But you have to be very careful and know what you're doing when
dealing with the mysql.xxx tables - you can easily leave yourself with
an non-working system.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

Jerry Stuckle wrote:

Hi Jerry,

Right - so I was right in thinking that was how it was behaving...

Yep.

I think I will have to do that - as a sysadmin, I don't want to be calling
the user for their password everytime this happens (often). Sounds like a
handy stored procedure could be written to do this - I'll look into that.

Longer term, I'll probably rationalise the whole thing so accounts have
access from entire sub-networks instead of host by host.

Pity they didn't have a nice option for "dup the password" on grant

Thanks for your help.

Cheers,

Tim

--
Tim Watts

On 4/18/2011 2:21 AM, Tim Watts wrote:
They don't have a "dup the password" because someone coming from a
different host is a different user, even if they have the same userid.

Allowing access from the entire subnet sounds like a good idea to me.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

Tim Watts <tw (AT) dionic (DOT) net> wrote:

In MySQL a user specification always has the form username (AT) hostname (DOT)
Both parts can contain wildcards. And the host part can also make use
of numerical ip address ranges (by using a net mask).

http://dev.mysql.com/doc/refman/5.1/...unt-names.html

Short: you are looking for the NO_AUTO_CREATE_USER SQL mode:

http://dev.mysql.com/doc/refman/5.1/...to_create_user

Long: if you execute a GRANT statement for a not yet existing user,
then this user is created automatically. If your GRANT statement lacks
a 'IDENTIFIED BY' clause, then this new user will have no password.
The abovementioned SQL mode modifies the behavior of GRANT, such that
it will never create a new user without a password.

You still can create a new user and GRANT privileges at once, but then
the GRANT statement must specify a nonempty password. Alternatively you
can use CREATE USER first and then GRANT to add permissions.

GRANT will never be a problem if you add permissions to existing users.


XL

Axel Schwenke wrote:

Hi Axel,

This all looks *vey* interesting. Thanks for the chapter references - I will
look at them now.

This is going to take some playing to get my head around fully but I think I
understand the gist.

Thanks again - I think that should help a great deal. I've only b*ggered up
a couple of minor users so I can drop those and start again.

Cheers,

Tim

--
Tim Watts

Be sure to handle the situation properly where you go to create a
user jsmith@somehost when you've already got three jsmith's with
three different hosts and encrypted passwords. This might be because
jsmith changes his own password on one account and not the others,
because he doesn't know he has to or he forgot. Or they might be
different users and the conflict was not noticed.

In a way, they do. For GRANT or CREATE USER, there are two forms.

CREATE USER ... IDENTIFIED BY 'myplaintextpassword' ...
and
CREATE USER ... IDENTIFIED BY PASSWORD '*94284756DEADBEEF02856778236554784848' ...

The first form uses a plaintext password. The second form allows
you to copy the encrypted password from one user entry to another.
You can select it from the user table, then cut-and-paste it into
a create user command. You should never have to call a user and ask
for their password. Although writing on the mysql database with
direct SQL queries can mess up your system, just reading it is
relatively harmless, and CREATE USER or GRANT will affect one user.

It is sometimes useful that the same username on different machines
can be a different account, and sometimes not. If the user is an
actual human, you probably want them to refer to the same account,
unless robert@host1 and robert@host2 really refer to 2 different
humans.

If the user is a role account, such as root, daemon, mail, nagios,
etc. or a role account created for this application, especially if
it is a script running on those machines (and the administration
is not the same for all machines), you may want to restrict access
by a particular machine to its own statistics / data.

gordonb.lozmd (AT) burditt (DOT) org (Gordon Burditt) wrote:

There is no need to SELECT this info. SHOW GRANTS also returns the
hashed password and can be used to copy it. Additionally each user
can use SHOW GRANTS to see his own permissions, but normally cannot
select from any table in the `mysql` database.


XL