oracle database 11g security

Full text

(1)Volume I • Student Guide. D50323GC20 Edition 2.0 April 2010 D66808. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and ORACLE CORPORATION use only. Oracle Database 11g: Security.

(2) 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 ORACLE CORPORATION use only. Contributors and Reviewers.

(3) Oracle University and ORACLE CORPORATION use only. Preface. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED.

(4) Oracle University and ORACLE CORPORATION use only THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED.

(5) 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 ORACLE CORPORATION use only. • Oracle Database 11g: Administration Workshop I (D50102GC20 ) inClass.

(6) Related Publications Oracle Publications Title. Part Number. Oracle Database Administrator's Guide 11g Release 2 (11.2). E10595-06. Oracle Database Advanced Security Administrator's Guide 11g Release 2 (11.2). E10746-01. Oracle Database Concepts 11g Release 2 (11.2). E10713-05. 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 ORACLE CORPORATION use only. Oracle Label Security Administrator's Guide.

(7) 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 ORACLE CORPORATION use only. The following table lists the typographical conventions that are used in text and code. Typographic Conventions in Text.

(8) 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 ORACLE CORPORATION use only. Typographic Conventions (continued) Typographic Conventions in Code.

(9) 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 ORACLE CORPORATION use only. Preface.

(10) 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 ORACLE CORPORATION use only. 2.

(11) 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 ORACLE CORPORATION 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.

(12) 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 ORACLE CORPORATION use only. 5.

(13) 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 ORACLE CORPORATION 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.

(14) 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 ORACLE CORPORATION use only. 9.

(15) 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 ORACLE CORPORATION 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.

(16) x THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and ORACLE CORPORATION 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.

(17) 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 Authorization Labels 14-21 Quiz 14-23 Oracle Label Security Special User Privileges 14-24 Example: READ Privilege 14-25 Example: FULL Privilege 14-26 Example: COMPACCESS Privilege 14-27 xi THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and ORACLE CORPORATION use only. Summary 12-56 Practice 12 Overview: Implementing a Virtual Private Database Policy 12-57.

(18) 15 Using the Data Masking Pack Objectives 15-2 Data Masking: Overview 15-3 Understanding Data Masking 15-4 Using the Data Masking Pack 15-5 Accessing the Data Masking Pack 15-6 Data Masking Pack: Features 15-7 Data Masking: Best Practices 15-8 Implementing Data Masking 15-9 Identifying Sensitive Data for Masking 15-11 Quiz 15-12 Determining How to Mask the Data 15-13 Managing the Data Mask Format Library 15-14 Using Oracle-Supplied Mask Formats 15-15 Types of Built-in Masking Primitives and Routines 15-16 Example: Data Masking of the EMPLOYEES Table 15-18 Creating Data Mask Formats 15-19 Creating a User-Defined Data Mask Format 15-20 Creating a Masking Format Using a User-Defined Function 15-21 Creating Data Masking Definitions 15-22 Using Masking Formats 15-23 Automatic Identification of Related Columns 15-24 Adding Dependent Columns 15-25 Importing Formats 15-26 Importing Formats and Modifying Properties 15-27 Using Condition-Based Masking 15-28 Using Compound Masking 15-29 Using a User-Defined Masking Function 15-30 Creating a Post-Processing Function 15-31 Implementing a Post-Processing Function 15-32 Generating the Data Masking Script 15-33 Viewing the Data Masking Impact Report 15-34 Viewing the Data Masking Script 15-35 xii THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and ORACLE CORPORATION use only. Using the PROFILE_ACCESS Privilege 14-28 Trusted Stored Package Units 14-30 Exporting with Oracle Label Security 14-31 Importing with Oracle Label Security 14-32 Performance Tips 14-33 Summary 14-35 Practice 14 Overview: Implementing Oracle Label Security 14-36.

(19) 16 Encryption Concepts Objectives 16-2 Understanding Encryption 16-3 What Problems Does Encryption Solve? 16-4 Cost of Encryption 16-5 Encryption Is Not Access Control 16-6 Access by Privileged Users 16-7 What to Encrypt 16-9 Quiz 16-10 Data Encryption: Challenges 16-11 Encryption Key Management: Key Generation 16-12 Encryption Key Management: Key Modification and Transmission 16-13 Encryption Key Management: Storage 16-14 Storing the Key in the Database 16-15 Storing the Key in the Operating System 16-17 Letting the User Manage the Key 16-18 Solutions 16-19 Summary 16-20 17 Using Application-Based Encryption Objectives 17-2 Overview 17-3 DBMS_CRYPTO Package 17-4 Generating Keys Using RANDOMBYTES 17-6 Quiz 17-9 Using ENCRYPT and DECRYPT 17-10 Enhanced Security Using Cipher Block Modes 17-13 Hash and Message Authentication Code 17-14 Summary 17-17 Practice 17 Overview: Using DBMS_CRYPTO for Encryption 17-18. xiii THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and ORACLE CORPORATION use only. Scheduling the Data Masking Job 15-36 Specifying Automatic Masking After Cloning 15-37 Understanding the Data Masking Process 15-38 Creating an Application Masking Template 15-39 Importing Data Masking Definitions 15-40 Controlling Data Masking Operations 15-41 Creating Custom Reports for Auditors 15-42 Summary 15-45 Practice 15 Overview: Implementing Data Masking 15-46.

