-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
Helge Moulding wrote: [snip] | | 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.
Yes, except for anything that requires the 'super' privilege (shutdown,e tc).
|
| 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)You need to be logged in as 'root' on Windows to 'grant' privileges.
| | 15 OK, so that didn't work. Why not? Try to update one of the privilege | entries for chicken.
Don't do this. Login as root, and use the GRANT statements whenever you can. If you don't, it is very easy to create very mysterious acting privileges that are hard to debug. (It's not impossible, but you need to follow the logic listed in the MySQL server manual in the 'Security' section to figure out what's going on).
| 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.
You didn't use password('soup') for the password field. If you used
'GRANT' it would if automatically done this for you ;)-Mark [snip] | So, to recap: Why does a new privileged user appear when I start mysql | without specifying a user?
MySQL on windows comes with a permission entry that allows that.
|Why can't I grant all privileges to user chicken?
Because the blank user does not have GRANT privileges, and you didn't use the PASSWORD function when storing the password.
| Why can't user chicken get access?
Because the password isn't crypted with the PASSWORD function.
I myself avoid mucking around with the tables in mysql.* as much as I can, and use GRANT for just this very reason...it's a lot harder to make mistakes with the GRANT command, than changing things by hand in the mysql.* tables.
-Mark
- -- MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/
For technical support contracts, visit https://order.mysql.com/?ref=mmma
~ __ ___ ___ ____ __ ~ / |/ /_ __/ __/ __ \/ / Mark Matthews <[EMAIL PROTECTED]> ~ / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java ~ /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ~ <___/ www.mysql.com -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQE+iZQFtvXNTca6JD8RAhJSAJ9ACRLj1KLpUietfRy81C5WQjq+TwCgutUo xslVP8CLmWZ1AIReXSjkcBE= =96Xx -----END PGP SIGNATURE-----
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
