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
Connected.
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
BEGIN p; END;

*
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>
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!

Wednesday, July 17, 2013

12c - Whitelists...

Starting in Oracle Database 12c - you can limit down to the package/procedure or function level what bits of code may invoke other bits of code in the database.  This process is called "white listing" and can be used to implement the concept of least privileges in your database.

In the past - if a given schema A had packages P1, P2, P3, ... Pn - then any of P1 .. Pn could invoke any function or procedure exposed in the specification of any of P1 .. Pn.  There would be no way to stop one bit of code from invoking any other bit of code.  This could have implications in the area of SQL Injection.  If one of the packages was subject to a SQL Injection bug - then that package could be used to execute ANY of the existing bits of code in that schema.  Additionally - even if none of the packages in that schema had a SQL Injection bug - but the application connected to the database itself did, an attacker could use that bug to execute any bit of code in that schema.

With the white list approach, the only way to execute a given piece of code would be to run it from a specific set of compiled units.  You cannot execute a white listed unit from the top level, it must be called by some specific set of units.  Now a SQL injection bug in the application cannot execute this code (it would have to call it as a top level call - but a white listed unit cannot be called that way).  And even further - a SQL injection bug in the code stored in the database will not be able to execute this white listed code (unless of course it was on the white list).

This is all accomplished with the new "accessible by" clause.  The use of this clause on a unit will restrict the calling set of units to be those in the accessible by clause and the unit itself (a units code is always accessible to itself).

For example, I'll create a package that is to be used only by procedure P1 (and itself) in some schema:

ops$tkyte%ORA12CR1> create or replace package my_pkg
  2  accessible by (p1)
  3  as
  4      procedure p;
  5      function f return number;
  6  end;
  7  /
Package created.

ops$tkyte%ORA12CR1> create or replace package body my_pkg
  2  as
  3
  4  procedure p
  5  is
  6  begin
  7      dbms_output.put_line( 'hello world' );
  8  end;
  9
 10  function f return number
 11  is
 12  begin
 13          p;
 14          return 42;
 15  end;
 16
 17  end;
 18  /
Package body created.

as you can see - I've used the accessible by clause in the package specification.  This will restrict this package to be invoked only by procedure P1 or the package MY_PKG in this same schema. We can see that MY_PKG can invoke itself since function F calls procedure P in that same package.  Additionally - we can see that only procedure P1 outside of MY_PKG can invoke the functionality of this package. For example:

ops$tkyte%ORA12CR1> create or replace procedure p1
  2  as
  3  begin
  4      my_pkg.p;
  5  end;
  6  /

Procedure created.

ops$tkyte%ORA12CR1> create or replace procedure p2
  2  as
  3  begin
  4      my_pkg.p;
  5  end;
  6  /

Warning: Procedure created with compilation errors.

ops$tkyte%ORA12CR1> show errors
Errors for PROCEDURE P2:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/5      PL/SQL: Statement ignored
4/5      PLS-00904: insufficient privilege to access object MY_PKG

Procedure P1 successfully compiles and would be able to invoke MY_PKG.P but P2 cannot.  Furthermore, an attempt to execute MY_PKG as a top level call will fail:

ops$tkyte%ORA12CR1> exec my_pkg.p
BEGIN my_pkg.p; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00904: insufficient privilege to access object MY_PKG
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

It should be noted that the acccessible by clause list is not evaluated at compile time for the unit being protected.  In the above example - we stated MY_PKG would be accessible by P1, before P1 was created.  That means you can put just about anything you want in the accessible by clause without raising an error (so be careful).

The accessible clause can be used across schemas as well.  If we recreate the package specification as:

ops$tkyte%ORA12CR1> create or replace package my_pkg
  2  accessible by (p1,scott.p)
  3  as
  4      procedure p;
  5      function f return number;
  6  end;
  7  /

Package created.

ops$tkyte%ORA12CR1> grant execute on my_pkg to scott;
Grant succeeded.

we'll be able to successfully compile and execute the code from SCOTT.P:

ops$tkyte%ORA12CR1> connect scott/tiger
Connected.
scott%ORA12CR1> create or replace procedure p
  2  as
  3  begin
  4          ops$tkyte.my_pkg.p;
  5  end;
  6  /
Procedure created.

scott%ORA12CR1> exec p
hello world

PL/SQL procedure successfully completed.

