28 September 2014

DBMS_REDACT and complete ROW update

My session on "Oracle 12c for Developers" is done. Afterwards someone asks the question:

What happens when you use DBMS_REDACT with a complete row update?
My guess was that it would place the redacted data in the column, but I haven't tried it, so here goes:

create table emp
as
select ename
      ,to_char (abs (dbms_random.random)) credit_card
  from scott.emp
;


begin
   dbms_redact.add_policy
     (object_schema  => 'A'
     ,object_name    => 'EMP'
     ,policy_name    => 'Hide Creditcard'
     ,expression     => '1=1'
     ,column_name    => 'CREDIT_CARD'  
     ,function_type  => dbms_redact.regexp
     ,regexp_pattern => dbms_redact.re_pattern_any_digit
     ,regexp_replace_string => 'X'
     );
end;
/

First to create a test table (of course called EMP) in schema "A". Next to place a DBMS_REDACT policy on it which replaces the credit_card information with X for each digit.
Just to verify that it works:

SQL> select *
  2    from a.emp
  3  /

ENAME      CREDIT_CARD
---------- --------------------
SMITH      XXXXXXXXXX
ALLEN      XXXXXXXXX
WARD       XXXXXXXXXX
JONES      XXXXXXXXX
MARTIN     XXXXXXXXX
BLAKE      XXXXXXXXXX
CLARK      XXXXXXXXXX
SCOTT      XXXXXXXXX
KING       XXXXXXXXXX
TURNER     XXXXXXXXXX
ADAMS      XXXXXXXX
JAMES      XXXXXXXXX
FORD       XXXXXXXXX
MILLER     XXXXXXXXX

14 rows selected.

Now for the row update without actually changing any data.

SQL> declare
  2 l_emp a.emp%rowtype;
  3  begin
  4 select ename, credit_card
  5   into l_emp.ename, l_emp.credit_card
  6   from a.emp
  7   where ename ='SMITH';
  8 update a.emp
  9    set row = l_emp
 10 where ename = 'SMITH';
 11  end;

PL/SQL procedure successfully completed.

The code above will update the information for SMITH without altering the data.
And now to unveil the actual data which is in the table, first remove the redaction

SQL> begin
  2    dbms_redact.drop_policy (object_schema => 'A'
  3          ,object_name => 'EMP'
  4          ,policy_name => 'Hide Creditcard');
  5  end;
  6 /

PL/SQL procedure successfully completed.

and inspect the data:

SQL> select ename
  2        ,credit_card
  3    from a.emp
  4  /

ENAME      CREDIT_CARD
---------- --------------------
SMITH      XXXXXXXXXX
ALLEN      146486740
WARD       1079838967
JONES      204239028
MARTIN     784659193
BLAKE      2086063983
CLARK      1949626638
SCOTT      736597519
KING       1541186772
TURNER     1456281762
ADAMS      61726886
JAMES      729938493
FORD       238314859
MILLER     714890479

14 rows selected.
And there you have it.. looks like we lost some information along the way.

23 September 2014

"Busy Button" with APEX5, jQuery and Font Awesome

Both jQuery and Font Awesome are standard included with APEX5 (still in early adopter). With a little bit of jQuery you can create an animated button that reflects that it is doing something in the background.
For this example I created a button "Text + Icon button". Simply drag and drop this in the Page Designer.
The Icon CSS Class: "fa-play-circle-o"
and the action: "Defined by Dynamic Action"
For the Dynamic Action: it should respond to the button click (of course)
Choose: Execute Javascript and enter:

$(this.triggeringElement).prop('disabled', true)
                         .children('span')
                         .toggleClass('fa-repeat')
                         .toggleClass('fa-spin')
                         .toggleClass('fa-play-circle-o')
                         ;

Add another TRUE action to the Dynamic Action, for my example I'll use "Execute Javascript" and enter:

alert ('You clicked the button, now it looks busy');

The third TRUE action for the Dynamic Action, also "Execute Javascript" will reset the button to the way it was:

$(this.triggeringElement).prop('disabled', false)
                         .children('span')
                         .toggleClass('fa-repeat')
                         .toggleClass('fa-spin')
                         .toggleClass('fa-play-circle-o')
                         ;

See an example here: Early Adopter APEX; login with "demo" and password "demo".

22 September 2014

Dynamic Action in Report - APEX5 version with Font Awesome

Almost two years ago, I wrote a little blog on how to trigger a Dynamic Action from a report. You can find that blog right here.
Things have changed with APEX5 (which is currently still in "early adopter 2") which allow you to do this in a more clean way (or at least I think so). No need to create a "fake link" so the user know that the icon is clickable. No need to upload your own images, use the already shipped Font Awesome library.

