CompleteMySQL4 is killing me (user problems)

[michaelsanford]

[amras@nimbus ~]% mysql -ujack -pjack1 jacks_db
ERROR 1045: Access denied for user: 'jack@localhost' (Using password: YES)

Jack is configured with GRANT ALL to jacks_db.*, and his user looks like this

*************************** 1. row ***************************
                 Host: %
                 User: jack
             Password: 5d259af02736f264
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: N
            Drop_priv: N
          Reload_priv: Y
        Shutdown_priv: N
         Process_priv: Y
            File_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: N
         Show_db_priv: Y
           Super_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
         Execute_priv: Y
      Repl_slave_priv: Y
     Repl_client_priv: Y
             ssl_type: 
           ssl_cipher: 
          x509_issuer: 
         x509_subject: 
        max_questions: 0
          max_updates: 0
      max_connections: 0

I first used the command line to make the new user, then I tried phpMyAdmin2.4.0

I granted everything to my own user (the username of my shell account) and gave it a blank password, accessible only from localhost. That one works fine, but jack doesn't work at all, not from the shell, not from my local scripts...

Now I'm a total security freak, so I've allocated only privieges to rick to let him see his own database, but I don't want him doing anything outside his DB, or having too much control over my server.

I'm using CompleteMySQL4.0.12 from www.aaronfaby.com

So, any ideas what's going on?

[azrad]

err... actually.. u should have login differently...

for ur case, u using % as the host...
u might want to use this command.

mysql -h <your_ip_adress> -u <username> -p <password>

or the other way around, you have to grant another account for your 'jack' account...

grant select,insert,update...
on db.*
to jack@localhost
identified by '<password>'

i hope this help... =)

[michaelsanford]

I did grants for everything, but the thing is I don't call jack from the command line usually, i use a PHP script with mysql_pconnect() which is where I first noticed that it failed.

Anyway I reverted to another mysql build (see sig) trying to get that to work too hehe.

Also, why do you say I should specify the host name at the command line? I've never done that before, since it's just calling the local mysqld server, and jack is configured with a wilcard host allowance anyway.

Thanks for the input azrad!

[azrad]

ehehe... no probs... cause i had the same problem too last time...

i did used the wildcard host... but end up errors... so i just config it to localhost... (that's why u didn't have to specified the -h variables...) since it connect to localhost by default...

as for PHP coding... i used this coding...

mysql_connect("<your_ip>", "<username>", "<password>");

or in your case

mysql_connect("localhost", "<username>", "<password>");

=)

[michaelsanford]

Yeah I know it connects to localhost by default that's why i was wondering hehe.

The thing is though, I have to let the user log in remotely (they're using a GUI MySQL manager from their own computer) from a dynamic-IP machine. So I can't set it to localhost (well I can but it would be a pain for them).

Also my machine is dynamic IP

Problems, problems, problems hehe

[azrad]

err...

i wish i can come up with good idea...

but u're right, since they use dynamic IP, u can;t set it to localhost... if u set it to localhost, they have to login from the same machine, right?

i wish i can help u with that... maybe if i found it somewhere over the net... i'll let u know... =)