scott%ORA12CR1> exec ops$tkyte.my_pkg.p
BEGIN ops$tkyte.my_pkg.p; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00904: insufficient privilege to access object MY_PKG
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

but note that once again - SCOTT cannot invoke this package from the top level either - meaning if the SCOTT schema has some SQL injection issues - we've removed the ability for an attacker to invoke OPS$TKYTE.MY_PKG from that schema.

Next time I'll be taking a look at another new PL/SQL security feature - code based access control, the ability to grant ROLES to code...



Thursday, July 11, 2013

12c - Implicit Result Sets...

Since version 7.2 of Oracle we've been able to return result sets from stored procedures to clients.  The way we've accomplished this in the past is via a REF CURSOR.  I've always liked this approach since it makes it so that the signature of a stored procedure/function makes it clear that a procedure/function a) actually returns a result set and b) can be done such that the 'shape' of the result set is known at compile time.

What that means is - the REF CURSOR would be a formal named parameter for the procedure or the return value of a function.  If you describe the procedure - you'll see it.  You'll know exactly how many result sets the procedure returns and you have the capability to know the number of columns, names of columns and datatypes if the developer chose to use strongly typed ref cursors.  In other words - you'll know what you are getting, the REF CURSORS are explicitly there, staring you in the face.

Other databases adopted an implicit approach.  If you describe their procedures - you will have no idea how many or what kind of result sets they return.  You have to run them and see what they decide to send back - and each time you run them, they could return a different result.  In other words, you had to read the code to see what results might be coming back to you.  I'm not a huge fan of this approach - it is much less self documenting, more error prone (in my opinion).

That said - this difference (explicit versus implicit result sets) can make migrating an application from these databases to Oracle difficult.  You have to change the inputs/outputs of your stored procedures - adding the ref cursors and you have to modify the client code.  You cannot just change the stored procedure, you have to change the client.  When using implicit result sets - the client code would look similar to this psuedo code:

execute stored procedure( param1, param2, .. paramN )
while more result-sets loop
   while more-data-from-that-result-set loop
       process data
   end while more-data-from-that-result-set
end while result-sets

while the code for explict result sets would resemble:

execute stored procedure
( param1, param2, ... paramN, 
  result-set1, result-set2, ... result-setN)

while more-data-in-result-set1 loop
    process data
end while more-data-in-result-set1
...
while more-data-in-result-setN loop
    process data
end while more-data-in-result-setN

