Hey all -

I've written a script that creates a database, creates a user with
permissions on that database and then continues to create the rest of
the entity model. What I'm finding, though, is that I can't connect
as the user that's created in the script. Everything seems to be in
place, though, if I login as my global admin user. The syntax I'm
using to create the user is:

GRANT SELECT,INSERT,DELETE,UPDATE on my_new_database.* to app_user
identified by 'uJ8xFkBTe';

As I mentioned, the user is created and the appropriate permissions
are created, but I can't authenticate as that user. Any insight would
be much appreciated. This is a MySQL 5 install.

Rob

On Nov 23, 6:57 pm, Rob Wilkerson <r.d.wilker...@gmail.com> wrote:
> Hey all -
>
> I've written a script that creates a database, creates a user with
> permissions on that database and then continues to create the rest of
> the entity model. What I'm finding, though, is that I can't connect
> as the user that's created in the script. Everything seems to be in
> place, though, if I login as my global admin user. The syntax I'm
> using to create the user is:
>
> GRANT SELECT,INSERT,DELETE,UPDATE on my_new_database.* to app_user
> identified by 'uJ8xFkBTe';
>
> As I mentioned, the user is created and the appropriate permissions
> are created, but I can't authenticate as that user. Any insight would
> be much appreciated. This is a MySQL 5 install.

Just following up. I read about issues with "_" in database names and
how they're interpreted, so I removed them and still have the same
problem. phpMyAdmin shows a user "app_user"@"%", but when I try to
authenticate as that user, I get:

Access denied for user 'app_user'@'localhost' (using password: YES)

Rob Wilkerson wrote:
> On Nov 23, 6:57 pm, Rob Wilkerson <r.d.wilker...@gmail.com> wrote:
>> Hey all -
>>
>> I've written a script that creates a database, creates a user with
>> permissions on that database and then continues to create the rest of
>> the entity model. What I'm finding, though, is that I can't connect
>> as the user that's created in the script. Everything seems to be in
>> place, though, if I login as my global admin user. The syntax I'm
>> using to create the user is:
>>
>> GRANT SELECT,INSERT,DELETE,UPDATE on my_new_database.* to app_user
>> identified by 'uJ8xFkBTe';
>>
>> As I mentioned, the user is created and the appropriate permissions
>> are created, but I can't authenticate as that user. Any insight would
>> be much appreciated. This is a MySQL 5 install.
>
> Just following up. I read about issues with "_" in database names and
> how they're interpreted, so I removed them and still have the same
> problem. phpMyAdmin shows a user "app_user"@"%", but when I try to
> authenticate as that user, I get:
>
> Access denied for user 'app_user'@'localhost' (using password: YES)
>

localhost is NOT the same as "%"!

"%" means any host OTHER THAN localhost.

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

On Nov 23, 9:09 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>
> localhost is NOT the same as "%"!
>
> "%" means any host OTHER THAN localhost.

ACK! That's what I was missing! I thought "%" was essentially a
wildcard. As in "from any host" (including localhost). Now I feel
like an even bigger dolt for the amount of time I spent trying to
figure this out.

Many, many thanks.

Michael Martinek wrote:
>>> "%" means any host OTHER THAN localhost.
>
> Actually, % _IS_ a wildcard. What you should be doing is:
>
> GRANT SELECT,INSERT,DELETE,UPDATE on my_new_database.* to
> 'appuser'@'%' identified by 'uJ8xFkBTe';
>
> To allow connections from any host. The underscore (_) is a wildcard
> limited to one character. Avoid it.
>

Any host EXCEPT localhost, that is. See the doc.

> If you do a grant like this:
>
> GRANT SELECT,INSERT,DELETE,UPDATE on my_new_database.* to
> 'appuser'@'localhost' identified by 'uJ8xFkBTe';
>
> Then open your connection using 127.0.0.1, it will fail. "127.0.0.1" !
> = "localhost"
> If you grant using a host name, use the hostname in your connection.
> If you grant using an IP, then use an IP in your connection.
>


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

