Access

MySQL Administration (Remote Access)

So, once you’re all set up and running, you may want to give users access from the outside world. This is clearly an entry point for hackers and script kiddies, so be sure that you want to open up your database to the net.

I believe that, out of the box, MySQL is bound to the localhost IP address (127.0.0.1) so that will need changing first of all.

Edit my.cnf and find the line ‘bind-address’:

bind-address=127.0.0.1

Now change this to allow access from other IP addresses, if you want *all* access then change it to 0.0.0.0 or comment it out completely. You can only set it to one IP address, or all. (as shown)

Ensure your users can connect from the IP address you set it to:

GRANT <privileges> TO 'user'@'<ipaddress> IDENTIFIED BY '<password>';

for example

GRANT SELECT,INSERT,DELETE,UPDATE TO 'user1'@'203.122.211.90' IDENTIFIED BY 'letm3in';

Or, to all this user access from anywhere:

GRANT SELECT,INSERT,DELETE,UPDATE TO 'user1'@'%' IDENTIFIED BY 'letm3in';

Finally, restart the server and you’re done.

Let me just re-iterate, ensure you will be able to connect to the database from anywhere before restarting the server. Don’t blame me if you lock down a remote server with no access to it!

MyODBC and Access – Urgh

Well. I have had to sort out a report for someone who likes using Microsoft Access.

We use the MySQL ODBC driver, called MyODBC Connector. But its relationship with Access is riddled with bugs. Can’t decide who to blame, but its pretty horrific.

So I created a blank Access database and connected the tables I required for my report. Alas, one of the fields has a name length of 16 or so characters, so this crashes Access completely. Great.

I made a copy of the MySQL table and renamed it to something shorter. This worked. So, I needed to alias the table somehow.

I created a VIEW in MySQL with a shorter name:

CREATE VIEW smallname SELECT * FROM longernamedtable;

I was able to link this in Access fine. Not a problem, however, all the VARCHAR fields in the view came out as chinese characters. AARGH

So, I managed to utililise the MERGE engine. Here goes:

CREATE TABLE smallname SELECT * FROM longernamedtable;
DELETE FROM smallname;
ALTER TABLE smallname ENGINE=MERGE;
ALTER TABLE smallname UNION=(longernamedtable);

Now, we have a mirrored table. Brilliant. It linked to Access fine, and the VARCHAR’s were back to normal.

Thought I would share that with you. Because this little problem was starting to drain all the life out of me…

 

PS. The tables need to be ISAM for this to work.

I wrote one of the first social networking sites

Its not a claim to fame, or a preempt to a court case to a ‘big hitter’, but back in the year 2000, when I was a fledgling junior programmer, I wrote an ASP web site based around a bunch of ‘buddies’. You could add people to your list, message them, upload a picture (just the one) of yourself, and view other’s profiles.

This sounds very familiar, right?

Continue reading