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?
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 >> >> >>-- >>MySQL General Mailing List >>For list archives: http://lists.mysql.com/mysql >>To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > >-- >Paul DuBois, Senior Technical Writer >Madison, Wisconsin, USA >MySQL AB, www.mysql.com > >Are you MySQL certified? http://www.mysql.com/certification/ -- 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]
