DA's-R-Us: 2011/11

Back in 2008 my wish list included sparse object allocation (deferred allocation in Oracle's translation). And bingo, I got it in 11gr2! It was almost as if someone came here for inspiration!

Prior to that in my "No Moore" series I actually described the Exadata architecture, long before it became public.

Not that such a thing would ever be acknowledged by Oracle, we know that! After all, I refused twice to become an Ace - and other such idiotic "rewards" Oracle thinks are important.
There is simply no way someone like me can possibly contribute anything of value to the product, is there now?

Still....    In the off chance that anyone with half a brain is listening - and before it gets claimed as someone else's idea - here is something that would be quite welcome.

Recently I visited Tyler Muth's blog for one of his posts on storage performance. I've known Tyler through the net for years and his messages are always full of great sense and value. I always make time to read what he posts. This time, while there I also noticed and commented on this.

Now, this is a subject that is dear to me.

I always had a great revulsion for the thousands and thousands of synonyms I see in many sites. This is usually the case with sites who try to run multiple applications in a single instance, each under its own schema.

The problem of course is that you have to create an exclusion strategy to use synonyms.

You either use private synonyms - one set for each authorized application login - which usually results in millions of the little blighters.

Or you use public synonyms, one table at a time. Presuming of course that two or more applications in that instance won't use tables of the same name. Nine times out of ten you end up with a need for duplicate public synonyms, which of course is an impossibility...

Either way you end up with horrible maintenance overhead for setting up and keeping all the synonyms, assuming you can bypass the limitations of their use.

As well as potentially bad performance: SQL using synonyms is not always the best performing one. Steve Adams pointed that problem out ages ago.

Now the problem here is that we want to provide logins that will "see" the application schema tables, without the need for the
<owner>.<table name>
syntax to be used in every SQL, and/or zillions of synonyms - public or otherwise.

You don't want that syntax as it will prevent you from running multiple copies of the SQL and application in a development database, for example: you most definitely don't want to go messing around with changing the SQL syntax itself!

One "traditional" way to do so - at least one that is often recommended - is to use proxy logins, available in the latest versions of Oracle.

The problem with those is that a proxy login does not preserve any roles that you may have assigned to the proxy: the session "becomes" the target login, with the target's access rights!

Call me weird if you want, but having all logins become the application owner might constitute a slight security problem?

So: what do I do to circumvent all these problems? I tried to explain in the reply at Tyler's, but here I'm detailing it a bit more.

Let's assume one application whose schema is called "PEOPLE_DATA". And a couple user logins - or application logins - that will be called "PEOPLE_A", "PEOPLE_B". Let's assume also that this application has all its SQL written without the <owner>.<table name> object naming syntax.

We might want PEOPLE_B to have only read access to PEOPLE_DATA, while PEOPLE_A has read and write.

OK, how do I then go about this without recoding the entire application or creating a synonym storm?

Simple.

Create a database login trigger that recognizes the PEOPLE_A and PEOPLE_B login strings by looking at the SYS_CONTEXT contents. I then simply do a

execute immediate 'ALTER SESSION SET CURRENT_SCHEMA=PEOPLE_DATA' ;

in my login trigger's PL/SQL code.

What does this do? It keeps the original login active as PEOPLE_A or PEOPLE_B - with all its rights and roles intact - but assumes from now on for all SQL that the default schema is PEOPLE_DATA.
This effectively sets the default <owner> portion of all SQL for that schema.

And I can still create a role that grants read/write on all tables of PEOPLE_DATA schema and another to read only, and grant them respectively to PEOPLE_A and PEOPLE_B.
Unlike a proxy where the login user becomes another user, the session change of CURRENT_SCHEMA only alters the default SQL syntax: all other profiles, grants and roles associated with the logins stay as they were before!

PEOPLE_A and PEOPLE_B can now execute the application code without the need for a single synonym, under full control of the roles I created for them!

This now means that if I want to have two copies of PEOPLE_DATA in my development database, I can name their schemas (owners) PEOPLE_DATA_DEV and PEOPLE_DATA_TST.
And I can then have PEOPLE_A_DEV and PEOPLE_A_TST logins that are recognized by the trigger and set the current_schema to PEOPLE_DATA_DEV accordingly. And so on for TST.

Now, some might want to create a straw man and claim that for thousands of logins this would make for a terribly complex login trigger. To which my response is: care to explain how creating thousands of synonyms is simpler?
As well: bear in mind that most modern, large user applications do NOT login to the database, user by user.    They login through a set of pooled connections from an app server, which use a global login. It is this global login that needs to be in the trigger!

I have now sucessfully setup various copies of our DW, Hyperion, and other schemas and applications in a single instance, without the need for a single synonym anywhere. And with complete control through roles of every application login and what it can see.

But - and this is the subject of this wish list! - I recognize that it is not the ideal process!

To me, the ideal would be for Oracle to let me create multiple contexts, each allowing me to set a CURRENT_SCHEMA - and roles as well!- and then just grant the context(s) to the respective logins as part of their profile.

Unfortunately the context facility in Oracle- as of 11gr2 - does not allow me to set session parameters, or other useful stuff like roles.

Badly needed!

Because then we don't need login triggers: all we need is to set a default context for each login type and we have absolute control over what can be seen by whom, without a single synonym anywhere - other than cross-application ones which are perfectly legitimate!

Then, Oracle will truly support multiple application consolidation into single instances. As is now, it's a bit of a joke, quite frankly...

(Yeah, yeah, I know: RAC solves all that. And it brews coffee, too. Go back to the start and re-read it all, if that's what you think!)

How about it, Oracle?   Here is something that would make early adoption of 12c a lot more desirable!
And look: given this is coming from a non-Ace site you can even find a way to invent some "monkey-du-jour" as the author of the idea and make him/her the travelling sales rep for the concept?

Because I really don't have the time nor the inclination for travelling, you know it! Been there, done that: incompatible with family life and I value that above all.

:-)

Speaking of which (travel, I mean): here are a few from this year's holiday trip to my youth paradise.

The nursery: Areia Branca beach and its sensational reefs:

Lovely place. I always spend entire days here snorkelling around all those reefs and drop-offs. Always lots to see.

Have you ever heard of giant clams? The ones that in cheap Hollywood movies always "snag" a diver's foot and "drown" them? This is what one looks like, when it is alive:

That baby was nearly one metre across! And it was only about 10 metres from the shore, at Jaco National Park. The mottled grey stuff is the mantle, spread all around the two shells. It is light sensitive, so if you wave a hand over it, it reacts!

Speaking of Jaco, this is what a sunrise looks like from over there:

Jaco is the little island on the extreme right. Moments like this make it all worthwhile, believe me!

Matebian, a 2300metre high coastal mountain that many hold sacred due to the thousands that were killed here by the Indonesian invading military, back in the 70s and 80s: