Oracle Security

Oracle Security

by William Heney, Marlene Theriault
Released October 1998
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781565924505

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Buy on Amazon
Start your free trial

Book description

Security in a relational database management system is complex, and too few DBAs, system administrators, managers, and developers understand how Oracle implements system and database security. This book gives you the guidance you need to protect your databases. Oracle security has many facets:

  • Establishing an organization's security policy and plan

  • Protecting system files and passwords

  • Controlling access to database objects (tables, views, rows, columns, etc.)

  • Building appropriate user profiles, roles, and privileges

  • Monitoring system access via audit trails

  • Oracle and Security

  • Oracle System Files

  • Oracle Database Objects

  • The Oracle Data Dictionary

  • Default Roles and User Accounts

  • Profiles, Passwords, and Synonyms

  • Developing a Database Security Plan

  • Installing and Starting Oracle

  • Developing a Simple Security Application

  • Developing an Audit Plan

  • Developing a Sample Audit Application

  • Backing Up and Recovering a Database

  • Using the Oracle Enterprise Manager

  • Maintaining User Accounts

  • Using the Oracle Security Server

  • Using the Internet and the Web

  • Using Extra-Cost Options

Oracle Security describes how these basic database security features are implemented and provides many practical strategies for securing Oracle systems and databases. It explains how to use the Oracle Enterprise Manager and Oracle Security Server to enhance your site's security, and it touches on such advanced security features as encryption, Trusted Oracle, and various Internet and World Wide Web protection strategies. A table of contents follows: Preface Part I: Security in an Oracle System

Part II: Implementing Security

Part III: Enhanced Oracle Security

Appendix A. References