Based on that old blogpost I reused the same table structure and the same PL/SQL procedure. The only change I made was to the query on which the report is based:

select id tsk_id
      ,case ind_complete
       when 'Y' then 'up'
       when 'N' then 'down'
       end ind_complete
      ,what
      ,complete_before
  from tasks
Instead of "ok" and "nok" for the "IND_COMPLETE" column, I am using "up" and "down". These names will be used to get the correct Font Awesome icon. If you want to use different icons, check the Font Awesome Cheatsheet, version 4.0.3 (the version currently used by the early adopter.

Instead of having to create a Link Column, linking to a dummy page like Page 0, the IND_COMPLETE column can stay a "Plain Text" column.
Adjust the HTML expression for that column to:

<span class="t-Icon fa-thumbs-o-#IND_COMPLETE# setComplete" id="#TSK_ID#" style="cursor: pointer;" ></span>
The class added to the IND_COMPLETE column will contain the reference to the Font Awesome icons you want to show. The class "setComplete" is there to have the Dynamic Action fire when the column is clicked. The id reference is there, so the Dynamic Action will know which ID to update in the table. Finally styling the cursor so the user will know that the icon is clickable.

Next the Dynamic Action. The Dynamic Action is basically the same as in the original version:

  1. Set the value of the clicked element in a hidden item
  2. Execute the stored Procedure
  3. Refresh the report
As far as the last point goes, there was a comment in the original blog which suggests using the "Submit Page" action because the pagination will return to the first set. I found a plugin to do a refresh which remembers the pagination, unfortunately it doesn't play well with APEX5.

And just for fun, add some CSS styling at page level:

.fa-thumbs-o-up{
  color: #2580D4;
}

.fa-thumbs-o-down {
  color:red;
  font-size: 20px;
}
That will really make the Font Awesome icons stand out :)

I put a small demo on the Early Adoptor site.

18 September 2014

Oracle 12c: Temporal Validity, multiple on one table

During a trial run for my presentation at Oracle Open World "Oracle 12c for Developers", you can find the slides on slideshare. there was a question regarding "Temporal Validity".
What is Temporal Validity?
The documentation says it best:

Temporal Validity Support lets you associate one or more valid time dimensions with a table and have data be visible depending on its time-based validity, as determined by the start and end dates or time stamps of the period for which a given record is considered valid.
The question was: "Is it possible to have multiple temporal valid periods for a table?"
According to the documentation definition above it should be possible to create multiple temporal valid periods. Let's try this.

SQL> create table t
  2  (id number primary key
  3  ,a_start date
  4  ,a_end date
  5  ,b_start date
  6  ,b_end date
  7  ,period for a_valid (a_start, a_end)
  8  ,period for b_valid (b_start, b_end)
  9 );
,period for b_valid (b_start, b_end)
            *
ERROR at line 8:
ORA-55603: invalid flashback archive or valid time period command

That didn't work.
But we're not done yet. Let's try something a little different:

SQL> create table t
  2  (id number primary key
  3  ,a_start date
  4  ,a_end date
  5  ,b_start date
  6  ,b_end date
  7  ,period for a_valid (a_start, a_end)
  8  );

Table created.

SQL> alter table t
  2 add period for b_valid (b_start, b_end);

Table altered.

That worked. There are now two valid period defined on table T. Maybe I got the syntax wrong, let's take a look at the Metadata and find out where I went wrong.

SQL> select dbms_metadata.get_ddl
  2     ('TABLE'
  3     ,'T')
  4    from dual;
ERROR:
ORA-01427: single-row subquery returns more than one row
ORA-06512: at "SYS.DBMS_METADATA", line 6069
ORA-06512: at "SYS.DBMS_METADATA", line 8666
ORA-06512: at line 1



no rows selected

Oh oh.. I think I found a little bug here.
I wonder what will happen if we add data and run some queries.

SQL> insert into t values
  2  (1, trunc (sysdate) -10, trunc (sysdate), trunc (sysdate), trunc (sysdate) +10) ;

1 row created.

SQL> insert into t values
  2  (2, trunc (sysdate), trunc (sysdate) +10, trunc (sysdate) -10, trunc (sysdate))
  3  ;

1 row created.

SQL> insert into t values
  2  (3, trunc (sysdate) -10, trunc (sysdate) +10, trunc (sysdate) -10, trunc (sysdate) +10)
  3  ;

1 row created.
 
SQL> commit;

Commit complete.

SQL> select *
  2    from t;

 ID A_START   A_END     B_START  B_END
---------- --------- --------- --------- ---------
  1 07-SEP-14 17-SEP-14 17-SEP-14 27-SEP-14
  2 17-SEP-14 27-SEP-14 07-SEP-14 17-SEP-14
  3 07-SEP-14 27-SEP-14 07-SEP-14 27-SEP-14