>
> Any host EXCEPT localhost, that is. See the doc.
>

Perhaps you'd like to point it out to me? I'm quite familiar with the
documents.. in fact, I double-checked just to make sure it wasn't
something released recently. Every reference to "%" cites it as a
wildcard.. and searching for "except" yields no results regarding
alternate usage of the "%" wildcard or exception to the "localhost"
value.

The documents support my version of it in releases 6.0, 5.1, 5.0,
3.23, 4.1, 4.0.

In fact, user comments on the MySQL forums even cite examples such as:

grant all on *.* to username@'%' identified by 'password';

This may be a great opportunity to refresh yourself with the message
boards, and ensure you're up to date with the most recent information.
Maybe even give it a shot, and do a new grant with a host of %.. then
do a mysql -u<user> -p -hlocalhost

Regards,
Michael Martinek

On Mon, 26 Nov 2007 13:43:23 -0800 (PST), Michael Martinek wrote:
>>
>> Any host EXCEPT localhost, that is. See the doc.
>>
>
> Perhaps you'd like to point it out to me? I'm quite familiar with the
> documents.. in fact, I double-checked just to make sure it wasn't
> something released recently. Every reference to "%" cites it as a
> wildcard.. and searching for "except" yields no results regarding
> alternate usage of the "%" wildcard or exception to the "localhost"
> value.
>
> The documents support my version of it in releases 6.0, 5.1, 5.0,
> 3.23, 4.1, 4.0.
>
> In fact, user comments on the MySQL forums even cite examples such as:
>
> grant all on *.* to username@'%' identified by 'password';
>
> This may be a great opportunity to refresh yourself with the message
> boards, and ensure you're up to date with the most recent information.
> Maybe even give it a shot, and do a new grant with a host of %.. then
> do a mysql -u<user> -p -hlocalhost

Find **

After connecting to the server as root, you can add new accounts. The
following statements use GRANT to set up four new accounts:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'
-> IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
-> IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
mysql> GRANT USAGE ON *.* TO 'dummy'@'localhost';

The accounts created by these GRANT statements have the following
properties:

Two of the accounts have a username of monty and a password of
some_pass. Both accounts are superuser accounts with full privileges to
do anything. **One account ('monty'@'localhost') can be used only when
connecting from the local host. The other ('monty'@'%') can be used to
connect from any other host. Note that it is necessary to have both
accounts for monty to be able to connect from anywhere as monty.** Without
the localhost account, the anonymous-user account for localhost that is
created by mysql_install_db would take precedence when monty connects
from the local host. As a result, monty would be treated as an anonymous
user. The reason for this is that the anonymous-user account has a more
specific Host column value than the 'monty'@'%' account and thus comes
earlier in the user table sort order. (user table sorting is discussed
in Section 5.7.5, .Access Control, Stage 1: Connection Verification..)

http://dev.mysql.com/doc/refman/5.1/...ing-users.html

Maybe those message boards are populated by people that A) can't read
and/or B) don't experiment.

--
Tomorrow is the first day of the rest of your life in a career where, if you
really suck, 100% of your work fails and if you're really good and really
lucky only 99% of it is a complete waste of time. - Hal Bogerd

> Note that it is necessary to have both
> accounts for monty to be able to connect from anywhere as monty.** Without
> the localhost account, the anonymous-user account for localhost that is
> created by mysql_install_db would take precedence when monty connects
> from the local host. As a result, monty would be treated as an anonymous
> user. The reason for this is that the anonymous-user account has a more
> specific Host column value than the 'monty'@'%' account and thus comes
> earlier in the user table sort order.

Doesn't that sound a little wrong to you? It also doesn't say anywhere
that '%' means anything EXCEPT localhost.

> Maybe those message boards are populated by people that A) can't read
> and/or B) don't experiment.
>

And as for B.. well.. admin much?

Ok, for the sake of argument.. I went ahead and added an anonymous
user again. Normally, I don't have an anonymous user even in the user
tables. Then, I set all the hosts to '%' instead of their restricted
LAN sources and did a FLUSH PRIVILEGES. Then, for shits and giggles, I
even restart the MySQL server.

