Oracle Tips and Tricks: 2016

Thursday, April 28, 2016

What amuses Tom Kyte? (aka: To wrap, or not to wrap?) Selective Encryption Part 3 of 3

So...this post is not about rap, its about wrapping plsql. If you've come here looking for a music site...run away! There's nothing about Jay-Z, the Beyonce formation Tour, Kanye West, Lil Wayne or Drake.

If you think you're getting security by wrapping your plsql, stick around...its not protection. AS Tom said, "I put "protection" in quotes because I've always been amused by 'wrapping' code."

In my previous post, I had a function that returned a 256-bit key, and mentioned a few ways of protecting it...but cautioned about using plsql wrap. This is only slightly better than doing nothing.

The function in the previous post is:

create or replace function blackan.my_key return varchar2 as
begin
return '0F9157255C4CB6292D5C7E5FE96D90B2B4DE33CF4FEF2742366705F36D40A143';
end;

I run the wrap utility and it creates my_function.plb:

wrap iname=my_function.sql

Taking a peek in the file I see:

create or replace function blackan.my_key wrapped
a000000
367
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
8
7e ba
mtACEgpb2btQRc85YlHfKReZyCowgyr6f8tqynSm3f80Fbor4H4vlzbU3ANp9BtM2jLlkqnk
vF5IANpFigWMarvPqiRJdFXQs+5cQGjZZxauuFyxO9bW1UkgQotGR+YJ78l+VqExIcanEJXy
LoQv715qogTgWUFi22C8R47oRG4zYWj5tlQcWQ==
/

You might be thinking, "Wow! That sure looks secure! Comparing it to the original, I can't see anything that's the same!" Don't think that...its a false sense of security. There are TONS of post/utilities...even websites that will "unwrap" your plsql.

Here's one:
http://www.codecrete.net/UnwrapIt

If you paste the wrapped code above in to the website, you'll see:

FUNCTION my_key RETURN VARCHAR2 AS 
BEGIN 
  RETURN '0F9157255C4CB6292D5C7E5FE96D90B2B4DE33CF4FEF2742366705F36D40A143'; 
END;

So...don't trust the security of wrapping plsql.

On the other hand, this allows you to get insight...when metalink is failing you, you may be able to paste the internal wrapped plsql packages from Oracle and diagnose your own issues.

I hope this helps you see you shouldn't trust the plsql wrap to keep your data secure.

SDE Part 1
SDE Part 2
YOU ARE HERE>> SDE Part 3

Selective Encryption Part 2 of 3

In my previous post I talked about the importance of encryption in the database. To be fair, sometimes it really isn't important, its just required for some reason. This is more of a solution to the 2nd, as part of an overall security scheme. Also, for a scenario where some of the data in a table is replicated to other databases/platforms, etc, this could be a way to auto-obfuscate. The encrypted data would be moved through GG or w/e CDC you use without a key...making it very secure after it leaves the database.

We're going to use a block cipher...so first, you need to pick a cipher algorithm (here are the block options), a block cipher chaining modifier and a padding modifier from below, and add up their associated numbers. (ie: AES256 with CBC and PKCS5 would be 8+256+4096=4360) We'll use 4360 for our example.

Here are some of the options in dbms_crypto:

-- Block Cipher Algorithms
ENCRYPT_DES := 1;
ENCRYPT_3DES_2KEY := 2;
ENCRYPT_3DES := 3;
ENCRYPT_AES := 4;
ENCRYPT_PBE_MD5DES := 5;
ENCRYPT_AES128 := 6;
ENCRYPT_AES192 := 7;
ENCRYPT_AES256 := 8;

-- Block Cipher Chaining Modifiers
CHAIN_CBC := 256;
CHAIN_CFB := 512;
CHAIN_ECB := 768;
CHAIN_OFB := 1024;

