Contents

List of Examples

List of Figures

List of Tables

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Organization
• Related Documentation
• Conventions

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 Identity Management Infrastructure

2 Security Checklists and Recommendations

• Physical Access Control Checklist
• Personnel Checklist
• Secure Installation and Configuration Checklist
• Networking Security Checklists
  • SSL 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 Roles to Verify IP Address
  • Tip 6: Use Application Context and Fine-Grained Access Control

Part II Security Features, Concepts, and Alternatives

4 Authentication Methods

• Authentication by the Operating System
• Authentication by the Network
  • Authentication Using SSL
  • Authentication Using Third-Party Services
    • Kerberos Authentication
    • PKI-Based Authentication
    • Authentication with RADIUS
    • Directory-Based Services
• Authentication by Oracle Database
  • Password Encryption While Connecting
  • Account Locking
  • Password Lifetime and Expiration
  • Password History
  • Password Complexity Verification
• Multitier Authentication and Authorization
  • Clients, Application Servers, and Database Servers
  • Security Issues for Middle-Tier Applications
  • Identity Issues in a Multitier Environment
  • Restricted Privileges in a Multitier Environment
    • Client Privileges
    • Application Server Privileges
• Authentication of Database Administrators

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
    • DML Operations
    • 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 of Roles
    • Application Roles
    • User 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
  • DDL Statements and Roles
  • Predefined Roles
  • 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
    • Session Level
    • Call Level
    • CPU Time
    • Logical Reads
    • Limiting Other Resources
• Profiles
  • Determining Values for Resource Limits

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

• Introduction to Views
• Fine-Grained Access Control
  • Dynamic Predicates
  • Application Context
  • Dynamic Contexts
• Security Followup: Auditing and 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
    • Application Developer 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
  • Setting the PASSWORD_LIFE_TIME Profile Parameter to a Low Value
  • 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 Audit Trails
    • Database Audit Trail (DBA_AUDIT_TRAIL)
    • Operating System Audit Trail
    • Syslog Audit Trail
    • Operating System and Syslog Audit Records
    • Records Always in the Operating System and Syslog 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
    • BY SESSION
    • BY ACCESS
  • Audit by User
• Auditing in a Multitier Environment
• Fine-Grained Auditing

Part III Security Implementation, Configuration, and Administration

9 Secure External Password Store

• How Does the External Password Store Work?
• Configuring Clients to Use the External Password Store
• Managing External Password Store Credentials
  • Listing External Password Store Contents
  • Adding Credentials to an External Password Store
  • Modifying Credentials in an External Password Store
  • Deleting Credentials from an External Password Store

10 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

11 Administering User Privileges, Roles, and Profiles

• Managing Oracle Users
  • Creating Users
    • Specifying a Name
    • Setting Up User Authentication
    • Assigning a Default Tablespace
    • Assigning Tablespace Quotas
    • Assigning a Temporary Tablespace
    • Specifying a Profile
    • Setting Default Roles
  • Altering Users
    • Changing User Authentication Mechanism
    • Changing User Default Roles
  • Dropping Users
• 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
  • User Roles
• 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
  • Dropping Roles
• 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 PUBLIC Role
• 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

12 Configuring and Administering Auditing

• Actions Audited by Default
• Guidelines for Auditing
  • Keeping Audited Information Manageable
  • Auditing Normal Database Activity
  • Auditing Suspicious Database Activity
  • Auditing Administrative Users
  • Using Triggers
  • Deciding 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
    • Specifying a Directory for the Operating System Auditing Trail
    • Specifying the Syslog Level
  • Standard Auditing in a Multitier Environment
  • Enabling Standard Auditing Options
    • Enabling Statement Auditing
    • Enabling Privilege Auditing
    • Enabling Object Auditing
    • Enabling Network Auditing
  • Disabling Standard Audit Options
    • Turning Off Statement and Privilege Auditing
    • Turning Off Object Auditing
    • Turning Off Network 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
  • The SYS.AUD$ Auditing Table: Example
• 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
    • Syntax
    • Parameters
    • Usage Notes
    • V$XML_AUDIT_TRAIL View
    • Examples
  • DISABLE_POLICY Procedure
    • Syntax
    • Parameters
  • DROP_POLICY Procedure
    • Syntax
    • Parameters
    • Usage Notes
  • ENABLE_POLICY Procedure
    • Syntax
    • Parameters

13 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
  • An Example of Creating a Secure Application Role
• Associating Privileges with User Database Roles
  • Using the SET ROLE Statement
  • Using the SET_ROLE Procedure
  • Examples of Assigning Roles with Static and Dynamic SQL