(20) 19 Applying File Encryption Objectives 19-2 RMAN-Encrypted Backups 19-3 Oracle Secure Backup Encryption 19-4 Encrypted Backups to Tape 19-6 Creating RMAN-Encrypted Backups 19-7 Using Transparent-Mode Encryption 19-8 Using Password-Mode Encryption 19-10 Using Dual-Mode Encryption 19-11 Quiz 19-12 Restoring Encrypted Backups 19-13 xiv THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and ORACLE CORPORATION use only. 18 Applying Transparent Data Encryption Objectives 18-2 Transparent Data Encryption 18-3 Benefits of TDE 18-4 Components of TDE 18-5 Using TDE 18-6 Creating the Master Key 18-7 Opening the Wallet 18-9 Using Auto Login Wallet 18-11 Backup and Recovery of the Wallet 18-12 Quiz 18-13 Master Key Re-Key Concepts 18-14 Re-Keying Table Keys 18-15 Using Hardware Security Modules 18-16 Configuring for Hardware Security Modules 18-17 Creating an Encrypted Column 18-20 Encrypt Clause Syntax 18-21 Creating an Index on an Encrypted Column 18-22 Altering an Encrypted Column 18-23 TDE Column Encryption Support 18-24 TDE Column-Level Storage Requirements 18-26 TDE Column Encryption: Restrictions 18-27 Tablespace Encryption: Advantages 18-28 Creating an Encrypted Tablespace 18-29 Tablespace Encryption: Restrictions 18-30 Exporting and Importing with TDE 18-31 SECUREFILE LOB Encryption 18-32 Summary 18-33 Practice 18 Overview: Implementing TDE 18-34.

(21) RMAN-Encrypted Backups: Considerations 19-14 Data Pump Encryption 19-15 ENCRYPTION Parameter 19-16 ENCRYPTION_MODE Parameter 19-18 Encrypting Dump Files 19-19 Summary 19-20 Practice 19 Overview: Using RMAN Backup File Encryption 19-21 20 Oracle Net Services: Security Checklists Objectives 20-2 Overview: Security Checklists 20-3 Client Checklist 20-4 Issues with Securing the Client Computer 20-5 Configuring the Browser 20-6 Network Security: Checklist 20-7 Using a Firewall to Restrict Network Access 20-8 Restricting Network IP Addresses: Valid Node Checking 20-9 Restricting Network IP Addresses: Guidelines 20-11 Configuring IP Restrictions with Net Manager 20-12 Quiz 20-13 Restricting Open Ports 20-14 Encrypting Network Traffic 20-15 End-to-End Encryption 20-17 Configuring Network Encryption 20-18 Checksumming 20-19 Configuring Checksumming 20-20 Oracle Net Services Log Files 20-21 Summary 20-23 Practice 20 Overview: Configuring Net Security 20-24 21 Securing the Listener Objectives 21-2 Listener Security: Checklist 21-3 Moving the Listener to a Nondefault Port 21-4 Password-Protecting the Listener 21-5 Preventing Online Administration of the Listener 21-7 Quiz 21-8 Administering the Listener Using TCP/IP for SSL 21-9 INBOUND_CONNECT_TIMEOUT 21-10 Setting Listener-Logging Parameters 21-12 xv THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and ORACLE CORPORATION use only. ENCRYPTION_PASSWORD Parameter 19-17.

(22) Analyzing Listener Log Files 21-14 Listener Log Connect: Examples 21-16 Listener Log Command: Examples 21-18 Summary 21-20 Practice 21 Overview: Securing the Listener 21-21. Appendix B: Using Oracle Connection Manager as a Firewall Objectives B-2 Overview of Firewalls B-3 Network Architecture Regions B-4 Guidelines for Positioning Servers Within Firewalls B-5 Using a Firewall to Restrict Database Access B-6 Types of Firewalls B-7 Control Traffic from the Internet B-8 Using Oracle Connection Manager as a Firewall B-10 Oracle Connection Manager: Overview B-11 Oracle Connection Manager Processes B-12 Oracle Connection Manager Architecture B-13 Access Control with Oracle Connection Manager B-14 Configuring Oracle Connection Manager B-15 Configuring the cman.ora File B-16 Preventing Remote Administration of Oracle Connection Manager B-18 Allowing or Denying Access B-19 Configuring Clients to Use CMAN B-21 Configuring Database Servers to Use CMAN B-22 Oracle Connection Manager Control Utility B-23 Starting and Shutting Down Oracle Connection Manager B-24 Additional Commands B-26 Monitoring Connection Events Using the CMAN Log File B-28 Analyzing Oracle Connection Manager Log Files B-30 Summary B-31 Practice 22 Overview: Implementing CMAN as a Firewall B-32. Appendix C: Securing SQL*Plus Objectives C-2 Limiting Commands Available in SQL*Plus C-3 Creating the PUP Table C-4 xvi THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and ORACLE CORPORATION use only. Appendix A: Practices and Solutions.

