Re: Database architecture and security
Think about how your going to make backups. 1) Would you backup one database with all the mailing lists together ? 2) Would you keep the backups of each user separate ? 3) Could users ask you to restore mailing lists from the past ? You could make one mysqldump for everybody from one database if no one ever asks for restoration of past mailing lists. You could create a database for each user. Then, backup (mysqldump) each database for each user. Should they request a restore, it's becomes an easy thing. How you answer the three questions I posed should help you decide. - Original Message - From: Jason Pruim [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Thursday, August 23, 2007 10:58:52 AM (GMT-0500) America/New_York Subject: Database architecture and security Hi Everyone, Just had a quick question about a database I'm working on. I am planning on having the database open to customers of mine to store their mailing addresses on-line, and be able to manage the records. Is it safe, to have 1 database with lots of tables? Or am I safer setting up separate databases for everyone? I should mention, no one will be accessing the database directly, it'll be through a web interface and php to display it. Any info would be greatly appreciated! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- Rolando A. Edwards MySQL DBA SWMX, Inc. 1 Bridge Street Irvington, NY 10533 (914) 406-8406 (Main) (201) 660-3221 (Mobile) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database architecture and security
I'd never have a separate database for everyone or even a separate table
for everyone. Here's a rough idea of how I'd do it
mysql CREATE TABLE customer (
- `custid` INT NOT NULL AUTO_INCREMENT,
- `lastname` VARCHAR(25) not null,
- `firstname` VARCHAR(25) NOT NULL,
- PRIMARY KEY(custid)
- );
Query OK, 0 rows affected (0.03 sec)
mysql CREATE TABLE address (
- `addressid` INT NOT NULL AUTO_INCREMENT,
- `custid` INT NOT NULL,
- `address` VARCHAR(100) NOT NULL,
- `city` VARCHAR(50),
- `state` CHAR(2) NOT NULL,
- `zip` MEDIUMINT(5) NOT NULL,
- PRIMARY KEY(addressid)
- );
Query OK, 0 rows affected (0.00 sec)
mysql INSERT INTO customer (lastname, firstname) VALUES ('Bolton',
'Mike'), ('Vader', 'Darth');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql SELECT * FROM customer;
++--+---+
| custid | lastname | firstname |
++--+---+
| 1 | Bolton | Mike |
| 2 | Vader| Darth |
++--+---+
2 rows in set (0.00 sec)
mysql INSERT INTO address (custid, address, city, state, zip) VALUES
- (1, '123 house drive.', 'Davie', 'FL', 33314),
- (1, '54325 awesome way', 'Sunrise', 'FL', 33521),
- (2, 'The Death Star', 'SPACE', 'NA', 6);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql SELECT * FROM address;
+---++---+-+---+---+
| addressid | custid | address | city| state | zip |
+---++---+-+---+---+
| 1 | 1 | 123 house drive. | Davie | FL| 33314 |
| 2 | 1 | 54325 awesome way | Sunrise | FL| 33521 |
| 3 | 2 | The Death Star| SPACE | NA| 6 |
+---++---+-+---+---+
3 rows in set (0.00 sec)
mysql SELECT lastname, firstname, address, city, state, zip FROM
customer JOIN address USING (custid);
+--+---+---+-+---+---+
| lastname | firstname | address | city| state | zip |
+--+---+---+-+---+---+
| Bolton | Mike | 123 house drive. | Davie | FL| 33314 |
| Bolton | Mike | 54325 awesome way | Sunrise | FL| 33521 |
| Vader| Darth | The Death Star| SPACE | NA| 6 |
+--+---+---+-+---+---+
3 rows in set (0.01 sec)
mysql SELECT address, city, state, zip FROM customer JOIN address USING
(custid) WHERE (lastname, firstname) = ('Bolton', 'Mike');
+---+-+---+---+
| address | city| state | zip |
+---+-+---+---+
| 123 house drive. | Davie | FL| 33314 |
| 54325 awesome way | Sunrise | FL| 33521 |
+---+-+---+---+
Now each customer/person can have multiple addresses listed.
Jason Pruim wrote:
Hi Everyone,
Just had a quick question about a database I'm working on.
I am planning on having the database open to customers of mine to
store their mailing addresses on-line, and be able to manage the records.
Is it safe, to have 1 database with lots of tables? Or am I safer
setting up separate databases for everyone?
I should mention, no one will be accessing the database directly,
it'll be through a web interface and php to display it.
Any info would be greatly appreciated!
--
Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database architecture and security
On 8/23/07, Jason Pruim [EMAIL PROTECTED] wrote: I am planning on having the database open to customers of mine to store their mailing addresses on-line, and be able to manage the records. Is it safe, to have 1 database with lots of tables? Or am I safer setting up separate databases for everyone? I should mention, no one will be accessing the database directly, it'll be through a web interface and php to display it. Assuming that the web server runs on the same box as the MySQL daemon ... you want to firewall the server so that nobody can connect to the MySQL daemon directly from outside the box. It is also a bad idea to allow the users to have shell accounts on that box unless you have taken additional security precautions (specifically, being sure the MySQL userid/password you're using are secure from all but the web server UID/GID, and that no other userid/passwords have access to the database you're using). Once that is done, all access to the database is controlled by the PHP scripts, and there is no security advantage to having multiple databases. I'm assuming that users have to log in individually (jsmith, bjones, etc.) and that the PHP scripts then carefully control what each user is allowed to modify. I'm also going to assume that you've handled all the obvious technology issues, such as: a)Database transactions/atomic actions. b)Terminating TCP connections and ensuring that each PHP script runs to completion, anyway, and that the database isn't left in an indeterminate state due to this. Dave.
Re: Database architecture and security
On Aug 23, 2007, at 11:44 AM, Gary Josack wrote:
I'd never have a separate database for everyone or even a separate
table for everyone. Here's a rough idea of how I'd do it
mysql CREATE TABLE customer (
- `custid` INT NOT NULL AUTO_INCREMENT,
- `lastname` VARCHAR(25) not null,
- `firstname` VARCHAR(25) NOT NULL,
- PRIMARY KEY(custid)
- );
Query OK, 0 rows affected (0.03 sec)
mysql CREATE TABLE address (
- `addressid` INT NOT NULL AUTO_INCREMENT,
- `custid` INT NOT NULL,
- `address` VARCHAR(100) NOT NULL,
- `city` VARCHAR(50),
- `state` CHAR(2) NOT NULL,
- `zip` MEDIUMINT(5) NOT NULL,
- PRIMARY KEY(addressid)
- );
Query OK, 0 rows affected (0.00 sec)
mysql INSERT INTO customer (lastname, firstname) VALUES ('Bolton',
'Mike'), ('Vader', 'Darth');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql SELECT * FROM customer;
++--+---+
| custid | lastname | firstname |
++--+---+
| 1 | Bolton | Mike |
| 2 | Vader| Darth |
++--+---+
2 rows in set (0.00 sec)
mysql INSERT INTO address (custid, address, city, state, zip) VALUES
- (1, '123 house drive.', 'Davie', 'FL', 33314),
- (1, '54325 awesome way', 'Sunrise', 'FL', 33521),
- (2, 'The Death Star', 'SPACE', 'NA', 6);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql SELECT * FROM address;
+---++---+-+---+---+
| addressid | custid | address | city| state | zip |
+---++---+-+---+---+
| 1 | 1 | 123 house drive. | Davie | FL| 33314 |
| 2 | 1 | 54325 awesome way | Sunrise | FL| 33521 |
| 3 | 2 | The Death Star| SPACE | NA| 6 |
+---++---+-+---+---+
3 rows in set (0.00 sec)
mysql SELECT lastname, firstname, address, city, state, zip FROM
customer JOIN address USING (custid);
+--+---+---+-+---+---+
| lastname | firstname | address | city| state | zip |
+--+---+---+-+---+---+
| Bolton | Mike | 123 house drive. | Davie | FL| 33314 |
| Bolton | Mike | 54325 awesome way | Sunrise | FL| 33521 |
| Vader| Darth | The Death Star| SPACE | NA| 6 |
+--+---+---+-+---+---+
3 rows in set (0.01 sec)
mysql SELECT address, city, state, zip FROM customer JOIN address
USING (custid) WHERE (lastname, firstname) = ('Bolton', 'Mike');
+---+-+---+---+
| address | city| state | zip |
+---+-+---+---+
| 123 house drive. | Davie | FL| 33314 |
| 54325 awesome way | Sunrise | FL| 33521 |
+---+-+---+---+
Now each customer/person can have multiple addresses listed.
I really like the idea of being able to have multiple addresses, some
of our customers right now have lots of seasonal addresses... But
that's a little bit out of my comfort zone right now... I'll add it
to the feature list though and keep your e-mail to reference :)
Thanks! :)
--
Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database architecture and security
On Aug 23, 2007, at 11:28 AM, Rolando Edwards wrote: Think about how your going to make backups. 1) Would you backup one database with all the mailing lists together ? If I went the route of 1 database, Many tables, I would just backup the entire database and all the tables in one shot. Unless Im misunderstanding how MySQL handles the backups, I would think that it would preserve the individual tables? 2) Would you keep the backups of each user separate ? I probably should, but hadn't thought that far ahead yet. Still working on getting the PHP Scripts and everything else set. 3) Could users ask you to restore mailing lists from the past ? The only reason I could see them asking for that is if they went through and deleted the entire database, which has to be done manually, meaning you have to hit delete on each record to delete it. I would hope they would realize what they were doing before going through the entire database... You could make one mysqldump for everybody from one database if no one ever asks for restoration of past mailing lists. You could create a database for each user. Then, backup (mysqldump) each database for each user. Should they request a restore, it's becomes an easy thing. How you answer the three questions I posed should help you decide. I am starting to lean towards the multiple databases with 1 table even though it makes more files on my server, I think it gives me the most flexibility/security. That way I can also edit 1 database and not screw it up for the entire user base, just the one user :) - Original Message - From: Jason Pruim [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Thursday, August 23, 2007 10:58:52 AM (GMT-0500) America/ New_York Subject: Database architecture and security Hi Everyone, Just had a quick question about a database I'm working on. I am planning on having the database open to customers of mine to store their mailing addresses on-line, and be able to manage the records. Is it safe, to have 1 database with lots of tables? Or am I safer setting up separate databases for everyone? I should mention, no one will be accessing the database directly, it'll be through a web interface and php to display it. Any info would be greatly appreciated! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- Rolando A. Edwards MySQL DBA SWMX, Inc. 1 Bridge Street Irvington, NY 10533 (914) 406-8406 (Main) (201) 660-3221 (Mobile) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database architecture and security
On Aug 23, 2007, at 11:50 AM, David T. Ashley wrote: On 8/23/07, Jason Pruim [EMAIL PROTECTED] wrote: I am planning on having the database open to customers of mine to store their mailing addresses on-line, and be able to manage the records. Is it safe, to have 1 database with lots of tables? Or am I safer setting up separate databases for everyone? I should mention, no one will be accessing the database directly, it'll be through a web interface and php to display it. Assuming that the web server runs on the same box as the MySQL daemon ... you want to firewall the server so that nobody can connect to the MySQL daemon directly from outside the box. It is also a bad idea to allow the users to have shell accounts on that box unless you have taken additional security precautions (specifically, being sure the MySQL userid/ password you're using are secure from all but the web server UID/GID, and that no other userid/passwords have access to the database you're using). Once that is done, all access to the database is controlled by the PHP scripts, and there is no security advantage to having multiple databases. I'm assuming that users have to log in individually (jsmith, bjones, etc.) and that the PHP scripts then carefully control what each user is allowed to modify. I'm also going to assume that you've handled all the obvious technology issues, such as: a)Database transactions/atomic actions. b)Terminating TCP connections and ensuring that each PHP script runs to completion, anyway, and that the database isn't left in an indeterminate state due to this. Dave. The server is currently firewalled to block all but the necessary ports from outside the local network. No user, other then myself, and a few admins on the server will have shell access... The MySQL userid/password will be changed once I go live with it, or get into the final testing. What do you mean by b? If all the connections come from the local box how could I configure that to make sure it's all set up so it won't leave the database all messed up? -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database architecture and security
On 8/23/07, Jason Pruim [EMAIL PROTECTED] wrote: b)Terminating TCP connections and ensuring that each PHP script runs to completion, anyway, and that the database isn't left in an indeterminate state due to this. Dave. What do you mean by b? If all the connections come from the local box how could I configure that to make sure it's all set up so it won't leave the database all messed up? I don't know all of the technical details, but if a user uses the STOP button on a browser or the TCP connection is otherwise terminated, there is a feedback mechanism where the PHP script producing the HTML can be terminated (it has no further utility, as it would be feeding a non-existent connection at that point). A worst case is where the PHP script is aborted in a critical section (depending on how transactions are handled in the database) so that the database is left in an inconsistent state. Whether this is possible depends on how you choose to do locking and transactions. The safest approaches I'm aware of are: #1)Form the database results and close the database connection before generating output. #2)Use the PHP function intended for that purpose. Here is the URL for the PHP function: http://us.php.net/manual/en/function.ignore-user-abort.php http://us.php.net/manual/en/features.connection-handling.php Best regards, Dave
RE: Database architecture and security
Personally, I think I'd go with one DATABASE per customer. That way the your code would be the same, and easier to handle. It would be easier to manage the security at the database level, I suspect. I'd set up a ../inc directory outside the web server root that would have one file per customer, and would have the customer-specific variables such as database name, password, and so forth. Each file would be named after a customer. You'd prompt for a user name and password, include the appropriate customer-specific .inc file, check the password against what the user supplied, and if it passed then create a session with the .inc file variables stored as session variables. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Jason Pruim [mailto:[EMAIL PROTECTED] Sent: Thursday, August 23, 2007 10:59 AM To: MySQL List Subject: Database architecture and security Hi Everyone, Just had a quick question about a database I'm working on. I am planning on having the database open to customers of mine to store their mailing addresses on-line, and be able to manage the records. Is it safe, to have 1 database with lots of tables? Or am I safer setting up separate databases for everyone? I should mention, no one will be accessing the database directly, it'll be through a web interface and php to display it. Any info would be greatly appreciated! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database architecture and security
I concur. Also it makes it easier to remove a customer if they leave. Finally your backups will only lock up one customer's database at time and for a much shorter period. On Thu, August 23, 2007 10:50, Jerry Schwartz said: Personally, I think I'd go with one DATABASE per customer. That way the your code would be the same, and easier to handle. It would be easier to manage the security at the database level, I suspect. I'd set up a ../inc directory outside the web server root that would have one file per customer, and would have the customer-specific variables such as database name, password, and so forth. Each file would be named after a customer. You'd prompt for a user name and password, include the appropriate customer-specific .inc file, check the password against what the user supplied, and if it passed then create a session with the .inc file variables stored as session variables. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Jason Pruim [mailto:[EMAIL PROTECTED] Sent: Thursday, August 23, 2007 10:59 AM To: MySQL List Subject: Database architecture and security Hi Everyone, Just had a quick question about a database I'm working on. I am planning on having the database open to customers of mine to store their mailing addresses on-line, and be able to manage the records. Is it safe, to have 1 database with lots of tables? Or am I safer setting up separate databases for everyone? I should mention, no one will be accessing the database directly, it'll be through a web interface and php to display it. Any info would be greatly appreciated! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
