D50323GC20_sg2.pdf - Volume II \u2022 Student Guide D50323GC20 Edition 2.0 April 2010 D66809 THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY

D50323GC20_sg2.pdf - Volume II u2022 Student Guide...

This preview shows page 1 out of 536 pages.

Unformatted text preview: Volume II • Student Guide D50323GC20 Edition 2.0 April 2010 D66809 THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED Oracle University and BUSINESS SUPPORT LTDA use only Oracle Database 11g: Security Authors Copyright © 2010, Oracle and/or it affiliates. All rights reserved. Donna Keesling James Spiller Disclaimer Tammy Bednar Tom Best Maria Billings Herbert Bradbury Howard Bradley Tomohiko Fukuda Philip Garm Joel Goodman Naveen Gopal Xander Heemskerk Uwe Hesse Magnus Isaksson Tomoki Ishii Chandrasekharan Iyer Sushma Jagannath Martin Jensen Dominique Jeunot Victor Lu Yi L Lu Tom Minella Sabiha Miri Pam Moutrie Lynn Munsinger Paul Needham Roman Niehoff Preetam Ramakrishna Surya Rekha Kevin Reardon Wayne Reeser Walter Romanski Ron Soltani Kar Srinivasan Glenn Tripp Branislav Valny Peter Wahl Andrew Webber Anthony Woodell Paul Youn This document contains proprietary information and is protected by copyright and other intellectual property laws. You may copy and print this document solely for your own use in an Oracle training course. The document may not be modified or altered in any way. Except where your use constitutes "fair use" under copyright law, you may not use, share, download, upload, copy, print, display, perform, reproduce, publish, license, post, transmit, or distribute this document in whole or in part without the express authorization of Oracle. The information contained in this document is subject to change without notice. If you find any problems in the document, please report them in writing to: Oracle University, 500 Oracle Parkway, Redwood Shores, California 94065 USA. This document is not warranted to be error-free. Restricted Rights Notice If this documentation is delivered to the United States Government or anyone using the documentation on behalf of the United States Government, the following notice is applicable: U.S. GOVERNMENT RIGHTS The U.S. Government’s rights to use, modify, reproduce, release, perform, display, or disclose these training materials are restricted by the terms of the applicable Oracle license agreement and/or the applicable U.S. Government contract. Trademark Notice Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. Editors Aju Kumar Amitha Narayan Raj Kumar Graphic Designer Satish Bettegowda Publishers Jayanthy Keshavamurthy Shaik Mahaboob Basha THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS Sujatha Nagendra COMPUTER IS STRICTLY PROHIBITED Oracle University and BUSINESS SUPPORT LTDA use only Contributors and Reviewers Oracle University and BUSINESS SUPPORT LTDA use only Preface THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED Oracle University and BUSINESS SUPPORT LTDA use only THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED Profile Before You Begin This Course Before you begin this course, you should have the following qualifications: Working experience with Oracle Database 11g Or have attended the following courses: • Oracle Database 11g: Administration Workshop II (D50079GC20) inClass How This Course Is Organized Oracle Database 11g: Security is an instructor-led course featuring lectures and hands-on exercises. Online demonstrations and written practice sessions reinforce the concepts and skills. Preface - 3 THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED Oracle University and BUSINESS SUPPORT LTDA use only • Oracle Database 11g: Administration Workshop I (D50102GC20 ) inClass Related Publications Oracle Publications Title Part Number Oracle Database Administrator's Guide 11g Release 2 (11.2) E10595-06 11g Release 2 (11.2) E10746-01 Oracle Database Concepts 11g Release 2 (11.2) E10713-05 Oracle Label Security Administrator's Guide 11g Release 2 (11.2) E10574-03 Oracle Database Net Services Administrator's Guide 11g Release 2 (11.2) E10836-03 PL/SQL Packages and Types Reference 11g Release 2 (11.2) E10577-04 Oracle Database Reference 11g Release 2 (11.2) E10820-03 Oracle Database Security Guide 11g Release 2 (11.2) E10574-03 Oracle Database SQL Reference 11g Release 2 (11.2) E10592-04 Oracle Internet Directory Administrator's Guide, 10g (10.1.4.0.1) B15991-01 Oracle Database Enterprise User Security Administrator's Guide 11g Release 2 (11.2) E10744-01 Additional Publications • System release bulletins • Installation and user’s guides • read.me files • International Oracle User’s Group (IOUG) articles • Oracle Magazine Preface - 4 THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED Oracle University and BUSINESS SUPPORT LTDA use only Oracle Database Advanced Security Administrator's Guide Typographic Conventions Convention Object or Term Example Uppercase Commands, functions, column names, table names, PL/SQL objects, schemas Use the SELECT command to view information stored in the LAST_NAME column of the EMPLOYEES table. Lowercase, italic Filenames, syntax variables, usernames, passwords where: role Initial cap Trigger and button names Assign a When-Validate-Item trigger to the ORD block. is the name of the role to be created. Select Cancel. Italic Quotation marks Books, names of courses and manuals, and emphasized words or phrases For more information on the subject see Oracle SQL Reference Manual Lesson module titles referenced within a course This subject is covered in Lesson 3, “Working with Objects.” Do not save changes to the database. Preface - 5 THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED Oracle University and BUSINESS SUPPORT LTDA use only The following table lists the typographical conventions that are used in text and code. Typographic Conventions in Text Convention Object or Term Example Uppercase Commands, functions SELECT employee_id FROM employees; Lowercase, italic Syntax variables CREATE ROLE role; Initial cap Forms triggers Form module: ORD Trigger level: S_ITEM.QUANTITY item Trigger name: When-Validate-Item . . . Lowercase Column names, table names, filenames, PL/SQL objects . . . OG_ACTIVATE_LAYER (OG_GET_LAYER ('prod_pie_layer')) . . . SELECT last_name FROM employees; Bold Text that must be entered by a user CREATE USER scott IDENTIFIED BY tiger; Preface - 6 THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED Oracle University and BUSINESS SUPPORT LTDA use only Typographic Conventions (continued) Typographic Conventions in Code Contents I Introduction to Database Security Course Objectives I-2 Agenda I-3 Prerequisites I-6 1 Understanding Security Requirements Objectives 1-2 Fundamental Data Security Requirements 1-3 Data Security Concerns 1-5 Compliance Mandates 1-6 Security Risks 1-8 Security Standards 1-10 Developing Your Security Policy 1-11 Defining a Security Policy 1-12 Implementing a Security Policy 1-14 Quiz 1-15 Techniques for Enforcing Security 1-16 Principle of Least Privilege 1-17 Defense in Depth 1-18 Common Exploits 1-19 Preventing Exploits 1-21 Summary 1-22 Case Study: Applying Security Practices 1-23 Understanding SQL Injection 1-24 Preventing SQL Injection 1-25 Reducing the Attack Surface 1-26 Using Invoker’s Rights 1-27 Avoiding Dynamic SQL 1-28 Validating Input to Dynamic SQL 1-29 Coding Review and Testing Strategy 1-30 Mitigating the Scope of Exploits 1-31 Avoiding Privilege Escalation 1-32 Trapping and Handling Exceptions 1-33 iii THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED Oracle University and BUSINESS SUPPORT LTDA use only Preface Choosing Security Solutions Objectives 2-2 Assuring Data Integrity 2-3 Data Protection 2-5 Authentication and Authorization 2-7 Networkwide Authentication 2-9 Access Control and Monitoring 2-10 Quiz 2-11 Oracle Database Vault 2-12 Oracle Audit Vault 2-13 Combining Optional Security Features 2-14 Compliance Scanner 2-16 Enterprise Manager Database Control: Policy Trend 2-17 Security at a Glance: Details 2-18 Enterprise Manager Grid Control Security Advisor 2-19 Policy Library 2-20 Summary 2-21 Practice 2 Overview: Hardening Database Access 2-22 3 Basic Database Security Objectives 3-2 Database Security: Checklist 3-3 Reducing Administration Effort 3-4 Installing Only What Is Required 3-5 Applying Security Patches 3-6 Secure Password Support 3-7 Automatic Secure Configuration 3-8 Password Configuration 3-9 SYS and SYSTEM Accounts 3-10 SYSDBA, SYSOPER, and SYSASM 3-11 Allowing Remote Database Administration 3-12 Locking and Expiring Default User Accounts 3-13 Changing Default Account Passwords 3-15 Enforcing Password Management 3-17 Enabling Built-in Password Complexity Checker 3-19 Quiz 3-20 Protecting the Data Dictionary 3-21 System and Object Privileges 3-22 Restricting the Directories Accessible by the User 3-23 Managing Fine-Grained Access to External Network Services 3-24 Managing Scheduler Security 3-26 iv THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED Oracle University and BUSINESS SUPPORT LTDA use only 2 4 Auditing Database Users, Privileges, and Objects Objectives 4-2 Monitoring for Suspicious Activity 4-3 Audit Tool Comparisons 4-5 Standard Database Auditing: Overview 4-6 Standard Database Auditing 4-7 Setting the AUDIT_TRAIL Parameter 4-9 Audit Log Location Options 4-10 Moving the Database Audit Trail from the SYSTEM Tablespace 4-11 Limiting the Size of the Operating System Audit Trail 4-13 Limiting the Age of the Operating System Audit Trail 4-14 Clearing the Size and Age Properties 4-15 Specifying Audit Options 4-16 Auditing Sessions 4-18 Viewing Auditing Options 4-20 Viewing Auditing Results 4-21 Quiz 4-22 Purging Audit Trail Records 4-23 Initializing the Audit Trail for Purging 4-24 Setting an Archive Timestamp for Audit Records 4-25 Manually Purging the Audit Trail 4-26 Scheduling an Automatic Purge Job for the Audit Trail 4-27 Auditing the SYSDBA and SYSOPER Users 4-29 Viewing the SYSDBA Audit Trails 4-30 Audit to XML Files 4-32 Writing Audit Records to syslog 4-33 Configuring Auditing to syslog 4-34 syslog Limitations 4-35 Value-Based Auditing 4-37 Triggers and Autonomous Transactions 4-39 Summary 4-41 Practice 4 Overview: Implementing Basic Auditing 4-42 v THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED Oracle University and BUSINESS SUPPORT LTDA use only External Jobs 3-27 Limiting Users with Administrative Privileges 3-28 Separation of Responsibilities 3-30 Using Available Database Security Features 3-32 Summary 3-33 Practice 3 Overview: Hardening Database Access 3-34 Auditing DML Statements Objectives 5-2 Fine-Grained Auditing (FGA) 5-3 FGA Policy 5-4 Triggering Audit Events 5-6 Data Dictionary Views 5-7 DBA_FGA_AUDIT_TRAIL 5-8 Quiz 5-9 DBMS_FGA Package 5-10 Enabling and Disabling an FGA Policy 5-11 Dropping an FGA Policy 5-12 FGA Policy Guidelines 5-13 FGA Policy Errors 5-14 Maintaining the Audit Trail 5-15 Summary 5-16 Practice 5 Overview: Implementing Fine-Grained Auditing 5-17 6 Using Basic User Authentication Objectives 6-2 User Authentication 6-3 User Identified by a Password 6-4 User Identified Externally 6-5 Protecting Passwords 6-6 Quiz 6-7 Fixed User Database Links 6-8 Encrypted Database Link Passwords 6-9 Database Links Without Credentials 6-10 Database Links and Changing Passwords 6-12 Auditing with Database Links 6-13 Restricting a Database Link with Views 6-14 Summary 6-16 Practice 6 Overview: Using Basic Authentication Methods 6-17 7 Using Strong Authentication Objectives 7-2 User Authentication 7-3 Strong User Authentication 7-4 Single Sign-On 7-6 Public Key Infrastructure (PKI) Tools 7-7 Certificates 7-8 How to Use Certificates for Authentication 7-9 vi THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED Oracle University and BUSINESS SUPPORT LTDA use only 5 8 Using Enterprise User Security Objectives 8-2 User Authentication 8-3 Enterprise User Security 8-4 Oracle Identity Management Infrastructure: Default Deployment 8-5 Oracle Database: Enterprise User Security Architecture 8-6 Authenticating Enterprise Users 8-7 OID Structure Overview 8-9 Quiz 8-10 Setting Up Enterprise User Security 8-11 Installing Oracle Application Server Infrastructure 8-12 Registering the Database 8-13 Managing Enterprise User Security 8-14 Creating an Enterprise User 8-15 Creating an Enterprise User in the Directory 8-16 Creating a Schema Mapping Object in the Directory: Subtree 8-17 Creating a Schema Mapping Object in the Directory: User Name 8-18 Identifying the Enterprise User 8-19 Enabling Current User Database Links 8-20 User Migration Utility 8-21 Enterprise-User Auditing 8-23 Summary 8-24 Practice 8 Overview: Implementing Enterprise User Security 8-25 vii THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED Oracle University and BUSINESS SUPPORT LTDA use only Configuring SSL on the Server 7-10 Configuring Oracle Net Files on the Server 7-11 Configuring SSL on the Client 7-12 Configuring Oracle Net Files on the Client 7-13 Creating a User Identified by a Certificate 7-15 Connecting to the Database 7-16 Quiz 7-17 orapki Utility 7-18 How to Use Kerberos for Authentication 7-19 How to Use KDC with Windows 2000 for Authentication 7-21 RADIUS Authentication: Overview 7-23 Secure External Password Store 7-24 Configuring the Wallet 7-25 Configuring sqlnet.ora 7-26 Managing the External Password Store 7-27 Summary 7-28 Practice 7 Overview: Configuring the External Secure Password Store 7-29 Using Proxy Authentication Objectives 9-2 User Authentication 9-3 Security Challenges of Three-Tier Computing 9-4 Identifying the Real User 9-5 Common Implementations of Authentication 9-7 User Reauthentication 9-9 Restricting the Privileges of the Middle Tier 9-11 Implementing Proxy Authentication Solutions 9-12 Quiz 9-14 Authenticating Database and Enterprise Users 9-15 Using Proxy Authentication for Database Users 9-17 Using Proxy Authentication for Enterprise Users 9-19 Proxy Access Through SQL*Plus 9-21 Enterprise User Proxy 9-22 Enterprise User Proxy: Example 9-23 Revoking Proxy Authentication 9-25 Application-User Model 9-26 Data Dictionary Views for Proxy Authentication 9-28 Data Dictionary Views: DBA_PROXIES and USER_PROXIES 9-29 Data Dictionary Views: V$SESSION_CONNECT_INFO 9-30 Auditing Actions Taken on Behalf of the Real User 9-31 Data Dictionary Views: DBA_STMT_AUDIT_OPTS 9-33 Data Dictionary Views: DBA_AUDIT_TRAIL 9-34 Summary 9-35 Practice 9 Overview: Implementing Proxy Authentication 9-36 10 Using Privileges and Roles Objectives 10-2 Authorization 10-3 Privileges 10-4 Roles 10-5 Benefits of Roles 10-6 Predefined Roles 10-7 CONNECT Role Privileges 10-8 Using Proxy Authentication with Roles 10-9 Quiz 10-10 Using Enterprise Roles 10-11 Creating an Enterprise Role 10-12 viii THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED Oracle University and BUSINESS SUPPORT LTDA use only 9 11 Using Application Contexts Objectives 11-2 Application Context: Description 11-3 Creating a Context in a Namespace 11-4 Using the Application Context 11-5 Setting the Application Context 11-6 Using the SYS_CONTEXT PL/SQL Function 11-7 Application Context Data Sources 11-8 Quiz 11-10 Implementing a Local Context 11-11 Step 1: Create an Application Context 11-12 Step 2: Create a PL/SQL Package That Sets the Context 11-14 Step 3: Call the Package 11-15 Step 4: Read the Context Attribute in the Application 11-16 Application Context Accessed Globally 11-17 Application Context Accessed Globally in Action 11-19 Using the DBMS_SESSION Package 11-21 Implementing the Application Context Accessed Globally 11-24 Step 1: Create the Application Context Accessed Globally 11-25 Step 2: Establish a Session 11-26 Step 3: Handle Subsequent Requests 11-27 Step 4: End a Session 11-28 Viewing Application Context Information 11-29 Application Context Usage Guidelines 11-31 Summary 11-33 Practice 11 Overview: Creating an Application Context 11-34 ix THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED Oracle University and BUSINESS SUPPORT LTDA use only Assigning an Enterprise User to an Enterprise Role 10-13 Securing Objects with Procedures 10-14 Secure Application Role 10-15 Implementing a Secure Application Role 10-16 Step 1: Create the Role 10-17 Step 2.a: Create the Package Specification 10-18 Step 2.b: Create the Package Body 10-19 Step 3: Grant the EXECUTE Privilege on the Package 10-21 Step 4: Write the Application Server Code That Sets the Role 10-22 Viewing Dictionary Information for Secure Application Roles 10-23 Summary 10-24 Practice 10 Overview: Implementing the Secure Application Role 10-25 x THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED Oracle University and BUSINESS SUPPORT LTDA use only 12 Implementing Virtual Private Database Objectives 12-2 Fine-Grained Access Control: Overview 12-3 Understanding Fine-Grained Access Control Policy Execution 12-5 Benefits of Using Fine-Grained Access Control 12-7 Virtual Private Database 12-8 Examples of Virtual Private Database 12-9 Quiz 12-11 Tools to Implement Virtual Private Database 12-12 Enterprise Manager 12-14 Managing VPD Policies 12-15 Using DBMS_RLS to Manage Policies 12-16 Column-Level VPD 12-18 Column-Level VPD: Example 12-19 Policy Types: Overview 12-20 Static Policies 12-21 Context-Sensitive Policies 12-22 Sharing Policy Functions 12-23 Exceptions to VPD Policies 12-24 Designing and Implementing a VPD Solution 12-25 Implementing a VPD Policy 12-26 Creating a Package and Context 12-27 Writing the Function That Creates a Predicate 12-29 Testing the Security Function 12-31 Writing a Function That Returns Different Predicates 12-32 Creating a Policy 12-34 Quiz 12-35 Implementing Policy Groups 12-36 Grouping Policies 12-38 Default Policy Group 12-39 Creating a Driving Context 12-41 Making the Context a Driving Context 12-43 Creating a Policy Group 12-45 Adding a Policy to a Group 12-46 Best Practices for VPD 12-48 Guidelines for Policies and Context 12-49 Policy Performance 12-51 Export and Import 12-53 Policy Views 12-54 Checking for Policies Applied to SQL Statements 12-55 13 Oracle Label Security Concepts Objectives 13-2 Access Control: Overview 13-3 Discretionary Access Control 13-4 Oracle Label Security 13-5 How Sensitivity Labels Are Used 13-6 Installing Oracle Label Security 13-7 Quiz 13-8 Oracle Label Security: Features 13-9 Comparing Oracle Label Security and VPD 13-11 Oracle Label Security and VPD Comparison 13-12 Analyzing Application Requirements 13-13 Summary 13-14 14 Implementing Oracle Label Security Objectives 14-2 Implementing an Oracle Label Security Solution 14-3 Step 3: Create Policies 14-5 Policy Enforcement Options 14-6 Step 4: Define Labels: Overview 14-8 Defining Levels by Using Enterprise Manager 14-9 Creating Levels 14-10 Defining Groups by Using Enterprise Manager 14-11 Creating Groups 14-12 Defining Compartments by Using Enterprise Manager 14-13 Creating Compartments 14-14 Identifying Data Labels 14-15 Creating Data Labels 14-16 Access Mediation 14-17 Administering Labels 14-18 Adding Labels to Data 14-19 Step 5: Apply the Policy to a Table 14-20 Step 6: Assign User Author...
View Full Document

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture