Shared Database Server 2

[dbeez]

Hi,

I am trying to build a php/mysql web app and host it on two Ubuntu linux servers. I want one to handle all of the mysql work and the other to act as the network/php/file server.

I'm fairly new to linux, and as such I am wondering is there anything in particular I should watch out for in this setup ?
But my main question is this, I want the php/network server to be able to build databases on the mysql server. So on the one server I guess it would look like

mysql> create database database;

But is there an easy, quick and safe way for one of my machines to issue orders to the other one to create databases ?

Thanks

 

[thedaver]

That's a perfectly legal PHP call to a mysql server, if you are willing to put your administrator password on the web/app box. Generally that it not done for security purposes.

D.E.R. Management - IT Project Management Consulting

http://www.dermanagement.com/

 

[flugh]

You could limit the scope of the remote mysql user's privileges to a single database, so that if Something Goes Terribly Wrong, only the single database is at risk. Mysql's authentication tables allow for specific user, host, (and maybe?) password combinations.

Not a perfect solution, but better than giving a webserver and scripting languange full control over every file mysql owns on the database server machine ;-)

----
JBR

 

[dbeez]

Hi guys,

Thanks for the replies. Thing is though I need the php/net box to be able to create databases at will on the mysql box. I think my Something Goes Terribly Wrong plan is going to have to involve a blank DVD and a healthy backup schedule I'll set it up as a user with no DROP DATABASE privileges and hope for the best methinks !

Can this be done safely ? Does the php/net box need to connect to the mysql box as root ?I guess this must seem like a fairly obvious question ... but what would the connection string be ?

Do I need to connect to the mysql server over a http connection ? Or could I setup something like a persistant SSL backchannel with a very limited performance hit ?

 

[zeland]

What I usually do is to create a connection file and place it out of your web server's path and later call it using the require() function in PHP.

Your connection file can look like this
<?php
$link = mysql_connect("mysql_server", "root", "password") or die("Could not connect");
?>


--== Anything can go wrong. It's just a matter of how far wrong it will go till people think its right. ==--

 

[thedaver]

Let me suggest an alternative.

You can use an SSH script to create the database using public key authentication from the web/app to the DB server.

That way, you can make a little BASH script to do the work to create the database and potentially add a user/password without having to give away the farm on the web/app server.

Just create a bash script on the db server that does the command:

mysql -uroot --password [theadminpass] -e "create database $1"

Then you can ssh to the script on the DB server and call it as
./domysqldbadd anewdatabase

You can obviously add other calls to add the required user details in another call to another script that creates the necessary access rights.

NOTE: THIS IS NOT A SECURE METHOD EITHER! However, I view it as a considerable trade-up in managing the risk of having your mysql admin password on the highly visible target of the web/app machine. Just be sure to chmod 700 on the bash script on the db server and guard that account sign-on!!!!

HTH.






D.E.R. Management - IT Project Management Consulting

http://www.dermanagement.com/

 

[dbeez]

Thanks for the suggestions guys,

As per option 1) zealand ... I guess this means that I'll be turning safe mode off then ?? or would that be necessary ?? Wouldn't the require() with safe mode off open me up to all kinds of other hacks on the server ? If I can grab files from outside the basedir, what's to stop tom/dick/harry from doing the same ? Unless I'm misinterpreting this ? Is safe mode still essential even if I only have one user/domain on apache ?

As per option 2) thedaver ... so if I get this right. I'd call a ssh script on the mysql server that would set up a database as root (while hiding the admin password), and it would also set up a general php user account that could be used on all databases over http. Why isn't that secure ?

Would this php account then be user 'nobody' as per apache directives ? Should I put a directive in httpd.conf limiting this account to the directory tree ?

Do ye think that it might be worth my while here going with FastCGI and opening each user in it's own process. I want to create 100's of databases here, and still make them all reasonably secure.

It would be nice if one db user could only hack their own database and not other peoples. Would it be possible to create a different user for each database and administer them all as root or superuser ?

Again I know I'm throwing out ideas left-right-center here guys but I'm very interested to learn more about the parameters that I'm dealing with - before I put it all together.

 

[thedaver]

Uh, wow. I'll elaborate on what I was trying to say... Perhaps I left ambiguity..

First, you are ALMOST duplicating what is done in mass-hosting environments....

DB server only contains the databases you create and the MySQL user privileges that you attribute to viewing a given database.

So let's build a scenario:
Real user: Fred
Mysql username: freduser
Mysql database: freddb
Mysql password: fredpass

You implement a suitably secured linux user account on the database server. You DO NOT access as 'root' user. You then implement public key authentication between the web/app and database server. This allows the web/app server to issue a parameterized call to the db server's script.

ssh user@dbserver '/home/secureuser/mydbadd freddb'
ssh user@dbserver '/home/secureuser/myuseradd freddb freduser fredpass'

The scripts themselves are left for you to prepare, but use the 'mysql' command...

So at this point, you have a MySQL database instantiated and you have created a user with privileges to use that database (remember to assign permissions!). You have done so without putting the MySQL Admin password on the web/app server and have not put the db secureuser password on the web/app server either.

I think the rest of your questions are exploring a number of other topics that would be best fielded in the Apache forum itself?





D.E.R. Management - IT Project Management Consulting

http://www.dermanagement.com/

 

[zeland]

IIRC, include(), require() & require_once() work in safe mode. However, thedaver's method of calling an external shell scripts or commands via shell_exec() may not work in safe mode.


--== Anything can go wrong. It's just a matter of how far wrong it will go till people think its right. ==--

 

[thedaver]

Hadn't considered safe_mode restrictions, but frankly you can run your own job queue by stuffing the data into a working table from the web/app server (dbname, username, pass, etc).

Then cron another script to process the data every minute on the db server. Might even be smarter than my previous example (not that I'm smarter,... you get it...)

Anyhow, just create a table that contains the data needed with a status flag whether it was processed. Every minute have cron run a perl/php/python/bash script to query the table for any new jobs/tables to handle. If any, then use the data...

It means that you may be up to 60-ish seconds between when the user submits their request to create the db and when it's ready, but now you've completely avoided vesting the web/app server with anything but the barest necessity. JUST MAKE SURE to scrub the data being queued for SQL injection characters!

D.E.R. Management - IT Project Management Consulting

http://www.dermanagement.com/