How to allow remote access to my database?
I am currently accessing the database by $_SESSION['connection'] = odbc_connect('Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=mysql;Option=3;', 'root', '');
Apart fromt the fact that I need to get a password put on that account (and maybe even use another account than root? And probably change the databse name?), I want to write some code which I can disrtribute to be installed at several sites.
I want to use the same code at each site, although I can't know the IP addresses in advance. How do I go about that? Do I just tell their IT guy to set a DNS trandlation for something like myDatabaseServer
and then substitue Server=localhost
by Server=myDatabaseServer
in the odbc_connect() call? Or do I need to do somehing with the ODBC manager in the Windows control panel?
Hmmm, I should probably obfuscate or comple the code before ditributing it...
Update: I found this question which said
In your MySQL configuration file (my.cnf), you need the following at least:
port = 3306 # Port MySQL listens on
bind-address = 192.168.1.15 # IP address of your server
# skip-networking # This should be commented out to enable networking
I presume thatbind-address
coudl be myDatabaseServer
, so that I don't need to edit the config file for each remote site(?)
It also said GRANT ALL ON test.* TO 'root'@'192.168.1.15' IDENTIFIED BY '';
So that is a command that has to be run onc开发者_JAVA百科e on the databse? But I can't know the IP adress in advance? In fact, multiple users may need access - can I wildcard it to 192.168..? Is thus really, really needed? Is there any way that I can dconfigure it once, not knowing in advance which range of netwrok addresses each user will use (I know that it sounds insecure, but could I just grant access to everyone and rely on password protection, plus no outsiders knowing that the databse is there?)
The bind-address
can only be set to an IP address, not a domain name, and refers to the outgoing IP address that the mysql server will bind to.
However, when connecting externally, you can always just set the bind-address
to 0.0.0.0
that way the mysql server will accept incoming 3306 connections on any interface it has access to. The admin side would look after naming the server, and you can connect to it by that name.
Once that is set correctly you just need the appropriate user credentials set up, so that an external user can connect from a given IP (or pattern).
e.g.
GRANT ALL PRIVILEGES ON *.* TO USERNAME@IP IDENTIFIED BY "PASSWORD";
See here for external IP patterns.
Of course, you should use a limited access account in general, and allow access to only specific database/tables (db.* is usually acceptable)
Just to add to Slomojo's accurate answer: Don't forget to flush privileges;
in mysql after making any changes to privileges.
精彩评论