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

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.

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

Replies are listed 'Best First'.
Re: Re: Twin interfaces, and one and a half databases to a project
by JPaul (Hermit) on Aug 15, 2002 at 04:56 UTC
    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 --

[reply]
      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.

[reply]
      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

[reply]
        That sounds pretty impressive. And you did it purely in transac-sql? No external scripts? Wow.

        Yves / DeMerphq
        ---
        Software Engineering is Programming when you can't. -- E. W. Dijkstra (RIP)

[reply]

In Section Meditations