Here's the set of users and hosts I'm working with now:
mysql> SELECT user, host FROM mysql.user;
+-----------+-----------+
| user | host |
+-----------+-----------+
| | % |
| mmartinek | % |
| root | % |
| test | localhost |
+-----------+-----------+
4 rows in set (0.00 sec)

mysql>

Ok. So, let's try logging in now.

mmartinek@Akede:~$ mysql -ummartinek -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.22-rc-log Source distribution

mysql> SELECT user, host FROM mysql.user;
ERROR 1142 (42000): SELECT command denied to user
'mmartinek'@'localhost' for table 'user'
mysql>

Ok, no surprise there.. restricted user account. Let's try from a
system that isn't loopback...

C:\Users\Mike>mysql -uroot -p -h192.168.1.110
Enter password: ***************
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.22-rc-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

No problem there, either. LAN connection on root account, with a host
of "%". Let's go ahead and try loopback on root now.

mmartinek@Akede:~$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6


Ok, let's trash the anonymous account and try it then.

mysql> DROP USER '';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> Aborted
mmartinek@Akede:~$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7

mysql> SELECT user, host FROM mysql.user;
+-----------+-----------+
| user | host |
+-----------+-----------+
| mmartinek | % |
| root | % |
| test | localhost |
+-----------+-----------+
3 rows in set (0.00 sec)

mysql>


And for the finale..


mmartinek@Akede:~$ mysql -uroot -p -h127.0.0.1
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.

mmartinek@Akede:~$ mysql -uroot -p -hlocalhost
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.


Using a host name of '%' works to allow connections from any host.

Michael Martinek wrote:
>> Note that it is necessary to have both
>> accounts for monty to be able to connect from anywhere as monty.** Without
>> the localhost account, the anonymous-user account for localhost that is
>> created by mysql_install_db would take precedence when monty connects
>> from the local host. As a result, monty would be treated as an anonymous
>> user. The reason for this is that the anonymous-user account has a more
>> specific Host column value than the 'monty'@'%' account and thus comes
>> earlier in the user table sort order.
>
> Doesn't that sound a little wrong to you? It also doesn't say anywhere
> that '%' means anything EXCEPT localhost.
>
>> Maybe those message boards are populated by people that A) can't read
>> and/or B) don't experiment.
>>
>
> And as for B.. well.. admin much?
>
> Ok, for the sake of argument.. I went ahead and added an anonymous
> user again. Normally, I don't have an anonymous user even in the user
> tables. Then, I set all the hosts to '%' instead of their restricted
> LAN sources and did a FLUSH PRIVILEGES. Then, for shits and giggles, I
> even restart the MySQL server.
>
> Here's the set of users and hosts I'm working with now:
> mysql> SELECT user, host FROM mysql.user;
> +-----------+-----------+
> | user | host |
> +-----------+-----------+
> | | % |
> | mmartinek | % |
> | root | % |
> | test | localhost |
> +-----------+-----------+
> 4 rows in set (0.00 sec)
>
> mysql>
>
> Ok. So, let's try logging in now.
>
> mmartinek@Akede:~$ mysql -ummartinek -p
> Enter password:
> Welcome to the MySQL monitor. Commands end with ; or \g.
> Your MySQL connection id is 4
> Server version: 5.1.22-rc-log Source distribution
>
> mysql> SELECT user, host FROM mysql.user;
> ERROR 1142 (42000): SELECT command denied to user
> 'mmartinek'@'localhost' for table 'user'
> mysql>
>
> Ok, no surprise there.. restricted user account. Let's try from a
> system that isn't loopback...
>
> C:\Users\Mike>mysql -uroot -p -h192.168.1.110
> Enter password: ***************
> Welcome to the MySQL monitor. Commands end with ; or \g.
> Your MySQL connection id is 5
> Server version: 5.1.22-rc-log Source distribution
>
> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
>
> mysql>
>
> No problem there, either. LAN connection on root account, with a host
> of "%". Let's go ahead and try loopback on root now.
>
> mmartinek@Akede:~$ mysql -uroot -p
> Enter password:
> Welcome to the MySQL monitor. Commands end with ; or \g.
> Your MySQL connection id is 6
>
>
> Ok, let's trash the anonymous account and try it then.
>
> mysql> DROP USER '';
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> FLUSH PRIVILEGES;
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> Aborted
> mmartinek@Akede:~$ mysql -uroot -p
> Enter password:
> Welcome to the MySQL monitor. Commands end with ; or \g.
> Your MySQL connection id is 7
>
> mysql> SELECT user, host FROM mysql.user;
> +-----------+-----------+
> | user | host |
> +-----------+-----------+
> | mmartinek | % |
> | root | % |
> | test | localhost |
> +-----------+-----------+
> 3 rows in set (0.00 sec)
>
> mysql>
>
>
> And for the finale..
>
>
> mmartinek@Akede:~$ mysql -uroot -p -h127.0.0.1
> Enter password:
> Welcome to the MySQL monitor. Commands end with ; or \g.
>
> mmartinek@Akede:~$ mysql -uroot -p -hlocalhost
> Enter password:
> Welcome to the MySQL monitor. Commands end with ; or \g.
>
>
> Using a host name of '%' works to allow connections from any host.
>

