Thanks to C. Reeve and Michael Shulman for their answers. However, if
the things that are documented worked, then I might not be complaining.
I tried some of the stuff you suggested, and eventually managed to
solidly lock myself out of mysql, with no further access to anything!
Arghh!
Note that my problem isn't establishing security at this point, its
setting things up so that [EMAIL PROTECTED] identified by 'password' has access.
Having MySQL so secure that not even [EMAIL PROTECTED] has access is
ridiculous, but that's the result I achieved by following Michael
Shulman's directions.
Since it's no big deal, I just reinstalled the entire damn thing and
started from scratch. This time I took careful notes to illustrate the
trouble I had. Here is the transcript. Things are already a bit weird
by step 7. Then, from steps 12 through 16 I try to add a privileged
user, but I discover in step 17 that this new user can't get access.
Notes on re-installation
1 delete mysql folders, my.ini from windows folder
2 re-install mysql from zip file
3 create new my.ini for windows folder (skip using winmysqladmin)
[mysqld]
basedir=c:/program files/mysql40
datadir=c:/program files/mysql40/data
4 open command prompt
5 start mysqld
6 start mysql without any options
7 who am I?
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| @127.0.0.1 |
+----------------+
8 switch to mysql database
use mysql;
9 find out what's all in the user table
mysql> select user, host, password from user;
+------+-----------+----------+
| user | host | password |
+------+-----------+----------+
| root | localhost | |
| root | % | |
| | localhost | |
| | % | |
+------+-----------+----------+
10 further investigation shows that these users have all privileges
mysql> select * from user;
+-----------+------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+
| Host | User | password | Select_priv | Insert_priv | Update_priv | Delete_priv
| Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv |
Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv |
Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv |
Repl_client_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions
| max_updates | max_connections |
+-----------+------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+
| localhost | root | | Y | Y | Y | Y
| Y | Y | Y | Y | Y | Y | Y
| Y | Y | Y | Y | Y | Y
| Y | Y | Y | Y
| | | | | 0 | 0 |
0 |
| % | root | | Y | Y | Y | Y
| Y | Y | Y | Y | Y | Y | Y
| Y | Y | Y | Y | Y | Y
| Y | Y | Y | Y
| | | | | 0 | 0 |
0 |
| localhost | | | Y | Y | Y | Y
| Y | Y | Y | Y | Y | Y | Y
| Y | Y | Y | N | N | N
| N | N | N | N
| | | | | 0 | 0 |
0 |
| % | | | N | N | N | N
| N | N | N | N | N | N | N
| N | N | N | N | N | N
| N | N | N | N
| | | | | 0 | 0 |
0 |
+-----------+------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+
11 it appears that the blank user from localhost without a password is
allowed to do anything he wants. Note that this blank user came into
existance simply by starting mysql monitor.
12 what we want is to be able to control what users have access, so lets
add a password protected user by using the database itself
mysql> insert into user (host, user, password)
-> values ('localhost', 'chicken', 'soup');
13 find out what that did
select * from user where user = 'chicken';
+-----------+---------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+
| Host | User | password | Select_priv | Insert_priv | Update_priv |
Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv |
File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv |
Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv
| Repl_client_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject |
max_questions | max_updates | max_connections |
+-----------+---------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+
| localhost | chicken | soup | N | N | N | N
| N | N | N | N | N | N |
N | N | N | N | N | N | N
| N | N | N | N
| | | | | 0 | 0
| 0 |
+-----------+---------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+
14 this resulted in a new user, with no privileges, so lets give that
new user all privileges.
mysql> grant all privileges on *.* to [EMAIL PROTECTED] identified by 'soup' with
grant option;
ERROR 1045: Access denied for user: '@127.0.0.1' (Using password: NO)
15 OK, so that didn't work. Why not? Try to update one of the privilege
entries for chicken.
mysql> update user set select_priv = 'y' where user = 'chicken';
Query OK, 1 row affected (0.16 sec)
Rows matched: 1 Changed: 1 Warnings: 0
16 So that worked. We'll update all of the other values the same way.
select * from user where user = 'chicken';
+-----------+---------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+
| Host | User | password | Select_priv | Insert_priv | Update_priv |
Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv |
File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv |
Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv
| Repl_client_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject |
max_questions | max_updates | max_connections |
+-----------+---------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+
| localhost | chicken | soup | Y | Y | Y | Y
| Y | Y | Y | Y | Y | Y |
Y | Y | Y | Y | Y | Y | Y
| Y | Y | Y | Y
| | | | | 0 | 0
| 0 |
+-----------+---------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+
17 Since that worked, the question "Why not?" posed in step 15 becomes a
bit more pressing. Another question is, of course, what exactly these
privileges are good for, since adding these privileges doesn't give any
information about the "on *.*" portion of the GRANT query. Anyway,
right now it's time to try the chicken user. Quit mysql and start it
again, this time for user chicken.
mysql> flush privileges;
Query OK, 0 rows affected (0.87 sec)
mysql> exit;
Bye
C:\Program Files\mysql40\bin>mysql -u chicken -p
Enter password: ****
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
18 Clearly there's something weird going on. I know that the password is
being entered correctly. I notice that it reports a problem with
[EMAIL PROTECTED], not with [EMAIL PROTECTED] I'm guessing that the
problem is in that, somewhere, but I have no idea about what to do next.
So, to recap: Why does a new privileged user appear when I start mysql
without specifying a user? Why can't I grant all privileges to user chicken?
Why can't user chicken get access?
--
Helge Moulding
mailto:[EMAIL PROTECTED] Just another guy
http://hmoulding.cjb.net/ with a weird name
_______________________________________________
Join Excite! - http://www.excite.com
The most personalized portal on the Web!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
