Re: Access denied for GRANT using root yet manual user insertion is fine

With assistance from a friend this issue has been rectified.

The current GRANT was not sufficient. After executing the new GRANT with 'ALL PRIVILEGES' (using the --init-file switch) the root user can now issue GRANTS.

Additionally I apologise for duplicate posts, It was not intentional.

On 9/2/07, Ed Cradock <ed.spam.me+mysql@gmail.com> wrote:
> The grant flag is enabled on the root account:
>
> mysql> SELECT Grant_priv FROM user WHERE User='root' AND Host='localhost';
>
> +------------+
> | Grant_priv |
> +------------+
> | Y |
> +------------+
> 1 row in set (0.01 sec)
>
> 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 ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD
> 'MYSQL5_PASSWORD_HASH' WITH GRANT OPTION |
> 1 row in set (0.00 sec)
>
> When I execute the GRANT on _ANY_ database, I receive the following error:
>
> mysql> GRANT ALL PRIVILEGES ON `foo`.* TO 'root'@'localhost'
> IDENTIFIED BY PASSWORD 'MYSQL5_PASSWORD_HASH';
> ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'foo'
>
> I have tried revoking the grant (with --init-file), then reissuing it.
> This has not remedied it.
>
> It was recently upgraded from MySQL 4, it is currently running on
> mysql Ver 14.12 Distrib 5.0.27.
>
> I have ran 'mysql_fix_privilege_tables'. I have also updated the
> password to use the updated PASSWORD() hash in the mysql.user table,
> but the issue persists.
>
> The root user is fully operational, users can be inserted via the
> mysql.user table, This works without a problem!
>
> Contents of the my.cnf:
>
> # cat /etc/my.cnf
> [mysqld]
> set-variable = max_connections=500
> safe-show-database
> log=/var/log/mysql.log
> old_passwords=0
>
> The server runs on CentOS release 4.5 (Final).
>
> Any assistance to rectifying this will be greatly appreciated.
>
> Ed
>

-- 
MySQL General Mailing List
For list archives: 
http://lists.mysql.com/mysql
To unsubscribe:    
http://lists.mysql.com/mysql?unsub=lists@pantek.com