11g: SQL Pivot

Just wanted to post a quick example of the new 11g SQL Pivot syntax (documented here).

Here’s the old pivot method (I believe Tom Kyte invented this one):

select sum("10") "10",sum("20") "20",
       sum("30") "30",sum("40") "40",
	   sum("50") "50",sum("60") "60",
	   sum("70") "70",sum("80") "80",
	   sum("90") "90",sum("100") "100",
	   sum("110") "110"
  from(
select max(decode(department_id,10,salary,null)) "10",
       max(decode(department_id,20,salary,null)) "20",
       max(decode(department_id,30,salary,null)) "30",
	   max(decode(department_id,40,salary,null)) "40",
       max(decode(department_id,50,salary,null)) "50",
       max(decode(department_id,60,salary,null)) "60",
	   max(decode(department_id,70,salary,null)) "70",
	   max(decode(department_id,80,salary,null)) "80",
	   max(decode(department_id,90,salary,null)) "90",
       max(decode(department_id,100,salary,null)) "100",
	   max(decode(department_id,110,salary,null)) "110"
  from employees
  group by department_id);

It’s a very effective method that I’ve used many times, but the syntax is a bit clunky.

And now, the 11g Pivot syntax:

select *
  from (select department_id,sum(salary) salary
          from employees
         where department_id > 0
         group by department_id)
  pivot (sum(salary)
    for department_id in (10,20,30,40,50,60,70,80,90,100,110));

10     20     30     40     50     60     70     80      90     100    110
------ ------ ------ ------ ------ ------ ------ ------- ------ ------ ------
4400   19000  24900  6500   156400 28800  10000  319500  58000  51600  20300 

1 rows selected

Wrap and compile in one step

The PL/SQL wrap utility is a great way to obfuscate code that you do not want anyone to view. Typically this is used for security functions Virtual Private Database and Fine Grained Auditing policies, encryption and hashing functions, and authentication and authorization procedures. The source of unwrapped code can easily be retrieved from the ALL_SOURCE view or almost any IDE that connects to the database.

Prior to 10g, you needed to use the command-line wrap binary located in $ORACLE_HOME/bin. This was a 2 step process, requiring you to first wrap the code from the shell, then compile it using SQL*Plus. 10g introduced the DBMS_DDL package, allowing you to wrap code from within PL/SQL and compile it in one step. This means you don’t need to be on a machine where Oracle is installed, allowing you to use other tools such as APEX and SQL Developer to wrap your code.

Here’s a quick example:

-- Run the following grant as sys:
-- grant execute on dbms_ddl to demo;
declare
  l_function    varchar2(32767);
begin
  l_function := q'!
    create or replace function wrap_test
      return varchar2
    is
    begin
       return 'Yep, it worked';
    end wrap_test; !';
  -- Toggle the comments on the following 2 lines to toggle wrapped / not wrapped
  -- execute immediate l_function;
  sys.dbms_ddl.create_wrapped(l_function);
end;
/

Here’s a screenshot of the wrapped source code in SQL Developer:

References:

• PL/SQL Packages and Types Reference
• Use Case example in the Database Security Guide

RMAN Encrypted Backups

A number of recent high profile data thefts have resulted from lost or stolen backups that were not encrypted. Offsite backups are a good first-step in a Disaster Recovery plan, but they also create a huge risk for data theft. Even if the backup is not offsite, a backup sitting around your office represents nicely packaged target for someone to steal. If you need a reality check, take a look at some of the events listed on the Chronology of Data Breaches:

• Bank of America: Lost backup tape, 1,200,000 records
• Ameritrade: Lost backup tape, 200,000 records
• Time Warner: Lost backup tapes, 600,000 records
• CitiFinancial: Lost backup tapes, 3,900,000 records

Encrypted backups provide a huge improvement in security without having to change your application code or database structures. Obviously, sensitive data stored unencrypted in the database is still a problem, but it’s typically a much more challenging problem to solve. More on that issue in future blog posts…

Oracle offers several solutions to encrypt backups. Oracle Secure Backup is designed for direct backups to tape, offers several options for encryption, and even comes in a free “Express” version for tape devices directly attached to a single server. Starting with 10gR2, RMAN can also encrypt backups to disk when used in conjunction with the Advanced Security Option (ASO) for the database. I’m only going to cover the RMAN solution in this post since I don’t have access to a tape device.

