Problem with mySQL connection permissions.

[AdmAckbar]

I'm running mySQL locally on my computer. When I run my C# application I have the connect as

string MySQLDBConnect = "DRIVER={MySQL ODBC 3.51 Driver};" +
"SERVER=localhost;" +
"DATABASE=filetransfer;" +
"UID=********;" +
"PASSWORD=********;" +
"OPTION=3";

Everything works fine when it's set at localhost. But that's pretty useless as it onlly works on mmy PC. So instead I'll put in my IP adress. Let's say I change Localhost to 111.111.111.111. Then I get an error.

ERROR #0
Message: [MySQL][ODBC 3.51 Driver] Host '111.111.111.111' is not allowed to connect to this MySQL server.
Native: 1130
Source:
SQL: HY000

I assume this is a problem with my Mysql setup.

The database does get connected. I can tell because I can see the connect going through my firewall. But I get a premission error. I'm guessing it's with my database setup but I couldn't be sure.

 

[sleipnir214]

It's the setup on your MySQL userid.

MySQL user credentials are a 3-tuple of userid, password, and the IP address from which the client is connecting. It looks as though your user is allowed to connect from localhost by not through the IP address of the server.




Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[AdmAckbar]

Ah thanks... I'm new to mysql. How would I change that access permission?

I'll try tonight when I get home to make a new mysql user, give that new user acess to the database, and then alter permissions for that new user. Hopefully it'll be an easy find in the online manual.

 

[sleipnir214]

It is very easy to find an online manual:

http://www.mysql.com

. You might find this page informative.

Every MySQL installation has a database called "mysql", in which is stored the permission tables. You can edit them as you would any other MySQL table. Just be careful of what you mess with and keep in mind that if you update MySQL's permission tables directly, the changes do not take effect until you issue a FLUSH PRIVILEGES query.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[AdmAckbar]

Off topic a bit, let me ask you a security question...

If I'm developing an application for use, and that application will be constantly acessing my database, is ther a best practice on how the database should be connected. I'm not talking about SSL or anything like that. I just mean the login procedure. Like the one I use in MySQLDBConnect above. Should I use any special login protocols that I'm not aware of?

 

[sleipnir214]

I don't know. The applications I create that interact with MySQL use native MySQL communication protocols. Does C# use those or ODBC?


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[AdmAckbar]

I'm using ODBC, using native mySQL commands.

 

[sleipnir214]

You might look at:

http://dev.mysql.com/doc/mysql/en/DSN_on_Windows.html

and

http://dev.mysql.com/doc/mysql/en/ODBC.NET(C-sharp).html

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[AdmAckbar]

Okay, I'm going nuts. It's been a little over two weeks, I've tried different things, and have had no luck. I went away to other projects and now I'm back again and no luck.

I've broken down to using the simplest things. I'm sorry if I sound clueless but this is driving me nuts.

For example, I have two users. root and csharp. It doesn't matterw hich account I use. SO I'll yuse root for an example.

If I run the command from the prompt...

mysqladmin -u root -h localhost -p versions

That works. I enter my password and my version comes up (8.40).

If I run: mysqladmin -u root -h 127.0.0.1 -p versions

That works too.

But, if I run my actual IP address on the same machine, it doesn't work. for example, I'll make one up.

mysqladmin -u root -h 85.96.76.284 -p versions

I get a cannot connect because "access denied to user 'root@85.96.76.284'" Now I understand you have to allow cionnects in the user tables. I've tried the command suggested above. I get the exact same problem with csharp.

I've tried :

mysql> GRANT ALL PRIVILEGES ON db.*
-> TO csharp@'85.96.76.284';

as an example from the page referenced above. I've even gone so far to use '%' But neither one of these worked.

I've also been referencing

http://dev.mysql.com/doc/mysql/en/Access_denied.html

in an attempt to divine a solution. They list my exact problem halfway down.

shell> mysqladmin -u root -pxxxx -h some-hostname ver
Access denied for user 'root'@'' (using password: YES)

But they list multiple solutions, some of which I've tried. Anyway, any help would be appreciated. I want to play around with file transfers.

The way my program should work (it's a console program) is that the user should be able to connect to the database. then he enters a username. The username, ip address, and if he has the test file to transfer is added as a column to the database. The program lists other users connected, and if they have the file or not. The user will select from the list and transfer the file (p2p) from another user. Once he quite from the program, his column in the database is deleted. It's simple but I'm learning here so that's the point.

In either case, I can't test anything if I can't get the program to connect to my mysql unless it's via localhost. SO first I need to solve the problem above, and it's definately with my database setup.

On a second note, Another thing I don't understand is how you set up connections from outside to mysql. For example, I've set '%' in the users table for the csharp user. I assume that means that csharp can connect from any ip number. Since I don't use what IP my program might be used if I take it to a lab or test on a network, I need to make it accessable to all IP's. But is says again and again in the manual, "However, use of hostnames ending with `%' is insecure and is not recommended!" If it's insecure then how do you have a peer connect if you don't know what IP he's coming from?

I know these are a lot of questions but I've wrestled with this for the past week again after getting frustrated the first time. Any help would be greatly appreciated. If I can get this working I will be most thankful. I'm a novice, so I know just enough to get in trouble but not enough to fix it!