Andre MATOS wrote:
Hi,
I insert a new user to access the MySQL database. I have 4 databases plus the mysql database. I want that this user can run the select instruction just into one database, so I did this:
[prompt]$ mysql u root p Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3147 to server version: 4.0.18-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> USE mysql; 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
INSERT INTO user (Host, User, Password, Select_priv) VALUES ('%', 'myuser', password('hispassword'), 'Y');
mysql> GRANT ALL ON database2.* TO myuser;
mysql> FLUSH PRIVILEGES; mysql> quit;
However, when I log using this new user myuser, I still can see the others database and a can insert, update and delete records into all databases instead of just have the rights to select in the database2. What I did was correct or it is missing something?
Best Regards,
Andre
This is why it's best to use GRANT and REVOKE instead of trying to manually edit the mysql tables. You gave global select privilege in your insert into user. The correct (and simpler) way to have done this would have been the one statement
GRANT ALL ON database2.* TO myuser IDENTIFIED BY 'hispassword';
That will create the user entry, if necessary, and grant the specified privileges (ALL on database2.*). It also handles the FLUSH for you.
There are a couple ways to fix what you have. I'd suggest
REVOKE ALL PRIVILEGES,GRANT OPTION FROM myuser; GRANT ALL ON database2.* TO myuser;
See <http://www.mysql.com/doc/en/GRANT.html> for more.
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