Table of contents

  1. Oracle Security
    1. Dedication
    2. Preface
      1. What This Book Is
        1. Part I
        2. Part II
        3. Part III
      2. What This Book Is Not
      3. Audience for This Book
      4. Conventions Used in This Book
      5. Platforms and Versions of Oracle
      6. Comments and Questions
      7. Acknowledgments
        1. From Both of Us
        2. From Marlene Theriault
        3. From William Heney
    3. I. Security in an oracle System
      1. 1. Oracle and Security
        1. What’s It All About?
          1. Potential Threats
          2. What’s the Harm?
        2. The Oracle Security Model
          1. Layers of Security
          2. The Physical Entities
            1. The Oracle system files
            2. The detached processes and the SGA
          3. The Logical Entities
          4. The Oracle Data Dictionary
          5. Oracle from the Outside
          6. Oracle from the Inside
          7. Connecting to the Database
          8. Backup and Recovery
          9. More Complex Approaches
          10. Web Sites
        3. Procedures, Policies, and Plans
          1. Security Policies and Security Plan
          2. Auditing Plan and Procedures
          3. A Problem with Auditing—and a Solution
          4. Backup and Recovery Plan and Procedures
        4. If I Had a Hammer...
          1. What’s “Free”?
          2. What Isn’t Free?
      2. 2. Oracle System Files
        1. What’s in the Files?
        2. The Instance and the Database: Starting an Oracle Database
        3. Types of Database Files
          1. Tablespace and Tablespace Datafiles
            1. Tablespace names
            2. Creating a tablespace
            3. Creating a table within a tablespace
            4. Creating an index for a table in a tablespace
            5. File placement and naming
            6. Tablespace security
          2. Redo Log Files
            1. Log switches
            2. How redo log files are created
            3. Using redo log groups
            4. MAXDATAFILES parameter
          3. Control File
            1. Modifying control files
            2. A sample control file
            3. How Oracle uses control files
          4. Initialization File
            1. Oracle’s sample initialization file
            2. Evolution of an initialization file
          5. Configuration File
            1. Contents of the configuration file
      3. 3. Oracle Database Objects
        1. The User Interface: User Versus Schema
          1. The Schema Concept
          2. About Quotas
        2. Objects
        3. Tables
          1. Table Parameters
          2. What Happens When a Table Is Created
          3. As the Table Grows
          4. Table Ownership
        4. Table Triggers
          1. About Creating a Trigger
          2. How the Trigger Works
          3. Naming Triggers
          4. What Cannot Be Trapped by a Trigger
        5. Views
          1. Using Views
          2. Using Views for Security
          3. A Caution About Using Views
          4. Updating Views
        6. Stored Programs
          1. Executing a Procedure or Function
          2. Why Use Packages?
          3. Using the PL/SQL Wrapper
          4. Procedure Ownership and Privileges
          5. Privileges, Procedures, and Roles
        7. Synonyms
          1. How Synonyms Are Used
          2. Why Synonyms Are Used
        8. Privileges
          1. About System and Object Privileges
            1. System privileges
            2. Object privileges
        9. Roles
          1. Determining Privileges Granted to a User
          2. Determining Privileges Granted to a Role
          3. Establishing Classes of Users
          4. Oracle-Supplied Roles
        10. Profiles
      4. 4. The Oracle Data Dictionary
        1. Creating and Maintaining the Data Dictionary
        2. The Data Dictionary Views
          1. The DICTIONARY View
          2. About Row-Level Security
            1. Examining the code for the first query
            2. Examining the code for the second and third queries
          3. About CATALOG.SQL
          4. Applying the Concepts
        3. About SQL.BSQ
          1. SQL.BSQ and CATALOG.SQL Locations
          2. How SQL.BSQ Is Used
          3. User and Role Names
          4. SQL.BSQ and Database Creation
          5. Inside SQL.BSQ
        4. Views Used for Security
          1. Tables Used to Build the Views
          2. Views and Auditing
          3. A Closer Look at the Views for Security
        5. The Composition of the Views
          1. The DBA_PROFILES View
          2. The DBA_ROLE_PRIVS View
          3. The DBA_ROLES View
          4. The DBA_SYS_PRIVS View
          5. The DBA_TAB_PRIVS View
            1. The object grants
            2. About the output
          6. The DBA_USERS View
          7. The ROLE_ROLE_PRIVS View
          8. The ROLE_SYS_PRIVS View
          9. The ROLE_TAB_PRIVS View
      5. 5. Oracle Default Roles and User Accounts
        1. About the Defaults
        2. The CONNECT Role
          1. System Privileges for the CONNECT Role
          2. Problems with the CONNECT Role
            1. The situation begins to compound
            2. Getting further into trouble
            3. The plot thickens
            4. Name of the role
        3. The RESOURCE Role
          1. System Privileges for the RESOURCE Role
          2. Problems with the RESOURCE Role
            1. The Oracle-supplied roles can be moving targets
            2. UNLIMITED TABLESPACE access
            3. About the CREATE TRIGGER privilege
        4. The DBA Role
          1. System Privileges for the DBA Role
          2. Who Gets the DBA Role?
        5. The SYSDBA and SYSOPER Roles
          1. Remote Database Administration
          2. System Privileges for the SYSDBA and SYSOPER Roles
            1. About OSOPER and SYSOPER
            2. About OSDBA and SYSDBA
          3. About CONNECT INTERNAL
        6. Using the Default Roles
          1. Creating Roles with Meaningful Names
          2. Advantages of Customized Roles
        7. Default User Accounts
          1. Default Users and Their Roles
            1. Scott and his tiger
            2. Demo, dbsnmp, and po8
            3. Example queries
            4. When to allow default users
            5. Checking on users and access
          2. Grants to “public”
        8. Segmenting Authority in the Database
      6. 6. Profiles, Passwords, and Synonyms
        1. Profiles
          1. Product Profiles
            1. PRODUCT_PROFILE and USER_PROFILE tables
            2. Disabling SQL privileges
            3. Using PRODUCT_PROFILE to enforce security
          2. System Resource Profiles
            1. The DEFAULT profile
            2. Imposing limits on a user
        2. Passwords
          1. Password Composition and Complexity
            1. Basic rules
            2. Writing your own function
          2. Password Aging and Expiration
          3. The Password Life Cycle
          4. Account Locking
          5. Password Enhancements in the Data Dictionary Views
          6. Passwords and Data Encryption
          7. Password Scripts and Commands
            1. Swapping passwords
            2. The Oracle8 PASSWORD command
        3. Synonyms
          1. About Public and Private Synonyms
          2. Examples Using Public and Private Synonyms
            1. Hiding the tables and owner
            2. Using private synonyms and path names
            3. Using public synonyms without user grants
            4. Using no synonyms/user grants and private synonyms with no grants
    4. II. Implementing Security
      1. 7. Developing a Database Security Plan
        1. About the Security Policy and Security Plan
          1. Management Considerations
            1. Who’s on the team?
            2. Establishing overall requirements
          2. Operating System Security Mechanisms
          3. Identifying Key Components
        2. Types of Accounts
          1. Administrator Accounts
          2. Security Manager
          3. Application Manager
          4. Network Manager
          5. Application Schema (User) Accounts
          6. General User Accounts
        3. Standards for Accounts
          1. Possible Account Requests
          2. Contents of the Form
          3. Ways to Create an Account
        4. Standards for Usernames
          1. Advantages and Disadvantages
          2. Suggested Username Standards
        5. Standards for Passwords
          1. Password Decisions
          2. Changing Passwords
        6. Standards for Roles
          1. Oracle-Supplied Roles
          2. Granting Access to the Database
        7. Standards for Views
        8. Standards for the Oracle Security Server
        9. Standards for Employees
          1. Employee Procedures
            1. Pre-employment tracking
            2. New hires
            3. Changing positions
            4. The disgruntled employee
          2. When an Employee Leaves
            1. Termination types
            2. When an employee gives notice
            3. The curious employee
          3. User Tracking
        10. Sample Security Plan Index
        11. Sample Security Plan Checklist
      2. 8. Installing and Starting Oracle
        1. Segmenting Application Processing
          1. Direct Connection to a Database Server
          2. Client/Server (Two-Tier) Architecture
          3. Thin Client (Three-Tier) Architecture
        2. Installing Oracle Securely
          1. Security and the Operating System
          2. Oracle and Operating System Authentication
            1. The OSDBA and OSOPER roles
            2. From the operating system
            3. OSOPER
            4. OSDBA
          3. Operating System Accounts
            1. Using CONNECT INTERNAL and CONNECT /
        3. Connecting to the Database Without a Password
          1. OPS$ Accounts
            1. Identified externally accounts
            2. OS_AUTHENT_PREFIX and OPS$
            3. OPS$ in version 7
            4. Another approach
            5. Two problems with REMOTE_OS_AUTHENT
          2. The ORAPWD Utility
            1. Steps to setting up the password file
        4. Installing and Configuring SQL*Net
          1. Required Files
            1. Installation is easy
            2. About the Names Server
            3. The listener and passwords
        5. Setting Up Initialization Parameters for Security
          1. Viewing the Parameters
      3. 9. Developing a Simple Security Application
        1. The Application Overview
          1. About Enterprise Tables
          2. Enterprise Tables Used by the Credit Card System
        2. Preparing the Role-Object Matrix
          1. Review the Security Plan
          2. Role-Object Access Matrix
          3. Naming Conventions
        3. Views
          1. View Syntax
          2. Creating the Views
            1. The APPROVERS_V view
            2. The CARD_HOLDER_V view
        4. Roles
        5. Grants
          1. Grant the Roles to the Users
          2. Limitation of Grants and Roles
        6. Application Control of Access
          1. Startup Control
          2. Application Row Access Control
          3. Using Password-Protected Roles
            1. Create the APP_ROLES table
            2. Create the security user
            3. Create the PL/SQL program that sets roles
            4. Implementation logic
            5. Execution
      4. 10. Developing an Audit Plan
        1. Why Audit?
          1. Auditing to Confirm Suspicions
          2. Auditing to Analyze Performance
        2. Where to Audit
          1. About the SYS.AUD$ Table
          2. A Problem
          3. Default Auditing Privileges
        3. How Auditing Works
          1. The Auditing Views
          2. The DBA as a Clairvoyant
          3. Available Audit Actions
          4. Auditing Options
          5. From the DICTIONARY View
          6. Views Related to SYS.AUD$
            1. What’s stored in SYS.AUD$?
            2. Creating a summary table
          7. Eliminating the Audit Views
        4. Auditing and Performance
        5. Default Auditing
          1. Auditing During Database Startup
          2. Auditing During Database Shutdown
          3. Auditing During Database Connection with Privileges
          4. Auditing During Database Structure Modification
        6. Types of Auditing
          1. Statement-Level Auditing
            1. Enabling and viewing statement-level auditing
            2. Connect and disconnect auditing
          2. Privilege Auditing
            1. Enabling audit by privileges
          3. Object-Level Auditing
            1. Enabling audit by object
            2. Capturing “before” data
            3. Capturing “after” data
          4. Auditing Shortcuts
        7. Purging Audit Information
          1. Removing All the Data from SYS.AUD$
          2. Removing Selected Data from SYS.AUD$
      5. 11. Developing a Sample Audit Application
        1. About the Audit Trail Application
          1. A Few Limitations
          2. Tracking Inserts
          3. The First Sequence Creation Script (SEQ-RID)
          4. The First Trigger Creation Script (Before-Insert)
          5. Tracking Updates and Deletions
          6. The Three Table Creation Scripts
            1. The AUDIT_ROW table
            2. The AUDIT_COLUMN table
            3. The AUDIT_DELETE table
          7. The Second Sequence Creation Script (SEQ_AUDIT)
          8. The Second Trigger Creation Script (After-Update)
          9. The Third Trigger Creation Script (After-Delete)
          10. The Package and Procedure Creation Scripts
        2. About Performance and Storage
          1. Storage Suggestions
          2. Performance Suggestions
        3. Using the Audit Data in Reports
          1. The Audit Trail Data Display
          2. The AUDIT_ROW Table Report
        4. SQL Scripts to Generate Scripts
          1. Generating a Before-Insert Trigger Script
          2. Generating an After-Update Trigger Script
          3. Generating an After-Delete Trigger Script
      6. 12. Backing Up and Recovering the Database
        1. What Are the Backup Options?
          1. About Archivelog Mode
          2. Cold Database Backups
          3. Hot Database Backups
          4. Logical Database Backups (Exports)
          5. Enterprise Backup Utility
        2. What’s New for Oracle8?
          1. The Oracle8 Recovery Manager
          2. The Recovery Catalog
          3. Backups Supported by Recovery Manager
            1. Types of datafile backups
            2. Using backup levels
        3. What Are the Recovery Options?
          1. Online Recovery
          2. Offline Recovery
      7. 13. Using the Oracle Enterprise Manager
        1. What Is the OEM?
          1. The OEM Components
          2. The DBA Toolkit
          3. Specifying the Database Repository
          4. A Potential Security Problem
          5. Running the Oracle Enterprise Manager
        2. The DBA Toolkit and Security
          1. The Oracle Backup Manager
            1. The Oracle Data Manager
            2. The Oracle Instance Manager
            3. The Oracle Replication Manager
            4. The Oracle Schema Manager
            5. The Oracle Security Manger
            6. The Oracle SQL Worksheet
            7. The Oracle Storage Manager
            8. The Oracle Software Manager
        3. OEM and the Job Scheduler
          1. Back Up Tablespaces, Export, Import, and Load Data
          2. Run SQL Scripts and SQL*Plus
          3. Start Up and Shut Down Your Database
          4. Broadcast Messages
          5. Run OS Commands and Tcl Commands
          6. Deinstall, Delete, Distribute, and Install Products
        4. OEM and the Event Management System
          1. Fault Management events
            1. Space Management events
            2. Resource Management events
            3. Performance Management events
      8. 14. Maintaining User Accounts
        1. Application Design Requirements
        2. Running the Application
          1. Initial Display
          2. Selecting or Creating a User
          3. Assigning Roles
          4. Creating a New Role
          5. Adding System Privileges to Roles
          6. Reserving the Security of the Security Maintenance Form
          7. How Does the Code Work?
            1. About the mg_usr package
            2. About the mg_usr package body
            3. Create user button code
        3. Documenting the User State
        4. A Sample Script
    5. III. Enhanced Oracle Security
      1. 15. Using the Oracle Security Server
        1. About Cryptography
          1. A Simple Code
          2. Algorithms, Plaintext, and Ciphertext
        2. Ways to Authenticate Users
          1. Private Keys
            1. The problem with private keys
          2. Public Keys
            1. Private keys, public keys, and authentication
            2. Advantages of a public key system
          3. Digital Signatures
          4. Certificates of Authority
            1. Certificate format
            2. Period of validity and revocation
            3. Distinguished names
        3. What’s in the OSS?
          1. The OSS Architecture
            1. The OSS Repository
            2. The OSS Manager
            3. The OSS Authentication Adapter
          2. Protocols and Algorithms
            1. Global users and global roles with OSS
            2. Creating a global user and global role
        4. Configuring and Using the OSS
          1. Creating and Deleting the OSS Repository
          2. A Known Problem
          3. Securing the OSS Repository
            1. Creating the OSS certificate authority
            2. Creating the repository identity
            3. Creating other identities
            4. Defining a server
            5. Defining a Server Authorization
            6. Defining an Enterprise Authorization
            7. Creating/downloading a wallet
            8. More about osslogin
            9. Revoking and restoring credentials
            10. Removing an identity
          4. Removing the Oracle Security Server Repository
      2. 16. Using the Internet and the Web
        1. Web Basics
          1. About Networking
            1. LANs and WANs
            2. Moving data around a network
            3. Internet and intranet terminology
            4. The Java language and security
        2. Evaluating Web Assets and Risks
          1. Viruses = disaster!
            1. It was here just a minute ago...
            2. Loss of competitive edge
            3. Where did the time go?
            4. Breach of privacy
        3. Protecting a Web Site
          1. Cookies
            1. Capturing an IP address
            2. A dual approach
          2. Firewalls
            1. Firewalls to protect privacy
            2. SQL*Net and firewalls
          3. Oracle Security Server
          4. Controlling Access from the Operating System
            1. Using a password file
            2. Access by IP address or host
            3. Access by group
        4. Getting Users Involved
          1. Educating Users
          2. Enforcing Policies
          3. Communicating with Other Sites
      3. 17. Using Extra-Cost Options
        1. Trusted Oracle
          1. How Trusted Oracle Works
          2. Accessing a Trusted Oracle Database
          3. Certifications
        2. Advanced Networking Option
          1. About Sniffers and Snoopers
          2. How ANO Works
        3. Oracle Application Server
          1. Constant-State Versus Stateless Connections
            1. Running a form using the OAS
            2. Running a dynamic HTML application
          2. How the OAS Works
          3. OAS Security
      4. A. References
        1. Oracle Books
          1. Of General Oracle Interest
          2. Database Administration
          3. System and Database Tuning
          4. Tools and Languages
          5. Data Warehousing
          6. Oracle and the Web
        2. Security Books
          1. General Computer Security and Risks
          2. Computer Viruses
          3. Network Administration and Security
          4. UNIX Administration and Security
          5. Windows NT Administration and Security
          6. Web and Java Security
        3. Oracle Electronic References
          1. Oracle Web Sites
            1. Oracle Corporation
            2. OraWorld
          2. Oracle User Groups
            1. International Oracle User Group - Americas
            2. European Oracle User Group
            3. Far East Oracle User Groups
          3. Oracle Usenet Groups
        4. Security Electronic References
          1. Security Web Sites
            1. COAST
            2. FIRST
            3. CERT-CC
            4. World Wide Web Consortium
            5. Web security
            6. Windows NT security
          2. Security Usenet Groups
    6. Index
    7. Colophon

Product information

  • Title: Oracle Security
  • Author(s): William Heney, Marlene Theriault
  • Release date: October 1998
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9781565924505