(23) Commands That Can Be Disabled C-6 Example: Disabling a Command C-7 Disabling a Role C-8 Example: Disabling a Role C-9 Using SET ROLE to Enable a Disabled Role C-11 PRODUCT_USER_PROFILE: Guidelines C-13 Summary C-14 Practice 23 Overview: Securing SQL*Plus C-15 Appendix D: Source Code Appendix E: USERENV Context. xvii THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and ORACLE CORPORATION use only. Example: Disabling SET ROLE C-12.

(24) Oracle University and ORACLE CORPORATION use only THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED.

(25) Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and ORACLE CORPORATION use only. Introduction to Database Security.

(26) After completing this course, you should be able to do the following: • Describe security risks and solutions • Secure your database and network • Choose a user-authentication model • Implement Enterprise User Security • Implement database and fine-grained auditing • Authorize users with roles and secure application roles • Manage data by using Virtual Private Database (VPD) and Oracle Label Security • Implement Data Masking • Encrypt and decrypt columns, tablespaces, and files Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle Database 11g: Security I - 2. Oracle University and ORACLE CORPORATION use only. Course Objectives.

(27) Agenda Lesson. 1. Topic Introduction to Database Security. 1. 1. Understanding Security Requirements. 1. 2. Choosing Security Solutions. 1. 3. Basic Database Security. 1. 4. Auditing Database Users, Privileges, and Objects. 2. 5. Auditing DML Statements. 2. 6. Using Basic User Authentication. 2. 7. Using Strong Authentication. 2. 8. Using Enterprise User Security. 3. 9. Using Proxy Authentication. Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle Database 11g: Security I - 3. Oracle University and ORACLE CORPORATION use only. Day.

(28) Day. Lesson. Topic. 3. 10. Using Privileges and Roles. 3. 11. Using Application Contexts. 3. 12. Implementing Virtual Private Database. 4. 13. Oracle Label Security Concepts. 4. 14. Implementing Oracle Label Security. 4. 15. Using the Data Masking Pack. 4. 16. Encryption Concepts. 5. 17. Using Application-Based Encryption. 5. 18. Applying Transparent Data Encryption. 5. 19. Applying File Encryption. Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle Database 11g: Security I - 4. Oracle University and ORACLE CORPORATION use only. Agenda.

(29) Day. Lesson. Topic. 5. 20. Oracle Net Services: Security Checklists. 5. 21. Securing the Listener. Optional. App B. Using Oracle Connection Manager as a Firewall. Optional. App C. Securing SQL*Plus. Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle Database 11g: Security I - 5. Oracle University and ORACLE CORPORATION use only. Agenda.

(30) For this course, it is assumed that you have attended: • Oracle Database 11g: Administration Workshop I (or equivalent experience) • Oracle Database 11g: Administration Workshop II (or equivalent experience). Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle Database 11g: Security I - 6. Oracle University and ORACLE CORPORATION use only. Prerequisites.

(31) Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle University and ORACLE CORPORATION use only. Understanding Security Requirements.

(32) After completing this lesson, you should be able to do the following: • Describe business security requirements • Define the following terms: – Least privilege – Authorization – Authentication. • • •. Describe security policies Describe the concept of in-depth security Apply these concepts to prevent SQL injection. Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle Database 11g: Security 1 - 2. Oracle University and ORACLE CORPORATION use only. Objectives.

(33) Security standards require security policies to meet fundamental data security requirements: • Confidentiality: Ensure that individuals see only the data they are supposed to see • Integrity: Ensure that data remains valid • Availability: Ensure that denial-of-service attacks do not impact access to and use of systems. Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. Fundamental Data Security Requirements Security standards include both legal and business standards. These standards require that your security policy meet the following fundamental requirements: • Confidentiality: Confidentiality ensures that individuals see only the data that they are supposed to see. This includes: - Privacy of communication - Secure storage of data, including backup media (may require encryption) - Secure transmission of sensitive data (may require network encryption) - Restricted access to services - Authenticated users (only authenticated users may see data) - Granular access control (only authorized users may view allowed data) • Integrity: A secure system ensures that the data remains valid. Data integrity means that data is protected from deletion and corruption, both while it resides within the database and while it is being transmitted over the network. Integrity has several aspects: - System and object privileges control access to application tables and system commands, so that only authorized users can change data.. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle Database 11g: Security 1 - 3. Oracle University and ORACLE CORPORATION use only. Fundamental Data Security Requirements.

(34) THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle Database 11g: Security 1 - 4. Oracle University and ORACLE CORPORATION use only. Fundamental Data Security Requirements (continued) - Constraints contribute to integrity by enforcing rules on data to keep it valid. For example, referential integrity is a constraint that maintains valid relationships between entities in the database, according to the rules that have been defined. - A database must be protected against viruses that are designed to corrupt the data. - The network traffic must be protected from deletion, corruption, and eavesdropping. • Availability: Availability is usually thought of as a backup and recovery issue, and not as a security issue; however, denial-of-service (DoS) attacks attempt to block authorized users’ ability to access and use the system when needed. Preventing DoS attacks is a security issue. These attacks usually gain unauthorized access to computers, and then use these computers to generate requests that flood the targeted system. Availability can sometimes be hindered by your own security measures. Very restrictive firewalls can protect your resources, but can also block access for authorized users..

(35) Data Security Concerns. Identity theft. Insider threats. • Data consolidation • Globalization • Right sourcing. Compliance mandates. SOX. HIPAA. EU directives. PCI. FDA. GLBA. Basel II SB1386. Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. Data Security Concerns Every business should recognize and develop policies that protect it. The accounting policies provide investors with the assurance that the company is sound and well managed. Engineering and safety policies assure customers and employees that proper precautions are being taken to protect the health and safety of both. These policies also protect the business from liability. Computer security policies serve a similar function. Privacy policies are required by the Payment Card Industry (PCI) for businesses that process credit cards. Customers may be attracted or repelled by certain privacy policies. Data security can prevent proprietary data from being stolen or abused. Worldwide, businesses are adopting new computer security policies. For some, these policies are driven by law; others, by the threat of theft, fraud, or sabotage; and still others, by the need for approval by financial regulators, access to credit cards, or investors.. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle Database 11g: Security 1 - 5. Oracle University and ORACLE CORPORATION use only. Industrial espionage. Security threats.

(36) • • • • • •. Sarbanes-Oxley Act (SOX), J-SOX European Union Data Protection Directive Other U.S. laws Payment Card Industry Data Security Standard (PCI DSS) Reasonable care Security Audits. Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. Compliance Mandates Security requirements have been a matter of individual concern until recently. Unless you were handling government or military data, there were few legal requirements. This is rapidly changing. A variety of laws have been passed to enforce privacy and accuracy of data in North America and Europe. Along with these laws comes a requirement to audit the security measures that are in place. These laws are becoming a pattern for laws in other countries, such as India and Japan. Legal: Each of the laws listed here has specific requirements. This list is representative of many other laws that are being passed worldwide. These laws and industry standards are being held as a measure of reasonable care. • Sarbanes-Oxley Act (SOX) requires that publicly traded companies in the United States strengthen and document internal controls to prevent individuals from committing fraudulent acts that may compromise an organization’s financial position or the accuracy of its financial statements. The chief executive officer and the chief financial officer must attest to the adequacy of the internal controls and accuracy of the financial report. These officers are subject to fines and imprisonment for a fraudulent report. The requirements of SOX include providing information that is used to generate the reports and providing documentation about the internal controls used to assure the integrity of the financial information. Other countries, such as Japan, are implementing similar laws. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle Database 11g: Security 1 - 6. Oracle University and ORACLE CORPORATION use only. Compliance Mandates.

(37) THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle Database 11g: Security 1 - 7. Oracle University and ORACLE CORPORATION use only. Compliance Mandates (continued) • European Union Data Protection Act is intended to protect individual privacy by restricting access to individually identifiable data. It has eight points, one of which requires that data be kept secure. • Other U.S. laws: - Health Information Portability and Accountability Act (HIPAA) is intended to protect personally identifiable health information (PII) from release or misuse. Information holders must provide audit trails of all who access this data. - Family Educational Rights and Privacy Act (FERPA) covers health and personally identifiable information held by schools. - California Security Breach Notification Law requires that an organization holding a variety of personally identifiable information (PII)—for example, credit card, driver’s license, and government identity numbers—must protect this information. If the information has been compromised, the organization must notify any resident of California whose unencrypted personal information was or is reasonably believed to have been acquired by an unauthorized person. There are two laws (CA-SB-1386 and CA-AB-1950) that apply to organizations that hold PII. - Federal Information Security Management Act (FISMA) creates security guidance and standards through Federal Information Processing Standard (FIPS) documents that are managed by the National Institute of Standards and Technology (NIST). These standards are applied to organizations that process information for the U.S. government. Payment Card Industry Data Security Standard (PCI DSS): For a business that captures credit card information, there are strict rules imposed by contract and possible fines. Security Audits: Many of these laws include provisions that require that the security plans (internal controls) be audited periodically. SOX requirements are vague and subject to interpretation by the officers of the organization. The implementation details can vary widely, depending on the level of detail that the officers require. Although SOX is vague, its penalties are severe. It is thus important to protect your company. The cost of security measures must be balanced against the risk. No one can certify that you are 100% secure. Reasonable Care: A good solution to determining the proper level of security measures to implement is industry consensus. If you meet the agreed-upon minimum security practices and have accomplished due diligence, you may be safe from the worst penalties of the law. The current legal environment also requires reasonable care or due care. Due care is defined as the conduct that a reasonable man or woman will exercise in a particular situation, in looking out for the safety of others. If one uses due care, an injured party cannot prove negligence. If the security audit is part of due diligence, applying patches and hardening the system could be considered part of due care. You, as DBA, security officer, or auditor, may be held responsible if you know of good practices and do not follow them, or if you neglect to be informed of common security solutions. Because of due diligence and due care, you must be able to advise management on the technical security measures that are available in the database. This course helps you to be informed of common security issues and solutions..

