Re: MySQL Administrator not working correctly

Can you send the results of the following queries?

select * from mysql.user where user='bob';
select * from mysql.db where user='bob';
select * from mysql.tables_priv where user='bob';

--
Alfredo

On Fri, 21 Jan 2005, Joey wrote:

> OK I have a Fedora Core 3 Box running MySQL-server-4.1.8-0, when I try to
> manage the users on that box using the windows version
> of MySQL administrator version 1.0.19 I get errors.
>
> When I add a new user it tells me:
> error while storing user information
>
> If I look at the mysql log file is shows this:
> 050121 17:19:02   25917 Query       set @new_user='bob'
>                  25917 Query       INSERT INTO mysql.user(User, Host,
> Password) VALUES(@new_user, '%', Password('testtest'))
>                  25917 Query       INSERT INTO mysql.user_info(User,
> Full_name, Description, Email, Contact_information, Icon) VALUES(@new_user,
> 'Bob the tester', '', '', '', '')
>                  25917 Query       DELETE FROM mysql.db WHERE
> User=@new_user
>                  25917 Query       DELETE FROM mysql.tables_priv WHERE
> User=@new_user
>
>
> I create a new schema / db called testing with NO ERRORS
> 050121 17:22:41   25917 Query       CREATE DATABASE `testing`
>                  25917 Query       show databases
>                  25917 Query       show databases
>                  25917 Query       show databases
>                  25917 Query       show databases
>                  25917 Query       show databases
>
> Then I assign security to bob for the db testing:
> And I get error while storing user information
>
> Which shows this in the log:
> 050121 17:23:34   25917 Query       set @new_user='bob'
>                  25917 Query       set @goal_user='bob'
>                  25917 Query       SELECT Full_name, Description, Email,
> Contact_information, Icon FROM mysql.user_info WHERE cast(cast(User AS
> BINARY) AS CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR
> CHARACTER SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(NULL AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'Select_priv' as pn, cast(cast(Select_priv AS BINARY) AS
> CHAR CHARACTER SET utf8) as pv from mysql.user WHERE cast(cast(User AS
> BINARY) AS CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR
> CHARACTER SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(NULL AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'Insert_priv' as pn, cast(cast(Insert_priv AS BINARY) AS
> CHAR CHARACTER SET utf8) as pv from mysql.user WHERE cast(cast(User AS
> BINARY) AS CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR
> CHARACTER SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(NULL AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'Update_priv' as pn, cast(cast(Update_priv AS BINARY) AS
> CHAR CHARACTER SET utf8) as pv from mysql.user WHERE cast(cast(User AS
> BINARY) AS CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR
> CHARACTER SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(NULL AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'Delete_priv' as pn, cast(cast(Delete_priv AS BINARY) AS
> CHAR CHARACTER SET utf8) as pv from mysql.user WHERE cast(cast(User AS
> BINARY) AS CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR
> CHARACTER SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(NULL AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'Create_priv' as pn, cast(cast(Create_priv AS BINARY) AS
> CHAR CHARACTER SET utf8) as pv from mysql.user WHERE cast(cast(User AS
> BINARY) AS CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR
> CHARACTER SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(NULL AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'Drop_priv' as pn, cast(cast(Drop_priv AS BINARY) AS CHAR
> CHARACTER SET utf8) as pv from mysql.user WHERE cast(cast(User AS BINARY) AS
> CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR CHARACTER
> SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(NULL AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'Reload_priv' as pn, cast(cast(Reload_priv AS BINARY) AS
> CHAR CHARACTER SET utf8) as pv from mysql.user WHERE cast(cast(User AS
> BINARY) AS CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR
> CHARACTER SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(NULL AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'Shutdown_priv' as pn, cast(cast(Shutdown_priv AS BINARY)
> AS CHAR CHARACTER SET utf8) as pv from mysql.user WHERE cast(cast(User AS
> BINARY) AS CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR
> CHARACTER SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(NULL AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'Process_priv' as pn, cast(cast(Process_priv AS BINARY) AS
> CHAR CHARACTER SET utf8) as pv from mysql.user WHERE cast(cast(User AS
> BINARY) AS CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR
> CHARACTER SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(NULL AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'File_priv' as pn, cast(cast(File_priv AS BINARY) AS CHAR
> CHARACTER SET utf8) as pv from mysql.user WHERE cast(cast(User AS BINARY) AS
> CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR CHARACTER
> SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(NULL AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'Grant_priv' as pn, cast(cast(Grant_priv AS BINARY) AS CHAR
> CHARACTER SET utf8) as pv from mysql.user WHERE cast(cast(User AS BINARY) AS
> CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR CHARACTER
> SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(NULL AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'References_priv' as pn, cast(cast(References_priv AS
> BINARY) AS CHAR CHARACTER SET utf8) as pv from mysql.user WHERE
> cast(cast(User AS BINARY) AS CHAR CHARACTER SET utf8)=cast(cast(@goal_user
> AS BINARY) AS CHAR CHARACTER SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(NULL AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'Index_priv' as pn, cast(cast(Index_priv AS BINARY) AS CHAR
> CHARACTER SET utf8) as pv from mysql.user WHERE cast(cast(User AS BINARY) AS
> CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR CHARACTER
> SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(NULL AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'Alter_priv' as pn, cast(cast(Alter_priv AS BINARY) AS CHAR
> CHARACTER SET utf8) as pv from mysql.user WHERE cast(cast(User AS BINARY) AS
> CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR CHARACTER
> SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(NULL AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'Show_db_priv' as pn, cast(cast(Show_db_priv AS BINARY) AS
> CHAR CHARACTER SET utf8) as pv from mysql.user WHERE cast(cast(User AS
> BINARY) AS CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR
> CHARACTER SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(NULL AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'Super_priv' as pn, cast(cast(Super_priv AS BINARY) AS CHAR
> CHARACTER SET utf8) as pv from mysql.user WHERE cast(cast(User AS BINARY) AS
> CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR CHARACTER
> SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(NULL AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'Create_tmp_table_priv' as pn,
> cast(cast(Create_tmp_table_priv AS BINARY) AS CHAR CHARACTER SET utf8) as pv
> from mysql.user WHERE cast(cast(User AS BINARY) AS CHAR CHARACTER SET
> utf8)=cast(cast(@goal_user AS BINARY) AS CHAR CHARACTER SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(NULL AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'Lock_tables_priv' as pn, cast(cast(Lock_tables_priv AS
> BINARY) AS CHAR CHARACTER SET utf8) as pv from mysql.user WHERE
> cast(cast(User AS BINARY) AS CHAR CHARACTER SET utf8)=cast(cast(@goal_user
> AS BINARY) AS CHAR CHARACTER SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(NULL AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'Execute_priv' as pn, cast(cast(Execute_priv AS BINARY) AS
> CHAR CHARACTER SET utf8) as pv from mysql.user WHERE cast(cast(User AS
> BINARY) AS CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR
> CHARACTER SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(NULL AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'Repl_slave_priv' as pn, cast(cast(Repl_slave_priv AS
> BINARY) AS CHAR CHARACTER SET utf8) as pv from mysql.user WHERE
> cast(cast(User AS BINARY) AS CHAR CHARACTER SET utf8)=cast(cast(@goal_user
> AS BINARY) AS CHAR CHARACTER SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(NULL AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'Repl_client_priv' as pn, cast(cast(Repl_client_priv AS
> BINARY) AS CHAR CHARACTER SET utf8) as pv from mysql.user WHERE
> cast(cast(User AS BINARY) AS CHAR CHARACTER SET utf8)=cast(cast(@goal_user
> AS BINARY) AS CHAR CHARACTER SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(NULL AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'max_questions' as pn, cast(cast(max_questions AS BINARY)
> AS CHAR CHARACTER SET utf8) as pv from mysql.user WHERE cast(cast(User AS
> BINARY) AS CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR
> CHARACTER SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(NULL AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'max_updates' as pn, cast(cast(max_updates AS BINARY) AS
> CHAR CHARACTER SET utf8) as pv from mysql.user WHERE cast(cast(User AS
> BINARY) AS CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR
> CHARACTER SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(NULL AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'max_connections' as pn, cast(cast(max_connections AS
> BINARY) AS CHAR CHARACTER SET utf8) as pv from mysql.user WHERE
> cast(cast(User AS BINARY) AS CHAR CHARACTER SET utf8)=cast(cast(@goal_user
> AS BINARY) AS CHAR CHARACTER SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(db AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'Select_priv' as pn, cast(cast(Select_priv AS BINARY) AS
> CHAR CHARACTER SET utf8) as pv from mysql.db WHERE cast(cast(User AS BINARY)
> AS CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR
> CHARACTER SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(db AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'Insert_priv' as pn, cast(cast(Insert_priv AS BINARY) AS
> CHAR CHARACTER SET utf8) as pv from mysql.db WHERE cast(cast(User AS BINARY)
> AS CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR
> CHARACTER SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(db AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'Update_priv' as pn, cast(cast(Update_priv AS BINARY) AS
> CHAR CHARACTER SET utf8) as pv from mysql.db WHERE cast(cast(User AS BINARY)
> AS CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR
> CHARACTER SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(db AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'Delete_priv' as pn, cast(cast(Delete_priv AS BINARY) AS
> CHAR CHARACTER SET utf8) as pv from mysql.db WHERE cast(cast(User AS BINARY)
> AS CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR
> CHARACTER SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(db AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'Create_priv' as pn, cast(cast(Create_priv AS BINARY) AS
> CHAR CHARACTER SET utf8) as pv from mysql.db WHERE cast(cast(User AS BINARY)
> AS CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR
> CHARACTER SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(db AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'Drop_priv' as pn, cast(cast(Drop_priv AS BINARY) AS CHAR
> CHARACTER SET utf8) as pv from mysql.db WHERE cast(cast(User AS BINARY) AS
> CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR CHARACTER
> SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(db AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'Grant_priv' as pn, cast(cast(Grant_priv AS BINARY) AS CHAR
> CHARACTER SET utf8) as pv from mysql.db WHERE cast(cast(User AS BINARY) AS
> CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR CHARACTER
> SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(db AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'References_priv' as pn, cast(cast(References_priv AS
> BINARY) AS CHAR CHARACTER SET utf8) as pv from mysql.db WHERE cast(cast(User
> AS BINARY) AS CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS
> CHAR CHARACTER SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(db AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'Index_priv' as pn, cast(cast(Index_priv AS BINARY) AS CHAR
> CHARACTER SET utf8) as pv from mysql.db WHERE cast(cast(User AS BINARY) AS
> CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR CHARACTER
> SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(db AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'Alter_priv' as pn, cast(cast(Alter_priv AS BINARY) AS CHAR
> CHARACTER SET utf8) as pv from mysql.db WHERE cast(cast(User AS BINARY) AS
> CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS CHAR CHARACTER
> SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(db AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'Create_tmp_table_priv' as pn,
> cast(cast(Create_tmp_table_priv AS BINARY) AS CHAR CHARACTER SET utf8) as pv
> from mysql.db WHERE cast(cast(User AS BINARY) AS CHAR CHARACTER SET
> utf8)=cast(cast(@goal_user AS BINARY) AS CHAR CHARACTER SET utf8)
>                  25917 Query       select cast(cast(host AS BINARY) AS CHAR
> CHARACTER SET utf8) as h, cast(cast(db AS BINARY) AS CHAR CHARACTER SET
> utf8) as o, _utf8'Lock_tables_priv' as pn, cast(cast(Lock_tables_priv AS
> BINARY) AS CHAR CHARACTER SET utf8) as pv from mysql.db WHERE cast(cast(User
> AS BINARY) AS CHAR CHARACTER SET utf8)=cast(cast(@goal_user AS BINARY) AS
> CHAR CHARACTER SET utf8)
>                  25917 Query       UPDATE mysql.user SET
> Password=Password('testtest') WHERE User=@new_user
>
> Now the bad part is if this person logs in there are times that things work,
> and there are times where that user can see ALL the DB's on the server.
> Additionally when they go to create a table within their DB the default
> collation is lanin1_swedish_ci which earlier mentioned that that may not be
> good.
>
> I am at a bad point here with the security being compromised and really need
> some help.
>
> Thanks,
>
> Joey
>
>
>
>
>
> -- 
> MySQL GUI Tools Mailing List
> For list archives: http://lists.mysql.com/gui-tools
> To unsubscribe:    http://lists.mysql.com/[email protected]
>

-- 
MySQL GUI Tools Mailing List
For list archives: http://lists.mysql.com/gui-tools
To unsubscribe:    http://lists.mysql.com/[email protected]