469,313 Members | 2,536 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,313 developers. It's quick & easy.

Grant Privileges

I have recently set up mySQL on a Mandrake release of Linux (Version 7 of
Mandrake, I believe), using the binary 4.0.13 standard release.

The set up and start up all were normal, as far as I could tell, with no
warnings or error messages.

In nearly all respects, the database appears to be running as expected. I
have the book "PHP and mySQL Web Development" by Luke Welling and Laura
Thomson, and have been working through the examples there.

The question I have is about the GRANT command, and the apparent results
there. I don't know if my problem is one of understanding what the results
should be, or if I have another problem.

I am attempting to set up an administrative user, that has the same
privileges as root. I have been able to set up a user that appears to have
all privileges, with the exception of GRANT privileges to other users. This
user can create new database tables, insert records, modify them, delete
them and so on.

But, when this user tries to GRANT privileges on any database, even ones it
has created, an 'access denied' error is generated. I have pasted a session
below. (I hope that wrapping does not make this difficult to read.)

mysql> show grants for chc;
+---------------------------------------------------------------------------
-------------------------------------------------------+
| Grants for chc@%
|
+---------------------------------------------------------------------------
-------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX,
ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO 'chc|
| GRANT ALL PRIVILEGES ON `mysql`.`aec2003` TO 'chc'@'%' WITH GRANT OPTION
|
| GRANT ALL PRIVILEGES ON `mysql`.`mysql` TO 'chc'@'%' WITH GRANT OPTION
|
+---------------------------------------------------------------------------
-------------------------------------------------------+
3 rows in set (0.01 sec)

mysql> GRANT ALL
-> ON *.*
-> TO fred IDENTIFIED BY 'classy'
-> WITH GRANT OPTION;
ERROR 1045: Access denied for user: 'chc@localhost' (Using password: YES)
mysql>

Now, if I change this GRANT command as follows:

mysql> GRANT ALL
-> ON aec2003
-> TO fred IDENTIFIED BY 'classy'
-> WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

Then, as shown in the results, the query appears to function as desired.

However, when I then attempt to log in as fred, again access is denied:

[chc@secure chc]$
[chc@secure chc]$ mysql -u fred -p
Enter password: ******
ERROR 1045: Access denied for user: 'fred@localhost' (Using password: YES)
[chc@secure chc]$

even though when we check the GRANT table, the results are:

mysql> show grants for fred;
+---------------------------------------------------------------------------
-+
| Grants for fred@%
|
+---------------------------------------------------------------------------
-+
| GRANT USAGE ON *.* TO 'fred'@'%' IDENTIFIED BY PASSWORD '677e059523c257eb'
|
| GRANT ALL PRIVILEGES ON `mysql`.`aec2003` TO 'fred'@'%' WITH GRANT OPTION
|
+---------------------------------------------------------------------------
-+
2 rows in set (0.00 sec)

I am obviously missing some part of the process, but I am not sure what it
is. Can you explain the piece I am missing? I would appreciate it very much.
Thanks.

Charles Cantrell


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw

Jul 19 '05 #1
0 10365

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

12 posts views Thread by | last post: by
3 posts views Thread by Bruce A. Julseth | last post: by
3 posts views Thread by Marc | last post: by
4 posts views Thread by Amardeep Verma | last post: by
reply views Thread by Charles Cantrell | last post: by
6 posts views Thread by maxwell | last post: by
6 posts views Thread by Xerxes | last post: by
reply views Thread by Marc | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.