(38) Security Risks External threats: – – – –. •. Internal threats: – – – – – –. •. Unauthorized users Denial of service Unauthorized data access Exploits: SQL injection and others Abuse: Data theft Sabotage: Data or service corruption Complexity Recovery Omission External threats listed above. Partners Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. Security Risks Security risks come with accessibility. Accessibility makes your data valuable. If all your data were in filing cabinets or vaults, you could be sure that it was secure, but the time to retrieve it would make it worthless. External exploits by criminals breaking into systems get big headlines, but industry specialists estimate that 80% to 90% of the damage to information systems is done by insiders. No system can be guaranteed to be 100% secure. The costs of securing a system are weighed against the possible costs of a security breach, whether it is internal or external. A thorough review can be expensive. But it is important to be aware of the issues, set the priorities, and determine the funding. Some of the security standards, such as ISO/IEC 17799:2005, require a risk assessment and provide guidance. External Threats • Unauthorized users: These are outsiders who gain access to your system. They may use software exploits, bypass login information, crack passwords, or use social engineering. They may be helped by poor passwords, unattended terminal sessions, and unsecured servers or modem lines. Even your trash may contain information that allows them to get into your system. • Denial of service: It can be an attack from a malicious source that requests limited resources such as port allocations to disrupt authorized users. It can be accidentally caused by authorized users who make inappropriate requests. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle Database 11g: Security 1 - 8. Oracle University and ORACLE CORPORATION use only. •.

(39) THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle Database 11g: Security 1 - 9. Oracle University and ORACLE CORPORATION use only. Security Risks (continued) • Unauthorized data and service access: When an outsider obtains authentication, the system sees him or her as a valid user. This user then has the privileges granted to insiders. • Exploits: This is a method of gaining access. SQL injection is a type of exploit that may accomplish one or more of the external threats listed. Internal Threats All the threats that are listed as outsider threats may also be performed by insiders. For example, an insider can gain unauthorized access by watching an authorized user enter a password. • Abuse of privilege: Internal users have a justified authorization to your system. But using that authorization to gain unauthorized access to certain data—or using the services in unauthorized ways—is considered to be abuse. • Data or service theft: After access to data and services is obtained, theft is easy. This can be as minor as playing games on the server or as severe as selling proprietary information to a competitor. Personally identifiable information (PII) has become the most valuable information on systems, both in terms of the value to unauthorized users (identity theft) and cost when this information is compromised. • Sabotage (data or service corruption): Sabotage by authorized users is always a possibility. This can be subtle or well intentioned: a data clerk altering data to help friends or a programmer leaving debug code in a program. The corruption can be intentional—for example, altering financial reports to move the stock price or cover embezzlement. • Complexity: Increased complexity of the system increases the likelihood of security holes or avenues of attack that have been overlooked. One of the most overlooked security holes is passwords. Users forget or write down passwords when passwords are required to meet stronger complexity checks. Users often use simple passwords or write passwords in easyto-find locations when they have many accounts. Some customers report that as much as 70% of their help-desk time is consumed by password resets. Single sign-on systems help reduce the complexity, allowing the user to remember one strong password instead of trying to remember several. But a poorly designed single sign-on system can allow a single breach to access multiple systems. • Recovery: What measures are in place to recover your systems if a breach should occur? How long will it take to have your system operational? Is there another system that can be placed in service so that the breached system may be examined forensically? • Omission: How do you verify that the policies that are determined to be necessary are in place, and consistently applied across your systems? If access control and authorization policies are not applied properly, OS security patches will not prevent unauthorized access. Omission in policies allow holes in the security that do not require sophisticated skills to exploit. Partners A relatively new area of concern and risk is partner access. The partner has authorized access to your system, but you do not have control of the partner’s security policies. • External threat: When external attackers breach the partner system, they have the same access that the partner is permitted. • Partner threat: A growing number of breaches occur when the partner accesses sensitive data or proprietary information that is not appropriate for the partner to access. This occurs when the partner is allowed internal user type access..

(40) Organizations publishing recognized security standards: • SANS Institute • Computer Emergency Response Team (CERT/CC) • International Standards Organization (ISO 17799/27002). Do your policies meet the standards?. Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. Security Standards Several organizations produce guidelines for industry standard practices. Some of these are: SysAdmin, Audit, Network, Security (SANS) Institute, and the Computer Emergency Response Team (CERT/CC) operated by Carnegie Mellon University for the United States of America Department of Defense. More information about these organizations and the services that they provide can be found at www.sans.org and www.cert.org The International Standards Organization (ISO) produces standards for a broad range of industries. ISO-17799/27002 is an international standard of computer security practices. It includes best practices, certification, and risk assessment. It covers a broad range of issues and includes prewritten policies. For more information about ISO-17799/27002, see: www.iso.org or www.computersecuritynow.com Security requirements are changing. It is important to be aware of the changes and their impact. The organizations listed in the slide (and others) provide newsletters and study opportunities.. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle Database 11g: Security 1 - 10. Oracle University and ORACLE CORPORATION use only. Security Standards.