3 rows selected.

SQL> select *
  2    from t as of period
  3   for a_valid sysdate;

 ID A_START   A_END     B_START  B_END
---------- --------- --------- --------- ---------
  2 17-SEP-14 27-SEP-14 07-SEP-14 17-SEP-14
  3 07-SEP-14 27-SEP-14 07-SEP-14 27-SEP-14

2 rows selected.

SQL> select *
  2    from t as of period
  3   for b_valid sysdate;

 ID A_START   A_END     B_START  B_END
---------- --------- --------- --------- ---------
  1 07-SEP-14 17-SEP-14 17-SEP-14 27-SEP-14
  3 07-SEP-14 27-SEP-14 07-SEP-14 27-SEP-14

2 rows selected.

SQL> select *
  2    from t as of period
  3   for a_valid sysdate -1;

 ID A_START   A_END     B_START  B_END
---------- --------- --------- --------- ---------
  1 07-SEP-14 17-SEP-14 17-SEP-14 27-SEP-14
  3 07-SEP-14 27-SEP-14 07-SEP-14 27-SEP-14

2 rows selected.

SQL> select *
  2    from t as of period
  3   for b_valid sysdate -1;

 ID A_START   A_END     B_START  B_END
---------- --------- --------- --------- ---------
  2 17-SEP-14 27-SEP-14 07-SEP-14 17-SEP-14
  3 07-SEP-14 27-SEP-14 07-SEP-14 27-SEP-14

2 rows selected.

It all seems to work fine.
So yes, you can add multiple temporal validity periods to a table but only with an alter table statement.

Links

Definition Temporal Validity

17 September 2014

Multirecord Master-Detail Report

A common requirement is to show a Master-Detail Report where both the Master as well as the Detail Report show multiple records. When you click on one of the Master records, the connected detail records are shown as well.
This is very easy to accomplish with a Hidden item and a Dynamic Action.
First the Master Report, for this example I am using the DEPT table:
select d.deptno
      ,d.dname
      ,d.loc
  from dept d
The detail report will consists of the employees which belong to the DEPT record which is clicked by the user.
select * from emp
 where deptno = :P9_DEPTNO
You will notice that there is a reference to P9_DEPTNO in the query, that will be the hidden item.
So there are two reports and a hidden item on the page.
The user will need to click on something, so the DEPTNO column from the first report will act as a link. Navigate to the column and fill in the section labelled "Column Link".
For the Link attributes fill in the following information:
 
onclick="return false;" class="show-employees" id="#DEPTNO#"
Now all components are in place, time to create the Dynamic Action.
The Dynamic Action will respond to a click on the master report(DEPT). The link column has a class attribute of "show-employees" which act as the jQuery Selector. Fill in the details when the Dynamic Action needs to fire as follows:
What does the Dynamic Action need to do? First it needs to set the value of the clicked DEPTNO in the hidden item. This can be done with a little bit of javascript:
apex.item( "P9_DEPTNO" ).setValue( this.triggeringElement.id );
The second part of the Dynamic Action is to refresh the Employees report. Add a TRUE action to the Dynamic Action where you specify "Refresh Region" and choose the details report (Employees).
You can find the demo right here.

29 August 2014

5 minutes: Grant Role to Package

The EOUC (EMEA Oracle User Group Community) hosts a special session on sunday during Oracle Open World. This session contains twelve things about Oracle 12c. Each of these 12 things is hosted by a different speaker, so this means that each section is only five minutes long (Strictly guarded by Debra Lilley).
Thankfully I was chosen to be one of the speaker during this special session, very excited and also very scared. Five minutes is not a whole lot to tell the story of PL/SQL security enhancements in 12c.

This blogpost will cover my five minutes of fame.

The thing that I will cover is Code Based Access Control (or actually only a small example where you grant privileges to a package).

1. Setting the stage

For this example I will use Schema A which will contain two tables (T1 and T2) and a package (PKG).
The structure of the tables is irrelevant, and so is the actual implementation of the package.

2. The Package's Objective

The actual implementation of the package is not really relevant, what is important to know is that it would query table T1 and would manipulate some data of table T2. However it should work on schema A's table T1 and on the calling schema's table T2.
The reason that schema A has a table T2 is that it would be impossible to compile the package if table T2 was missing. Table T2 only acts as a template table in schema A.
The calling schema should have a table named T2 in order for the package to be able to work.

note: the calling schema is the schema which would call the package that schema A has defined.

The package (located in schema A) should therefor be compiled with Invoker Rights (authid current_user) as opposed to Definer Rights (authid definer).
The reference to table T1 should be fully qualified (a.t1) and the reference to table T2 should not be fully qualified (t2).
Because of the Invoker Rights specification on the package PKG the table T2 of the calling schema is used. And because of the full qualification of table T1 (a.T1) the correct table is referenced (the one in schema A).
For a calling schema to be able to use this package (PKG) - let's assume the calling schema is called B - EXECUTE privileges need to be granted to schema B

