Although I know of no such sql statements in MySQL to let you do this, but you can write a stored procedure to manage this and grant the stored procedure to the user. Inside the procedure you can then create the db and grant all the said privileges to the user.
When you log into mysql with
mysql -u root -p
please run this command
SELECT USER(),CURRENT_USER();
USER() reports how you attempted to authenticate in MySQL
CURRENT_USER() reports how you were allowed to authenticate in MySQL
I have discussed this before : how do I see which user I am logged in as in MySQL?
In your case, my guess would be that CURRENT_USER() is 'root'@'localhost'
.
There are two things you need to connect to MySQL using 127.0.0.1
:
- You need the user
'root'@'127.0.0.1'
defined. That's probably what USER() said.
- You need to explicit use the TCP/IP protocol
To create the user, login to MySQL and run SHOW GRANTS;
Whatever comes backup, copy and paste it into an editor, replace localhost
with 127.0.0.1
, command the edited command into mysql and execute it.
Now, to login using TCP/IP do this:
mysql -u root -p -h 127.0.0.1 --protocol=tcp
Once you login, run SELECT USER(),CURRENT_USER();
and you will see something different.
Give it a Try !!!
Best Answer
Check mysql configs in
/etc/mysql/
or the runscript in/etc/init.d/
.Edit:
Try this: GRANT ALL, GRANT OPTION ON . TO user@localhost;
http://dev.mysql.com/doc/refman/5.1/en/grant.html