New User – Accessing Multiple Databases With Single Login

[peterlaursen]

1) If credentials are different for every database (and you do not have the 'root' user's (or similar) credentials) you will need to create a connection for each of those that you want to access.  What do you think passwords are for? 😛

2) By default MySQL only gives access to users connecting from 'localhost' (the same machine as whre the MySQL server runs).  '%' is a “wildcard” 'any characters'? (so ''user'@'%' may connect 'from everywhere' and 'user'@'123.124.125.%' may connect from any host from the complete ip-range from 123.124.125.0 to 123.124.125.255

[vinyla]

peterlaursen wrote on Jan 3 2007, 02:13 PM:

1) If credentials are different for every database (and you do not have the 'root' user's (or similar) credentials) you will need to create a connection for each of those that you want to access. What do you think passwords are for? 😛

2) By default MySQL only gives access to users connecting from 'localhost' (the same machine as whre the MySQL server runs). '%' is a “wildcard” 'any characters'? (so ''user'@'%' may connect 'from everywhere' and 'user'@'123.124.125.%' may connect from any host from the complete ip-range from 123.124.125.0 to 123.124.125.255

Thanks for the quick response. I was tried setting up a connection using a root user/pass but it comes back with error: Access denied for user 'root' at host XXX (using password: YES). I'm assuming I simply have the wrong user/pass combination here but just in case are there any other factors that could cause the error?

Thanks.

[peterlaursen]

Access denied for user 'root' at host XXX (using password: YES).

Can you create a user 'root'@'%' (or better 'root'@'XXX') and give him all privileges.

Most likely only 'root'@'localhost' exists.

You will of course need to log on as root from localhost to do this …

The SQL from cmd-line is (in case there is no SQLyog there)

Code:

GRANT ALL on *.* to 'root'@'%' WITH GRANT OPTION

this will create the user and give him same priv's as 'root'@'localhost'

[vinyla]

edit: double post.

[vinyla]

I will try what you suggested.

I did download a copy of the webadmin.ini file from the server and it has the following entries under [MYSQL]

[codebox]

user_privileges = SELECT

admin_user_password = XXX

dbo_privileges = SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX,CREATE TEMPORARY TABLES,LOCK TABLES

admin_username = root

[/codebox]

The entry for admin_user_password in this file is the same as the one I tried entering into SQLyog and received the 1045 error.

Do you know the dbo_privileges relate to the privileges for the root user?

Anyways, I'll have a look at the GRANT ALL info and see how that works.

Thanks again.

[vinyla]

Accessed server via SSH [using Putty] and entered the GRANT ALL… command as above but received error: -bash: GRANT command not found.

I must be doing something wrong but can't see what it is. Any further ideas?

Thanks.

[peterlaursen]

the GRANT statement must be issued form inside the 'mysql' client.   the OS does not know abaout it.

Once connected with putty:

1) start the mysql client like “mysql -uroot -p” (you may need to navigate to the /etc folder first).  Also some ISP rename the 'root' user (to 'admin' for instance) for security reasons.

2) you get the mysql> prompt

3) send “use mysql” (most often not required actually!)

4) now “GRANT ….” remember to conclude with a “;” (semicolon)

5) “exit” the mysql client

6) close putty connection

7) now try to connect from a remote host

[vinyla]

peterlaursen wrote on Jan 4 2007, 05:12 AM:

the GRANT statement must be issued form inside the 'mysql' client. the OS does not know abaout it.

Have you guessed my background is Windows yet…

Thank you for taking the time to explain those steps. I'll get a look at it tomorrow hopefully.

The server the system is on is supposed to be a dedicated one but the datacentre have come back saying that it's a shared database. I need to get this clarified as obviously if it's a shared MySQL server they won't be handing out root access but I've got a feeling the helpdesk has either misunderstood my query or have looked at the wrong account number [we have several shared reseller accounts and various dedicated servers with them].

Thanks again.

[Author]

Posts