Hi paul, Thanks for the quick reply.
I have revoke all the privileges and ran grant all privileges on testdb.* to [EMAIL PROTECTED]; and i login as testuser and i have confirm with select Current_user(); that i am a testuser; and i can still select on mysql.user. just an info. select * from mysql.user where user='testuser'; | localhost | testuser | xxxxxxxxxxxxx | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | any clue? Cheers, Clive -----Original Message----- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Monday, October 27, 2003 12:30 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: How do I restrict a mysql user only can work on a database At 12:26 PM +1100 10/27/03, Clive Luk wrote: >Thanks Paul, > >but how do i reset all the previous setting before I try to run the command >again? Or do I need to reset the privileges? Try: REVOKE ALL PRIVILEGES ON testdb FROM [EMAIL PROTECTED]; > >Cheers, >Clive > >-----Original Message----- >From: Paul DuBois [mailto:[EMAIL PROTECTED] >Sent: Monday, October 27, 2003 12:21 PM >To: [EMAIL PROTECTED] >Cc: [EMAIL PROTECTED] >Subject: RE: How do I restrict a mysql user only can work on a database > > >Please reply to the list, not to me personally, so that others can >follow this discussion. > >At 12:13 PM +1100 10/27/03, Clive Luk wrote: >>it says the following.. >> >>mysql> select current_user(); >>+-------------------+ >>| current_user() | >>+-------------------+ >>| [EMAIL PROTECTED] | >>+-------------------+ >>1 row in set (0.00 sec) >> >>Cheers, >>Clive > >Okay, that's correct. Let's see... ha, I didn't read your original >message closely enough. Your first grant statement should says >"ON testdb.*" rather than "ON testdb". The latter syntax grants >privileges to "the testdb table in the current database", which isn't >what you want. > >> >>-----Original Message----- >>From: Paul DuBois [mailto:[EMAIL PROTECTED] >>Sent: Monday, October 27, 2003 12:10 PM >>To: [EMAIL PROTECTED]; [EMAIL PROTECTED] >>Subject: Re: How do I restrict a mysql user only can work on a database >> >> >>I'll bet the real problem is that you're not being authenticated as >>testuser. >> >>After you try connecting to the server as testuser, issue this query: >> >>SELECT CURRENT_USER(); >> >>What's the result? >> >> >>At 11:57 AM +1100 10/27/03, Clive Luk wrote: >>>Hi all, >>> >>>I have a question. I have created >>> >>>===================mysql_command start============================== >>>mysql> GRANT ALL PRIVILEGES ON testdb to [EMAIL PROTECTED] >>> -> identified by 'some_passwd'; >>>===================mysql_command end============================== >>> >>>the user can login no problem. but can see testdb >>> >>>===================mysql_command start============================== >>>mysql> show databases; >>>+----------+ >>>| Database | >>>+----------+ >>>| mysql | >>>| test | >>>+----------+ >>>2 rows in set (0.11 sec) >>> >>>mysql> use test; >>>Database changed >>>mysql> show tables; >>>Empty set (0.00 sec) >>> >>>mysql> use mysql; >>>Database changed >>>mysql> show tables; >>>Empty set (0.00 sec) >>> >>>mysql> use testdb >>>ERROR 1044: Access denied for user: '[EMAIL PROTECTED]' to database >>>'testdb' >>>mysql> use openwebmail; >>>ERROR 1044: Access denied for user: '[EMAIL PROTECTED]' to database >>>'testdb' >>>mysql> quit >>> >>>===================mysql_command end============================== >>> >>>and then i did that with a root login >>> >>>===================mysql_command start============================== >>>mysql> use testdb; >>>Reading table information for completion of table and column names >>>You can turn off this feature to get a quicker startup with -A >>> >>>Database changed >>>mysql> grant select,update on *.* to [EMAIL PROTECTED]; >>>Query OK, 0 rows affected (0.00 sec) >>> >>>===================mysql_command end============================== >>> >>>But the testuser can actually view all databases. That's not what I really >>>want. I just want testuser can select and update testdb. But not others. >>> >>>How can I restrict testuser only work on testdb? >>> >>>Thanks. >>> >>> >>>Cheers, > >>Clive -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
