Lee Zelyck wrote:
Greetings all, I have taken it upon myself to try and learn MySQL management this weekend! No more using root for everything anymore! However, I cannot seem to do anything but create a login.
I have tried to build an extrmely simply Forum using MySQL and PHP. I can create the DB and TABLES as MySQL root without any problems. Its just trying to create an administrative user that I run into the problems. Ideally I would like to be able to create a MySQL user account, where that user can create there own DB, and do whatever the heck they want to it, while not being able to do anything with any other DB.
Please find a list of all my efforts below. Based on what I have read, these attempts should have satisfied my goals 3 times over. Anyway, if some can see any mistakes or ommissions, I would love to hear it!
mysql> INSERT INTO user VALUES('localhost','ForumUser',PASSWORD('forum123'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'); Query OK, 1 row affected (0.00 sec)
Don't edit the user table directly unless you really need to. It's better, and easier, to use GRANT. For one thing, this statement won't have any effect until you FLUSH PRIVILEGES or restart mysqld, while GRANT handles that for you.
mysql> GRANT ALL PRIVILEGES ON *.* TO [EMAIL PROTECTED] IDENTIFIED BY 'forum123' WITH GRANT OPTION; Query OK, 0 rows affected (0.11 sec)
Right, this is better. You've created the [EMAIL PROTECTED] account and set a password. Note, however, that this new user is equivalent to root, because you've given all privs on all dbs (ALL ON *.*). I thought that isn't what you want. You might consider something like:
REVOKE ALL ON *.* FROM [EMAIL PROTECTED]; GRANT ALL ON `forum%`.* TO [EMAIL PROTECTED];
The first strips ForumUser of root power, while the second gives him/her the ability to create and control databases whose names start with "forum", but no others. I think that's closer to what you want.
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON *.*
-> TO [EMAIL PROTECTED]
-> IDENTIFIED BY 'forum123';
Query OK, 0 rows affected (0.00 sec)
This probably didn't do anything, as you'd already given all those privileges in the previous command.
Spider:/usr/bin# mysql -u ForumUser
You gave [EMAIL PROTECTED] a password, but notice that you didn't use it to log in. You probably aren't logged in as [EMAIL PROTECTED] You should change this command to
mysql -u ForumUser -p
The "-p" will cause mysql to prompt you for the password.
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 39 to server version: 3.23.49-log
It may not be relevant, but that version is pretty old. The latest 3.23 revision is 3.23.58, which is about one and a half years newer. The current version is 4.0.18.
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SHOW DATABASES; +----------+ | Database | +----------+ | forums | | phpBB | | test | +----------+ 3 rows in set (0.00 sec)
mysql> USE forums; ERROR 1044: Access denied for user: '@localhost' to database 'forums' mysql> USE forums; ERROR 1044: Access denied for user: '@localhost' to database 'forums'
Notice that the errors tell you that you were really logged in as the anonymous user, ''@localhost. Most people, I believe, delete the anonymous user in the name of security, and to avoid this sort of thing.
mysql> Aborted Spider:/usr/bin# mysql -u ForumUser -p forums Enter password: ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)
I expect this is either because you mistyped [EMAIL PROTECTED]'s password, or because mysql thinks [EMAIL PROTECTED] does not have access to the forums db. To test, try logging in without specifying the db, like this
mysql -u ForumUser -p
If that lets you in, try
USE forums
You can verify who mysql thinks you are with
SELECT CURRENT_USER();
You can verify [EMAIL PROTECTED]'s privileges with
SHOW GRANTS FOR [EMAIL PROTECTED];
Sincerely, Lee
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
