Introduction

The database has two important roles: 1. Serve the data—databases are commonly referred to as data servers; 2. Protect the data. Yes that’s right, the database is there to serve and protect. Database security features are vast and complex. While the complexity can afford us many ways to provide a robust database application, it also can be very confusing. Users need to know what is available, when to use it, when not to use it, and how it can be leveraged with other complementary technologies. Knowledge is power. Not utilizing these features is analogous to the blinking 12:00 on the VCR—you are not getting your money’s worth.

Effective Oracle Database 10g Security by Design provides solutions for the Oracle security puzzle and includes the new Oracle Database 10g features. Recommendations, best practices, and code examples lead the reader through examples that illustrate how to build secure applications. This book shows how to effectively utilize, in a complementary manner, the most common Oracle product features: proxy authentication, secure application roles, Enterprise Users, Virtual Private Database, Oracle Label Security, database encryption, and standard and fine-grained auditing. Web applications and client-server applications are addressed as well as PL/SQL programming security best practices. The explanations allow a non-security expert to grasp the relevance of the technology, and the countless examples comprehensively show the different nuances associated with each technology. All of the information will allow you to effectively design, develop, and deploy secure database applications.

This book is targeted to Oracle Database application developers, Oracle DBAs, and anyone whose role is to ensure that proper procedures and due diligence have been followed in building applications (CIO’s, CISO’s, etc.). This book is about designing, building, and deploying secure applications running against an Oracle Database. The challenge faced for people wishing to do this today is that there are few, if any, best practice documents, technical blueprints (architectures), or other reference guidelines showing how to link together varying technologies to build secure database applications.

row-level security with views, VPD, OLS, and the new DBMS_CRYPTO encryption package. The new Oracle Database 10g features aren’t alluded to or quickly summarized but rather are presented in comprehensive yet easy-to-understand detail.

Throughout the book, careful attention has been paid to provide enough information to describe a technology without being redundant with Oracle Corporation’s existing documentation. Since it’s useful to be pointed to the place in the official documentation that describes a technology, you’ll see references to other documents throughout the book. Unless otherwise noted, the documents are part of Oracle Corporation’s product documentation, which can be retrieved from the Oracle Technology Network web site at http://otn.oracle.com.

The examples given in this book are generally run by the SEC_MGR. See Appendix A for how this schema is created and how privileges are granted. Many of the examples are drawn from or use the SCOTT schema and the EMP and DEPT tables, because the tables are simple and most Oracle DBAs are familiar with this schema. The script to install SCOTT is located in the $ORACLE_HOME/rdbms/admin/scott.sql file. A BLAKE user was also created for some of the examples. You can create this user as follows:

CREATE USER blake IDENTIFIED BY blake; GRANT CREATE SESSION TO blake;

Almost all the code samples are displayed as the output from a SQL*Plus session. There are three reasons for this. First, it shows and validates that the example works. Second, line numbers are automatically displayed, which helps in the readability of the code. Third, the SQL*Plus prompt has been set up with the user’s name and database instance. This was performed by adding the following code to the login.sql file in the $ORACLE_HOME/sqlplus/admin directory.

COLUMN global_name new_value gname  SET termout off  SELECT    LOWER (USER)=        || '@'        || DECODE (GLOBAL_NAME,                   'KNOX10G', 'KNOX10g',                   GLOBAL_NAME) GLOBAL_NAME    FROM GLOBAL_NAME; SET sqlprompt '&gname> ' SET termout on

This step is helpful because it shows who was executing the code and on which database. I find it is too easy to get confused without these hints.

While product documentation covers the features and functions of a specific product or technology, it often omits how to piece together these various technologies to create effective layers of security. For example, Oracle documentation may describe how to set up Enterprise Users, but it doesn’t give any examples of how to link Enterprise Users with proxy authentication, auditing, and its row-level security products. It’s rational that the documentation remains autonomous; however, it’s then incumbent upon the developer, architect, or DBA to figure out how to link these eclectic pieces together to form some basis of security. They also, then, have to decide when it is and is not appropriate to use a technology. This book offers not only brief functional descriptions, but also instructions on how to use these technologies as tools to build a high assurance technology platform.