Monday, November 21, 2011

Source Code For Download

We had a bit of a problem coordinating my source code submissions with the Apress web site for the book, so the source code may not have been available for download, for those who looked. Here is another link for the source code:

Updated on 12/09/2011

In this update I have made all the source available under the GNU General Public License.

Friday, November 4, 2011

The Final Solution

It will be helpful for those evaluating this book and considering spending their hard-earned cash to acquire a copy, to know what the final solution is -- where this book is going. Some of it is obvious from the title and descriptions on the cover; but I would like to put into just a few paragraphs a description of the security system that we develop in the book.

First let me say that there are only two things required to complete the entire system: download of Oracle 11g Enterprise edition and download of Java Development Kit (JDK) 1.5 or higher. These two downloads are free! All the rest is code that we develop in the book. You will not be burdened by any other application frameworks or ancillary (additional-purchase) applications.

The Final Solution:
Each user has an Oracle account (options are discussed and are possible), identified externally (no password). The user opens a Java application on their computer. The Java application gets the logged-in user ID from NTSystem or UnixSystem (JAAS). Thus begins Single Sign-On. The authenticated user establishes a proxy connection through a centralized access user.

The password for the centralized access user is the only password that is exposed in the application. For this user, we define him on a hardened Oracle database instance (as described in the book). He has very little access - just enough to be an application gatekeeper. His password is embedded in the application in encoded form. Various password encryption / hiding / access methods are discussed in the book, but we resolve to an encoded password that is encoded (encrypted) using an obfuscated Java class.

The access user determines if a 2-factor authentication code was provided, and if not, Oracle sends a valid for 10-minute, 2-factor code to mobile devices (cell phone SMS or pager) and/or e-mail for the authenticated user. Once the code is received, the user submits the code in a second connection, proxied through the access user. If the application, authenticated user, and 10-minute time to live are all acceptable, a series of encrypted Oracle passwords (connection strings) are returned for use in the application.

These passwords are encrypted on disk using DBMS_CRYPTO and a calculated password, specific to the client application. They are decrypted, then reencrypted using session-specific encryption for the current user / application. The session-specific encryption uses random encryption parameters like this: First a public / private RSA key pair is generated and the artifacts of the public key are submitted to Oracle. The Oracle instance generates a DES shared password key using random algorithm parameters (respect the power of random) and transmits the parameters to the client application -- the DES key parameters are encrypted with the RSA public key. Those passwords (connection strings) that Oracle returns to the client application are encrypted with the DES key for decryption as needed.

So now the application is free to begin using sensitive Oracle data. One of the encrypted connection strings is decrypted just-in-time, and the authenticated user is validated as a user of that Oracle connection through a secure application procedure. The user must be granted access to proxy through the Oracle user and must be granted the application role required for the connection. It is all very complex, and this brief summary is insufficient (so buy the book, please).

When we connected with this new encrypted connection string, and the user was validated for access, a new set of session-specific RSA and DES keys was generated. All sensitive data transferred for use in the application is transferred in encrypted form. We primarily use stored procedures that return result sets (cursors) for queries and stored procedures for data updates.

In the last chapter, I provide a GUI interface for bootstrapping the entire security system and then managing users, access, administration, applications and connection strings. If you'd rather build it than buy it, you can have Expert Security using this book. Or if you just want to learn secure programming approaches and the technologies used in commercial products, then this is a good way to get that insight. There is also a Supplement with the source code that provides some further insights and scenarios.

Oracle Security Controversies

There are several areas of security that I delve into in the book where I take a contrarian position: Client Wallets, Virtual Private Database and Password-Protected Roles. It is not so much that I oppose using those things; it's just that there are security weaknesses that I believe are unacceptable. There are also some areas that I discuss in the book that need to be considered carefully when used in your Oracle Security scheme, for example Password storage for Data On Disk Encryption.

Why are Client Wallets bad?
Client Wallets are a secure storage file for Oracle account passwords. The wallet file is encrypted with a password, so it cannot be easily deciphered. The algorithm parameters are embedded in Java and DLLs, so they are not beyond attack, but that is not the real problem. The truly scary reality of client wallet files is that if someone gets hold of the file, they can use it to log into Oracle as the target account without any further authentication. Normally these files are stored in user's home directories, and so the assumption is that they are protected by the Operating System file system security; but the wallet is simply a file that can be copied or even e-mailed (by a hacker, administrator or hapless user), or even stolen from backups or archives.

One more security lapse that is created by client wallet files is the dependency on SQLnet configuration files for application. An encrypted password in the wallet is tied to an entry in the TNSNames.ora file or equivalent. For example the user mUser on the Oracle instance mOra might have a TNSNames entry mOra_mUser for which a password exists in the client wallet. The obvious security loophole here is that the TNSNames entry can point to ANY oracle instance -- if the Oracle user exists on that instance, the client wallet can be used to log in as that user. In some Oracle instances, mUser may have more privileges or more sensitive data access, and if the user account has the same password on that instance, the wallet can be used to get access, even if that's not the instance for which the wallet entry was created.