-- Block Cipher Padding Modifiers
PAD_PKCS5 := 4096;
PAD_NONE := 8192;
PAD_ZERO := 12288;
PAD_ORCL := 16384;

1. First, create a key and capture the string that gets generated...don't lose this key:

set serveroutput on size 1000000
declare
v_Number number := 256/8; --256 bit key
v_Key RAW(32);
begin
v_Key := DBMS_CRYPTO.RANDOMBYTES(v_Number);
dbms_output.put_line(v_Key);
end;

2. Now replace the red string below with the output generated above. This is meant as a very simple example. In real life you'd want to do everything you can to make this complex and difficult to read. Putting it in a package and wrapping it would make it slightly less simple to get at. Putting this data somewhere else encypted (like the OS or a different db) would be better. How you do this is up to you...but just creating a plain text function isn't sufficient, and depending on plsql wrap is a bad idea...as Tom Kyte put it, "Using the latest wrap would provide the highest degree of "protection". That is all. I put "protection" in quotes because I've always been amused by 'wrapping' code." I'll explain that more in a different post.

create or replace function blackan.my_key return varchar2 as

begin

return '0F9157255C4CB6292D5C7E5FE96D90B2B4DE33CF4FEF2742366705F36D40A143';

end;

3. Now generate the base table and the decrypting view on the table:

drop table blackan.hacker_booty_base;

create table blackan.hacker_booty_base (EmpName raw(120), Emp_Num raw(88), SSN raw(80), Credit_Card raw(120), CC_Ex_Date raw(88), Address raw(120), PW raw(120));

create or replace view blackan.hacker_booty as

select

SUBSTRB(UTL_I18N.RAW_TO_CHAR (DBMS_CRYPTO.DECRYPT(EmpName,4360,my_key), 'AL32UTF8'),1,30) EmpName,

SUBSTRB(UTL_I18N.RAW_TO_CHAR (DBMS_CRYPTO.DECRYPT(Emp_Num,4360,my_key), 'AL32UTF8'),1,22) Emp_Num,

SUBSTRB(UTL_I18N.RAW_TO_CHAR (DBMS_CRYPTO.DECRYPT(SSN,4360,my_key), 'AL32UTF8'),1,20) SSN,

SUBSTRB(UTL_I18N.RAW_TO_CHAR (DBMS_CRYPTO.DECRYPT(Credit_Card,4360,my_key), 'AL32UTF8'),1,30) Credit_Card,

SUBSTRB(UTL_I18N.RAW_TO_CHAR (DBMS_CRYPTO.DECRYPT(CC_Ex_Date,4360,my_key), 'AL32UTF8'),1,30) CC_Ex_Date,

SUBSTRB(UTL_I18N.RAW_TO_CHAR (DBMS_CRYPTO.DECRYPT(Address,4360,my_key), 'AL32UTF8'),1,30) Address,

SUBSTRB(UTL_I18N.RAW_TO_CHAR (DBMS_CRYPTO.DECRYPT(PW,4360,my_key), 'AL32UTF8'),1,30) PW

from hacker_booty_base;

4. Now create the encrypting "instead of" trigger on the view to redirect the dml to the base table after encrypting it:

CREATE OR REPLACE TRIGGER blackan.ioiudr_hacker_booty

INSTEAD OF INSERT OR UPDATE OR DELETE

ON blackan.hacker_booty

FOR EACH ROW

BEGIN

if INSERTING then

insert into hacker_booty_base values (

DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW(:NEW.EmpName,'AL32UTF8'),4360,my_key),

DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.Emp_Num, 'AL32UTF8'),4360,my_key),

DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.SSN, 'AL32UTF8'),4360,my_key),

DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.Credit_Card, 'AL32UTF8'),4360,my_key),

DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.CC_Ex_Date, 'AL32UTF8'),4360,my_key),

DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.Address, 'AL32UTF8'),4360,my_key),

DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.PW, 'AL32UTF8'),4360,my_key));