grant execute on pkg to b;

3. The Old way (< 2013) and the flaw

Before the release of Oracle 12c, only granting execute privileges on package PKG to schema B would not be sufficient.
When invoking the package:

begin
   a.pkg;
end;
there would be an exception raised because of the reference to table T1 which resides in schema A.
ORA-00942: table or view does not exist
What you needed to do was to grant privileges on schema A's table T1 as well.
grant select on t1 to b;
Now all works fine. The package can be executed from schema B, and they lived happily ever after.
So, what is the security issue here?
Not only can the package be executed from schema B, the table T1 (in schema A) can also be queried directly without using the package. What if there are columns in table T1 that contain sensitive information?

4. Fixing the flaw

The solution is to grant privileges to the package and not to the user.
First of all: revoke the select privileges on schema A's T1 from schema B

revoke select on t1 from b
Next create a role and grant the select privileges on schema A's table T1 to that role. What you couldn't do before was grant a role to a package, but now you can.
create role only_this_role;

grant select on a.t1 to only_this_role;

grant only_this_role to package a.pkg;
And that's it.
The execute privileges on the package still exists of course otherwise schema B wouldn't be able to execute the package.
Table T1 (in schema A) can not be called directly by schema B any longer.
Now only the package can make calls to table T1.
This adheres to the concept of least privileges.

27 August 2014

Order, Order.. Sorting Happens Last

While preparing for my session at Oracle Open World on "Oracle 12c for Developers" I ran into a little remarkable thing.

When sorting a dataset, the sorting is always done last. That is what I was taught anyway. There is probably some obscure way to detect the exact execution plan, but personally I never bothered to go and investigate.
When I was created some test scripts I found there was a way to see that sorting really happens as the final step.

Oracle 12c made some enhancements in the Subquery Factoring clause. Specifically now it is possible to create Functions and Procedures in the WITH clause.
Something like the following:

SQL> with
  2     function formatname (p_name in varchar2)
  3        return varchar2
  4     is
  5     begin
  6        return initcap (p_name);
  7     end formatname;
  8  select ename
  9        ,formatname(ename)  formatted
 10    from emp;

ENAME      FORMATTED
---------- -------------------------
SMITH      Smith
ALLEN      Allen
WARD       Ward
JONES      Jones
MARTIN     Martin
...
After the WITH keyword (first line) a simple function is defined (lines 2 - 7). This function is used in the SELECT part of the query (line 9).

When you want to include a named query in the WITH clause, it has to be after the Procedures and Functions that you have defined.
In the following query, I included a named query called ordered_emps which is a resultset of the EMP table sorted by ENAME.
SQL> set serveroutput on
SQL> with
  2    procedure show (p_what in varchar2)
  3    is
  4    begin
  5      dbms_output.put_line ('input is: '||p_what);
  6    end show;
  7    function formatname (p_name in varchar2)
  8      return varchar2
  9    is
 10    begin
 11       show (p_name);
 12      return initcap (p_name);
 13    end formatname;
 14  ordered_emps as
 15    (select ename from emp order by ename asc)
 16  select ename
 17        ,formatname(ename) formatted
 18    from ordered_emps
 19  /
 
 ENAME       FORMATTED
---------- -----------
ADAMS      Adams
ALLEN      Allen
BLAKE      Blake
CLARK      Clark
FORD       Ford
JAMES      James
JONES      Jones
KING       King
MARTIN     Martin
MILLER     Miller
SCOTT      Scott
SMITH      Smith
TURNER     Turner
WARD       Ward

14 rows selected.

input is: SMITH
input is: ALLEN
input is: WARD
input is: JONES
input is: MARTIN
input is: BLAKE
input is: CLARK
input is: SCOTT
input is: KING
input is: TURNER
input is: ADAMS
input is: JAMES
input is: FORD
input is: MILLER

After the WITH keyword, a procedure (SHOW) is defined which acts as a wrapper for DBMS_OUTPUT.PUT_LINE (lines 2 - 6). Next a function (FORMATNAME) is defined which calls the SHOW procedure with the given input and formats the name to INITCAP (lines 7-13). Next a named query is defined ORDERED_EMPS (lines 14 - 15), and finally the "real" query is specified calling the FORMATNAME function.

Because SERVEROUTPUT is turned on, you can see the results as produced by the query as well as the calls to the SHOW procedure.
The resultset by the query is sorted based on the ENAME as we expect, the calls to the function (and procedure) happen as the data is fetched from the table in no particular order which can be seen in the output from DBMS_OUTPUT.