What's wrong with Virtual Private Database?
Let me say that there is nothing really wrong with VPD, and it can be configured so that it does enhance security. But the way it is usually defined, it bases security on data. For example: only show the current user rows in the employee table for employees with the same company code as the current user. This is data filtering data -- it is not a basis for security.

In the book, I show VPD based on two parameters that are security-related, not data-related: proxy grants and role grants. I also show that without VPD, by limiting grants to the data and limiting grants to stored procedures, you can embed dynamic where clauses (the heart of VPD) into your views and procedures and accomplish the same thing. My argument for doing that is to make the security enforcement obvious and not hidden in policy settings. There is nothing inherently more secure about a policy setting, which can be unset with a single command, than in the application of common security measures.

My take on Password-Protected Roles.
In Oracle 11g, Oracle Corporation has removed the ability to acquire a password-protected role without entering the password, even if the role is a default password. This is a good correction, and my argument is that a role should not be granted if the user should not have access. Then if the user should have access, why is a password required?

I realize that some applications acquire the password and then set the role, and the idea is that within the application a user can get the role, but from outside the application, she can not. Well, we have to ask, can she really NOT get the password and get the role? Perhaps not if you are limiting all other clients by Product User Profile. But an impostor application that presents the same Product User Profile (or none) can do whatever the original application does to acquire the role: query a view or execute a procedure or function.

In other words, the addition of passwords to roles buys you nothing, but requires more user maintenance and code support. My final analysis is that you should not grant a role to anyone who does not need it all the time, and if you really want to password-protect a role, then make the user enter it -- do not encode the password acquisition.

Password Storage for Data Encryption on Disk
As in the discussion of Client Wallet encryption, here again the algorithms and parameters used for encrypting data using the DBMS_CRYPTO package are codified in Java and DLLs and perhaps wrapped procedures. They can be attacked. But once again, that is not the real problem. The problem is where to store the password.

The solution I came up with is to calculate an application-specific password based on identity artifacts from the application and designated starter codes. I use a complex wrapped procedure to do the password calculation. So where is the password stored? It really isn't. Someone accessing the data or the Oracle server will not find password records in the database tables, nor in files on the server. It would require much more effort.

PUBLIC and the SYS Data Dictionary Views
For the most part, those views in the data dictionary that are granted to PUBLIC are helpful and probably even necessary for use of the database. However, it would be nice to be able to create an Oracle user/schema and not have them acquire ANYTHING. Perhaps I handle authentication through a database (as I do in the book), so I have to let users connect, but I don't want to expose some of the PUBLIC data dictionary views to the user until they have proven themselves. I solve this by turning off PUBLIC access to views like SYS.ALL_USERS.

What I would prefer is for PUBLIC to be a regular role, with one addition. PUBLIC never disappears, even when you set role -- that is a nice feature. I would like to be able to revoke PUBLIC from users, or to have the requirement to specifically grant PUBLIC before it became active. Perhaps there could be a PUBLIC_REQUIRED like the current PUBLIC, and a PUBLIC_OPTIONAL that is more like an ordinary role, that can be granted and revoked, with the additional feature of never disappearing.

That is all the controversies I'd like to address at this moment. I hope this discussion has led to more insight, not just more argument.

Synonyms Are Not Good
This is something I spend time discussing in the book, and I wanted to mention it as a controversy... Perhaps you and your application programmers depend on synonyms to find things scattered around many different schemas. This is a bad dependency - just say "No".

Basically, there are security, code-sharing and maintenance issues, as well as potential production issues with code promoted from a lower environment. All this for a shortcut syntax!?! Just say "No", and make everyone refer to your Oracle structures with the schema prefix. This goes for public synonyms as well as private.

I admit that one instance of a synonym may be beneficial - a synonym for a structure reference across a database link. The benefits in this case are (1) removing the complex reference from the application code and (2) giving the Oracle DBAs an extra layer of flexibility to use in moving databases and structures around. Yes, these may be the same arguments (flexibility) that you use for regular synonyms; but if you are using views, you already have sufficient flexibility for local structures. In truth, you can point a database link at the new location and get flexibility with just the link, but you may want to maintain link names that correlate to the database instance names, so you need another layer of flexibility.

Friday, October 7, 2011

Book Code Corrections

For the VPD functions defined in Chapter 12, the return clause lengths that I defined are insufficient. The return clause for appsec.apps_for_admin is almost 400 characters, so it is fine; but I would increase the definition of rtrn_clause to VARCHAR2(600). In appsec.apps_for_user, the return clause is over 600 characters already, so I would increase the definition of rtrn_clause in that function to VARCHAR2(1000). I like to have room to breath, even if the actual lengths of these return clauses never change.

I will make this correction in the source code in the next update.

Sunday, October 2, 2011

Updates to Supplement

This is an update to the section entitled: Using Structures without Two-Factor Authentication

