Ok thank you for your replies. I finally got it working, but as you
stated I granted too many permissions. I found that in the user table
my testUser had N for every column with the exception of the last few
which where 0.
I first logged into the database using:
shell>mysql -u root -p mysql
So I used:
mysql>GRANT ALL PRIVILEGES ON test.* TO 'testUser'@'localhost'
IDENTIFIED BY 'some_password';
and:
mysql>GRANT ALL PRIVILEGES ON test.* TO 'testUser'@'%' IDENTIFIED BY
'some_password';
Then I did:
mysql> select * from user where user.User='testUser';
Which returned:
| 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 | Create_view_priv | Show_view_priv
| Create_routine_priv | Alter_routine_priv | Create_user_priv |
ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions |
max_updates | max_connections | max_user_connections |
+-----------+----------+-------------------------------------------
+-------------+-------------+-------------+-------------+-------------
+-----------+-------------+---------------+--------------+-----------
+------------+-----------------+------------+------------
+--------------+------------+-----------------------+------------------
+--------------+-----------------+------------------+------------------
+----------------+---------------------+--------------------
+------------------+----------+------------+-------------
+--------------+---------------+-------------+-----------------
+----------------------+
| % | testUser | *12F46AB3EF1939F7B217B125466177AFA18495CF | N
| N | N | N | N | N
| N | N | N | N | N
| N | N | N | N | N
| N | N | N | N
| N | N | N | N
| N | N | |
| | | 0 | 0 |
0 | 0 |
| localhost | testUser | *12F46AB3EF1939F7B217B125466177AFA18495CF | N
| N | N | N | N | N
| N | N | N | N | N
| N | N | N | N | N
| N | N | N | N
| N | N | N | N
| N | N | |
| | | 0 | 0 |
0 | 0 |
I then did:
UPDATE user SET
Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y',Create_p
riv='Y' WHERE user.User='testUser';
I can now access the server using my test user: testUser.
However, even though I specified 'test.*' my test user can access the
mysql database. doesn't test.* limit the user to the test database? or
should it have been 'test' no quotes of course.
Is there a way to update the database access without deleting the user
and redoing it all?
Thanks for the help.
On Nov 6, 2005, at 12:23 AM, Michael Stassen wrote:
Bruce Martin wrote:
When I log in as root using:
mysql -u root -p mysql
I get the mysql prompt:
mysql>
I then issue this command or statement:
mysql>GRANT ALL PRIVILEGES ON *.* TO 'testUser'@'' IDENTIFIED BY
'some_password' WITH GRANT OPTION;
First, your hostname is blank. That won't work. You need to either
specify a hostname, or use the wildcard character, '%'. See the
manual for details
<http://dev.mysql.com/doc/refman/5.0/en/connection-access.html>.
Second, why are you making testuser equivalent to root? Don't give a
user any more privileges than necessary. For a test user, I'd suggest
starting with just
GRANT ALL PRIVILEGES ON test.* TO 'testUser'@'localhost'
IDENTIFIED BY 'some_password';
I get the following returned:
Query OK, 0 rows affected (0.00 sec)
Why is this not working?
What makes you think it didn't work? You didn't get an error message.
Instead, you got "Query OK". Looks like it worked, to me.
To test it further I try to log in as testUser but it tells me access
denied for user [EMAIL PROTECTED]
That's not the whole error message. Please show us your exact comand,
and copy/paste the exact error message. We could try to guess what's
wrong (probably there is no [EMAIL PROTECTED]), but it's better not
to guess.
Even if I grant the testUser @ localhost.
No spaces. Again, show us the exact GRANT command, the exact
connection command, and the exact error message.
I can look in the user table and sure enough user [EMAIL PROTECTED]
is there.
Better yet, you can verify a user's privileges with the SHOW GRANTS
command:
SHOW GRANTS FOR [EMAIL PROTECTED];
Bruce Martin
The Martin Solution
PO Box 644
Delaware Water Gap, PA
(570) 421-0670
[EMAIL PROTECTED]
Michael
Bruce Martin
The Martin Solution
PO Box 644
Delaware Water Gap, PA
(570) 421-0670
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
