Jerry Held

1
(No Transcript)
2
Security and the Data Warehouse
Session id 40176

• Kristy Edwards
• Principle Product Manager, Security
• Oracle Corporation
• George Lumpkin
• Director Product Management, DW
• Oracle Corporation

3
Agenda

• Data warehousing security issues
• Data Warehouse security strategy
• Secure the entire DW environment
• Oracle database security is the foundation of a
  DW security strategy
• Summary Customer success stories

4
Technical challenges

• More valuable data since current and historical
  records are stored from all parts of the
  organization
• Customer profiles
• Companys financial history
• Quarterly sales information
• Performance is paramount
• Security cannot adversely affect performance
• More users have direct, ad-hoc query access to a
  data warehouse
• Sensitive data stored in data marts and ETL
  servers

5
Business challenges

• How can I consolidate multiple data marts in the
  same database?
• How can I share the information in my data
  warehouse with partners and customers?
• How can I ensure that my data warehouse obeys
  laws and regulations regarding data privacy?

6
Agenda

• Data warehousing security issues
• Data Warehouse security strategy
• Secure the entire DW environment
• Oracle database security is the foundation of a
  DW security strategy
• Summary Customer success stories

7
Oracle Strategy for DW Security

• End-to-End Security
• Secure the entire DW process
• From ETL all the way through the end-user
• Consolidation
• Leverage consolidation to increase security
• Secure Foundation
• Oracle database security is the foundation of a
  DW security strategy

8
Consolidation Provides Better Security

• Centrally managed by an expert
• Fewer points of attack
• Apply security patches quickly in one place
• Enforce security consistently
• Protect the data itself

9
Agenda

• Data warehouse security issues
• Data warehouse security strategy
• Secure the entire DW environment
• Oracle database security is the foundation of a
  DW security strategy
• Summary Customer success stories

10
Layers of Security

• Physical
• Policies
• Perimeter and Network
• Applications and RDBMS
• OS

11
Security Trends
Identity Management
Audit
Fine-grained Access Control
OLTP
Network Crypto
Data Warehousing
Access Control
Authentication
Time
12
Oracle Database 10g Provides the Security
Foundation

• Security built into the server cannot be bypassed
• Consistent security policy regardless of the tool
  or interface used to access data
• Oracle preserves the identity of the user
• Security is transparent to tools and applications

Discoverer
Identical Data Access Policies
Business Objects
Oracle DB 10g
SQLPlus
13
Assurance of Oracle Database 10g Security
X

• Builds on 15 17 independent security evaluations
• Effective way to validate vendors security
  assertions
• NCR Teradata has 0 evaluations
• IBM DB2 has 0 evaluations
• SQL Server has 1 evaluation

14
Leverage Oracle Database 10g Security
Protect data in transit
Protect stored data
Access Control
Authenticate
Authorize
Audit
u35nhnti3
aud
Enterprise Data Warehouse
Identity Management
15
Row-level Security Benefits for Data Warehousing

• Consolidation
• with assurance of data separation
• Lower cost of ownership
• Build security once, not within multiple
  applications
• Minimizes risks through database-enforced
  security
• Highly scalable without performance impact
• Leverage RLS to protect private information in
  data warehouses

16
Security Issue 2Accountability

• Keep users accountable for their actions
• Identify misuse of legitimate user privilege
• E.g., data snoopers (e.g., why is a user
  accessing his own record from June 29 so often?)
• Support intrusion detection

17
SolutionFine-grained Auditing
Relevant column

• Audit for accountability
• Audit with precision
• Monitor data access based on content
• Attach audit policy to table or view
• with WHERE condition
• Relevant column
• PL/SQL package DBMS_FGA to apply policies to
  table/view
• Exact query text in audit record
• Data warehouses cannot afford to audit too broadly

FGA Policy
18
Fine-grained Auditing
Audit Policy
AUDIT_CONDITION name !
USER AUDIT_COLUMN DIAGNOSIS
PATIENTS
SELECT name, job, deptno FROM patients
SELECT name, diagnosis FROM patients WHERE
nameKING
19
Benefits for Data Warehousing

• Audit analysis
• Fewer, but more precise, audit records
• Reduce false or misleading audits makes it easier
  to analyze audit records
• SQL text gives insight to intent of the query
• Accountability and deterrence
• Minimizes security risks if a user knows he/she
  being monitored
• Supports fact tables and materialized views

20
Security Issue 3Protect Data In Transit

• Stored data has inherent protection of user
  authentication, access control, audit
• Data traveling over network does not have these
  protections
• 80 of data loss is to insiders

21
Solution Network Encryption

• Oracle Advanced Security encrypts all
  communication with the database
