Monday, January 27, 2014

Upcoming Events...

I took some time off from the road at the beginning of 2014 - getting ready to get back on the road again, lots of trips scheduled from February till the end of June.  This is just looking at public events for the next three months, hope to see you at one of them!

Virtual Developer Day Feb 4th

I'll be speaking, along with Jonathan Lewis, about the last 20 years or so of technology and where we think it is all going as part of the Virtual Developer Day - an entirely online event with many technical speakers.  Something to definitely look into!

RMOUG Feb 6th-7th

At the beginning of February, I'll be at RMOUG Feb 6th and 7th.  Truth be told, this won't be a trip for me - I'm located in Denver, CO now (since the beginning of the year).  I will just be returning from Toronto at the beginning of the week (I know all of the best places to go in February!).

Ohio Feb 11th-13th

Of course, if it is February - it must be Ohio.  That is the best place to fly into and then drive around in during that month :)  I'll be coming down from Ottawa (another 'must see' location in February) and spending Feb 11th in Dayton, Feb 12th in Cincinnati and the 13th in Columbus.  Hitting all of the user groups across the state.

Real World Performance Netherlands, Germany and Bulgaria Feb 18th-21st

Then I'm off to continue the Real World Performance tour with Andrew Holdsworth and Graham Wood.  This month we are hitting the town of Breda in the Netherlands on the 17th, Munich in Germany on the 18th and finally Sofia in Bulgria on the 21st.

Hotsos Symposium Mar 3-6th

If it is March, then it is definitely time for the annual Hotsos Symposium.  I'll be returning this year with a few sessions.  It should be nice and warm in Dallas in March!

Ireland March 11th-12th

I'll be in Ireland on March 11th for the Ireland OUG conference.  I'll be speaking on performance and the new In-Memory capabilities coming in Oracle Database soon.  On the 12th - I'll be delivering a one day Optimizer Master Class.

Real World Performance London and Latvia

The tour continues with a date of March 26th in London and March 28th in Riga Latvia.  

Belgrade Serbia, April 1st-2nd

I'll post more details when I have them, but I'll be doing a two day seminar on the optimizer and developer related topics at this time.  It will be in conjunction with Oracle University so you can monitor their event website for information soon too.

IOUG Collaborate April 7th-11th

I'll be doing a one day optimizer seminar and four other sessions during the week.  April in Las Vegas - perfect time to be there!  And Admin Savage will be speaking too - love the mythbusters!

Oracle Conference on the James, April 24th

I'll be doing a keynote and a technical session in the morning of the 24th for the VOUG and HROUG.  Lots of great speakers here - definitely check this out if you are in the Virginia area!

Manila, Philippines - week of April 28th

I'll be doing some events in Manila this week - details to follow soon!

Friday, November 01, 2013

All day optimizer event....

I've recently taken over some of the responsibilities of Maria Colgan (also known as the "optimizer lady") so she can move onto supporting our new In Memory Database features (note her new twitter handle for that: ).

To that end, I have two one day Optimizer classes scheduled this year (and more to follow next year!).  The first one will be Wednesday November 20th in Northern California.  You can find details for that here: .

The next one will be 5,500 miles (about 8,800 km) away in the UK - in Manchester.  That'll take place immediately following the UKOUG technical conference taking place the first week of December on December 5th.  You can see all of the details for that here:

I know I'll be doing one in Belgrade early next year, probably the first week in April. Stay tuned for details on that and for more to come.

Monday, September 16, 2013

Checking out Adaptive Execution Plans in 12c

Just a short note pointing out a new video on how/when adaptive execution plans happen on Oracle Database 12c:

Sunday, September 15, 2013

Oracle OpenWorld 2013, where I'll be...

I have a fairly busy schedule next week at Oracle OpenWorld 2013.

It all starts on Sunday with a day long seminar.  I'll be doing a series of "five things about" - in the areas of SQL, PL/SQL, Performance and more.  You can check out and register for those sessions (and many others) here:

Next, on Monday I'll be delivering:

Session ID: GEN8579
Session Title: General Session: What’s New in Oracle Database Application Development
Venue / Room: Marriott Marquis - Salon 8
Date and Time: 9/23/13, 12:15 - 13:15

I've done a session like this every year for the last 8 or so years, It'll cover what new Oracle Database Application development techniques have become available over the last year (and will include a few Oracle Database 12c repeats from last year where relevant).

Next, on Tuesday I'll be doing:

Session ID: CON8426
Session Title: The Five Best Things to Happen to SQL
Venue / Room: Moscone South - 103
Date and Time: 9/24/13, 10:30 - 11:30

This is not just the five best things to happen to SQL in Oracle Database 12c - but rather - five of the coolest things to happen to the SQL language over the years.  You might be surprised at how many you did not know about....

And lastly - on Wednesday - the last slot before the appreciation event - I'll be presenting on:

Session ID: CON11637
Session Title: What’s New in Oracle Database 12c
Venue / Room: Moscone South - 103
Date and Time: 9/25/13, 17:00 - 18:00