I'm going to give you the quickest path to disabling the 2-factor authentication. This will not remove references to 2-factor authentication from the code, nor will it remove the exchange of authentication codes between the client and server; it's just that, the blank codes will be exchanged and accepted. No codes will be required, and no codes will be sent to mobile devices or e-mail.

Modify the appsec.appsec_public_pkg.p_get_app_conns procedure, commenting the 2-factor authentication code test and distribute lines, as shown below:

--IF( m_two_factor_cd IS NULL )
--THEN
-- m_err_txt := appsec_only_pkg.f_send_2_factor( return_user, m_application_id );
--ELSIF( appsec_only_pkg.f_is_cur_cached_cd(
-- return_user, m_application_id, m_two_factor_cd ) = 'Y' )
--THEN
secret_pass_salt :=
app_sec_pkg.f_get_crypt_secret_salt( ext_modulus, ext_exponent );
secret_pass_count :=
app_sec_pkg.f_get_crypt_secret_count( ext_modulus, ext_exponent );
secret_pass :=
app_sec_pkg.f_get_crypt_secret_pass( ext_modulus, ext_exponent );
secret_pass_algorithm :=
app_sec_pkg.f_get_crypt_secret_algorithm(ext_modulus, ext_exponent);
m_crypt_connections := appsec_only_pkg.f_get_crypt_conns( m_class_instance );
--ELSE
-- -- Wrong 2-Factor code entered
-- RAISE NO_DATA_FOUND;
--END IF;

Make that same change to the appsec.appsec_admin_pkg.p_copy_app_conns procedure.

Sunday, September 25, 2011

What was I thinking?

This morning I realized that I have exaggerated in my author biography. While it is true that I have over 30 years' experience programming computers, I would not categorize my responsibility as "multi-platform network integration and systems programming" until starting in about 1985. That would make it more like 26 years.

I apologize for the exaggeration in my author biography. I really should have said "over 25 years".

In 1985, my multi-platform network and system programming consisted of serial ports and modems, exporting mainframe database records for import to a personal computer project management system. From those simple beginnings, it wasn't until 1989 that I installed and managed my first packet-switching (more like modern) network for my workgroup. Then in 1991, I managed the most complex network of my career. Since then, multi-platform networking has become pretty much simpler.

In 1991, we had to install Ethernet cards in our computers and set a non-conflicting IRQ. And we had to add software to the Windows 3.1 operating system in order to do networking. We used either Trumpet winsock or what became our standard, the Netware client software. Each computer had to have a unique TCP/IP address manually set, and had to also speak IPX/SPX for Netware.

We also had a whole other side of the house in 1991: Apple. Those folks networked with Appletalk at the time (not even Ethertalk) and I connected both sides together using a Gaterbox. I can still remember the feeling of success I had when I printed a screen capture of my Windows 3.1 desktop on a color Apple printer through a Netware print queue (using the Netware NLM for Appletalk and the Gatorbox). I still have that printout.

From where I worked, we could share files and information world-wide, but the world-wide-web was just a rumor. I didn't know I needed it, because I had Telnet and FTP.

What was computing like for me, 30 years ago? I learned BASIC on a terminal to the mainframe in class, and had a SHARP programmable calculator that ran a simplified version of BASIC. In my last year of undergraduate college, my dad bought a Radio Shack TRS-80, Model 2 with a cassette deck for storage. I knew enough BASIC to use the TRS-80 to communicate through the serial port with a plotter and have it render my design class CAD drawings in color, from multiple perspectives (of course I calculated and entered all the points manually.)

When I got my first real job, the computing resources at work were limited to dedicated word processors (a cross between a typewriter and a computer) and punch cards which I transported to the local university to have run and have reports printed. When I got my second real job, I got access to an original IBM PC. I started doing all my work in Lotus 123. This was in 1984. Big Brother became my friend, or was that Compuserve (I spent hours using TAPCIS over a modem). I bought a Compaq portable (definitely not a laptop computer) with DOS 1 something and an AST 6-pack board, an external modem and a plus (+) drive, 10 megabytes! My software was all from a company named Ashton-Tate. I communicated with friends and played long games on Bulletin Board Systems.

If you recognize what I said in that last paragraph, then you are old!

Tuesday, September 20, 2011

Why I Wrote the Book

Beyond what I said in the front-matter of the book, Expert Oracle and Java, Programming Secure Oracle Applications in Java, I have other agendas. Perhaps one being that desire to have my skills and accomplishments recognized... it would be an illusion if I pretended that vanity didn't enter my psyche. I have a bit more altruistic goals as well. For one, I wanted to provide tools that you, my peer programmers can use, collected in one volume. Unfortunately, you will not find the book to be organized as a handy reference guide, because of my other goal. My primary goal is to teach, to have you delve down deep into application security issues and to apply some practical coding practices to address the issues. A final goal that I have is definitely selfish -- I want to learn. I am a lifelong student, and this book provided a focus not just for pulling together what I knew, but also for exploring and learning what I had not previously known.