2011/11/05

wish list for 12c

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:



And yet: when you wake up to this sort of view, peace can be one's only thought:






As such, folks:
pacem - and until the next time.

Catchyalata!

9 Comments:

Anonymous Doug Burns said...

Seems like a sound idea and you would think it would help facilitate Oracle's recent cloud obsession.

Of course, I *would* tour the world and claim the invention but I'm not sure I'd quite qualify as a monkey-du-jour ;-)

Monday, November 07, 2011 9:40:00 am  
Blogger Noons said...

I've been using this login trigger technique for more years than I care to remember. For some weird reason, I never ended up doing a post about it! It's a very simple technique and it works like a charm.

But what is really needed is for the context concept and functionality in Oracle to be expanded to include really useful stuff. It's already nearly there, just needs a bit more of a nudge.
;-)

Oh, and: the comment about the simian of the day was not directed at you! Nor at anyone of the folks who run useful blogs with real information. More to the "show ponies" Justin's "team" seems to favour of late.

I'm sure there will be plenty of "haters for life" at Oracle, after this one. As if I ever cared?...

Nuff said, anyway. What do you reckon of those photos and the place? Paradise? ;-)

Monday, November 07, 2011 10:03:00 am  
Anonymous Doug Burns said...

What do you reckon of those photos and the place? Paradise? ;-)

I suppose it's ok if you like that kind of thing, but clearly not a patch on Scotland ;-)

Beautiful photos actually. Just making me feel even colder in London though .....

Tuesday, November 08, 2011 5:13:00 pm  
Blogger Noons said...

:-) Scotland?
I wish you could have tasted the local palm wine brew - called "tuara".

I'm well above normal size and can digest a full glass of scotch without any major side effects.

One glass of the stuff and I was on all fours...

I'm quite sure there is more in it than just alcohol, probably similar to the Fijian kava. Takes a lot more than a glass of rocket fuel to make me do a 4WD impersonation!

Tuesday, November 08, 2011 10:30:00 pm  
Anonymous Doug Burns said...

Sounds like something that would hit my stomach, have a quick look around and pop straight back out! Contrary to popular belief, I'm not actually the greatest drinker of the hard stuff ;-)

Thursday, November 10, 2011 5:43:00 pm  
Blogger Chris said...

Did you try to make a generic logon trigger, like this?

--
-- "application" users people_data_dev and people_data_tst
--
create user people_data_dev identified by test;
create user people_data_tst identified by test;
--
-- application roles
--
create role noons_cs$people_data_dev;
create role noons_cs$people_data_tst;
--
-- logon trigger, checking user against 1st app role
--
create or replace trigger noons_logon_trg
after logon on database
begin
for i in (select granted_role
from dba_role_privs
where granted_role like 'NOONS_CS$%' escape 'x'
and grantee = ora_login_user)
loop
execute immediate
'alter session set current_schema='||
sys.dbms_assert.enquote_name(substr(i.granted_role,10));
exit;
end loop;
end;
/
--
-- demo users
--
create user user_a identified by test;
grant create session,alter session,noons_cs$people_data_dev to user_a;
create user user_b identified by test;
grant create session,alter session,noons_cs$people_data_tst to user_b;
--
-- test
--
conn user_a/test
select sys_context('userenv','current_schema') from dual;
conn user_b/test
select sys_context('userenv','current_schema') from dual;

Tuesday, November 15, 2011 7:42:00 am  
Blogger Noons said...

Very much along those lines. In a nutshell, I use a "logons" table with information on what each login should have.
Case in point: the Hyperion logins need not only a different schema, but also different session init.ora parameters.All that info is stored in the table. All we do is fish out the login from the context, check it against the table and on a match run the required "alter session" commands.

The login table is managed by a small Apex application,which the application managers manipulate themselves and I check from time to time.

Roles are managed simply as independent Oracle roles and assigned to each login as needed, outside the trigger context.

As an example, we have a number of "read-only" Peoplesoft logins for consultants who need to go into the HR tables. They get a read-only role for all the Peoplesoft tables and views, and a login trigger sets them as current_schema=PSMAN. Again: no synonyms in sight - which with Peoplesoft's number of tables would be an absolute nightmare to manage.

I should really make this into a presentation for the Sydney Oracle Meetup group. Maybe early next year.

Tuesday, November 15, 2011 10:29:00 pm  
Blogger Phliso said...

Thanks Noons,

I was having withdrawals, and your Oracle Exadata rant was like pleasant sweet music.

Mr. B

Wednesday, December 05, 2012 10:47:00 pm  
Blogger Noons said...

Ah well, you know how it is: someone else in Oracle marketing hates me now.
All I can say to them is "join the queue!"
:)

Thursday, December 06, 2012 5:10:00 pm  

Post a Comment

<< Home