FAQ
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 testUser@localhost
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 testUser@localhost), 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 testUser@localhost
is there.
Better yet, you can verify a user's privileges with the SHOW GRANTS
command:

SHOW GRANTS FOR testUser@localhost;
Bruce Martin
The Martin Solution
PO Box 644
Delaware Water Gap, PA
(570) 421-0670
bruce@martinsolution.com
Michael
Bruce Martin
The Martin Solution
PO Box 644
Delaware Water Gap, PA
(570) 421-0670
bruce@martinsolution.com

Search Discussions

Discussion Posts

Previous

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 7 of 9 | next ›
Discussion Overview
groupmysql @
categoriesmysql
postedNov 5, '05 at 2:55a
activeNov 6, '05 at 7:43p
posts9
users5
websitemysql.com
irc#mysql

People

Translate

site design / logo © 2022 Grokbase