(41) To develop your security policy, perform the following steps: 1. Assemble your security team. 2. Define your security requirements. 3. Develop procedures and systems to meet these requirements. 4. Implement security procedures. 5. Audit.. Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. Developing Your Security Policy 1. Assembling Your Security Team: First, determine which people in your organization will participate on the security team. To be effective, this team must include system architects, system administrators, database administrators (DBAs), network administrators, data owners, and representatives of end users. Although technical personnel monitor security, you must also include data owners and end users when defining your security requirements. Others to be included in the team are business experts who understand the organization, security, and disclosure requirements, and legal experts who know the legal requirements for handling the data of the organization. 2. Defining Security Requirements: To define your security requirements, examine who needs access to which data and services, and why they need it. 3. Developing Security Procedures and Systems: After you know the security requirements of your organization, you can develop procedures and systems to meet these requirements. 4. Implementing Security Procedures: Now that you have defined a policy, implement it to secure the data on a day-to-day basis. 5. Audit: A security policy will have specific details that can be audited. An audit of your systems and procedures will confirm that the security policy has been implemented.. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle Database 11g: Security 1 - 11. Oracle University and ORACLE CORPORATION use only. Developing Your Security Policy.

(42) Defining a Security Policy What is a security policy? – – – –. •. A set of rules Specific to an area and site Required Approved by management. What is a standard? – Rules specific to a system or process – Required for everyone. •. What are guidelines? – Suggestions and best practices – Specific to a system or a process. •. Best practices. Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. Defining a Security Policy A security policy is set of documents that are specific to your site. Policy documents are approved by the management. They identify what is required by the company. The policy applies to a specific security area, such as acceptable use of modems, or formation of passwords. A policy is a set of mandatory rules. It must be only as long as required (a couple of pages) and easy to understand. It must be enforceable. To be successful, a policy must balance protection and productivity. Policies should include: • Configuration management: Who is responsible for the security and to what level? • Incident reports: How are security incidents handled? • Infractions: What are the consequences of security violations? A standard is a set of rules that apply to a specific system or process (for example, securing external procedures). Policies refer to standards. Standards, procedures, and guidelines are established at the department level and are much more detailed. They spell out what must be done to meet the policy— for example, your developers should have a secure coding standard that specifies certain coding practices that must be followed. A guideline is a suggestion or a best practice for a specific system or process. Policies refer to guidelines. For novices, it is critical to get examples, especially for meeting the requirements of SOX or HIPPA. The SANS Institute and CERT/CC are very good resources. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle Database 11g: Security 1 - 12. Oracle University and ORACLE CORPORATION use only. •.

(43) Legal Implications The legal department must be consulted and must approve the policy or procedure. How the policies are written and enforced can have a direct impact on whether you can prosecute violations and on whether your company is financially liable when breaches occur. The legal comment is critical. Security consultants have been sued for running password crackers on the network without written permission, even when they were being proactive. Legal advice is also critical when looking at warning banners and email monitoring. For your own protection, establish approved procedures for all forms of monitoring, sniffing, and cracking; such procedures should specify approved monitoring activities and should explicitly identify who performs these activities. Best Practices Security policies can often include references to best practice recommendations. Oracle provides a set of recommendations that can be accessed on the Oracle Database 11g: Maximum Security Architecture Web page at http://www.oracle.com/technology/deploy/security/databasesecurity/maximum-security-architecture.html.. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle Database 11g: Security 1 - 13. Oracle University and ORACLE CORPORATION use only. Defining a Security Policy (continued) Policies must be reviewed and updated regularly. By creating policies in a modular format, the pieces may be updated as needed without having to change a massive document. Policies should not go into details that change frequently, such as OS versions or hardware models..

(44) • • • • •. Implement your standards and procedures. Implement the plan for developing new systems and applications. Monitor and enforce the policy. Keep systems and applications up-to-date with security patches. Educate users.. Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. Implementing a Security Policy Now that you have a policy defined, implement it to secure the data on a day-to-day basis. If you need to add systems or develop new applications to complete your security policy, immediately implement as much of the policy as you can without these systems. Then as you complete the new systems and applications, revise your security policy to include them. Monitoring and Enforcing Security Ensure that all employees understand the significance of keeping your organization secure. Employees must understand the security issues associated with their functions in the organization. They must also be aware of how they may be disciplined if they breach the security policy. Applying Security Patches System administrators and DBAs who are responsible for software installations must search for and apply security patches on a periodic basis. As part of the security policy, include a schedule to search for and apply new security patches.. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle Database 11g: Security 1 - 14. Oracle University and ORACLE CORPORATION use only. Implementing a Security Policy.

(45) A successful security policy should balance protection and productivity. a. True b. False. Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. Answer: a. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle Database 11g: Security 1 - 15. Oracle University and ORACLE CORPORATION use only. Quiz.

(46) • • • • •. Authentication Authorization Access control Auditing Encryption. Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. Techniques for Enforcing Security The following techniques are used to enforce security: • Authentication is the process by which a user’s identity is checked. When a user is authenticated, the user is verified as an authorized user of an application. • Authorization is the process by which a user’s privileges are ascertained. • Access control is the process by which a user’s access to physical data in the application is limited, based on the user’s privileges. Access control is a critical issue in systems that hold sensitive or personally identifiable information (PII). • Auditing is the process that allows users to be held accountable for their actions, both by verifying that users are not using their privileges improperly and as a deterrent to unauthorized access attempts. • Encryption is the process of transforming data into meaningless symbols until a key is applied to transform it into the original form. Encryption is used to protect data during transmission and on disk. Encryption is not access control. For example, if JAUSTEN is trying to access the database, authentication would identify her as a valid user. Authorization would verify her right to connect to the database with Product Manager privileges. Access control would enforce the Product Manager privileges upon her user session. Auditing could record her connection times and any access made to sensitive data. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle Database 11g: Security 1 - 16. Oracle University and ORACLE CORPORATION use only. Techniques for Enforcing Security.