so, the structure of the client code must be modified in addition to the stored procedures signature (it's inputs and outputs).  

In order to ease migrations from implicit result set databases to Oracle, Oracle database 12c introduced support for implicit result sets - result sets that can be returned to a client but do not necessitate a formal named parameter.  The client code to process such a result set is identical now - the client code needs not change, the stored procedure signature need not change - the body of the procedure just needs to be implemented in PL/SQL.

We accomplish this magic in PL/SQL via two new API calls in the DBMS_SQL package - one for returning REF CURSORS and one for returning DBMS_SQL cursors.  They are:



PROCEDURE RETURN_RESULT(rc IN OUT SYS_REFCURSOR, to_client IN BOOLEAN DEFAULT TRUE); 
PROCEDURE RETURN_RESULT(rc IN OUT INTEGER, to_client IN BOOLEAN DEFAULT TRUE);

TO_CLIENT => true, return to client layer
TO_CLIENT=> false, return to invoker, immediate caller - could be another plsql routine


to demonstrate this, we can use SQL*Plus as the client and show an implicit result set being returned to the client and auto-magically printed (this will only work with a 12c SQL*Plus!! it is the first SQL*Plus to recognize that there might be result sets to print)


ops$tkyte%ORA12CR1> declare
  2      c1 sys_refcursor;
  3      c2 sys_refcursor;
  4  begin
  5      open c1 for select * from dept;
  6      dbms_sql.return_result(c1);
  7
  8      open c2 for select * from dual;
  9      dbms_sql.return_result(c2);
 10  end;
 11  /

PL/SQL procedure successfully completed.

ResultSet #1

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

ResultSet #2

D
-
X

ops$tkyte%ORA12CR1>

as you can see - there were no inputs/outputs to this block of code - we implicitlly returned the two result sets using the new DBMS_SQL API and SQL*Plus used a method that would result the second set of psuedo code above to discover what results might be available and then print them out.

We can process these implicit result sets in PL/SQL if we want (they don't have to be returned to a client - the ref cursor/dbms_sql cursor can be implicitly returned to a PL/SQL function/procedure) using the two new API calls:

PROCEDURE GET_NEXT_RESULT(c IN INTEGER, rc OUT SYS_REFCURSOR);
PROCEDURE GET_NEXT_RESULT(c IN INTEGER, rc OUT INTEGER);

(see http://docs.oracle.com/cd/E16655_01/appdev.121/e17622/dynamic.htm#LNPLS2176 for a description and example of this API).

But in general, it'll be a client bit of code that processes these result sets and a client java program might resemble:

Connection conn = DriverManager.getConnection(jdbcURL, user, password);
try 
{
    Statement stmt = conn.createStatement (); 
    stmt.executeQuery ( “begin foo; end;” );
 
    while (stmt.getMoreResults())
    {
         ResultSet rs = stmt.getResultSet();
         System.out.println("ResultSet");
         while (rs.next())
         {   
               /* get results */
         }
     }
}

I see Tim Hall has written about this recently as well - you can see his writeup here: http://www.oracle-base.com/articles/12c/implicit-statement-results-12cr1.php



Monday, July 08, 2013

12c - SQL Plus new things....

SQL Plus is still my typical tool of choice to "talk" to Oracle - 25+ years and going...  Oracle Database 12c has introduced a few new things in this venerable old tool

Last Login Time

$ sqlplus /

SQL*Plus: Release 12.1.0.1.0 Production on Sun Jul 7 13:53:15 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Wed Jul 03 2013 14:30:14 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

ops$tkyte%ORA12CR1>

By default, SQL Plus will display your last login time - if you don't want that, just use -nologintime.

More on invisible columns

I recently wrote about invisible columns and how they wouldn't be displayed via a DESCRIBE command.  That is - SQL Plus won't show them by default.  However, there is a SET command that will display them:

ops$tkyte%ORA12CR1> create table t
  2  ( x int,
  3    y int invisible
  4  );

Table created.

ops$tkyte%ORA12CR1> desc t
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 X                                                 NUMBER(38)

ops$tkyte%ORA12CR1> show colinvisible
colinvisible OFF
ops$tkyte%ORA12CR1> set colinvisible ON
ops$tkyte%ORA12CR1> desc t
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 X                                                 NUMBER(38)
 Y (INVISIBLE)                                     NUMBER(38)


Support for Pluggable Databases

There is support for starting pluggable databases from a container database, as well as three new SHOW commands to see what pluggable databases there are and information about the current pluggable database you are connected to:

sys%ORCL> startup pluggable database pdborcl;
Pluggable Database opened.

sys%ORCL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBORCL                        READ WRITE NO



sys%ORCL> connect scott/tiger@pdborcl
Connected.

scott%PDBORCL> show con_id

CON_ID
------------------------------
3

scott%PDBORCL> show con_name

CON_NAME
------------------------------
PDBORCL

For more details...


For more details - check out the SQL Plus guide.  It is always good to look through the existing commands anyway - to remind you of something you forgot you already knew (I do that all a lot - forget things I knew once upon  a time.  Just looking at the table of contents for SQL Plus could remind you of many forgotten SHOW and SET commands!)

Friday, July 05, 2013

In Israel...

I'm in Israel now getting ready for the http://www.iloug.org.il/ conference in Jerusalem next week.  Looks to be a good one with Mark Rittman, Joel Kallman and many others speaking!

See http://www.iloug.org.il/TechDays/?page=BigData_BI#track  for the agenda...

The weather is beyond phenomenal here - see https://twitter.com/OracleAskTom/status/353171810784772098  for a quick picture.

12c - Multiple same column indexes...

Another new capability of Oracle Database 12c is the ability to create more than one index on the same set of attributes.  That is - you can have two or more indexes on the column "X", or the set of columns "A,B,C".

So, that would beg the question "why - why would you want to do that?".  The answer is twofold - testing and availability.  The testing part is obvious - you might want to test how the performance of a bitmap index would be in your reporting system as compared to a b*tree index on the same set of columns.  You can now create that bitmap index even though the b*tree exists, test it out in your session, and then decide to get rid of the b*tree index in favor of the bitmap (or get rid of the bitmap of course).

The availability part isn't as obvious perhaps.  If you wanted to roll out a change to production that would replace a normal b*tree index with a reverse key index - that would have called for an outage in the past.  You would have to drop the original index and then create the new one.  During the period of time the index was initially dropped and the new one completed work, it is likely the applications that depended on that index for data retrieval would have to be offlined.  If they were not - their performance, in light of the missing index, could be disastrous - in effect, a denial of service attack on the database.

In Oracle Database 11g and before - this is what we would expect if we attempted to create more than one index on the same set of attributes:

ops$tkyte%ORA11GR2> create table t ( x int, y int, z int );
Table created.

ops$tkyte%ORA11GR2> create index t_idx on t(x,y);
Index created.

ops$tkyte%ORA11GR2> create bitmap index t_idx2 on t(x,y);
create bitmap index t_idx2 on t(x,y)
                                *
ERROR at line 1:
ORA-01408: such column list already indexed

ops$tkyte%ORA11GR2> create bitmap index t_idx2 on t(x,y) invisible;
create bitmap index t_idx2 on t(x,y) invisible
                                *
ERROR at line 1:
ORA-01408: such column list already indexed


as you can see - it just wasn't going to happen.  But now in 12c - as long as only one index is "visible" - we can create multiple indexes:

ops$tkyte%ORA12CR1> create table t ( x int, y int, z int );
Table created.

ops$tkyte%ORA12CR1> create index t_idx on t(x,y);
Index created.

ops$tkyte%ORA12CR1> create bitmap index t_idx2 on t(x,y) invisible;
Index created.

Now that the second index is there, we can test it - to evaluate the performance for example - easily:

ops$tkyte%ORA12CR1> alter session set optimizer_use_invisible_indexes=true;

Session altered.

ops$tkyte%ORA12CR1> exec dbms_stats.set_table_stats( user, 'T', numrows => 1000000, numblks => 100000 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA12CR1> set autotrace traceonly explain
ops$tkyte%ORA12CR1> select count(*) from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 1106681275

---------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Cost (%CPU)|
---------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |     1 |     0   (0)|
|   1 |  SORT AGGREGATE               |        |     1 |            |
|   2 |   BITMAP CONVERSION COUNT     |        |  1000K|            |
|   3 |    BITMAP INDEX FAST FULL SCAN| T_IDX2 |       |            |
---------------------------------------------------------------------

ops$tkyte%ORA12CR1> set autotrace off

if we deemed that index to be what we really wanted - we would just drop the old index and make this one visible, or set the old one invisible and set this one visible - publish it in effect.

See http://docs.oracle.com/cd/E16655_01/server.121/e17906/chapter1.htm#FEATURENO09740 and especially http://richardfoote.wordpress.com/2013/07/02/12c-intro-to-multiple-indexes-on-same-column-list-repetition/ for more info.  (and subscribe to Richard's blog if you aren't already - best index information out there)


Wednesday, July 03, 2013

12c - flashforward, flashback or see it as of now...

Oracle 9i exposed flashback query to developers for the first time.  The ability to flashback query dates back to version 4 however (it just wasn't exposed).  Every time you run a query in Oracle it is in fact a flashback query - it is what multi-versioning is all about.

However, there was never a flashforward query (well, ok, the workspace manager has this capability - but with lots of extra baggage).  We've never been able to ask a table "what will you look like tomorrow" - but now we do.

The capability is called Temporal Validity.  If you have a table with data that is effective dated - has a "start date" and "end date" column in it - we can now query it using flashback query like syntax.  The twist is - the date we "flashback" to can be in the future.  It works by rewriting the query to transparently the necessary where clause and filter out the right rows for the right period of time - and since you can have records whose start date is in the future - you can query a table and see what it would look like at some future time.

Here is a quick example, we'll start with a table:

ops$tkyte%ORA12CR1> create table addresses
  2  ( empno       number,
  3    addr_data   varchar2(30),
  4    start_date  date,
  5    end_date    date,
  6    period for valid(start_date,end_date)
  7  )
  8  /

Table created.



the new bit is on line 6 (it can be altered into an existing table - so any table  you have with a start/end date column will be a candidate).  The keyword is PERIOD, valid is an identifier I chose - it could have been foobar, valid just sounds nice in the query later.  You identify the columns in your table - or we can create them for you if they don't exist.  Then you just create some data:

ops$tkyte%ORA12CR1> insert into addresses (empno, addr_data, start_date, end_date )
  2  values ( 1234, '123 Main Street', trunc(sysdate-5), trunc(sysdate-2) );

1 row created.

ops$tkyte%ORA12CR1>
ops$tkyte%ORA12CR1> insert into addresses (empno, addr_data, start_date, end_date )
  2  values ( 1234, '456 Fleet Street', trunc(sysdate-1), trunc(sysdate+1) );

1 row created.

ops$tkyte%ORA12CR1>
ops$tkyte%ORA12CR1> insert into addresses (empno, addr_data, start_date, end_date )
  2  values ( 1234, '789 1st Ave', trunc(sysdate+2), null );

1 row created.


and you can either see all of the data:

ops$tkyte%ORA12CR1> select * from addresses;

     EMPNO ADDR_DATA                      START_DAT END_DATE
---------- ------------------------------ --------- ---------
      1234 123 Main Street                27-JUN-13 30-JUN-13
      1234 456 Fleet Street               01-JUL-13 03-JUL-13
      1234 789 1st Ave                    04-JUL-13

or query "as of" some point in time - as  you can see in the predicate section - it is just doing a query rewrite to automate the "where" filters:

ops$tkyte%ORA12CR1> select * from addresses as of period for valid sysdate-3;

     EMPNO ADDR_DATA                      START_DAT END_DATE
---------- ------------------------------ --------- ---------
      1234 123 Main Street                27-JUN-13 30-JUN-13

ops$tkyte%ORA12CR1> @plan
ops$tkyte%ORA12CR1> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID  cthtvvm0dxvva, child number 0
-------------------------------------
select * from addresses as of period for valid sysdate-3

Plan hash value: 3184888728

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| ADDRESSES |     1 |    48 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter((("T"."START_DATE" IS NULL OR
              "T"."START_DATE"<=SYSDATE@!-3) AND ("T"."END_DATE" IS NULL OR
              "T"."END_DATE">SYSDATE@!-3)))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


24 rows selected.

ops$tkyte%ORA12CR1> select * from addresses as of period for valid sysdate;

     EMPNO ADDR_DATA                      START_DAT END_DATE
---------- ------------------------------ --------- ---------
      1234 456 Fleet Street               01-JUL-13 03-JUL-13

ops$tkyte%ORA12CR1> @plan
ops$tkyte%ORA12CR1> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID  26ubyhw9hgk7z, child number 0
-------------------------------------
select * from addresses as of period for valid sysdate

Plan hash value: 3184888728

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| ADDRESSES |     1 |    48 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter((("T"."START_DATE" IS NULL OR
              "T"."START_DATE"<=SYSDATE@!) AND ("T"."END_DATE" IS NULL OR
              "T"."END_DATE">SYSDATE@!)))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


24 rows selected.

ops$tkyte%ORA12CR1> select * from addresses as of period for valid sysdate+3;

     EMPNO ADDR_DATA                      START_DAT END_DATE
---------- ------------------------------ --------- ---------
      1234 789 1st Ave                    04-JUL-13

ops$tkyte%ORA12CR1> @plan
ops$tkyte%ORA12CR1> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID  36bq7shnhc888, child number 0
-------------------------------------
select * from addresses as of period for valid sysdate+3

Plan hash value: 3184888728

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| ADDRESSES |     1 |    48 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter((("T"."START_DATE" IS NULL OR
              "T"."START_DATE"<=SYSDATE@!+3) AND ("T"."END_DATE" IS NULL OR
              "T"."END_DATE">SYSDATE@!+3)))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


24 rows selected.


All in all a nice, easy way to query effective dated information as of a point in time without a complex where clause.  You need to maintain the data - it isn't that a delete will turn into an update the end dates a record or anything - but if you have tables with start/end dates, this will make it much easier to query them.

*Note added 4-jul-2013: this feature currently is not supported/working with the pluggable database infrastructure.  This is a temporary limitation.

Tuesday, July 02, 2013

12c - Silly little trick with invisibility...

This is interesting, if you hide and then unhide a column - it will end up at the "end" of the table.  Consider:

ops$tkyte%ORA12CR1> create table t ( a int, b int, c int );
Table created.

ops$tkyte%ORA12CR1>
ops$tkyte%ORA12CR1> desc t;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 A                                                              NUMBER(38)
 B                                                              NUMBER(38)
 C                                                              NUMBER(38)

ops$tkyte%ORA12CR1> alter table t modify (a invisible);
Table altered.

ops$tkyte%ORA12CR1> alter table t modify (a visible);
Table altered.

ops$tkyte%ORA12CR1> desc t;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 B                                                              NUMBER(38)
 C                                                              NUMBER(38)
 A                                                              NUMBER(38)


Now, that means you can add a column or shuffle them around.  What if we had just added A to the table and really really wanted A to be first.  My first approach would be "that is what editioning views are great at".  If I couldn't use an editioning view for whatever reason - we could shuffle the columns:

ops$tkyte%ORA12CR1> alter table t modify (b invisible);
Table altered.

ops$tkyte%ORA12CR1> alter table t modify (c invisible);
Table altered.

ops$tkyte%ORA12CR1> alter table t modify (b visible);
Table altered.

ops$tkyte%ORA12CR1> alter table t modify (c visible);
Table altered.

ops$tkyte%ORA12CR1>
ops$tkyte%ORA12CR1> desc t;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 A                                                              NUMBER(38)
 B                                                              NUMBER(38)
 C                                                              NUMBER(38)


Note: that could cause some serious invalidations in your database - so make sure you are a) aware of that b) willing to pay that penalty and c) really really really want A to be first in the table!

12c - Invisible Columns...

Remember when 11g first came out and we had "invisible indexes"?  It seemed like a confusing feature - indexes that would be maintained by modifications (hence slowing them down), but would not be used by queries (hence never speeding them up).  But - after you looked at them a while, you could see how they can be useful.  For example - to add an index in a running production system, an index used by the next version of the code to be introduced later that week - but not tested against the queries in version one of the application in place now.  We all know that when you add an index - one of three things can happen - a given query will go much faster, it won't affect a given query at all, or... It will make some untested query go much much slower than it used to.  So - invisible indexes allowed us to modify the schema in a 'safe' manner - hiding the change until we were ready for it.

Invisible columns accomplish the same thing - the ability to introduce a change while minimizing any negative side effects of that change.  Normally when you add a column to a table - any program with a SELECT * would start seeing that column, and programs with an INSERT INTO T VALUES (...) would pretty much immediately break (an INSERT without a list of columns in it).  Now we can add a column to a table in an invisible fashion, the column will not show up in a DESCRIBE command in SQL*Plus, it will not be returned with a SELECT *, it will not be considered in an INSERT INTO T VALUES statement.  It can be accessed by any query that asks for it, it can be populated by an INSERT statement that references it, but you won't see it otherwise.

For example, let's start with a simple two column table:

ops$tkyte%ORA12CR1> create table t
  2  ( x int,
  3    y int
  4  )
  5  /
Table created.

ops$tkyte%ORA12CR1> insert into t values ( 1, 2 );
1 row created.

Now, we will add an invisible column to it:

ops$tkyte%ORA12CR1> alter table t add 
                    ( z int INVISIBLE );
Table altered.

Notice that a DESCRIBE will not show us this column:

ops$tkyte%ORA12CR1> desc t
 Name              Null?    Type
 ----------------- -------- ------------
 X                          NUMBER(38)
 Y                          NUMBER(38)

and existing inserts are unaffected by it:

ops$tkyte%ORA12CR1> insert into t values ( 3, 4 );
1 row created.

A SELECT * won't see it either:

ops$tkyte%ORA12CR1> select * from t;

         X          Y
---------- ----------
         1          2
         3          4

But we have full access to it (in well written programs! The ones that use a column list in the insert and select - never relying on "defaults":

ops$tkyte%ORA12CR1> insert into t (x,y,z) 
                        values ( 5,6,7 );
1 row created.

ops$tkyte%ORA12CR1> select x, y, z from t;
         X          Y          Z
---------- ---------- ----------
         1          2
         3          4
         5          6          7

and when we are sure that we are ready to go with this column, we can just modify it:

ops$tkyte%ORA12CR1> alter table t modify z visible;
Table altered.

ops$tkyte%ORA12CR1> select * from t;

         X          Y          Z
---------- ---------- ----------
         1          2
         3          4
         5          6          7


I will say that a better approach to this - one that is available in 11gR2 and above - would be to use editioning views (part of Edition Based Redefinition - EBR ).  I would rather use EBR over this approach, but in an environment where EBR is not being used, or the editioning views are not in place, this will achieve much the same.

Read these for information on EBR:


Monday, July 01, 2013

12c - SQL Text Expansion

Here is another small but very useful new feature in Oracle Database 12c - SQL Text Expansion.  It will come in handy in two cases:

  1. You are asked to tune what looks like a simple query - maybe a two table join with simple predicates.  But it turns out the two tables are each views of views of views and so on... In other words, you've been asked to 'tune' a 15 page query, not a two liner.
  2. You are asked to take a look at a query against tables with VPD (virtual private database) policies.  In order words, you have no idea what you are trying to 'tune'.
A new function, EXPAND_SQL_TEXT, in the DBMS_UTILITY package makes seeing what the "real" SQL is quite easy. For example - take the common view ALL_USERS - we can now:

ops$tkyte%ORA12CR1> variable x clob

ops$tkyte%ORA12CR1> begin
  2          dbms_utility.expand_sql_text
  3          ( input_sql_text => 'select * from all_users',
  4            output_sql_text => :x );
  5  end;
  6  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA12CR1> print x

X
--------------------------------------------------------------------------------
SELECT "A1"."USERNAME" "USERNAME","A1"."USER_ID" "USER_ID","A1"."CREATED" "CREAT
ED","A1"."COMMON" "COMMON" FROM  (SELECT "A4"."NAME" "USERNAME","A4"."USER#" "US
ER_ID","A4"."CTIME" "CREATED",DECODE(BITAND("A4"."SPARE1",128),128,'YES','NO') "
COMMON" FROM "SYS"."USER$" "A4","SYS"."TS$" "A3","SYS"."TS$" "A2" WHERE "A4"."DA
TATS#"="A3"."TS#" AND "A4"."TEMPTS#"="A2"."TS#" AND "A4"."TYPE#"=1) "A1"

Now it is easy to see what query is really being executed at runtime - regardless of how many views of views you might have.  You can see the expanded text - and that will probably lead you to the conclusion that maybe that 27 table join to 25 tables you don't even care about might better be written as a two table join.

Further, if you've ever tried to figure out what a VPD policy might be doing to your SQL, you know it was hard to do at best.  Christian Antognini wrote up a way to sort of see it - but you never get to see the entire SQL statement: http://www.antognini.ch/2010/02/tracing-vpd-predicates/.  But now with this function - it becomes rather trivial to see the expanded SQL - after the VPD has been applied.  We can see this by setting up a small table with a VPD policy 

ops$tkyte%ORA12CR1> create table my_table
  2  (  data        varchar2(30),
  3     OWNER       varchar2(30) default USER
  4  )
  5  /
Table created.

ops$tkyte%ORA12CR1> create or replace
  2  function my_security_function( p_schema in varchar2,
  3                                 p_object in varchar2 )
  4  return varchar2
  5  as
  6  begin
  7     return 'owner = USER';
  8  end;
  9  /
Function created.

ops$tkyte%ORA12CR1> begin
  2     dbms_rls.add_policy
  3     ( object_schema   => user,
  4       object_name     => 'MY_TABLE',
  5       policy_name     => 'MY_POLICY',
  6       function_schema => user,
  7       policy_function => 'My_Security_Function',
  8       statement_types => 'select, insert, update, delete' ,
  9       update_check    => TRUE );
 10  end;
 11  /
PL/SQL procedure successfully completed.

And then expanding a query against it:

ops$tkyte%ORA12CR1> begin
  2          dbms_utility.expand_sql_text
  3          ( input_sql_text => 'select * from my_table',
  4            output_sql_text => :x );
  5  end;
  6  /
PL/SQL procedure successfully completed.

ops$tkyte%ORA12CR1> print x

X
--------------------------------------------------------------------------------
SELECT "A1"."DATA" "DATA","A1"."OWNER" "OWNER" FROM  (SELECT "A2"."DATA" "DATA",
"A2"."OWNER" "OWNER" FROM "OPS$TKYTE"."MY_TABLE" "A2" WHERE "A2"."OWNER"=USER@!)
 "A1"

Not an earth shattering new feature - but extremely useful in certain cases.  I know I'll be using it when someone asks me to look at a query that looks simple but has a twenty page plan associated with it!