Thoughts inspiring. Inspiring thoughts
Here is the necessary sql to create a user and database of the same name. Note to my future self.
CREATE USER 'username'@'%' IDENTIFIED BY 'password';
GRANT USAGE ON * . * TO 'username'@'%' IDENTIFIED BY 'password' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
CREATE DATABASE IF NOT EXISTS `username` ;
GRANT ALL PRIVILEGES ON `username` . * TO 'usernamel'@'%';
Yes, the code is created using phpmyadmin.
ThinkingNectar talks about the interest of Chin Yong, a PHP/Python/Web developer residing in Singapore. Life, society, and codes should entails most of what goes between the ears of this coffee drinker.
What makes you think?
Tamas Herman
June 27th, 2010 at 12:38 pm
it’s way too much. just use this:
create user LOOSER@localhost identified by password ‘PASS’;
grant all on *.* to LOOSER@localhost;
create database LOOSER;
============
why?
1, CREATE USER ‘username’@'%’ IDENTIFIED BY ‘password’;
% means from any host, isn’t it?
sounds a bit brutal to me.. :/
usually user@localhost is enough.
and u dont even have to use single quotes in such case…
i suppose u protect mysql at the IP level.
example command how do u do that?
2, GRANT USAGE ON * . * TO ‘username’@'%’ IDENTIFIED BY ‘password’ WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
these are all default values – i guess – so u can just omit them.
also i didn’t find necessary to grant the usage explicitly, so u can omit this step.
3, CREATE DATABASE IF NOT EXISTS `username` ;
no backquotes are necessary (if there are no spaces or other crazy shit in the db name).
i don’t see a need for IF NOT EXISTS, since u wont run this script twice probably, OR if u do, u will get an error message when u try to create the user again, so u have to run the mysql client w the -f option to let it proceed further after errors.
and u dont have to create the db before specifying grants.
the grant statements dont check the db existence.
(unlike postgres, iirc)
4, GRANT ALL PRIVILEGES ON `username` . * TO ‘usernamel’@'%’;
looks like there is a typo in the user specs.
if it was a myphpadmin generated query, then who did it get there? :/
l is not close to either the apostrophe or the letter e on a qwerty keyboard…
============
i usually make it broader, so the users can create their own dbs where the db names are prefixed by their user names. it requires some quoting and escaping:
grant all on `LOOSER\_%`.* to LOOSER@localhost;
============
u might ask how do u access a db remotely if only LOOSER@localhost has access to it.
well, u should use SSH tunneling:
ssh -L 3307:localhost:3306 whatever-ssh-user@mysql.server.com
mysql -P 3307 -h 127.0.0.1 -u LOOSER LOOSER_somedb
and just leave the SSH connection open OR use the -fN options to put the connection into the background and execute no commands at the remote side (but just open up the tunnel)
port 3307 is opened on ur localhost. i choose this port because i assumed u might already have a mysql running locally on port 3306, so i’d like to avoid collision.
if for some reason 4 digit port numbers are not working, just choose a port over 10′000.