(47) • • • • • •. Install only the required software on the machine. Activate only the required services on the machine. Give operating system (OS) and database access to only those users who require access. Limit access to the root or administrator account. Limit access to privileged database accounts. Limit users’ access to only the database objects that they require to do their jobs.. Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. Principle of Least Privilege Apply the principle of “least privilege” starting at the lowest level, and continue at every level. Least privilege is the principle that users should have the fewest privileges necessary to perform their duties. There will always be new security exploits that cannot be anticipated. By applying this principle, the possibility of the exploit is reduced and the damage can be contained. • Install only the required software on the machine: Reduce maintenance, upgrades, the possibility of security holes, and software conflicts by reducing the number of software packages installed. • Activate only the required services on the machine: Fewer services imply fewer open ports and fewer attack vectors. • Give OS and database access to only those users who require access: Having fewer users means requiring fewer passwords and accounts. Reduce the possibility of open or stale accounts. With fewer accounts, it is easier for the administrator to keep the accounts current. • Limit access to the root or administrator account: The administrator account must be carefully guarded, audited, and never shared. • Limit access to privileged database accounts: Any user that can connect as SYSDBA, SYSOPER, or SYSASM has access to a higher level of privileges. Users who require access as SYSDBA, SYSOPER, or SYSASM must each have his or her own account, and must be audited. • Limit users’ access to only the database objects required to do their jobs: Do they have a need to know? Users who have access to more objects and services than they require have an THESE eKIT opportunity MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS for mischief. COMPUTER IS STRICTLY PROHIBITED Oracle Database 11g: Security 1 - 17. Oracle University and ORACLE CORPORATION use only. Principle of Least Privilege.

(48) Using the concept of “defense in depth:” • Enforce security policies • Train users • Harden the operating system • Use firewalls • Use network security • Use database security features. Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. Defense in Depth “Defense in depth” means that you consider every level (OS, network, file system permissions, database, firewall, password protection, user education, and software bug fixes). The defeat of one security feature must not give full access to all data or services. A policy is a roadmap for security implementation, but it must be implemented to be effective. Users must be trained to avoid activities that could easily breach security. You may have a perfect firewall and antivirus checking on all incoming traffic, but if a user working from home downloads a virus or a Trojan, it can infect the network from behind the firewall. The operating systems on machines that host the application server, the database, the mail server, and other critical services must be hardened. The network services, firewalls, and proxies each add another layer. Then, secure the database. Every layer shown in the slide needs to be in place. Each level complements the others to achieve better security. Defense in depth considers everything. The list presented in the slide is an outline of best practices. This course deals with database-related items from several of these items.. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle Database 11g: Security 1 - 18. Oracle University and ORACLE CORPORATION use only. Defense in Depth.

(49) There are several classes of common exploits: • Phishing • Well-known and default accounts • Backdoors • Debug code • Cross-scripting • SQL injection Two main methods of preventing these attacks: • Educate users about social engineering (limited effectiveness). • Use secure coding practices.. Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. Common Exploits An exploit is a technique used to attack, sabotage, gain unauthorized access to data or services, or to deny service to authorized users. There are several general classes of methods used. Some of the most common are listed. • Phishing is a social engineering method. A carefully crafted email, Web page, or even a phone call to unsuspecting end users can be used to obtain personal information that can then be used for identity theft, or to access their accounts—for example, when users receive an email apparently from their bank asking them to connect to a corporate Web site, and log in, a certain percentage of people will do so. The malicious site then captures their login information. • Default accounts: Many applications have well-known demonstration or default accounts. These accounts should have a method of being secured or deleted. • Back doors: A programmer builds in an undocumented method of bypassing authorization. These should never be allowed to be coded. These can be prevented only by administrative controls and code review. • Debug code: Often, debug code is included in the production code to help during development and later to aid support. This code should have clearly documented methods to be enabled and disabled. Debug code may give additional privileges or bypass authorizations.. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle Database 11g: Security 1 - 19. Oracle University and ORACLE CORPORATION use only. Common Exploits.

(50) There are two methods of prevention. The first is educating the end user to limit social engineering attacks such as phishing and crossscripting. Because of the imagination of the attackers, the attacks will always be changing. The educators can publish warnings and counter measures only after the exploit is discovered. The second method is a secure-coding practice, which implements verified methods to prevent attacks. The methods to prevent these exploits are similar across the various exploit types. A few methods to limit SQL injection attacks are presented here. For more detail, see the course titled Oracle Database 11g: Advanced PL/SQL or the Tutorial on Defending Against SQL Injection Attacks available at http://st-curriculum.oracle.com/tutorial/SQLInjection/index.htm. Standard Configuration Policies: Enforcing standard configurations policies by scanning is another way to help prevent attacks. Some companies go as far as to have internal hackers attempting to break into systems to test the security policies.. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle Database 11g: Security 1 - 20. Oracle University and ORACLE CORPORATION use only. Common Exploits (continued) • Cross-scripting: This is a very common way of gaining information. A crafted URL or script injected into a vulnerable Web page can result in elevated privileges, or access to sensitive information, such as session identifiers, cookies, or login credentials. Often, a cross-scripting attack is hidden in a seemingly innocent email or Web site that invites the recipient to access a URL. • SQL injection: SQL injection makes use of security holes in applications to input and execute crafted SQL statements. Preventing attacks.