elsif updating then

update hacker_booty_base set

EmpName=DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW(:NEW.EmpName,'AL32UTF8'),4360,my_key),

Emp_Num=DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.Emp_Num, 'AL32UTF8'),4360,my_key),

SSN=DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.SSN, 'AL32UTF8'),4360,my_key),

Credit_Card=DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.Credit_Card, 'AL32UTF8'),4360,my_key),

CC_Ex_Date=DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.CC_Ex_Date, 'AL32UTF8'),4360,my_key),

Address=DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.Address, 'AL32UTF8'),4360,my_key),

PW=DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW (:NEW.PW, 'AL32UTF8'),4360,my_key)

where SSN=:OLD.SSN;

else

delete from hacker_booty_base where SSN=:OLD.SSN;

end if;

end;

...and that's it. Let's insert a row of fake data:

insert into blackan.hacker_booty values ('Richy Rich',1,'123-45-6789','111 2222 3333 4444', sysdate+365, '1 Main St, St Louis, MO, 12345', 'Shhh');

When you look at the actual data-at-rest, its encrypted:

select empname, emp_num, ssn from blackan.hacker_booty_base;

EMPNAME	EMP_NUM	SSN
454460100E82C91F8B26AEE3E35EE3E8	9F1BE1034151056A760A880E5D7BC5C3	29B471D31D8E2255D1B0C7B6D5084759

When you look at the data via your application, which would use the view, you see the data exactly as you entered it:

select empname, emp_num, ssn from blackan.hacker_booty;

EMPNAME,EMP_NUM,SSN

Richy Rich 1 123-45-6789

SDE Part 1
YOU ARE HERE>> SDE Part 2
SDE Part 3

Selective Encryption - Part 1 of 3

Encryption in databases is nothing new, but its becoming a more prominent topic every day. One of my first experiences with it was in dealing with a database that had extremely confidential data, and it was a known target to foreign countries who were actively attempting to access the network to get to the database. Not to be overly dramatic, but if this data was compromised, people would be killed. I wasn't working for a Gov't agency at the time...so don't read too much into all I just said. The point is, every company stores data that needs to be secured, and the reality of it is, security is an illusion we can tell ourselves we have until we're proven wrong. So from that you have two choices:

1. Give up, its inevitable our data will be breached.
2. Make it as difficult as possible for our data to be breached, and if it is, encrypt everything valuable to make it more difficult at that point.

I choose 2. :) In the scenario I mentioned, we had security layer on security layer..and I don't mean to say this is the complete solution to make your data secured. Given the data in your database will eventually be in the hands of people who took it, what can you do? Oracle sells TDE, which does a lot of the work for you, but its a licensed feature. It used to include transparent data encryption at rest and data in flight. Thanks to a security issue a few years ago that exposed encrypted network traffic, encryption of network traffic in flight is now part of your normal db license (aka no additional cost.)

Strongauthentication services (Kerberos, PKI, and RADIUS) and network encryption (native network encryption and SSL/TLS) are no longer part of Oracle Advanced Security andare available in all licensed editions of all supported releases of the Oracle database.

What about data at rest and backups? Every company keeps PII (personal identifiable information) on their employees...Name, SSN, Address, Phone number, etc. What about your company's customers? If your unencrypted data is breached, your company is required by law to notify the people who may have had PII stolen. Would your company lose future business if this happened? I've been told the FCC requires databases storing network endpoint addresses to be 256-bit encrypted. For almost all dba's, encryption isn't really optional anymore.

So...We talked about TDE...there's also something Oracle refers to as SDE (selective data encryption) ...and this is free (1930738.1). This is really easy to implement...its not perfect, but its better than nothing. I'll show you how in the next post.

YOU ARE HERE>> SDE Part 1
SDE Part 2
SDE Part 3

Tuesday, March 29, 2016

Goldengate Monitor Memory Leak/monitoring

I've seen issues with Goldengate Monitor crashing over and over. I've had very limited success from Oracle support...one company I worked with scheduled something in cron to stop/start Monitor during a low activity time to keep it from crashing unexpectedly.

Although that may be a work around, that shouldn't be necessary. Monitor has been around for a few years now...it should be more stable than this. I found a blog today where somebody had this issue and dug pretty deeply into the root issue...here's the link:

http://darthdba.blogspot.com/

Wednesday, March 9, 2016

To OEM 13c, or not to OEM 13c?

Last week I met with some Oracle people for an OEM 13c presentation with some other customers to look at the great new features offered.

When they asked, "Who has installed 13c?". I was the only one in the group of ~30 who raised my hand. By the end of the presentation and at the urging of the Oracle guys presenting, everybody was going to rush back to the office and download and install it to take advantage of all the great new features.

This week I'm at Hotsos in Dallas at a presentation by Kellyn Pot'Vin-Gorman (DBA Kevlar), and she just mentioned a new revision of OEM (13.2) is going to be released in the next quarter with new features and a lot of refinements.

So...you might want to wait for it. :)

Monday, March 7, 2016

Hotsos 2016

...and so it begins! If you haven't heard of it...Hotsos is a symposium based in Dallas, TX focused on performance in Oracle database and tools. Its the best, most intensive, focused place to gain internals knowledge and network with some of the best minds in the industry, IMHO.

Kellyn Pot'Vin-Gormon (aka dbakevlar and OEM 13c guru) and Jeff Smith (aka The Jeff Smith) gave the keynote this morning.