• Protecting Database Objects by Using Schemas
  • Unique Schemas
  • Shared Schemas
• Managing Object Privileges
  • What Application Developers Need to Know About Object Privileges
  • SQL Statements Permitted by Object Privileges

14 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
    • Security Policies Based on Tables, Views, and Synonyms
    • Multiple Policies for Each Table, View, or Synonym
    • Grouping of Security Policies
    • High Performance
    • Default Security Policies
  • About Creating a VPD 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
    • Secure Data Caching
    • Returning a Specific Predicate (Security Policy)
    • Providing 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
    • Limiting Roles Through PRODUCT_USER_PROFILE
    • Using Stored Procedures to Encapsulate Business Logic
    • Using VPD for Highest Security
  • VPD and Oracle Label Security Exceptions and Exemptions
• User Models and VPD

15 Implementing Application Context and Fine-Grained Access Control

• About Using Application Context
• Using Secure Session-Based Application Context
  • Task 1: Create a PL/SQL Package that Sets the Secure Context for Your Application
    • SYS_CONTEXT Syntax
    • SYS_CONTEXT Example
    • Using Dynamic SQL with SYS_CONTEXT
    • Using SYS_CONTEXT in a Parallel Query
    • Using SYS_CONTEXT with Database Links
  • Task 2: Create a Unique Secure Context and Associate It with the PL/SQL Package
  • Task 3: Set the Secure Context Before the User Retrieves Data
  • Task 4: Use the Secure Context in a VPD Policy Function
• Examples: Secure Application Context Within a Fine-Grained Access Control Function
  • Example 1: Implementing the Policy
    • Step 1: Create a PL/SQL Package To Set the Secure Context for the Application
    • Step 2: Create a Secure Application Context
    • Step 3: Access the Secure Application Context Inside the Package
    • Step 4: Create the New Security Policy
  • Example 2: Controlling User Access with an Application
    • Step 1: Create a PL/SQL Package to Set the Secure Context
    • Step 2: Create the Secure Context and Associate It with the Package
    • Step 3: Create the Initialization Script for the Application
  • Example 3: Event Triggers, Secure Application Context, Fine-Grained Access Control, and Encapsulation of Privileges
• Initializing Secure Application Context Externally
  • Obtaining Default Values from Users
  • Obtaining Values from Other External Resources
• Initializing Secure Application Context Globally
  • Using Secure Application Context with LDAP
  • How Globally Initialized Secure Application Context Works
  • Example: Initializing Secure Application Context Globally
• Using Client Session-Based Application Context
  • Setting a Value in CLIENTCONTEXT
  • Clearing a Particular Setting in CLIENTCONTEXT
  • Clearing all Settings in CLIENTCONTEXT
• 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 Process
    • 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
  • Validating the Application Used to Connect to the Database
• 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 Exempt from VPD Policies
  • SYS User Exempted from VPD Policies
  • EXEMPT ACCESS POLICY System Privilege
• Automatic Reparse
• VPD Policies and Flashback Query

16 Preserving User Identity in Multitiered Environments

• Security Challenges of Three-Tier Computing
  • Who Is the Real User?
  • Does the Middle Tier Have Too Many Privileges?
  • 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 Reauthentication 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
    • Reauthenticating 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

17 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
• Stored Data Encryption Using DBMS_CRYPTO
  • DBMS_CRYPTO Hashing and Encryption Capabilities
• Data Encryption Challenges
  • Encrypting Indexed Data
  • Key Generation
  • Key Transmission
  • Key Storage
    • Storing the Keys in the Database
    • Storing the Keys in the Operating System
    • Users Managing Their Own Keys
    • Using Transparent Database Encryption
  • Changing Encryption Keys
  • BLOBS
• Example of a Data Encryption Procedure
• Example of AES 256-Bit Data Encryption and Decryption Procedures
• Example of Encryption and Decryption Procedures for BLOB Data

Part IV Appendixes

A Addressing The CONNECT Role Change

• How Applications Are Affected
  • Database Upgrade
  • Account Provisioning
  • Installation of Applications Using New Databases
• How Users Are Affected
  • General Users
  • Application Developers
  • Client Server Applications
• Approaches to Addressing the CONNECT Role Change
  • Approach 1 - Create a new database role
  • Approach 2 - Restore CONNECT privileges
    • New View Showing CONNECT Grantees
  • Approach 3 - Conduct least privilege analysis

B Verifying Data Integrity with DBMS_SQLHASH

• Overview of the DBMS_SQLHASH Package
• The DBMS_SQLHASH.GETHASH Function
  • Syntax
  • Parameters

Glossary

Index