Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.mysql > #567 > unrolled thread

MySQL user management vs PostgreSQL

Back to article view | Back to comp.databases.mysql

Contents

  MySQL user management vs PostgreSQL Tim Watts <tw@dionic.net> - 2011-04-17 20:24 +0100
    Re: MySQL user management vs PostgreSQL Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-17 15:34 -0400
      Re: MySQL user management vs PostgreSQL Tim Watts <tw@dionic.net> - 2011-04-18 07:21 +0100
        Re: MySQL user management vs PostgreSQL Jerry Stuckle <jstucklex@attglobal.net> - 2011-04-18 05:45 -0400
        Re: MySQL user management vs PostgreSQL gordonb.lozmd@burditt.org (Gordon Burditt) - 2011-04-19 04:35 -0500
          Re: MySQL user management vs PostgreSQL Axel Schwenke <axel.schwenke@gmx.de> - 2011-04-19 12:10 +0200
    Re: MySQL user management vs PostgreSQL Axel Schwenke <axel.schwenke@gmx.de> - 2011-04-18 12:27 +0200
      Re: MySQL user management vs PostgreSQL Tim Watts <tw@dionic.net> - 2011-04-18 16:42 +0100

#567 — MySQL user management vs PostgreSQL

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

[toc] | [next] | [standalone]

#568

On 4/17/2011 3:24 PM, Tim Watts wrote:
> 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

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@attglobal.net
==================

[toc] | [prev] | [next] | [standalone]

#569

Jerry Stuckle wrote:

Hi Jerry,

> 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.

Right - so I was right in thinking that was how it was behaving...
 
> MySQL is not PostGres and it's authentication works differently.  And
> DB2, Oracle and SQL Server each work differently, also.

Yep.

> 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.
> 

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

[toc] | [prev] | [next] | [standalone]

#572

On 4/18/2011 2:21 AM, Tim Watts wrote:
> Jerry Stuckle wrote:
>
> Hi Jerry,
>
>> 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.
>
> Right - so I was right in thinking that was how it was behaving...
>
>> MySQL is not PostGres and it's authentication works differently.  And
>> DB2, Oracle and SQL Server each work differently, also.
>
> Yep.
>
>> 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.
>>
>
> 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
>

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@attglobal.net
==================

[toc] | [prev] | [next] | [standalone]

#576

>> 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.
>> 
> 
> 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.

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.
 
> 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 :(

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.

[toc] | [prev] | [next] | [standalone]

#577

gordonb.lozmd@burditt.org (Gordon Burditt) wrote:

>> Pity they didn't have a nice option for "dup the password" on grant :(
>
> In a way, they do.

> CREATE USER ... IDENTIFIED BY PASSWORD '*94284756DEADBEEF02856778236554784848' ...
>
> ... 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

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

[toc] | [prev] | [next] | [standalone]

#573

Tim Watts <tw@dionic.net> wrote:

> 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".

In MySQL a user specification always has the form username@hostname.
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/en/account-names.html

> 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.

Short: you are looking for the NO_AUTO_CREATE_USER SQL mode:

http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html#sqlmode_no_auto_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

[toc] | [prev] | [next] | [standalone]

#574

Axel Schwenke wrote:

> Tim Watts <tw@dionic.net> wrote:
> 
>> 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".

Hi Axel,

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

> 
> In MySQL a user specification always has the form username@hostname.
> 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/en/account-names.html
> 
>> 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.
> 
> Short: you are looking for the NO_AUTO_CREATE_USER SQL mode:
> 
> http://dev.mysql.com/doc/refman/5.1/en/server-sql-
mode.html#sqlmode_no_auto_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.

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

> 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

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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.mysql

csiph-web