Contents

Title and Copyright Information

List of Figures

List of Tables

Send Us Your Comments

Preface

Related Documentation

Documentation Accessibility

What's New in Oracle Database Security?

New Features in Virtual Private Database

New Features in Auditing

New PL/SQL Encryption Package: DBMS_CRYPTO

Part I Overview of Security Considerations and Requirements

1 Security Requirements, Threats, and Concepts

Identity Management: Security in Complex, High Volume Environments

Desired Benefits of Identity Management

Components of Oracle's Identity Management Infrastructure

2 Security Checklists and Recommendations

Physical Access Control Checklist

Personnel Checklist

Secure Installation and Configuration Checklist

Networking Security Checklists

SSL (Secure Sockets Layer) Checklist

Client Checklist

Listener Checklist

Network Checklist

3 Security Policies and Tips

Introduction to Database Security Policies

Security Threats and Countermeasures

What Information Security Policies Can Cover

Recommended Application Design Practices to Reduce Risk

Tip 1: Enable and Disable Roles Promptly

Tip 2: Encapsulate Privileges in Stored Procedures

Tip 3: Use Role Passwords Unknown to the User

Tip 4: Use Proxy Authentication and a Secure Application Role

Tip 5: Use Secure Application Role to Verify IP Address

Tip 6: Use Application Context and Fine-Grained Access Control

Part II Security Features, Concepts, and Alternatives

4 Authentication Methods

5 Authorization: Privileges, Roles, Profiles, and Resource Limitations

Introduction to Privileges

System Privileges

Granting and Revoking System Privileges

Who Can Grant or Revoke System Privileges?

Schema Object Privileges

Granting and Revoking Schema Object Privileges

Who Can Grant Schema Object Privileges?

Using Privileges with Synonyms

Table Privileges

Data Manipulation Language (DML) Operations

Data Definition Language (DDL) Operations

View Privileges

Privileges Required to Create Views

Increasing Table Security with Views

Procedure Privileges

Procedure Execution and Security Domains

System Privileges Needed to Create or Alter a Procedure

Packages and Package Objects

Type Privileges

System Privileges for Named Types

Object Privileges

Method Execution Model

Privileges Required to Create Types and Tables Using Types

Example of Privileges for Creating Types and Tables Using Types

Privileges on Type Access and Object Access

Type Dependencies

Introduction to Roles

Properties of Roles

Common Uses for Roles

Application Roles

Granting and Revoking Roles

Who Can Grant or Revoke Roles?

Security Domains of Roles and Users

PL/SQL Blocks and Roles

Named Blocks with Definer's Rights

Anonymous Blocks with Invoker's Rights

Data Definition Language Statements and Roles

Predefined Roles

The Operating System and Roles

Roles in a Distributed Environment

Secure Application Roles

Creation of Secure Application Roles

User Resource Limits

Types of System Resources and Limits

Limiting Other Resources

Profiles

Determining Values for Resource Limits

6 Access Controls on Tables, Views, Synonyms, or Rows

Introduction to Views

Fine-Grained Access Control

Dynamic Predicates

Application Context

Dynamic Contexts

Security Followup: Auditing as well as Prevention

7 Security Policies

System Security Policy

Database User Management

User Authentication

Operating System Security

Data Security Policy

User Security Policy

General User Security

Password Security

Privilege Management

End-User Security

Using Roles for End-User Privilege Management

Using a Directory Service for End-User Privilege Management

Administrator Security

Protection for Connections as SYS and SYSTEM

Protection for Administrator Connections

Using Roles for Administrator Privilege Management

Application Developer Security

Application Developers and Their Privileges

The Application Developer's Environment: Test and Production Databases

Free Versus Controlled Application Development

Roles and Privileges for Application Developers

Space Restrictions Imposed on Application Developers

Application Administrator Security

Password Management Policy

Account Locking

Password Aging and Expiration

Password History

Password Complexity Verification

Password Verification Routine Formatting Guidelines

Sample Password Verification Routine

Auditing Policy

A Security Checklist

8 Database Auditing: Security Considerations

