MySQL

[alexillsley]

Hi,
Please can you help me fix the code to create a user & database, it just gives me an error. Heres the 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:
#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

[falko]

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.

[alexillsley]

Hi,
Thanks, i got it working with this:
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

[falko]

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

[alexillsley]

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:
#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

[falko]

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.

[alexillsley]

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