• Uses standard algorithms
• AES
• 3DES, DES
• RC4
• Diffie-Hellman key exchange
• Supplies SSL
• Secures all protocols into the database
• Net8, IIOP, Thin JDBC

22
Benefits for Data Warehousing

• Minimizes security risks since data traveling
  over the network is encrypted
• Protects all network communications with Oracle
  with minimal impact
• Easy to deploy
• 6 sqlnet.ora parameters

23
Security Issue 4 Protect Stored Data

• Especially sensitive information requires
  additional layer of security
• social security numbers
• credit card numbers
• Mitigate threats
• hackers compromising the OS and reading database
  files
• malicious DBA

24
SolutionStored Data Encryption

• Oracle DB 10g has a new encrypt/decrypt interface
  called DBMS_CRYPTO
• AES, 3DES and DES encryption
• MD5, MD4 and SHA-1 cryptographic hashes
• Supports all datatypes (LOB, BLOB, CLOB)
• Application provides key generation, management,
  recovery
• Encryption keys must be stored somewhere
• Can be stored in a database file, on the OS, in
  application, on diskette, etc.
• Dont solve access control problem with encryption

25
Stored Data Encryption Benefits

• Minimizes security risks by storing very
  sensitive data encrypted
• Oracle Database 10g supports third party security
  solutions

26
Security Issue 1Access Control

• Not all users should have access to all the data
  in the data warehouse
• Database must preserve user identity to determine
  exactly what the user can access
• Database must control access

27
Access Control

• Object-level security
• Authorization
• Roles and privileges
• Manage locally or centrally
• Role-based Access Control (RBAC)
• Row-level Security (RLS)
• Control access within objects
• Fine-grained access control
• Virtual Private Database
• Oracle Label Security

28
Virtual Private Database

• Data access is managed at the database level
• Fine-Grained Access Control
• Application Context
• Users only see data that they have access to
• Row-level access control
• Add policy to any table, view or synonym
• Administrator binds PL/SQL package with table
  using Oracle-supplied API (dbms_rls)

Table or View
29
VPD for Data Warehousing

• Server dynamically rewrites SQL
• Query modification based on PL/SQL package
  assigned to an object
• Where clause appended to SQL statement

SALES_HISTORY
Access to his/her territorys sales
Western Sales Rep
SELECT sum(s) FROM SALES_HISTORY
Product Manager
Access to one products sales across all
territories
1MM rows
30
VPD for Data Warehousing

• No sacrifice in SQL performance
• All performance features (indexes, parallelism,
  partitioning, etc) apply to VPD-based queries
• Fact Tables
• Policies can be applied to very large fact tables
• Most popular implementation
• Secures data at the source
• Materialized Views
• Policies can be applied to summaries
• Materialized Views increase performance, and VPD
  increases security

31
Oracle Label Security for DW

• Out-of-the-box, row level security
• Built on VPD
• Extends VPD by adding label-based access control
• Same benefits as VPD, but works out of the box
• Installable product vs. write your own
• Data access is based on sensitivity labels and
  customizable enforcement options
• Labels can be applied to fact tables and/or
  materialized views

32
Oracle Label Security for DW
Oracle Label Security authorizations sensitive
testscore District Schools
Superintendent
Student Fact Table
Fname Lname Score Sensitivity Label
Ana Adams 155 sensitivetestscoreJFK
High
Mona Patel 150 sensitivetestscoreML
K High
Jen Chu 175 sensitivetestscore
MLK High
Ron Alta 145 sensitivetestscore
JFK High
Oracle Label Security authorizations sensitive
testscore JFK High
JFK Principal
33
Agenda

• Data warehouse security issues
• Data warehouse security strategy
• Secure the entire DW environment
• Oracle database security is the foundation of a
  DW security strategy
• Summary Customer success stories

34
Business challenges

• How can I consolidate multiple data marts in the
  same database?
• How can I share the information in my data
  warehouse with partners and customers?
• How can I ensure that my data warehouse obeys
  laws and regulations regarding data privacy?

35
Case Study Province of British Columbia

• Business requirements
• Consolidated financial information for 28
  Ministries
• Individual Ministry data mart look and feel
• Security issues
• Confidentiality of inter-Ministry financial
  budgetary and expenditure information
• Users require different levels of access -
  Ministry staff restricted to their data,
  Controller General staff have open access for
  consolidated and audit reporting
• Oracle VPD solves data warehouse security issues
• No separate data marts
• All financial information for 28 Ministries
  stored in one database

36
Business challenges

• How can I consolidate multiple data marts in the
  same database?
• How can I share the information in my data
  warehouse with partners and customers?
