Designing for database multitenancy and Oracle 12c

I’ve been keeping a low profile over the last few months as I’ve been busy working in a bio-informatics startup, but I wanted to join the Oracle 12c release celebration by discussing database multitenancy and adding some reality check on Oracle’s new option.

So, you are building a database app and want it to be multitenant – having different users and companies trusting you and your shared infrastructure with their data. What are your design options?

Single Database, Shared Schema – I guess that’s the most common one (think of most webapps). There is a big security burden on the app here – a SQL injection vulnerability means game over. There is some positive side-effects – easy to do internal reports across all users and no duplicated objects.

If you use Oracle Enterprise Edition you could potentially use VPD to enforce row-level security (isolation) – safeguarding against standard SQL injections. However, SQL injection bugs in your app could be escalated using database-level zero-days / unpatched vulnerabilities.

Single Database, Multiple Schemas – here you can leverage standard database access control features to isolate different users. As a nice bonus, you can have an additional shared schema for your app metadata tables (so you can do regular joins of your metadata tables with customer tables).SQL injections vulnerabilities could still exploit database vulnerabilities for privilege escalation and accessing other customers data.

Single Instance, Multiple Databases – here you have multiple isolated databases sharing the same runtime (memory, background processes, logs etc). Having worked with Greenplum database, I was getting used to that, as this have been a basic feature of PostgreSQL, SQL Server and many others for a decade or two. In a sense, Oracle CDB/PDB is just very lately catching up. Anyway, the databases are isolated (so no join / access possible to another database), but since they share the runtime, SQL injections vulnerabilities could still exploit database vulnerabilities for privilege escalation.

Different Instances, Shared O/S – if you want to have a more secured isolation, the minimum is to have each customer on a separate instance of the database software with a different O/S user. To break the isolation, attacker needs to find a SQL injection vulnerability, run O/S command and exploit unpatched O/S vulnerability to get root. I’m not sure this type of isolation is being really used, as it is quite clumbersome to manage the environment this way, and it is inferior to the last option:

Different Instances, Virtualized – each customer is in its own VM (cloned from some base image), fully isolated. The VMs could grow, shrink and be moved around to manage the changing workload and different customer sizes. This is the only option in my opinion that give real database level isolation between customers (you still have to worry about shared storage and network, although those could also be virtualized, and of course middleware vulnerabilities).

So, what about 12c CDB/PDB?

Well, outside multitenancy use case, CDB/PDB combo should really help simplify Oracle patching (and is included at no extra cost for Enterprise Edition for a single PDB per CDB). That by itself is a good reason to adopt it eventually, once it proves to be stable.

However, for multitenancy use cases, it is harder to justify it. First, business-wise, this is an extra cost option on top of Enterprise Edition. It is priced 17.5K $ per license, so for a modern 16-core server (8 licenses) it costs an additional 140K $ per node! Even with hypothetical 50% or even 75% discounts, it is very hard to find economical justification for that given the alternatives (especially as it can’t guarantee real isolation). For me, this is where I lost interest… Now, there might be some theoretical savings regarding database management, but if you really design a new multitenancy operation, you will surely invest effort to fully automate everything across all layers, and you could still use the free CDB/PDB features to help you get there.

On the technical side, there are some mysteries. The biggest one is that a single CDB can only hold about 252 PDBs. So, if I have 10,000 medium-sized customers, should I install 40 PDBs? After some thinking about it, it seems to me that maybe this feature was originally designed to throw together a relatively small number of heavy databases on a shared cluster to ease resource contention in RAC environment, not as a new paradigm for database multitenancy.  But your guess is as good as mine.

So, for real multitenant isolation, I believe virtualization will make a lot more sense to most customers. It is a very lightweight and proven technology, could be used across all the layers in the stack (with minimal vendor lock-in), provides resource management, best isolation and is (mostly) free. For less isolated multitenant supporting many thousands of users, seperate schemas might do it, and if there will be millions of customers, shared schema might be the only thing that works.

The only case where I think CDB/PDB could be useful for Oracle multitenancy might be if a customer is already heavily invested in a large RAC cluster that is not virtualized, and is now running into challenges of putting many databases on this shared cluster. In other words, it solves an Exadata-specific challenge – if you are married to Exadata and can’t effectively consolidate, it might be worth the pay once you are convinced 12c is stable enough, so your Exadata environment won’t be under-utilized.

Bottom line – CDB/PDB is a great catch-up that will ease future patching, but I believe the fancier use cases around multitenancy don’t make economical and technical sense. What do you think?

Advertisements

3 thoughts on “Designing for database multitenancy and Oracle 12c

  1. After sleeping over it, here are a few specific questions I’m interested to hear the community / Oracle thoughts:
    – Given the many multitenant design alternatives, what is the main multitenant scenario that this new functionality is aimed at?
    – What is Oracle recommended strategy to support multitenancy for a new app that targets few 1000s to few 10,000s customers? Also, what about millions of customers?
    – Given the best use case for this new extra-cost option, is there an ROI analysis to justify it? (for example, versus a cluster of Oracle VM with Oracle Database Enterprise Edition and no Multitenant / RAC options)

  2. Hi Ofirm,

    I stumbled on your post because I was Googling for what other bloggers think about Oracle Multitenant and if they are comparing it to SQL Server (which is what I was doing in my mind while reading about Oracle Multitenant).

    Hypothetically, I think the feature would see more use if it was not an extra-cost option, and drive quite a few upgrades to 12c.

    Outside of hypotheticals, the only real-world use cases I can see are in environments that, for whatever reason, have multiple databases across many servers but feature very low resource utilization. So for example, 50 databases across 50 servers, with each individual server not being very busy. I could see it as advantageous in such a case to say, build a few RAC clusters and consolidate the whole environment. I could see Multitentant being advantageous in this case as compared to another solution such as virtualization because the management would be much easier (ie. a few big CDB as compared to 50 virtualized non-CDBs). In such a scenario you would also hopefully be making much better use of the hardware you’re paying for, and (maybe?) spend less money on Oracle licenses.

    I think that many Oracle people make a big deal out of Multitenant because they aren’t aware that many other products have been working this way for years. Don’t get me wrong, it is useful and needed in Oracle, but I find it a little amusing how Oracle makes it sound like some kind of revolutionary feature.

    I think though, as I learn the feature more, advantages over say SQL Server will pop-up. For instance, in Oracle you can have a CDB with 50 PDBs. You can backup all 50 PDBs at once by backing up the CDB as a whole. So, it could be more than just a simple “catch up.”

    -Justin

  3. Thanks for the comment Justin!
    I agree with your analysis.
    The way I see it, Oracle promotes it heavily as the main 12 feature because it is the only new extra-cost option in 12c…And the Oracle community eagerly follows, as it is always interesting to have a new toy to play with…. As I wrote, I think CDB/PDB IS a good feature, just one that is very hard to economically justify. The way I see it:
    – The solution to having many under-utilized Oracle servers (expensive) is consolidation to fewer physical nodes – many ways to get there…
    – The solution to managing a lot of Oracle databases (or any other resource) is investment in automation of all monitoring and maintenance.
    Oh, and I agree that Oracle’s implementation might have some advantages. It is still catching up – introducing pluggable database concept more than a decade after other players…
    I’d love to see a more detailed comparison to SQL Server 2012/2014…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s