Auditing Types and Records

Audit Records and the Audit Trails

Database Audit Trail (DBA_AUDIT_TRAIL)

Operating System Audit Trail

Operating System Audit Records

Records Always in the Operating System Audit Trail

When Are Audit Records Created?

Statement Auditing

Privilege Auditing

Schema Object Auditing

Schema Object Audit Options for Views, Procedures, and Other Elements

Focusing Statement, Privilege, and Schema Object Auditing

Auditing Statement Executions: Successful, Unsuccessful, or Both

Number of Audit Records from Multiple Executions of a Statement

Auditing in a Multitier Environment

Fine-Grained Auditing

Part III Security Implementation, Configuration, and Administration

9 Administering Authentication

User Authentication Methods

Database Authentication

Creating a User Who is Authenticated by the Database

Advantages of Database Authentication

External Authentication

Creating a User Who is Authenticated Externally

Operating System Authentication

Network Authentication

Advantages of External Authentication

Global Authentication and Authorization

Creating a User Who is Authorized by a Directory Service

Advantages of Global Authentication and Global Authorization

Proxy Authentication and Authorization

Authorizing a Middle Tier to Proxy and Authenticate a User

Authorizing a Middle Tier to Proxy a User Authenticated by Other Means

10 Administering User Privileges, Roles, and Profiles

Managing Oracle Users

Specifying a Name

Setting a User's Authentication

Assigning a Default Tablespace

Assigning Tablespace Quotas

Assigning a Temporary Tablespace

Specifying a Profile

Setting Default Roles

Changing a User's Authentication Mechanism

Changing a User's Default Roles

Viewing Information About Database Users and Profiles

User and Profile Information in Data Dictionary Views

Listing All Users and Associated Information

Listing All Tablespace Quotas

Listing All Profiles and Assigned Limits

Viewing Memory Use for Each User Session

Managing Resources with Profiles

Dropping Profiles

Understanding User Privileges and Roles

System Privileges

Restricting System Privileges

Accessing Objects in the SYS Schema

Object Privileges

Managing User Roles

Creating a Role

Specifying the Type of Role Authorization

Role Authorization by the Database

Role Authorization by an Application

Role Authorization by an External Source

Role Authorization by an Enterprise Directory Service

Granting User Privileges and Roles

Granting System Privileges and Roles

Granting the ADMIN OPTION

Creating a New User with the GRANT Statement

Granting Object Privileges

Specifying the GRANT OPTION

Granting Object Privileges on Behalf of the Object Owner

Granting Privileges on Columns

Row-Level Access Control

Revoking User Privileges and Roles

Revoking System Privileges and Roles

Revoking Object Privileges

Revoking Object Privileges on Behalf of the Object Owner

Revoking Column-Selective Object Privileges

Revoking the REFERENCES Object Privilege

Cascading Effects of Revoking Privileges

System Privileges

Object Privileges

Granting to and Revoking from the User Group PUBLIC

When Do Grants and Revokes Take Effect?

The SET ROLE Statement

Specifying Default Roles

Restricting the Number of Roles that a User Can Enable

Granting Roles Using the Operating System or Network

Using Operating System Role Identification

Using Operating System Role Management

Granting and Revoking Roles When OS_ROLES=TRUE

Enabling and Disabling Roles When OS_ROLES=TRUE

Using Network Connections with Operating System Role Management

Viewing Privilege and Role Information

Listing All System Privilege Grants

Listing All Role Grants

Listing Object Privileges Granted to a User

Listing the Current Privilege Domain of Your Session

Listing Roles of the Database

Listing Information About the Privilege Domains of Roles

11 Configuring and Administering Auditing

Actions Audited by Default

Guidelines for Auditing

Keep Audited Information Manageable

Auditing Normal Database Activity

Auditing Suspicious Database Activity

Auditing Administrative Users

Decide Whether to Use the Database or Operating System Audit Trail

What Information is Contained in the Audit Trail?

Database Audit Trail Contents

Audit Information Stored in an Operating System File

Managing the Standard Audit Trail

Enabling and Disabling Standard Auditing