And as Peter indicated - MySQL creates a default user account at %. You
really shouldn't delete this for security reasons.

You deleted it, so you suffer the consequences.

And as he also said:

Maybe those message boards are populated by people that A) can't read
and/or B) don't experiment.

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

> And as Peter indicated - MySQL creates a default user account at %. You
> really shouldn't delete this for security reasons.

The only "security reason" a default and anonymous user would exist is
to assist in preventing random people from brute forcing connections.
So this is to assume that your firewall has failed to prevent remote
connections as it may be misconfigured.

Even allowing a successful connection from anyone can be deemed a
security risk, and you may fail many security assessments by allowing
an anonymous connection to your server from an unknown source. How
would it go down in the news if you could connect to an AOL internal
database, even if you are just an anonymous user with absolutely no
rights to do anything? How long would it be before a hacker found an
exploit in the server running because they were able to open a
connection and issue commands?

One example reason you wouldn't want anonymous connections is in the
connection string:

Server version: 5.1.22-rc-log Source distribution


> You deleted it, so you suffer the consequences.
Well, I'd hardly consider security a consequence to be suffered.
Normally, I don't allow connections from '%'.. the configuration was
modified to show you that it is in fact very possible to connect using
'%' as a host name; and that '%' does NOT mean accept anything EXCEPT
'localhost'. Advising that '%' means anything EXCEPT localhost can
pose a security risk for anyone who believes that and implements it.
Basic rule of securing: Deny all, allow exceptions.


> And as he also said:
>
> Maybe those message boards are populated by people that A) can't read
> and/or B) don't experiment.

Not sure why this keeps coming up. It's just blatantly ignorant to
claim that they aren't able to read. In fact, everything they state in
the forum on that page is accurate. You're arguing that it is not..
which brings us to B, which I have demonstrated for you that the
messages on the forums are indeed accurate. Even by adding an
anonymous account account again, which yielded no change in results.
Before, you argued that using '%' as a hostname meant everything
EXCEPT 'localhost', which is not the case. Now it is apparently a
"consequence to suffer" by removing anonymous access?

There's nothing wrong with removing the anonymous user. If your system
isn't already properly firewalled and secured, you're going to look
like a fool as soon as someone connects to it, does a SELECT
UNIX_TIMESTAMP() or SELECT VERSION() and goes screaming to the world
that they connected to your database server (which may stored secure
information), and was able to issue SQL commands to the database
without providing a username or password; and that they can detect
what version you're running to help pin-point known security exploits.
Then again, maybe I'm just looking at this from the perspective of a
company. Certifications like CISP would also require you to disable
anonymous user accounts.