Bryn Llewellyn (Oracle's PLSQL product manager) and Carlos Sierra are up next...but I'm really looking forward to Tanel Poder, Richard Foote and Kerry Osborne's presentations.

Wednesday, January 6, 2016

How to modify the preinstall rpm for non-OEL

Oracle Enterprise Linux (OEL) is a wonderful thing. Oracle does a lot of work to streamline and perfect the Redhat OS to work efficiently. Customer Support is extremely competitive relative to support directly from RH too. Unfortunately, asking a company to switch OS vendors is a little like asking somebody to switch religions. Most companies would rather be burned at the stake and become saints than switch from Linux to MS or MS to Linux or even RH to Centos or OEL. This prevents Oracle's database customers from taking advantage of some of the features available in OEL.

If you read the database install documents (and you should), they don't cover everything...there are a lot of best practices left out re:configuring your server. Some of it was discovered as bugs after the install documentation was published. The Oracheck utility is updated ~quarterly...and has the most recent best practices over and above the installation documents. The idea is that, after an install...you run oracheck and it points out anything in your configuration that differs from the current best practices. This is great...but there's an even better way....

One of the features OEL offers is the "Oracle Validate RPM", now called the preinstall RPM. This is the latest, greatest set of system configuration settings and prerequisites from Oracle, but it requires OEL. If you're forced to use RH or Centos, how can you use this RPM? You can get the open source RPM from Oracle's YUM site and modify it. When you're done, you're be able to very quickly set up a RH server for Oracle. If you run POC's for the Oracle database where you need to install the OS over and over, this can really speed things along. After you modify it, you can add it to your local yum repository. After a minimal install of RH, you just have to say "yum install my_new_rpm" and it'll set up all the prerequisites and system configurations for Oracle in a few seconds.

NOTE: If you already have a preinstall RPM for your distro, you can proceed to step 21. If you don’t, and you’re not running OEL (ie, you use Centos or RH) start at step 1.

1. Depending on your version, you either need to edit the existing or create a new repo file with the information below (change this to match your OS version...this is for 7:

1. vi /etc/yum.repos.d/public-yum-ol7.repo

[ol7_latest]

name=Oracle Linux $releasever Latest ($basearch)

baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL7/latest/$basearch/

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle

gpgcheck=0

enabled=1

2. yum install wget

3. yum install yum-utils

4. yum install rpm-build

5. cd ~

6. mkdir src

7. cd src

8. yumdownloader --source oracle-rdbms-server-12cR1-preinstall

9. Unpack the rpm into .spec and .tar.gz:

rpm2cpio oracle-rdbms-server-12cR1-preinstall-1.0-4.el7.src.rpm | cpio –idmv

10. vi oracle-rdbms-server-12cR1-preinstall.spec

11. In vi, search for Requires:kernel-uek (and anything else that looks suspiciously OEL-specific) and

comment it:

Requires:kernel-uek

Becomes:

#Requires:kernel-uek

12. tar xvzf oracle-rdbms-server-12cR1-preinstall-1.0.tar.gz

13. vi oracle-rdbms-server-12cR1-preinstall-1.0/oracle-rdbms-server-12cR1-preinstall-verify

14. Search in the file for USERID and GROUPID and change them to the userid of the oracle OS user and the oinstall group at your company. ie:

USERID="5061"

GROUPID="5011"

15. Optionally, vi oracle-rdbms-server-12cR1-preinstall-1.0/oracle-rdbms-server-12cR1-preinstall.param and change parameters as needed.

16. mv oracle-rdbms-server-12cR1-preinstall-1.0.tar.gz oracle-rdbms-server-12cR1-preinstall-1.0.tar.gz.old

17. tar -zcvf oracle-rdbms-server-12cR1-preinstall-1.0.tar.gz oracle-rdbms-server-12cR1-preinstall-1.0/oracle-rdbms-server-12cR1-preinstall-verify oracle-rdbms-server-12cR1-preinstall-1.0/oracle-rdbms-server-12cR1-preinstall-firstboot oracle-rdbms-server-12cR1-preinstall-1.0/oracle-rdbms-server-12cR1-preinstall.param

18. mkdir -p /root/rpmbuild/SOURCES

cp oracle-rdbms-server-12cR1-preinstall-1.0.tar.gz /root/rpmbuild/SOURCES/oracle-rdbms-server-12cR1-preinstall-1.0.tar.gz

19. rpmbuild -ba oracle-rdbms-server-12cR1-preinstall.spec

…look for “+ exit 0” at the last line.

20. ls ~/rpmbuild/RPMS/x86_64/oracle-rdbms-server-12cR1-preinstall*

[root@rhel7 src]# ls ~/rpmbuild/RPMS/x86_64/oracle-rdbms-server-12cR1-preinstall*

/root/rpmbuild/RPMS/x86_64/oracle-rdbms-server-12cR1-preinstall-1.0-4.el7.x86_64.rpm

/root/rpmbuild/RPMS/x86_64/oracle-rdbms-server-12cR1-preinstall-debuginfo-1.0-4.el7.x86_64.rpm

21. At this point, you have an RPM and you’re ready to go. If you can, add the new RPM to your repository and just do a yum install. ...if you don't have your own yum repository, you can do it manually:

rpm -ivh /root/rpmbuild/RPMS/x86_64/oracle-rdbms-server-12cR1-preinstall-1.0-4.el7.x86_64.rpm

(You’ll likely get output of missing rpm’s. Use yum to install everything missing.)

22. yum install [each item listed above]

You can install multiple at once, just add a space between them.

23. After the prereq's are installed...add the new RPM:

1. rpm -ivh /root/rpmbuild/RPMS/x86_64/oracle-rdbms-server-12cR1-preinstall-1.0-4.el7.x86_64.rpm

Preparing... ################################# [100%]

Updating / installing...

1:oracle-rdbms-server-12cR1-preinst################################# [100%]

24. At this point, you’re ready to install Oracle 12c. J

Search This Blog

Thursday, April 28, 2016

Tuesday, March 29, 2016

Wednesday, March 9, 2016

Monday, March 7, 2016

Wednesday, January 6, 2016