Hello,
I wonder how Scriptcase let you create multi-tenant (saas) applications and if it cannot, how do you handle such needs ?
Many thanks.
Didier
You can do it in 2 different ways.
A discriminatory field in every table, and using this on your WHERE clause in every SQL.
Or a database that works as login point, with information about the database of this customer. Once login is succesfully, then you use sc_edit_connection to “overwrite” connection info to point to the customer database.
Regards.
For me only option 2 is valid. Main issue with option 1 is security. You need to be absolutely sure that you are not mixing the data of different customers. If you have a separate database for each customer your application becomes more safe, secure and it’s less complex to build and maintain.
Option 1 is especially valid for situations where you have to limit your data depending on department, pool etc.
Interesting. I have also been thinking about this.
I will put my software on the internet. I will offer a free trial, so I would expect over time there could be a lot of users.
I have been thinking of creating a new database for each new user, as suggested by Giuseppe and Albert.
Is there a downside to doing this?
I am using MySQL now. Is it OK for lots of users?
Any problems I should think about?
Any suggestions…
@Didier- I agree 100% with Albert. Giu’s option 2 is the way to go. I am doing that right now with no issues.
@jack123 - Check out this interesting thread: http://dba.stackexchange.com/questions/7924/one-big-database-vs-several-smaller-ones and some of the references. The beauty of the separate database approach is that you can easily move clients between servers. Virtual LEMP/LAMP servers are very inexpensive and you can grow easily as demand increases. You might place all your trial accounts on one server, and when someone moves to a paid subscription, move their database to a more robust server. Also, you can have multiple “trial” servers and multiple “paid servers” to balance the load appropriately.
Brad
I would go so far as only sharing the server for “trials” (with separate databases – option 2) . Once an installation became paid, I would move it to an entirely separate server for the following reasons:
Security is just better at a fundamental level
I agree with Albert that option 1 is really only valid for departmental split-up of data within the same organisation
Even option 2 with same server different database may not be really secure. One hears about vulnerabilities all the time. There could be one in MySQL. The application may have files besides the database, which could create vulnerabilities. With a totally separate server that is just precluded.
Updating the application
One might think that it is a hassle to update the application software. However, auto-updating can be built into the application so that it regular checks for new updates at a deployment FTP/SFTP/FTPS or GIT/SVN sever and download these. Of course, handling database changes may require some scripts.
If we are talking many customers, one might have a template machine ready at e.g. Digital Ocean with the basic installation in place, so a new installation can be up and running in minutes.
There is no trouble with some customers using server resources at the expense of others on the same server
When they each have their own separate virtual or physical server they get what they pay for. This is a good selling point as well.
Also, the server can easily be expanded in short order. A server droplet at e.g. Digital Ocean or Amazon can easily be expanded with more CPU cores, memory and disk space.
Thanks for suggesting that thread. It is very interesting, as are the comments and references provided there.
Frank makes a good point about security. Something I still have to find out about…
I must admit that I follow Frank’s approach. Virtual servers are not so expensive any more (at least here in Europe) so I run several of my clients on their own server. Especially in medical care applications that’s (and other privacy depending) essential.
Albert,
What you describe is not a multi-tenant application as each client is on their own server.
NB:
If security and privacy are of utmost importance (when is it not?) then a multi-tenant application is not the way to go.
There are precious few exceptions to that.
NB2:
Even with virtualization, there can be security issues that involve isolation of instances. Hey, such is life.
I know and I agree. However, sharing the same database for everybody is the least secure. Splitting them up is more secure, but if a connection can be tampered then you still are able to meet each others data. Then a separate virtual server is more secure. But at the end nothing is 100%. And yes, that’s life 
When one offers multi-tenant, one may also inherently own the responsibility of the security, i.e. the customer may in fact sue, if breaches were to occur – unless you contract your way out of it, which is sometimes difficult to do with 100% certainty. If, on the other hand, the customer has their own server at an agreed up provider (e.g. Amazon, Digital Ocean, Linode, just to mention a few), it is much easier to contractually let the security be the customer’s own choice and thus inherent responsibility:
They can choose a cheap virtual server (included in the license price) that is quite secure or, if they are very concerned, a physical separate, but also more expensive server, where a monthly operation fee could be added to the licensing price of the application.
As Albert mentions, virtual servers are so cheap now. With the price customers pay per user per month for the application itself, I see no reason to save just a few bucks trying to implement multi-tenant.
If one has an application that uses several databases, it can also get messy with databases such as MySQL that does not have hierarchical schema definition, but just one tier. One then has to use prefixes on the database names as they do with shared hosting. It can easily get messy with several customers and application databases for each. One little mistake in the naming of these (e.g. in a script) and the door might be open. It is just not worth it, in my opinion.
But for trials and testing, etc. its fine.
Each server becomes it’s own maintenance headache. Besides your SC application updates, you would also be responsible for server updates and backups. A few users, this is manageable. 100 users each on their own server, that’s a different situation entirely. That’s where multi-tenant architecture (security issues duly noted) can be beneficial. It is a trade-off.
If anyone is using Digital Ocean, there is a vendor called ServerPilot (https://serverpilot.io/) who will manage Digital Ocean droplet updates for you for a reasonable charge. I have not had the chance to evaluate their service, but plan on doing so in the near future. Although they seem to be marketing themselves now as a CMS hosting company (Wordpress, Drupal, etc.), their LEMP stack offering is what interested me and might appeal to the SC community.
Yeah, if there is only one user per installation, it will run up fast – economically and in administrative workload. In all my eagerness, I was thinking 5-10 or more users per installation.
Sounds interesting.
My thinking on reducing the administrative workload was:
-
As part of the application have a script that automatically download new updates from a deployment FTPS, SVN, or whatever server which in turn is updated with latest tested application updates. Update steps to be written to the local database and a central database on the deployment server.
-
Script automation through SSH calls from a central server to handle operating system updates on the application servers, e.g. Ubuntu Linux’s “apt-get update”. It will be easier if the server is dedicated to just the application, so it has a minimum of packages and dependencies.
I like your thinking. Perhaps some of our more experienced SC developers have done something similar and can share their wisdom.
As said, use the same database maybe is no the best option. But multiple VPS is not needed at all IMHO. A database for login with database information, for connecting to the correct database after login is the way to go. Once app grows, if needed, then you can change this system, but for first steps of the web app, I think is enough.
A new user will create a username and password, and then login.
Does anyone have a good idea on how to then create a new database for that user?
Just catching up to this message. Yes, when a new user is created, you could have a function generate a random string and store it in a column named database_name which is part of your security table. By placing a UNIQUE constraint on the column, you will guarantee than one and only one instance of any database name exists. For example, if a user “Jack” generated a database_name value of “jack123” and stored it in the security table, you could use that value as a database name when accessing SC. As part of the “new user” creation process, you could create the database “jack123” so that it is ready for the next step - login.
Right, but I consider this still as a less secure solution. A simple application mistake could make a breach in the security imho.
So what can one do to make it better?
(A separate server for each user is not practical when there are lots of users, and each uses the application only occasionally).
As you describe your application “lots of users who will use the application occasionally”, then perhaps multi-tenant is the best approach. The support costs of individual servers or databases will be high - perhaps too high for your business case.
I just spoke to a company who is using a multi-tenant approach in a security-sensitive application (I do not know if they are using SC for development, but PHP and other similar technologies are in their application stack). They serve lawyers and healthcare professionals so the risks of a security breach are very real and very costly. They are not a small company, so they have resources to manage infrastructure, develop their software, and manage their legal and insurance needs to protect them as well.
There are some good ideas in this thread on how to reduce the security risk. Another option is to add two-factor authentication to your application. Giu had an interesting idea to add a username field to every record and a where clause in every SQL statement to limit access.
When you solve this problem, please share the magic with all of us. We’d all like to know how to do this!