Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change
 
PerlMonks  

Twin interfaces, and one and a half databases to a project

by JPaul (Hermit)
on Aug 15, 2002 at 01:39 UTC ( #190277=perlmeditation: print w/replies, xml ) Need Help??

Greetings folks;

I have a large project coming up which has a slightly strange request on the table. The project is a custom accounting/management system, with a CGI interface and RDBMS back-end as one would expect.
The chap I'm writing this for is somewhat paranoid (Having worked for a PKI/digital certificate company) and wants me to be able to split, or rather replicate, only part of the database for customer manipulation.

It works like this:
The management system is naturally all accessable by the Administrator. However parts are accessable by customers, so they are able to set up their own accounts if they choose to.
Instead of having some reasonably simple set of CGIs with, if you'll pardon the over-dramatic phrasing, security clearance checks to see who is an Administrator, and who can only access their own personal information, The Man wants something quite different.
He wants to have the entire database and software on one server, and then have PART of the database replicated in a second database on a second server with only a subset of the management CGIs. This second server's DB will only contain information about the users and none of the accounting information, and the CGIs will only be able to modify the users accounts accordingly.

I understand his wants to separate the accounting system out to make it more "secure" - and I can work out a basic system of dualled CGIs and various replicated DB tables... But I'm not sure its the best way to deal with this problem.

What does the Monk population think?

Cheers,
JP

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

  • Comment on Twin interfaces, and one and a half databases to a project

Replies are listed 'Best First'.
Re: Twin interfaces, and one and a half databases to a project
by dws (Chancellor) on Aug 15, 2002 at 02:05 UTC
    He wants to have the entire database and software on one server, and then have PART of the database replicated in a second database on a second server with only a subset of the management CGIs. This second server's DB will only contain information about the users and none of the accounting information, and the CGIs will only be able to modify the users accounts accordingly.

    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

Re: Twin interfaces, and one and a half databases to a project
by Zaxo (Archbishop) on Aug 15, 2002 at 01:54 UTC

    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
Re: Twin interfaces, and one and a half databases to a project
by dthacker (Deacon) on Aug 15, 2002 at 05:38 UTC
    Allow me to comment from the DBA/SysAdmin side of the fence. You haven't specified an RDBMS, I'll use Informix as an example, since I know it well.

    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


    Code On!
Re: Twin interfaces, and one and a half databases to a project
by tjh (Curate) on Aug 15, 2002 at 15:25 UTC
    "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.
    Sounds like you're going to be seeing to the CGI security issues. Some relevent light reading is mentioned in just some of these places:

    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

Re: Twin interfaces, and one and a half databases to a project
by Abigail-II (Bishop) on Aug 15, 2002 at 11:27 UTC
    Having worked as a sysadmin and a DBA, I certainly won't dismiss your managers suggestion. Your webserver is reachable from the outside, and hence it can be compromised. Even if you have the most recent security patches installed the moment they are available. The less data is on it - the better. Replicating only the necessary data to it certainly has a lot of merit.

    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

Re: Twin interfaces, and one and a half databases to a project
by adrianh (Chancellor) on Aug 15, 2002 at 13:21 UTC

    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'.

Re: Twin interfaces, and one and a half databases to a project
by Anonymous Monk on Aug 17, 2002 at 22:42 UTC

    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...

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlmeditation [id://190277]
Approved by newrisedesigns
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (1)
As of 2022-10-02 10:51 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My preferred way to holiday/vacation is:











    Results (8 votes). Check out past polls.

    Notices?