This is not a repeat of last years "Top 12 Things about Oracle Database 12c", this is all new content - things discovered and found useful in the last year of using Oracle Database 12c.  So, if you have seen my "12 things" talk - or videos - or read the articles in Oracle Magazine, this will be all new and different.

I hope to see you around - I'll be on site all week from Saturday evening until Wednesday night (flying back home on a redeye Wednesday).

Enjoy the show!

Thursday, July 18, 2013

12c - Code Based Access Control (CBAC) part 1

One frequently asked question posed by stored procedure developers since version 7.0 was released in 1992 has been "why do I get an ora-1031 in PL/SQL but not in SQL*Plus directly".  I get this question on asktom a lot, I've written articles about it in Oracle magazine, I must have explained this thousands of times by now.

And now, it all has to change - roles and stored procedures are no longer like matter and anti-matter.  Staring in Oracle Database 12c a role may be granted to a bit of code, and that role will be active only while that procedure is executing.  Think about what that means - you can have a schema chock full of code - hundreds of packages - but have only one package that has a certain privilege at run time.  This will allow you to implement the concept of "least privileges" fully.  What could be more least privileges than granting a privilege to a specific bit of code?

Think about this from a SQL injection protection point of view.  You could either grant privilege X directly to the schema  - meaning this privilege would be available for every single stored unit in that schema to use at anytime, or you can grant this privilege to a role and then grant that role to that unit.  That unit and only that unit would be able to use privilege X at runtime.  If some other units in that schema had a SQL injection bug - they would not be able to utilize that privilege.

We'll take a look at this new capability from two perspectives - from that of a definers rights routine (the default) and from that of an invokers rights routine.  In the case of the definers rights routine, this new capability will only make sense when you use dynamic SQL.  In the case of the invokers rights routine, this new capability has a much larger impact and makes the use of invokers rights routines much wider than it was in the past.  I'll defer talking more about invokers rights routines until next time and we'll concentrate on definers rights routines for now.

Definers rights routines compile with the set of privileges granted directly to the owner of the procedure - roles are never enabled during the compilation of a compiled stored object.  This is true in Oracle Database 12c still - and is the reason this new capability only makes sense with dynamic SQL in a definers rights routine.  In order for the unit to compile, all of the privileges necessary for the static SQL and PL/SQL in the unit must be granted directly to the owner of the unit.  Therefore - any privileges granted via roles cannot be used for static SQL or PL/SQL.  The compilation would fail without the direct privilege.  However, any dynamically executed code would not be security checked until runtime, the compiler would not "see" this code.  And with CBAC - the set of privileges the dynamic SQL will be checked with will be all of the privileges granted directly to the owner of the unit and any privileges associated with roles granted to the unit.

So, if we start with a simple user and role:

ops$tkyte%ORA12CR1> create user a identified by a
  2  default tablespace users
  3  quota unlimited on users;
User created.
ops$tkyte%ORA12CR1> create role create_table_role;
Role created.

and then we grant some privileges to the user and the role:

ops$tkyte%ORA12CR1> grant create table to create_table_role;
Grant succeeded.

ops$tkyte%ORA12CR1> grant create session, create procedure to a;
Grant succeeded.

ops$tkyte%ORA12CR1> grant create_table_role to a with admin option;
Grant succeeded.

ops$tkyte%ORA12CR1> alter user a default role all except create_table_role;
User altered.

we are ready to start. Note that the user A has only the create session and create procedure privilege granted to the directly. They do have the CREATE TABLE privilege, but that privilege is granted via a role to the user - it will not be available to that user during the compilation of a stored unit, nor would it be available at runtime (until we grant it to the code itself).

So, let's create a procedure in this account:

ops$tkyte%ORA12CR1> connect a/a
a%ORA12CR1> create or replace procedure p
  2  as
  3  begin
  4          execute immediate
  5          'create table t ( x int )';
  6  end;
  7  /
Procedure created.

Now, the procedure created successfully since we had the create procedure privilege, but if you try to run it you would receive:

a%ORA12CR1> exec p

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "A.P", line 4
ORA-06512: at line 1

and in fact in 11g and before - that would be the only thing you would ever receive.  You would have to grant CREATE TABLE to the schema A - making it available to every single stored unit in that schema.  But in Oracle Database 12c - we can grant the CREATE_TABLE_ROLE to the procedure:

a%ORA12CR1> set role create_table_role;
Role set.

a%ORA12CR1> grant create_table_role to procedure p;
Grant succeeded.

a%ORA12CR1> exec p

PL/SQL procedure successfully completed.

a%ORA12CR1> set linesize 40
a%ORA12CR1> desc t
 Name              Null?    Type
 ----------------- -------- ------------
 X                          NUMBER(38)

the CREATE TABLE privilege is now available to the stored procedure P and only that stored procedure.  No other bits of code in this schema would be able to create a table.

So, in short, dynamic SQL and PL/SQL executed within a definers rights routine can now take advantage of privileges granted to roles.  This will allow you to implement the concept of "least privileges" (and to use roles in definers rights routines).

In the next article, we'll look at this from the perspective of an invokers rights routine.  That is where this new capability gets really interesting!