Setting the AUDIT_TRAIL Initialization Parameter

Setting the AUDIT_FILE_DEST Initialization Parameter

Standard Auditing in a Multitier Environment

Setting Standard Auditing Options

Specifying Statement Auditing

Specifying Privilege Auditing

Specifying Object Auditing

Turning Off Standard Audit Options

Turning Off Statement and Privilege Auditing

Turning Off Object Auditing

Controlling the Growth and Size of the Standard Audit Trail

Purging Audit Records from the Audit Trail

Archiving Audit Trail Information

Reducing the Size of the Audit Trail

Protecting the Standard Audit Trail

Auditing the Standard Audit Trail

Viewing Database Audit Trail Information

Audit Trail Views

Using Audit Trail Views to Investigate Suspicious Activities

Listing Active Statement Audit Options

Listing Active Privilege Audit Options

Listing Active Object Audit Options for Specific Objects

Listing Default Object Audit Options

Listing Audit Records

Listing Audit Records for the AUDIT SESSION Option

Deleting the Audit Trail Views

Example of Auditing Table SYS.AUD$

Fine-Grained Auditing

Policies in Fine-Grained Auditing

Advantages of Fine-Grained Auditing over Triggers

Extensible Interface Using Event Handler Functions

Functions and Relevant Columns in Fine-Grained Auditing

Audit Records in Fine-Grained Auditing

NULL Audit Conditions

Defining FGA Policies

An Added Benefit to Fine-Grained Auditing

The DBMS_FGA Package

ADD_POLICY Procedure

DROP_POLICY Procedure

ENABLE_POLICY Procedure

DISABLE_POLICY Procedure

12 Introducing Database Security for Application Developers

About Application Security Policies

Considerations for Using Application-Based Security

Are Application Users Also Database Users?

Is Security Enforced in the Application or in the Database?

Managing Application Privileges

Creating Secure Application Roles

Example of Creating a Secure Application Role

Associating Privileges with the User's Database Role

Using the SET ROLE Statement

Using the SET_ROLE Procedure

Examples of Assigning Roles with Static and Dynamic SQL

Protecting Database Objects Through the Use of Schemas

Managing Object Privileges

What Application Developers Need to Know About Object Privileges

SQL Statements Permitted by Object Privileges

13 Using Virtual Private Database to Implement Application Security Policies

About Virtual Private Database, Fine-Grained Access Control, and Application Context

Introduction to VPD

Column-level VPD

Column-level VPD with Column Masking Behavior

VPD Security Policies and Applications

Introduction to Fine-Grained Access Control

Features of Fine-Grained Access Control

Table-, View-, or Synonym-Based Security Policies

Multiple Policies for Each Table, View, or Synonym

Grouping of Security Policies

High Performance

Default Security Policies

About Creating a Virtual Private Database Policy with Oracle Policy Manager

Introduction to Application Context

Features of Application Context

Specifying Attributes for Each Application

Providing Access to Predefined Attributes through the USERENV Namespace

Externalized Application Contexts

Ways to Use Application Context with Fine-Grained Access Control

Using Application Context as a Secure Data Cache

Using Application Context to Return a Specific Predicate (Security Policy)

Using Application Context to Provide Attributes Similar to Bind Variables in a Predicate

Introduction to Global Application Context

Enforcing Application Security

Use of Ad Hoc Tools a Potential Security Problem

Restricting SQL*Plus Users from Using Database Roles

Limit Roles Through PRODUCT_USER_PROFILE

Use Stored Procedures to Encapsulate Business Logic

Use Virtual Private Database for Highest Security

Virtual Private Database and Oracle Label Security Exceptions and Exemptions

User Models and Virtual Private Database

14 Implementing Application Context and Fine-Grained Access Control

About Implementing Application Context

How to Use Application Context

Task 1: Create a PL/SQL Package that Sets the Context for Your Application

SYS_CONTEXT Example

SYS_CONTEXT Syntax

Using Dynamic SQL with SYS_CONTEXT

Using SYS_CONTEXT in a Parallel Query