(51) Common exploits may be prevented or mitigated by: • Reducing the attack surface • Limiting privileges to avoid privilege escalation • Avoiding known vulnerable code constructs – – – –. • •. Avoid dynamic SQL. Avoid known weak protocols. Always validate input. Trap and handle exceptions.. Designing code that is immune to common exploits Reviewing and testing code for common exploits. Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. Preventing Exploits The principles outlined in the slide can be applied to many types of attacks: SQL injection, crossscripting, and buffer overflow exploits. These methods are derived from the policies. Reducing the attack surface and limiting privileges are applications of the principle of least privilege. Applying the methods shown in the slide are covered in more detail in the case study at the end of this lesson. There are two white papers available on OTN that give more details about SQL and PL/SQL coding practices: Doing SQL and SQL Best and Worst Practices at www.oracle.com/technology/tech/pl_sql/pdf/doing_sqlfrom_plsql.pdf and How to Write Injection-Proof PL/SQL at www.oracle.com/technology/tech/pl_sql/pdf/how_to_write_injection_proof_plsql.pdf.. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle Database 11g: Security 1 - 21. Oracle University and ORACLE CORPORATION use only. Preventing Exploits.

(52) Summary. – Least privilege – Authorization – Authentication. • • •. Describe security policies Describe the concept of in-depth security Apply these concepts to prevent SQL injection. Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. Summary Security is not easy. Developing and implementing thorough security policies require management support. The types of attacks on a secure environment are constantly changing. To counter these risks, sometimes you need to think like a hacker. Maintaining a secure environment often requires a certain level of paranoia. The exploits and attacks are becoming more sophisticated. The average administrator or security officer may not have the time or the background to keep up with new attack vectors. The recommendations presented here are derived from the best industry practices. Applying these recommendations may not prevent an attack, but they can help mitigate the damage, reduce the access, and track the offender.. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle Database 11g: Security 1 - 22. Oracle University and ORACLE CORPORATION use only. In this lesson, you should have learned how to: • Describe fundamental security requirements • Define the following terms:.

(53) Case Study: Applying Security Practices. Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. Case Study: Applying Security Practices The following slides are a case study of how secure-coding practices can be used to reduce or eliminate the possibility of SQL injection exploits. Your instructor will guide you through this section. The basic methods used in reducing the possibility of SQL injection can be adapted and applied to other common exploits. Specifics such as removing dynamic SQL would be changed to not allowing certain characters in XML or HTML to prevent cross-scripting. But general techniques such as peer review and testing are applicable across all type of exploits.. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle Database 11g: Security 1 - 23. Oracle University and ORACLE CORPORATION use only. This case study covers applying security practices to a SQL injection exploit..

(54) SQL injection: • Tricks the SQL engine into executing unintended commands • Exploits a common vulnerability in the application • Supplies crafted user-supplied strings, which are used in dynamic SQL statements. Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. Understanding SQL Injection SQL injection is a technique for maliciously exploiting applications that use client-supplied data in SQL statements. The flaw is common; a user-supplied input string is concatenated to a dynamic SQL statement, and then executed. Attackers trick the SQL engine into executing unintended commands by supplying specially crafted string input. This input can allow the attacker to gain unauthorized access to a database to view or manipulate restricted data. SQL injection techniques may differ, but they all exploit a common vulnerability in the application. That vulnerability is a user-supplied string literal interpreted as code by the SQL engine. These literals may be input through forms, URLs, or hidden fields in Web pages. The vulnerability comes whenever the input string is added to a dynamic SQL statement by the application code. To immunize your code against SQL injection attacks, you must use bind arguments (either automatically with static SQL, or explicitly with dynamic SQL), or validate and sanitize all input concatenated to dynamic SQL. Although a program or an application may be vulnerable to SQL injection, Web applications are at a higher risk because an attacker can perpetrate SQL injection attacks without any database or application authentication.. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle Database 11g: Security 1 - 24. Oracle University and ORACLE CORPORATION use only. Understanding SQL Injection.

(55) Preventing SQL Injection. – Use invoker’s rights. – Reduce arbitrary inputs.. •. Avoiding dynamic SQL with concatenated input – Use static SQL. – Use bind arguments. – Validate input.. • •. Designing code that is immune to SQL injections Testing code for SQL injection flaws. Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. Preventing SQL Injection Applying the principles outlined in the lesson to prevent and mitigate common exploits to SQL injection attacks yields the list shown in the slide. Methods shown are covered in more detail in the following slides.. THESE eKIT MATERIALS ARE FOR YOUR USE IN THIS CLASSROOM ONLY. COPYING eKIT MATERIALS FROM THIS COMPUTER IS STRICTLY PROHIBITED. Oracle Database 11g: Security 1 - 25. Oracle University and ORACLE CORPORATION use only. SQL injection can be prevented by: • Reducing the attack surface.

No results found