| Bug #13117 | missing ROOT privileges | ||
|---|---|---|---|
| Submitted: | 12 Sep 2005 3:53 | Modified: | 14 Sep 2005 18:05 |
| Reporter: | Peter Laursen (Basic Quality Contributor) | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.12 | OS: | Windows (WinXP SP2) |
| Assigned to: | CPU Architecture: | Any | |
[12 Sep 2005 4:02]
Peter Laursen
just forget to mention that I never experienced that with earlier versions, befor 5.0.12 I have always been able to create SP's as ROOT without grating any new privileges.
[13 Sep 2005 15:27]
MySQL Verification Team
Hello Peter, Thank you for the report, but I wasn't able reproduce this error. Can you show us the output of SELECT CURRENT_USER() and output of SHOW GRANT FOR <user@host>? <user@host> - the result of the first statement.
[13 Sep 2005 15:52]
Peter Laursen
Is your syntax correct ? I get: ********************************** mysql> select current_user(); +----------------+ | current_user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.02 sec) mysql> show grant for root@localhost; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'grant for root@localhost' at line 1 mysql> And BTW I have GRANTED those privileges myself now. So will it give any useful information?.
[13 Sep 2005 16:02]
Peter Laursen
however using INFORMATION_SCHEMA select * from `information_schema`.`USER_PRIVILEGES`; GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE ------------------- ------------- ----------------------- ------------ 'root'@'localhost' (NULL) SELECT YES 'root'@'localhost' (NULL) INSERT YES 'root'@'localhost' (NULL) UPDATE YES 'root'@'localhost' (NULL) DELETE YES 'root'@'localhost' (NULL) CREATE YES 'root'@'localhost' (NULL) DROP YES 'root'@'localhost' (NULL) RELOAD YES 'root'@'localhost' (NULL) SHUTDOWN YES 'root'@'localhost' (NULL) PROCESS YES 'root'@'localhost' (NULL) FILE YES 'root'@'localhost' (NULL) REFERENCES YES 'root'@'localhost' (NULL) INDEX YES 'root'@'localhost' (NULL) ALTER YES 'root'@'localhost' (NULL) SHOW DATABASES YES 'root'@'localhost' (NULL) SUPER YES 'root'@'localhost' (NULL) CREATE TEMPORARY TABLES YES 'root'@'localhost' (NULL) LOCK TABLES YES 'root'@'localhost' (NULL) EXECUTE YES 'root'@'localhost' (NULL) REPLICATION SLAVE YES 'root'@'localhost' (NULL) REPLICATION CLIENT YES 'root'@'localhost' (NULL) CREATE VIEW YES 'root'@'localhost' (NULL) SHOW VIEW YES (privileges for other users not submitted)
[13 Sep 2005 19:15]
Peter Laursen
OK then .. I have new info. I did a repair install and changed the datadir to the empty one created with the installation. Now problem is not there. I can create procedures. However the grant statement provided by you still returns the error. But it must be some problem with the system tables in the old /datadir with my old data. I guess I can dump and import data. But a smarter way ? I HAVE been running the script updating the Grant table. But data are back form 4.0.10 or something like that, and server has been updated no less than 20 times. I guess it will be hard to tell at which stage something went wrong - also if it is an error on my side or an issue with one of the many scripts. I suggest you let this be open for a while, just in case that someone has a similar experience.
[14 Sep 2005 1:33]
MySQL Verification Team
The command for show grants should be: show grants for root@localhost; c:\mysql\bin>mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 5.0.12-beta-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show grants for root@localhost; +---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+ 1 row in set (0.00 sec) and not: show grant for root@localhost; Please try again.
[14 Sep 2005 1:54]
Peter Laursen
that was better :-)
But in the meantime I have dumped and imported the most important data to the new empty datadir created with the repair install
With the /datadir as of a fresh install it says:
mysql> show grants for root@localhost;
+-------------------------------------------------------------------------------
---------------------------------------------------------+
| Grants for root@localhost
|
+-------------------------------------------------------------------------------
---------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*396
1DAEA01D1F619E1B71B8285A9932A426B5994' WITH GRANT OPTION |
+-------------------------------------------------------------------------------
---------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
However I saved a complete copy of the old datadir. After restarting the server poiting to this old one I get
mysql> show grants for root@localhost;
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------+
| Grants for root@localhost
|
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS,
FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES,
LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW
VIEW ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '44ea98571c75bc6b' WITH
GRANT OPTION |
| GRANT CREATE ROUTINE, ALTER ROUTINE ON `test3`.* TO 'root'@'localhost'
|
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER,
CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, AL
TER ROUTINE ON `nymusik`.* TO 'root'@'localhost' WITH GRANT OPTION
|
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER,
CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, AL
TER ROUTINE ON `musikkopi`.* TO 'root'@'localhost' WITH GRANT OPTION
|
| GRANT CREATE ROUTINE, ALTER ROUTINE ON `test`.* TO 'root'@'localhost'
|
| GRANT CREATE ROUTINE, ALTER ROUTINE ON `test2`.* TO 'root'@'localhost'
|
+-------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------+
6 rows in set (0.00 sec)
mysql>
It must be added that I have granted the privilege to some databases from MySQL Administrator. But obvious not some.
**************
Additional info:
The SP's that I am using was created with 5.0.9 or 5.0.10 I believe. At that time I did not experience any problems.
And these ROUTINES are still functional.
I don't remememeber if I ever tried to create a SP with 5.0.11, but it is likely that I did not.
I'm afraid that this posting is going to look like a mess, but not much I can do about it! :-(
[14 Sep 2005 18:05]
MySQL Verification Team
I was unable to repeat the behavior reported.
[14 Sep 2005 18:10]
Peter Laursen
OK then - let's hold it. After all a fresh install solved the problem after dump and import of data. And the old datadir of mine has been "tweaked" so much now that it is no good for further research into the issue. Thanks everybody!
Description: It seems that ROOT user does not have CREATE_ROUTINE and ALTER_ROUTINE privileges with MySQL 5.0.12. I am not able to create or edit a SP without first granting these privileges to ROOT. I do that using MySQL Administrator "Schema Object Privileges" - as ROOT! Tested on two PC's 1) A freshly installed PC where no earlier mySQL ver. 5 versions has ever been installed installed. 2) On another PC where mySQL ver 5 has been installed since I think 5.03 or 5.04 an continously upgraded. BTW: was is the idea that these privileges can't be global privileges ? How to repeat: mysql> delimiter // mysql> CREATE PROCEDURE `musik`.`goodnews`() -> BEGIN -> set @myvar = "We now have GUI support for Stored Procedures in SQLyog"; -> END// ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'musik' mysql> Suggested fix: Make those privileges available as global priveleges at give them to ROOT by default.