Using SYS_CONTEXT with Database Links

Task 2: Create a Unique Context and Associate It with the PL/SQL Package

Task 3: Set the Context Before the User Retrieves Data

Task 4. Use the Context in a VPD Policy Function

Examples: Application Context Within a Fine-Grained Access Control Function

Example 1: Implementing the Policy

Step 1. Create a PL/SQL Package Which Sets the Context for the Application

Step 2. Create an Application Context

Step 3. Access the Application Context Inside the Package

Step 4. Create the New Security Policy

Example 2: Controlling User Access by Way of an Application

Step 1. Create a PL/SQL Package to Set the Context

Step 2. Create the Context and Associate It with the Package

Step 3. Create the Initialization Script for the Application

Example 3: Event Triggers, Application Context, Fine-Grained Access Control, and Encapsulation of Privileges

Initializing Application Context Externally

Obtaining Default Values from Users

Obtaining Values from Other External Resources

Initializing Application Context Globally

Application Context Utilizing LDAP

How Globally Initialized Application Context Works

Example: Initializing Application Context Globally

How to Use Global Application Context

Using the DBMS_SESSION Interface to Manage Application Context in Client Sessions

Examples: Global Application Context

Example 1: Global Application Context

Example 2: Global Application Context for Lightweight Users

How Fine-Grained Access Control Works

How to Establish Policy Groups

The Default Policy Group: SYS_DEFAULT

New Policy Groups

How to Implement Policy Groups

Step 1: Set Up a Driving Context

Step 2: Add a Policy to the Default Policy Group.

Step 3: Add a Policy to the HR Policy Group

Step 4: Add a Policy to the FINANCE Policy Group

Validation of the Application Used to Connect

How to Add a Policy to a Table, View, or Synonym

DBMS_RLS.ADD_POLICY Procedure Policy Types

Optimizing Performance by Enabling Static and Context Sensitive Policies

About Static Policies

About Context Sensitive Policies

Adding Policies for Column-Level VPD

Default Behavior

Column Masking Behavior

Enforcing VPD Policies on Specific SQL Statement Types

Enforcing Policies on Index Maintenance

How to Check for Policies Applied to a SQL Statement

Users Who Are Exempt from VPD Policies

SYS User Exempted from VPD Policies

EXEMPT ACCESS POLICY System Privilege

Automatic Reparse

VPD Policies and Flashback Query

15 Preserving User Identity in Multitiered Environments

Security Challenges of Three-tier Computing

Who Is the Real User?

Does the Middle Tier Have Too Much Privilege?

How to Audit? Whom to Audit?

What Are the Authentication Requirements for Three-tier Systems?

Client to Middle Tier Authentication

Middle Tier to Database Authentication

Client Re-Authentication Through Middle Tier to Database

Oracle Database Solutions for Preserving User Identity

Proxy Authentication

Passing Through the Identity of the Real User by Using Proxy Authentication

Limiting the Privilege of the Middle Tier

Re-authenticating The User through the Middle Tier to the Database

Auditing Actions Taken on Behalf of the Real User

Advantages of Proxy Authentication

Client Identifiers

Support for Application User Models by Using Client Identifiers

Using the CLIENT_IDENTIFIER Attribute to Preserve User Identity

Using CLIENT_IDENTIFIER Independent of Global Application Context

16 Developing Applications Using Data Encryption

Securing Sensitive Information

Principles of Data Encryption

Principle 1: Encryption Does Not Solve Access Control Problems

Principle 2: Encryption Does Not Protect Against a Malicious DBA

Principle 3: Encrypting Everything Does Not Make Data Secure

Solutions For Stored Data Encryption in Oracle Database

Oracle Database Data Encryption Capabilities

Data Encryption Challenges

Encrypting Indexed Data

Key Transmission

Storing the Keys in the Database

Storing the Keys in the Operating System

Users Managing Their Own Keys

Changing Encryption Keys

Binary Large Objects (BLOBS)

Example of a Data Encryption PL/SQL Program

Example of Encrypt/Decrypt Procedures for BLOB Data

Glossary

Index