RMAN encryption can either use a password, an Oracle Wallet, or both as keys to encrypt a backup. If you choose to use both a wallet and a password to encrypt, you only need to one of the keys to decrypt your backup. So, choosing to use both options doesn’t make your backup more secure, it simply allows you to use either method for decryption in case you lose the wallet or forget the password. The beginning of this article on TDE provides a simple example of configuring a wallet. For more detailed information, here’s a link to the documentation for RMAN Encrypted Backups.

The following 5 minute screencast demonstrates RMAN encrypted backups, first using Enterprise Manager Database Control, then in more detail using the RMAN command-line interface.

ScreenCast Click here for the full-size version

Code

Unencrypted Backup:
rman target /
configure channel device type disk format ‘/backup/%N_%s’ maxpiecesize 2 G;
backup tablespace test1 tag=unencrypted;

Password only Encrypted Backup:
rman target /
set encryption on identified by ‘L!eFLW@Bf=U,ptC>’ only;
backup tablespace test1 tag=encrypted;


Password and Wallet Encrypted Backup:
rman target /
configure encryption for database on;
set encryption on identified by ‘L!eFLW@Bf=U,ptC>’;
backup tablespace test1 tag=encrypted;


Restore Tablespace from Backup:
rman target /
sql ‘alter tablespace test1 offline immediate’;
set decryption identified by ‘L!eFLW@Bf=U,ptC>’;
restore tablespace test1;
recover tablespace test1;
sql ‘alter tablespace test1 online’;

Oracle Wallet w/ Self-Signed Certificate

I was working with some of the security tools included with the Oracle Advanced Security Option and wanted to create a new wallet without going through the hassle of requesting a certificate from one of the popular certificate authorities. After struggling a bit with creating a self-signed certificate, I found a great HowTo by Jim Coulter entitled “How To Build an Oracle Wallet with OpenSSL”. Since I’m planning on a number of future security posts that rely on the Oracle Wallet Manager, I wanted to repost Jim’s HowTo here so they would all be at the same location. Just to be clear, this is NOT my HowTo, it’s Jim’s and I want to give him full credit for a very concise and accurate HowTo.

This HowTo is relevant for any Oracle component that uses the Oracle Wallet Manager, including the Oracle Database with the Advanced Security Option (ASO), Oracle Application Server, Oracle HTTP Server (OHS), Oracle Internet Directory (ODI). For production environments, you should purchase a certificate from a well known certificate authority such as Entrust, Thawte, GoDaddy, or VeriSign. Using self-signed certificates provides no protection against man in the middle attacks so they should NOT be used in production environments.

Even though the example is Linux / Unix centric, I also tested this on Windows using CygWin and it worked flawlessly.

From Jim Coulter’s site, with one addition:

1. Download and unpack the ssl helper scripts named ssl.ca-0.1.tar.gz from the OpenSSL > Contributions page.
   
2. Open Oracle Wallet Manager and create a new wallet and certificate request.
   
3. Export the certificate request to a file. Give it a .csr extension
   
4. Move the certificate request to the directory containing the openSSL certificate authority scripts (e.g. /usr/src/crytpo/openssl/apps/ssl.ca-0.1)
   
5. Create a self-signed root certificate by running the new-root-ca.sh script. This will create a file called ca.crt
   
6. Create the self-signed server certificate by running the sign-server-cert.sh script, e.g. # sign-server-cert.sh <certificate-request-filename>. This will create a file called <certificate-request-filename>.crt
   
7. Import the ca.crt into the Oracle wallet as a trusted certificate. Import the <certificate-request-filename>.crt as a user certificate.
   
8. Enable auto-login and save the wallet. It is now ready for use.
   

References:

• Oracle Database Advanced Security Administrator’s Guide: “9 Using Oracle Wallet Manager”
  
• Jim Coulter’s HowTo
  
• Anton Nielsen’s blog: “Setting Up SSL for Application Express”
  
• OID Tutorial: “Creating a wallet for SSL connectivity between OID and AD”
  

Fine Grained Auditing

Below is a short screencast (code in action, no PowerPoint) of Fine Grained Auditing in the context of Application Express. It’s in response to this article, which only dedicates a single sentence to the topic of trip-wires, but I do credit them for raising the issue. The code is posted below the screencast. Other things to keep in mind:

• This policy will fire for every insert,update,delete, or select statement for this table, but not every row
• It would be easy to defeat this policy by setting module yourself and logging in as the APEX user, but the idea is that this is more of a silent alarm meant to catch people who are not aware of this policy, or at least not aware of exactly what it’s looking for
• It would be a good idea to wrap the CHECK_FOOTPRINT function, making it difficult to see what it is looking for
• Fine Grained Auditing requires Enterprise Edition (no SE or XE)
• The FGA_NOTIFY procedure uses UTL_MAIL which means you need to set the initialization parameter SMTP_OUT_SERVER to the servername:port of your email server

ScreenCast Click here for the full-size version
Code

– Make sure you set the spfile parameter SMTP_OUT_SERVER to the
– name and port of your mail server: yourmailserver.com:25
– Grant execute on utl_mail to FGA;
– Grant execute on DBMS_FGA to FGA;
– Also make sure you change the “sender” and “recipients”
– parameters in the call to utl_mail

create or replace function check_footprint
return number
as
begin
    if (sys_context(’userenv’,’session_user’) = ‘APEX_PUBLIC_USER’
        AND
        sys_context(’userenv’,'module’) = ‘APEX:APPLICATION 106′)
        — Other thoughts:
        — Business Hours
        — (to_char(sysdate, ‘D’) between 2 and 6 and to_char(sysdate, ‘HH24′) between 8 and 18
    then
        return 1;
    else
        return 0;
    end if;
end check_footprint;
/

grant execute on check_footprint to public;

create or replace procedure fga_notify (
    schema1 in varchar2,
    table1  in varchar2,
    policy1 in varchar2)
as
    l_msg   varchar2(32767);

    function t_row(
        p_label     in varchar2,
        p_data      in varchar2)
    return varchar2
    is
    begin
        return ‘<tr><td style=”text-align:right;”>’||p_label||’</td>’||
    ‘    <td style=”font-weight:bold;”>’||p_data||’</td></tr>’||utl_tcp.crlf;
    end t_row;

begin
    l_msg := ‘<html><head><style type=”text/css”>body{font-family:helvetica}</style></head><body>’||
    ‘<table style=”boder:0px;”>’||
    t_row(’Schema’,schema1)||
    t_row(’Table’,table1)||
    t_row(’Policy’,policy1)||
    t_row(’User’,user)||
    t_row(’Client Info’,sys_context(’userenv’, ‘client_info’))||
    t_row(’Client Identifier’,sys_context(’userenv’, ‘client_identifier’))||
    t_row(’IP Address’,sys_context(’userenv’, ‘ip_address’))||
    t_row(’Auth Type’,sys_context(’userenv’, ‘authentication_type’))||
    t_row(’Session ID’,sys_context(’userenv’, ’sessionid’))||
    t_row(’DB Name’,sys_context(’userenv’, ‘db_name’))||
    t_row(’Host’,sys_context(’userenv’, ‘host’))||
    t_row(’OS User’,sys_context(’userenv’, ‘os_user’))||
    t_row(’External Name’,sys_context(’userenv’, ‘external_name’))||
    t_row(’Current SQL’,'<pre> ‘||sys_context(’userenv’, ‘current_sql’)||’</pre>’)||
    ‘</table></body></html>’;

    utl_mail.send(
        SENDER      => ‘orcl_db@demo1′,
        RECIPIENTS  => ‘oracle@localhost’,
        –CC          => ”,
        –BCC         => ”,
        SUBJECT     => ‘Policy Violation’,
        MESSAGE     => l_msg,
        MIME_TYPE   => ‘text/html’,
        PRIORITY    => 1);
end fga_notify;
/
show errors
set termout off
BEGIN
DBMS_FGA.DROP_POLICY
(object_schema       => ‘FGA’,
     object_name         => ‘EMP’,
     policy_name         => ‘CHECK_FOOTPRINT);
END;
/
set termout on
BEGIN
DBMS_FGA.add_policy
   (object_schema       => ‘FGA’,
    object_name         => ‘EMP’,
    policy_name         => ‘ONLY_APEX_APP’,
    audit_condition     => ‘FGA.CHECK_FOOTPRINT = 0′,
    audit_column        => null,
    handler_schema      => ‘FGA’,
    handler_module      => ‘FGA_NOTIFY’,
    statement_types     => ‘INSERT,UPDATE,DELETE,SELECT’,
    ENABLE              => TRUE);
END;
/
– Audit entries are stored in sys.dba_fga_audit_trail

APEX Meta Blog

The community around Oracle Application Express (APEX) has grown by leaps and bounds over the last few years. It’s finally reached a critical mass, such that the community attracts more developers and in turn the developers give back to the community. Just take a look at the APEX Community page: there are sample applications, a wiki, special interest groups, hosting solutions, consulting companies, How-To’s and Articles, Podcasts, and more blogs than you can shake a stick at.

To help make it easier to consume some of the blog content, I’ve added all of the English blogs to a Netvibes tab that you can preview by clicking this button . If you like it and are already logged-in to Netvibes, you can simply add it to your page. I’ve tried a number of the content aggregation sites, such as BlogLines and PageFlakes, but Netvibes is by far my favorite. Mozilla Thunderbird is another great way to consume RSS feeds if you use it as your mail client. If are already using a service other than Netvibes, here’s the opml of the APEX blogs that you can simply copy and paste into a new file (apex_blogs.ompl) and import into your current reader:
<?xml version="1.0" encoding="utf-8"?><opml version="1.0">
<head>
<title>Netvibes.com Subscriptions</title>
</head>
<body>
<outline title=”APEX Blogs” text=”APEX Blogs” icon=”http://www.oracle.com/favicon.ico”>
<outline type=”rss” title=”The Tom Kyte Blog” text=”The Tom Kyte Blog” xmlUrl=”http://tkyte.blogspot.com/feeds/posts/default” />
<outline type=”rss” title=”Marc Sewtz” text=”Marc Sewtz” xmlUrl=”http://marcsewtz.blogspot.com/feeds/posts/default” htmlUrl=”http://marcsewtz.blogspot.com/index.html” />
<outline type=”rss” title=”daust_de :: Oracle XE / Apex” text=”daust_de :: Oracle XE / Apex” xmlUrl=”http://daust.blogspot.com/feeds/posts/default” htmlUrl=”http://daust.blogspot.com/index.html” />
<outline type=”rss” title=”Tyler Muth’s Blog” text=”Tyler Muth’s Blog” xmlUrl=”http://tylermuth.wordpress.com/feed/” />
<outline type=”rss” title=”Scott Spendolini’s Blog” text=”Scott Spendolini’s Blog” xmlUrl=”http://spendolini.blogspot.com/feeds/posts/default?alt=rss” />
<outline type=”rss” title=”Dimitri Gielis Blog” text=”Dimitri Gielis Blog” xmlUrl=”http://dgielis.blogspot.com/feeds/posts/default” htmlUrl=”http://dgielis.blogspot.com/” />
<outline type=”rss” title=”Denes Kubicek ApEx BLOG” text=”Denes Kubicek ApEx BLOG” xmlUrl=”http://deneskubicek.blogspot.com/feeds/posts/default” htmlUrl=”http://deneskubicek.blogspot.com/index.html” />
<outline type=”rss” title=”iAdvise” text=”iAdvise” xmlUrl=”http://iadvise.blogspot.com/feeds/posts/default” htmlUrl=”http://iadvise.blogspot.com/” />
<outline type=”rss” title=”Carl Backstrom’s Blog” text=”Carl Backstrom’s Blog” xmlUrl=”http://carlback.blogspot.com/feeds/posts/default” htmlUrl=”http://carlback.blogspot.com/index.html” />
<outline type=”rss” title=”Inside Oracle APEX | by Patrick Wolf” text=”Inside Oracle APEX | by Patrick Wolf” xmlUrl=”http://feeds.feedburner.com/InsideApex” />
<outline type=”rss” title=”Jack of Everything, Master of Nothing” text=”Jack of Everything, Master of Nothing” xmlUrl=”http://kristianjones.blogspot.com/feeds/posts/default” />
<outline type=”rss” title=”del.icio.us/orcl.apex” text=”del.icio.us/orcl.apex” xmlUrl=”http://del.icio.us/rss/orcl.apex” />
<outline type=”rss” title=”Johns Blog” text=”Johns Blog” xmlUrl=”http://jes.blogs.shellprompt.net/feed/” htmlUrl=”http://jes.blogs.shellprompt.net” />
<outline type=”rss” title=”Anton Nielsen” text=”Anton Nielsen” xmlUrl=”http://c2anton.blogspot.com/feeds/posts/default” />
<outline type=”rss” title=”Discussion Forums: Message List - Application Express” text=”Discussion Forums: Message List - Application Express” xmlUrl=”http://forums.oracle.com/forums/rss/rssmessages.jspa?forumID=137″ />
</outline>
</body></opml>

Linux Package Management - The “Smart” Way

Intro
Anyone who has ever worked with Linux has run into the RPM dependency nightmare. You simply want to install RPM x, but it requires y and y requires z (it gets worse, but I will leave it at that). Package managers such as UP2DATE, APT, and YUM have made this significantly easier by handling the dependencies for you. About a year ago, I stumbled upon the Smart Package Manager and my Linux experience has never been the same. Why I love Smart:

• Smart can be configured with YUM, APT, UP2DATE, URPMI, directory of RPMs (and more) repositories
• It handles package dependencies, even if it needs to pull packages from several different sources
• It provides both GUI and Command Line interfaces

I’m a big Linux advocate, but my interest in Linux is really about the software I run on Linux, not Linux itself. I view Linux as a service that runs my software (mostly Oracle), and doesn’t get in my way. For me, any extra time I invest in configuring Linux is a waste. This post is all about spending less time messing with RPMs and more time with more important topics. This post is targeted at people running Linux in a development or demo environment (VMWare comes to mind), and NOT FOR PRODUCTION SYSTEMS. The 3rd party packages may invalidate your support agreements or de-stabilize your environment.

For this example, I downloaded Enterprise Linux (x86, 32-bit) from http://edelivery.oracle.com/linux It’s 100% source and binary compatible with Red Hat Enterprise Linux 4 and it’s free. Since it’s really RedHat 4, Update 4 with some bug fixes, we’ll point at those repositories, as well as some from CentOS 4.4. There are HowTos out there for many other distros, including Suse (my desktop distro of choice). For 64-bit EL, you’ll need to adjust all of the URLs and test, but the same concepts apply.

Run all commands from a bash shell as root. I’ve omitted all prompts and output to make copy-and-paste easier, so interpret each line as a new command.

Install Smart
mv /etc/yum.repos.d/ULN-Base.repo /etc/yum.repos.d/ULN-Base.repo.old # These repositories are not used
rpm -Uhv http://apt.sw.be/packages/rpmforge-release/rpmforge-release-0.3.6-1.el4.rf.i386.rpm # –httpproxy someproxy.com –httpport 80
yum install smart*

Add Local Repository
Copy all Enterprise Linux RPMs locally. In my case, they are on the host OS, accessed via VMWare shared folders at /mnt/hgfs/el-rpms. This will be our primary Smart channel. Make sure you adjust the path in this command based on the location your RPMs.
smart channel --add local-EL-rpms name="Oracle Enterprise Linux RPMs" manual=true type=rpm-dir path=/mnt/hgfs/el-rpms/ -y;

Add Remote Repositories
smart channel --add Dag-Wieers type=rpm-md priority=-5 baseurl=http://apt.sw.be/redhat/el4/en/i386/dag/ -y;
smart channel --add VA-Tech-Extras type=rpm-md priority=-5 baseurl=http://mirror.cs.vt.edu/pub/CentOS/4.4/extras/i386/ -y;
smart channel --add VA-Tech-Base type=rpm-md priority=-5 baseurl=http://mirror.cs.vt.edu/pub/CentOS/4.4/os/i386/ -y;
smart channel --add Dries type=rpm-md priority=-5 baseurl=http://ftp.belnet.be/packages/dries.ulyssis.org/redhat/el4/en/i386/dries/RPMS/ -y;
smart channel --add atrpms name="ATrpms Repository" type=rpm-md priority=-5 baseurl=http://dl.atrpms.net/el4-i386/atrpms/stable/ -y;

Notice I set the priority of all 3rd party repositories to -5, leaving the default priority of 0 for installed packages and the local RPMs. This will cause the UBL packages to take precedence whenever the same package is found in multiple places.

Command-Line Usage
Find all packages with PDF in their title
smart search pdf
Install the xpdf packge
smart install xpdf
Install a local or remote RPM, using the configured channels to fulfill required dependencies
smart install somefile.rpm
smart install ftp://someurl.com/somefile.rpm

Demo, Including GUI Usage
Click here for the full-size version

Advanced
If you are required to use a proxy server to access the internet, use the following commands to set the proxy server for smart (documented here):
smart config --set http-proxy=http://someproxy.com:80
smart config --set ftp-proxy=ftp://someproxy.com:80