3 newbie mySQL questions

[TommyWillB]

I've also cross-posted this to the Linux > PHP/mySQl section:

I followed this great turtorial on setting up mySQL: http://www.entropy.ch/software/macosx/mysql/

... I can do just about anything in the test database, but when I try to use a new database only _root_ can access it from PHP.

What magic do I have to perform to grant the user "mysql" read/write access to my database named "tommyb"?

Also, I'm not clear if I do or do NOT want to require that the password for "mysql" be passed from php as part of the mysql_conenct string. If I do, what is the command to make the password required on the mySQl side? (I think I already have the mysql user/encrypted password added to mySQL's mysql.user table correctly.)

Lastly, since I'm running PHP and mySQL on the same box, how do I limit the "mysql" user so that it can only conenct from localhost/127.0.0.1?

 

[domih]

One way to do it, it to change the privileges in the administration database (the "mysql" database). Log to mysql as the mysql root user and do something like:

# Get rid of the default privileges

DELETE FROM columns_priv;

DELETE FROM db;

DELETE FROM func;

DELETE FROM host;

DELETE FROM tables_priv;

DELETE FROM user;

# The account we'll use on the local console has all privileges on all databases
# (replace 'db-master' and 'big-enchilada-007' with your own values)

INSERT INTO user VALUES ('localhost','db-master',password('big-enchilada-007'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
INSERT INTO user VALUES ('127.0.0.1','db-master',password('big-enchilada-007'),'Y','Y','Y','Y','','Y','Y','Y','Y','Y','Y','Y','Y','Y');

# The account we'll use with PHP can do... well, nothing
# replace 'php' and 'web101' with your own values

INSERT INTO user VALUES ('localhost','php',password('web101'),'N','N','N','N','N','N','N','N','N','N','N','N','N','N');
INSERT INTO user VALUES ('127.0.0.1','php',password('web101'),'N','N','N','N','N','N','N','N','N','N','N','N','N','N');

# That same PHP account can do anything with the 'my_new_db' database (except granting privileges)
# replace 'my_new_db' with your own value

INSERT INTO db VALUES ('localhost','my_new_db','php','Y','Y','Y','Y','Y','Y','N','Y','Y','Y');
INSERT INTO db VALUES ('127.0.0.1','my_new_db','php','Y','Y','Y','Y','Y','Y','N','Y','Y','Y');

# Optionally Create other user or db rows as you wish.

# Tell the MySQL daemon to reload the access privileges tables

FLUSH PRIVILEGES;

---

You can do that using the monitor (in such a case you might want to clear up your hidden mysql history log file in your home directory afterwards) or you can edit a text file, let's call it 'my-own-mysql-init.sql' and pass it to mysql from the shell

mysql -u your_mysql_root_password -p < /Users/YourHome/my-own-mysql-init.sql

Then you can put that file in a secure location for reuse in the future.

Note: if you use a text file, save it with linefeeds not CRs otherwise MySQL won't get it.

 

[TommyWillB]

There is a lot that I like about mySQL, but these permissions take some getting used to. (Its been a couple years since I use Oracle, and I think I've become a bit spoiled by MS SQL Server's GUI..)

Thanks again for the details!

 

[slur]

There is a web-based interface for MySQL called PHPMyAdmin available at this site.

I haven't actually used PHPMyAdmin because I wanted to write my own MySQL administrative tool in PHP and I didn't want to accidentally "steal" any of their clever ideas. (Ah, what a wonderful world intellectual property makes!) Someday I might finish the thing and post it on Versiontracker. Ironically mysql permissions are its one languishing feature....