A couple of days ago I needed a quick solution that would limit access to some sensitive data (columns). Only a limited group of end users, selected developer and a DBA should see the content of some columns. Target table is otherwise stored in application schema that is shared by several developers, but only one of them should see the data. I knew that encryption is the way to go. Based on example published in Oracle 10g PL/SQL Packages and type reference for package DBMS_CRYPTO, I wrote simple package that will set decryption key for the session and allow for encryption and decryption of data.

Create or replace package dbcrypt is
  Procedure setkey( p_key  IN VARCHAR2 ); 
  Function encrypt( p_data IN VARCHAR2 ) Return RAW DETERMINISTIC;
  Function decrypt( p_data IN RAW ) Return VARCHAR2 DETERMINISTIC;
end dbcrypt;
/

Create or replace package body dbcrypt is
    
    ecryption_type PLS_INTEGER := SYS.DBMS_CRYPTO.ENCRYPT_AES128 + 
                                  SYS.DBMS_CRYPTO.CHAIN_CBC +
                                  SYS.DBMS_CRYPTO.PAD_PKCS5;
    v_key RAW(16) := null;
    
    Procedure setkey (p_key IN VARCHAR2)
    IS
    BEGIN
      if p_key is not null then
        v_key := UTL_RAW.cast_to_raw(p_key);
       dbms_output.put_line(v_key);
      end if;
    END setkey;
    
    Function encrypt( p_data IN VARCHAR2 ) Return RAW DETERMINISTIC
    IS
      l_data RAW(2048) := UTL_I18N.STRING_TO_RAW(p_data,'AL32UTF8');
      l_encrypted RAW(2048);
    BEGIN
      l_encrypted := sys.dbms_crypto.encrypt                       
                     ( src => l_data,
                       typ => ecryption_type,
                       key => v_key );
      Return l_encrypted;
      EXCEPTION 
        WHEN OTHERS THEN
          -- for the security reason I want to completely silence the error 
          -- stack that could reveal some technical details to imaginary attacker.
          -- Remember, such miss-use of WHEN OTHERS should be considered 
          -- as a bug in almost all other situations.
          RAISE_APPLICATION_ERROR(-20001,'Access denied!');
    END encrypt;

    Function decrypt( p_data IN RAW ) Return VARCHAR2 DETERMINISTIC
    IS
      l_decrypted RAW(2048);
    BEGIN
      l_decrypted := sys.dbms_crypto.decrypt                              
                      ( src => p_data,
                        typ => ecryption_type,
                        key => v_key );
      Return UTL_I18N.RAW_TO_CHAR(l_decrypted,'AL32UTF8');
      EXCEPTION 
        WHEN OTHERS THEN
          RAISE_APPLICATION_ERROR(-20001,'Access denied!');
    END decrypt;
End dbcrypt;
 /

Let’s see dbcrypt package in action:

--
-- Let's pretend we want to protect first and 
-- last name in table MY_EMP. 
-- Note that encrypted values are much larger than
-- non-encrypted, that's why I used VARCHAR2(100)
--
create table my_emp (
 id  number(10) not null,
 first_name varchar2(100),
 last_name  varchar2(100));

--
-- We can prepare view that will decrpyt columns,
-- this view can then be used in application.
--
create or replace view v_my_emp as
  select id, 
  substr(dbcrypt.decrypt(first_name),1,35) as First_Name,
  substr(dbcrypt.decrypt(last_name),1,35) as Last_Name
  from my_emp;  
--
-- once per session we need to set key that
-- is used fro encryption/decryption for the duration of session.
--
execute dbcrypt.setkey('mysecretpassword');
--
--
-- now, wen can insert some encrypted data
--
insert into my_emp values(1,dbcrypt.encrypt('Bruce'),dbcrypt.encrypt('Scott'));
--
-- you can check that column data is encrypted
-- 
select id, first_name, last_name from my_emp;

  ID FIRST_NAME                       LAST_NAME
---- -------------------------------- --------------------------------
   1 372EC43843E2782F30B56DA46034324D 65FE9535E2FF1FA61B495C7A29F1779F
--
-- since encryption key was already set (see execute statement above),
-- we can simply select from the view and view will take care 
-- of decryption.
--
SQL> select * from v_my_emp;

  ID FIRST_NAME                       LAST_NAME
---- -------------------------------- ----------
   1 Bruce                            Scott

Of course we must be careful that encryption key is not lost and/or exposed (network sniffing, written in some configuration file for application etc.), but this is a different story.

Can you figure out what is wrong with this picture:

Well, according to MOS note 207303.1 “Client / Server / Interoperability Support Between Different Oracle Versions” you supposedly should not be seeing this picture at all :).

