New User – Accessing Multiple Databases With Single Login

[vinyla]

I've installed SQLyog v5.21 [Community Edition] and successfully connected to a database on our server [MySQL v4.1.15-standard].

The server has multiple databases each with a different username/password all of which can be seen and browsed using Object Browser on the left side of SQLyog .

I can edit and save the table data for the individual database I supplied the username/password for but would like to be able to do this for each of the databases on the server through a single login. Is it possible to set the user permissions to allow this?

I've taken a look at:

– Tools > User Manager > Manage Permissions and

– Tools > User Manager > Edit User

I think I need to be using the Edit User dialogue as I want open access to all tables/columns within each database. I tried changing the Global Privileges but keep getting Error 1044: Access denied for user XXX to database YYY.

Excuse my ignorance but:

– what are the differences between eg. myDB@% and myDB@localhost in the Edit User: UserName dropdown?

– how [if it is possible] can I set permissions for one login to be able to edit data in each of the databases on the server?

Any help appreciated.

[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