• How can I ensure that my data warehouse obeys
  laws and regulations regarding data privacy?

37
Case Study Data Syndicator

• Business requirements
• Deliver marketing research data to companies
  cost-effectively
• Deliver timely web-based information to customers
• Security issues
• Separate proprietary information between each
  company
• Within each company, users require different
  levels of access
• Oracle VPD solves data warehouse security issues
• Terabyte DW with all customer information in one
  table
• Did not need to build complex security layer
• Does not have to maintain a separate data
  warehouse for each customer (30)
• Generate the same report for multiple customers
  quickly

38
Business challenges

• How can I consolidate multiple data marts in the
  same database?
• How can I share the information in my data
  warehouse with partners and customers?
• How can I ensure that my data warehouse obeys
  laws and regulations regarding data privacy?

39
Case Study Los Angeles Unified School District

• Business requirements
• Provide its 750,000 students information about
  themselves
• Web-based access for administrators to grades,
  test scores, graduation rates and demographics
• Security and privacy issues
• Confidentiality of student information based on
  California Education Code and the Family
  Education Rights and Privacy Act of 1974
• Oracle VPD solves data warehouse security issues
• Student information stored the same data
  warehouse
• Different types of users get different levels of
  access student, teacher, principal, district
  manager, district-wide programs manager

40
Data Warehouse Security Summary

• Security is crucial for data warehouses
• Data warehouses contain valuable data
• Users have direct access to data
• Security adds value to data warehouses
• Enables efficiency via consolidation
• Oracles data warehouse customers rely on our
  security solutions

41
Next Steps.Security and Identity Management
Sessions

• Identity Management for Database Applications
• Oracle Platform Security Solving Real-World
  Problems
• Ask the Security Expert
• Unbreakable What We Learned
• Securing J2EE Applications with Oracle Identity
  Management

• Planning Your Oracle Identity Management
  Deployment
• Improving Information Assurance of Linux
• Oracle and Thor Identity Management Provisioning
• Implementing Identity Management at Lawrence
  Livermore National Labs - Thursday 315 Rm. 120

42
Next StepsOracle DEMOgrounds

• Virtual Private Database Column Enforcement
• Oracle Label Security Directory Integration
• Directory Based Database Authentication
• Single Sign-On
• CA PKI Platform
• Windows Integration eBusiness Provisioning
• Highly Available LDAP

43
Next Steps
Oracle  Security Command Center (Booth 1736) Win
a Dell Axim X5 handheld device

• A4vison
• Accela
• Acsys Biometrics
• Alert Technologies
• Ascendent Telecommunicatons
• BIO-Key International
• Compressus
• Dell Environmax
• eSpatial
• nCipher

• Netegrity
• PCI Geomatics
• PlanGraphics
• 3Ship Analytics
• Targusinfo
• Thor Technologies
• Vigilos
• Waveset
• Xybernaut
• Applications Security (Booth 841)
• Vormetric (Booth 2243)

44
Next Steps for DW / BIData Warehousing DB
Sessions
Tuesday
Monday

• 1100 AM
• 40153, Room 304
• Oracle Warehouse Builder
• New Oracle Database 10g Release
• 330 PM
• 40176, Room 303
• Security and the Data Warehouse
• 400 PM
• 40166, Room 130
• Oracle Database 10g
• SQL Model Clause

• 830 AM
• 40125, Room 130
• Oracle Database 10g
• A Spatial VLDB Case Study
• 330 PM
• 40177, Room 303
• Building a Terabyte Data Warehouse,
• Using Linux and RAC
•  
• 500 PM
• 40043, Room 104
• Data Pump in Oracle Database 10g
• Foundation for Ultrahigh-Speed Data Movement

For More Info On Oracle BI/DW Go To
http//otn.oracle.com/products/bi/db/dbbi.html
45
Next Steps for DW / BIData Warehousing DB
Sessions
Thursday
Business Intelligence and Data Warehousing Demos
All Four Days In The Oracle Demo Campground
830 AM 40179, Room 304 Oracle Database 10g
Data Warehouse Backup and Recovery 1100
AM 36782, Room 304 Experiences with Real-Time
Data Warehousing using Oracle 10g
100PM 40150, Room 102 Turbocharge your
Database, Using the Oracle Database 10g SQLAccess
Advisor

• Oracle Database 10g
• Oracle OLAP
• Oracle Data Mining
• Oracle Warehouse Builder
• Oracle Application Server 10g

For More Info On Oracle BI/DW Go To
http//otn.oracle.com/products/bi/db/dbbi.html
46
Reminder please complete the OracleWorld
online session surveyThank you.
47
(No Transcript)