It’s one of our legacy client/server application built with Designer6i R3 (with some Headstart sugar) that runs on Windows 7 with database session on Oracle11g R2. Application runs without a hitch. Against all odds! Remember, we’re talking about Net 8.0.6 underneath, client version that should receive ORA-3134 as a welcome message while attempting to connect to 11g. But it doesn’t. We’re also talking about Windows 7 (without relying on XP mode, btw.!) as a guest machine. If someone asked me a few days ago if this is possible I would say “No way José”. A simple question that was published on community forum triggered my curiosity and fortunately curiosity didn’t kill the cat, at least not this time.

Funny thing is that at the time when Oracle11g R1 was released I tried to connect with sqlplus 8.0.6 to 11g database and received the error ORA-00248 (see Jarneil blog), immediately dismissed the idea of even trying Forms6i app. against 11g R1.

Totally unsupported combination, of course. But who cares, Developer6i is out of support since 2005 and just seeing some posts on OTN about Developer 11g make one starting to choke. Of course, in our case this legacy application is not that important for the business and eventually the day will come when merciful shot will end the production life of an old dog.

Last night Oracle finally released patchset 4 (10.2.0.5) for Oracle 10g R2 on Windows x64 (my download is going on right now :-).

In the last couple of days I was busy installing and testing 32-bit version of the patchset for Windows. Didn’t experience any difficulties so far, but it’s too early to push the upgrade to the client machines. There are some known issues related to ODP.NET for which I’m not sure that I did it right (need a confirmation from .net developers).

The rest of the week I’ll be installing and testing x64 version of the patchset on Windows 2003/2008 servers and if I find something worth sharing with the Oracle community, I’ll publish it here.

The other day, I had some spare time to kill, so I decided to install recently released Oracle 11.2 ASM on one of my Windows 2003 x64 sandbox. I didn’t expect that I’ll end-up troubleshooting installment for the rest of the day. What happened?

I first installed ASM from 11.2 Grid infrastructure “CD” to Oracle home D:\ORACLE\ORA11R2GRID with “Install and Configure Grid Infrastructure for a Standalone Server” installation option. Then, I changed my mind and decided to remove R2 from the Oracle home name (if you’re asking why I’m not using Oracle defaults…->did I mention that I hate Oracle stupid default paths? I’m all for OFA “rules”, just not the Oracle ones – especially on Windows! ;-)).
No problem, I whispered to myself. I launched OUI and selected ORA11R2GRID for de-installation, OUI instructed me that I need to run deinstall script from \ORA11R2GRID\deinstall directory. Which I did. All went well and ORA11R2GRID home was gone, or so I thought. Launched setup from grid “CD” again and repeated installation, this time in different home, D:\ORACLE\ORA11GRID. All went well until I hit the error at step “Grid Infrastructure configuration”:

[INS-20802] Grid Infrastructure Configuration failed.

as shown on picture:

…needless to say that I checked OUI log:

…there was nothing in the log that would help me find the root cause of the problem. I repeated installation several times, trying different things to identify the problem.

Finally, I sorted the Oracle inventory log files based on timestamp in descending order and in addition to installations* log files noticed additional logs, with more reassuring names, such as asmcadc_check*.log. As soon as I opened the last generated asmcadc_check log file I knew what might be causing the problem. At the end of the log I spotted reference to now non-existing home (ORA11R2GRID):

....
[main] [ 2010-04-13 09:38:09.609 PDT ] [WindowsSystem.dorunRemoteExecCmd:2061]  _WS_: Calling windowsNative with cmd: D:\ORACLE\ORA11GRID\bin\getcrshome.exe
[main] [ 2010-04-13 09:38:10.625 PDT ] [WindowsSystem.dorunRemoteExecCmd:2064]  _WS_: WinNative returned: true
[main] [ 2010-04-13 09:38:10.625 PDT ] [HAUtils.getCRSHome:594]  Configured CRS/HAS home = D:\ORACLE\ORA11R2GRID
[main] [ 2010-04-13 09:38:10.625 PDT ] [HAUtils.getCRSHome:572]  returning CRS Home: D:\ORACLE\ORA11R2GRID
[main] [ 2010-04-13 09:38:10.625 PDT ] [ASMUtils.checkASMExistence:562]  Exception in method:checkASMExistencePRCI-1113 : Directory D:\ORACLE\ORA11R2GRID does not exist
[main] [ 2010-04-13 09:38:13.953 PDT ] [USMCheckConfig.checkConfigInternal:177]  ASM was not detected in the Oracle Home  

I opened regedt32 and checked HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE for orphaned keys. I noticed that Oracle de-installation procedure didn’t remove HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ocr key which was pointing to non-existing grid infrastructure home, D:\ORACLE\ORA11R2GRID. After I deleted the ocr key I repeated the Grid infrastructure installation for single server and this time around “Grid Infrastructure Configuration” step succeeded.

I guess troubleshooting episode such as this is (now and then!) acceptable “collateral damage” for going with the “my way” of doing things. Or is it just a convenient excuse for schallabweiserrei?:-)