Perhaps he doesn't understand that most database support the notion of separate logical databases within a physical database (sort of like namespaces). Each database contains a distinct schema, and each database has its own access controls (i.e., grants on a table in one database are kept separate from grants to tables in other databases).

It's easy to set up application to use multiple logical databases. Database users with the correct grants can do queries against tables in separate logical databases (e.g., JOIN queries), while another user might be restricted to seeing only tables in one database. This would work for your application, unless your client has some other concerns that haven't been articulated.

This begs the question, though, of whether it is safe to keep a database on the same box as the web server. For secure applications, the answer is a resounding NO. IIS is notoriously insecure. Even Apache is subject to an occassional exploit. I rant further on this in this thread on storing credit card numbers in databases.

Greetings;

Basic information that, on reflection, would have been useful:
The machine is a Linux machine running Apache w/MySQL... The usual poor mans super-server.

As is my understanding, replication very specifically only works in one direction, master -> slave, and there's a very good reason for this. I'm hoping to perhaps argue him away from this model.
And, (unfortunately|fortunately), this man also happens to be very famaliar with databases, of many different brands.

His notion is for, I suppose, some form of physical security on data - credit card numbers may be stored in the database and he doesn't want the second, "Customer management", server to be able to ever know this information exists...
While I appreciate his paranoia in protecting his customers, the server that he has for his current inelegant management system runs SSH, qmail and Apache... Not what I would usually consider high-risk software packages. With this very limited opportunities for black-hat access, I see the data as being somewhat secure.

But of course, maybe thats why he worked at the PKI company and I never will?

JP,
-- Alexander Widdlemouse undid his bellybutton and his bum dropped off --

the server that he has for his current inelegant management system runs SSH, qmail and Apache... Not what I would usually consider high-risk software packages.

I lost a home linux box to an SSH exploit, and I thought I had it pretty well nailed down, with a firewall that only let SSH and HTTP traffic in.

My earlier advice stands. If you have senstive data, keep it on a separate box.

Replication can be done both ways, regardless of what Sybase or Oracle or whoever are saying. But it isn't easy, you won't find off-the-shelf solutions, and you might not get support from your vendor.

But I've implemented two way replication between 4 (!) Sybase servers, purely on a database level (no non-database programs were written except to present status information).

Don't think it was simple though. In total, it took about 18 months of work. It's hard to get everything right!

Abigail

The Man has cooked up a poor design. It breaks normalization of the database, creating synchronization problems and providing a definite security problem to replace a speculative one.

The solution is dependent on the RDBMS, but starts with providing several levels of privilege in db access to correspond to the cgi groups.

If you can't talk him out of his notion, you may be able to devise an implementation in terms of slave db's mirroring a master copy.

After Compline,
Zaxo

But setting up slave db to mirror a master copy may not work either. I figure that the Man wants only portions of the master being replicated. Further, data boundaries are determined by user priviledges. That is, he doesn't simply want to copy table C and leave tables A and B on the main server. It seems like they want to copy portions of various tables.

But you are right, this is a dangerous path. My best bet would be to definitely create multiple database groups and tablespaces (for Oracle) or separate databases (for MySQL). Have certain cgi scripts run as user Z who's only allowed to access database X. Appears pretty secure to me! :).

Well, the contractor could always ditch the project and search for greener (sane) pastures ;-)

_____________________

# Under Construction
[download]

As DBA I have the ability to control down to the column level, what operations a user can perform on the database through database permissions. I can also define views that are subsets of a table or combinations of several different tables and control access to those. In a customer-input environment, the various CGI scripts connect to the database as users with various levels of priveleges. End users have the lowest, admins have the highest. I'm able to take advantage of the RDBMS's capabilities for locking and transaction integrity as the data is processed.

I've also just completed a medium sized (200+ tables) replications setup for disaster recovery. The most difficult thing to handle in replication is conflict resolution. If the same row is changed on both databases you run the risk of corrupting the data in one or both copies of the table. In many cases you are chasing a conflict after the transaction itself is completed. Informix replication works by "snooping" through the transaction log to find the transactions in the tables you have defined to replicate. Bottlenecks can occur that interfere with the replication process and lead to time intensive re-syncronization. In short, the replication process adds a layer of data management tasks to the app that can be very costly.

If it was me, I'd argue for controlling permissions at the CGI and the database level. I think those tools are better suited to the task at hand. The subset/replication scheme seems like "security through obscurity." Build the security into the database design and the code, and save the replication for disaster recovery.

Dave

"The project is a custom accounting/management system, with a CGI interface ...", "The chap I'm writing this for is somewhat paranoid "

Given the type of system it is, and the obvious sensitivity of the data, not only (IMO) is your employer not paranoid, I would be concerned that the measures aren't going far enough. I hope you purposefully didn't mention other security measures such as firewalls in front of your net connection, firewalls between the front end servers and the separate machines on a separate net (or subnet) that the data lives on, as well as the secure redundancy for failover and backups. Surely he has others working on the hardware configurations, firewall maintenance issues, so to provide these minimum needs as well as many others.

I'm not a network architect or otherwise a system's designer, but I have worked with such systems connected to the internet (or any other net for that matter). Having seen what it can take for even minimal systems, it is very easy to underestimate the needs in hardening such systems.

Instead of having some reasonably simple set of CGIs with, if you'll pardon the over-dramatic phrasing,

But I'm not sure its the best way to deal with this problem.

• The World Wide Web Security FAQ
• Essential CGI Security Practices, very good
• One of Ovid's lessons in Here
• web site design, or lack thereof, excellent
• (OT) Security Rant, must read
• Safeguarding sensitive information
• Security
• How can I secure MySQL & CGI?
• Secure State Maintenance
• A quick search on Super Search for "cgi secure" will yield plenty of solid material.

Given your employer's background, it's likely he's thought of these things (hardware, middleware and custom software issues) regarding privacy, security and his company's substantial risk, and it's significant that you're thinking it through with him. Do well.

hth

Now whether it is the best solution or not, I cannot judge. That depends on a lot of things, more than you can write in a short message like this. You'd have to know more about the data and the services that need to be provided. Competence of the staff is important too - I've seen too many replicating systems not functioning well because the admins didn't have the necessary knowledge.

Abigail

In my experience this sort of setup is not at all unusal when you're dealing with sensitive data. I've implemented this sort of thing. I've also recommended this kind of solution where the security issues required it.

Keeping the more secure info on completely separate box, database, etc. protects your most sensitive data even if the first box is compromised.

While this makes implementation harder (keeping chucks of database in sync, etc.) the issue is security - not ease of implementation :-)

Whether this is sensible for your particular situation depends on the risk assessment of the info in question becoming public.... so it may well be overkill. It's not completely stupid tho'.

Depends a lot on the exact boundary for the split... if it's a simple 'these tables are billing, these tables are customer accounts', then why not use two different DBs? The customer webserver only has access to the DB with customer-modifiable data, and the administrator's webserver has access to both DBs.

Couple of different ways to split it, based on paranoia level:
- two separate database servers on physically (and logically, eg. network-wise!) isolated machines
- two servers on the same machine, which is only marginally better than
- two users on the same server, with permissions set appropriately

Trying to keep a copy of the customer account data in the administrative DB as well seems unnecessary. It won't kill you to have the administrative interface dealing with two different databases.

Hope this helps...

Jeremy Weatherford
xidus@xidus.net
http://xidus.net

I guess I should register here eventually...