Table of Contents

effective oracle database 10g security by design
Effective Oracle Database 10g Security by Design
byDavid Knox
Oracle Press 2004 (512 pages)

With an emphasis on real-world problems and detailed solutions, this comprehensive text provides all the information you need to develop and deploy secure database applications, covering the entire security cycle.

Table of Contents
Effective Oracle Database 10g Security by Design
Part I - Quick Start
Chapter 1 - General Security Best Practices
Chapter 2 - Securing The Database
Part II - Identification and Authentication
Chapter 3 - Understanding Identification and Authentication
Chapter 4 - Connection Pools and Proxy Authentication
Chapter 5 - Identity Management and Enterprise Users
Chapter 6 - Identification and Authentication for Web Applications
Part III - Authorizations and Auditing
Chapter 7 - Privileges and Roles
Chapter 8 - Effective Auditing for Accountability
Part IV - Fine-Grained Access Control
Chapter 9 - Application Contexts For Security And Performance
Chapter 10 - Implementing Fine-Grained Access Controls with Views
Chapter 11 - Row-Level Security with Virtual Private Database
Chapter 12 - Oracle Label Security
Chapter 13 - Database Encryption
Part V - Appendixes
Appendix A - Setting Up the Security Manager
Appendix B - DATA_CRYPTO Package
Appendix C - DBMS_CRYPTO Performance Test Results


Experience is often the best teacher. That’s true for life in general and certainly true for cybersecurity. In my experience in the security world, including 32 years at the Central Intelligence Agency, I’ve not found anyone with more experience in the theory and practice of protecting data than David Knox. David’s expertise in this field is born of both his academic training and especially his more than eight years of hands-on experience with Oracle Corporation. In my view, the real value of this book is centered in David’s ability to treat technical issues in depth as well as his ability to discuss these issues in terms that the less technically inclined of us can understand. That’s especially important today because cybersecurity is as much a responsibility of the CEO and the Board of Directors as it is of the organization’s information technology provider. This book may not replace your favorite mystery novel on your bedside table, but I guarantee it will become dog-eared with use. Mine has.

—David W. Carey
former Executive Director
of the Central Intelligence Agency


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

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:


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)=
       || '@'
                  'KNOX10G', 'KNOX10g',
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.