MySQL

Hi,
Please can you help me fix the code to create a user & database, it just gives me an error. Heres the code:

Code:

CREATE USER 'testing'@ '%' IDENTIFIED BY 'test';

 GRANT USAGE ON * . * TO 'testing'@ '%' IDENTIFIED BY 'test' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

 CREATE DATABASE `testing` ;

 GRANT ALL PRIVILEGES ON `testing` . * TO 'test'@ '%';

Heres the error:

Code:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''%' IDENTIFIED BY 'test'' at line 1 

Please can you help me fix the SQL,
Alex

 

Take a look here: http://dev.mysql.com/doc/refman/5.0/en/grant.html
You don't need the CREATE statement because the user is created automatically when you run a GRANT command.

 

Hi,
Thanks, i got it working with this:

Code:

GRANT ALL PRIVILEGES ON testing.* TO 'test'@'localhost'
IDENTIFIED BY 'test' WITH GRANT OPTION

How can i hide the database called "information_schema" ?

Thanks,
Alex

 

Take a look here: http://forums.mysql.com/read.php?101,85251,86464#msg-86464

 

Thanks it work perfectly

I was also wondering, how can i stop users from creating databases, in phpmyadmin when you create a database it says:

Code:

#1044 - Access denied for user 'mysql2_test'@'%' to database 'test' 

It still stops the user but however if you create a user with a garnt option in phpmyadmin, it has a little cross next to the create database option in phpmyadmin, any ideas how to do this?

Thanks,
Alex

 

Simply do not grant the CREATE priviledge to that user.

 

The global create privilege is off for this user, the only create option is on the users database This happens also with users created with ispconfig