BID212: Securing Sybase IQ and Providing Secure OLAP Functionality

1
BID212 Securing Sybase IQ and Providing Secure
OLAP Functionality
Jim CampbellPrincipal Sales Consultantjames.camp
bell_at_sybase.com / 301-896-1231 August 6, 2003
2
Agenda

• Overview why secure ASIQ?
• Business Case for Security
• Common-Criteria and Protection-Levels
• Why ASIQ?
• Review ASIQ Security Out-of-the-Box
• ASIQ Security Gaps
• ASE Security
• Securing ASIQ
• Data Explorer
• Data Explorer Demo

3
Overview
4
Business Case for Security

• Protect organizational assets from threats where
  threats are defined as the the potential abuse of
  protected assets
• Provide data repository access to people with
  different rights
• Provide a safe-infrastructure that prevents
  unauthorized use or view of data-objects
• Provide audit-trails to help detect unauthorized
  or malicious use of assets

5
Common Criteria

• Common-Criteria (CC) is meant to be used as the
  basis for evaluation of security-properties of
  IT-products and systems
• CC is comprised of a series of evaluation levels
  that are used to evaluate a target profile
• CC is required for use of all software in the
  Federal Government. However, this rule is not
  generally enforced
• CC should not be confused with Protect-Levels

6
Evaluation Assurance Level

• EAL 1 Functionally tested
• EAL 2 Structurally tested
• EAL 3 Methodically tested and checked
• EAL 4 Methodically designed, tested, and
  reviewed
• EAL 5 Semi-formally designed and tested
• EAL 6 Semi-formally verified designed and tested

7
Protection Profile and Security Target Evaluation

• TOE Target operating environment
• Protection profile An implementation-independent
  set of security requirements for a category of
  TOEs that meet specific consumer needs
• Security Target A set of security requirements
  and specifications to be used as the basis for
  evaluation of an identified TOE

8
Protection Levels

• Protect levels are internal certifications of
  information systems by agencies in the Intel
  community which is spelled out in CIA Directive
  6/3
• The use of Common-Criteria certified components
  aide the certification of information-systems
  (IS)
• Protect levels are not Evaluation Assurance
  Levels!

9
Levels of Concern and Protection Levels

• Levels-of-Concern
• Confidentiality sensitivity of information that
  the IS maintains, processes, and transmits
• Integrity degree of resistance to unauthorized
  modification
• Availability Degree of ready availability
  required for the information maintained,
  processed, and transmitted
• Protection Levels
• PL 1 All users have all required approvals for
  access to all information on the IS
• PL 2 - When all users have all required formal
  approvals for access to all information, but at
  least one users lacks administrative approval for
  some information
• PL 3 When at least one user lacks at least one
  required formal approval to information on the IS
• PL 4 When at least one user lacks sufficient
  clearance for access to some of the information
  on the IS, but all users have at least a
  Secret-Clearance
• PL 5 When at least one user lacks any clearance
  for access to some of the information on the IS

10
ASIQ
11
Why ASIQ?

• Warehouses based on traditional RDBMS technology
  are destined to fail and support growth The
  issues are
• SPEED
• data base engines read every data-column
• adding users slows response time
• improvements in response time require manual time
  to tune systmes and/or additional hardware
• as queries increase, loading speed decreases
• SCALABILITY
• Adding traditional RDBMS servers is not a simple
  scalable solution
• Traditional RDBMS systems start to loose servers
  as the amount of data grows
• ECONOMICS

12
IQ Multiplex Storage economy

• IQ-M will typically need 1/2 to 1/10 of storage
  compared to other DBMS
• IQ-M will need (for a given performance level)
  1/2 to 1/5 of IO Bandwidth
• IQ-M uses high end storage which is 1/2 the price
  of OLTP small disk storage
• Savings include
• disk capacity 300K/TB (Unix)
• disk connectivity and BW 100K/TB (Unix)
• annual maintenance 50K/TB (includes DBA time,
  backups, electricity, AC, etc.)
• restore time in case ? (sometimes priceless)
• Secondary site (DT, if needed) 200K/TB (?)

13
Example using traditional database
Calculate the average sales for the A stores
in NY

• Traditional approach
• Data stored by row
• Select a filter if available
• Bitmap indexes are most efficient for data
  with few values
• If filter is not selective enough (often lt1)
  then scan table
• Go to selected pages and add up sales numbers
• Randomly distributed data will result in most
  pages being read
• Still have to read irrelevant data in each page

14
ASIQ - Vertical Partitioning of Data
ASIQ - Columns are stored independently

• Benefits
• Consistent data types are easy to compress
• Resulting storage size is typically less than
  50 the size of the raw data

15
ASIQ Out-Of-The-Box Security

• DBA-role accounts own the database and can set
  permissions
• Uses ASA role-based security
• Views and stored-procedures
• No SSL or password encryption over TDS
• Packet encryption available over non-TDS
  protocols
• No row-level access-controls

16
ASIQ Security Gaps
17
ASIQ Security Requirement Gap Analysis

• Protected against the abuse of protected assets
  ASIQ does not provide strong encrypted
  authentication across TCP/IP
• Support access to people with different rights
  ASIQ does not provide row-level access controls
• Prevent unauthorized use or view of data-objects
  ASIQ does not support the transmission of
  encrypted data over TCP/IP
• Provide audit-trails ASIQ does not have a
  strong auditing capability

18
Adaptive Server Enterprise (ASE)
19
ASE Key Security Features

• Application transparency
• Log-in triggers
• Single sign-on
• Role-based column access control and policy-based
  row-level access control
• Column-based domain integrity rules
• Application context facility
• LDAP support for user identification

• Proxy authorization
• Secure Socket Layer (SSL) encryption
• PKI to secure data in transit
• Server-based administration
• Data item level encryption
• Separate keys for different data columns
• Encrypted logs and common log format

20
Security Services in ASE Solution in ASE 12.5

• SSL Plus v 3.0.x integrated with ctlib and ASE
• Secure 128 bit encryption on the wire
• Support digital certificates from CA like
  Entrust, RSA, Baltimore and Verisign for server
  authentication

21
Security Services in ASE Solution in ASE 12.5

• Row level security
• uses user defined rules for constraints
• powerful constraints can be built using Java
• association done at login time

• sp_addtype typeA, int
• create access rule ruleA as
• _at_col suser_id()
• sp_bindrule ruleA, typeA
• create table tableA (c1 typeA, c2 int, )
• Running select returns only those rows where
  value of c1 matches suser_id()

22
Directory Services in ASE Solution in ASE 12.5

• When configured to use LDAP, ASE retrieves server
  information from an LDAP server
• A 3rd party LDAP server must be used Netscape
  4.0 or OpenLDAP 2.0.7
• LDAP can be used for client-server and
  server-server communication

23
Securing ASIQ
24
Security solution requirements

• Encrypted authentication
• Encrypted network connection
• Row-level access control
• Ability to use users security-profile to enforce
  access-rights
• Auditing

25
Securing ASIQ - Architecture
ASE
LDAP
ASIQ
CIS
Users
WCC
Data Explorer
Warehouse data
Warehouse Control Center Data Explorer
26
Securing ASIQ Setup Configuration

• Securing ASIQ Architecture
• Setting Up CIS
• Creating ASE Proxy Tables to ASIQ
• Login-Trigger
• Access Rules

27
Setting up Secure ASIQ

• Connections to ASIQ through ASE-CIS
• ASE and ASIQ installed on same machine with a TDS
  connection through localhost
• Create proxy tables to ASIQ
• Creating login procedure to set application
  context security variables
• Create access control rules

28
Adding the Remote Server

• exec sp_addserver snowflake, ASIQ, snowflake
• exec sp_serveroption snowflake, "timeouts", true
• exec sp_serveroption snowflake, "net password
  encryption", false
• exec sp_serveroption snowflake, "readonly", false
  
• exec sp_serveroption snowflake, "rpc security
  model A", true
• exec sp_addexternlogin snowflake, sa, DBA, SQL
• exec sp_addexternlogin snowflake, sybase, DBA,
  SQL
• go

29
Creating the Proxy Tables

• create proxy_table CUSTOMER
• at 'snowflake..DBA.CUSTOMER'
  
• create proxy_table CUSTOMER_CATEGORY
• at 'snowflake..DBA.CUSTOMER_CATEGORY'
  
• .
• .
• .
• create proxy_table GROCERY_TRANSACTION
• at 'snowflake..DBA.GROCERY_TRANSACTION'
  
• create proxy_table TIME_PERIOD
• at 'snowflake..DBA.TIME_PERIOD'
  

30
Setup of App-Context Table
create table app_context ( userid
varchar(30), appname varchar(30), attr
varchar(30), value char(1)) go grant select
on app_context to public go
31
Setup of App-Context Table
insert into app_context values ('test1','demo','la
bela','1') insert into app_context values
('test1','demo','labelb','0') insert into
app_context values ('test1','demo','labelc','0') i
nsert into app_context values ('test1','demo','lab
eld','0') insert into app_context values
('test2','demo','labela','1') insert into
app_context values ('test2','demo','labelb','1') i
nsert into app_context values ('test2','demo','lab
elc','0') insert into app_context values
('test2','demo','labeld','0')
32
Creating a Login Trigger

• create proc loginproc as
• declare _at_appname varchar(30), _at_attr varchar(30),
  _at_value char(1), _at_retval int
• declare cur1 cursor for
• select appname, attr, value from app_context
  where userid suser_name()
• open cur1
• fetch cur1 into _at_appname, _at_attr, _at_value
• while (_at__at_sqlstatus 0)
• begin
• select _at_retval set_appcontext(rtrim(_at_appname),r
  trim(_at_attr),_at_value)
• fetch cur1 into _at_appname, _at_attr, _at_value
• end
• go

33
Binding a Login-Trigger to User-Accounts

• grant execute on loginproc to public
• go
• sp_modifylogin test1,"login script","loginproc"
• go

34
Creating Access Rules

• create access rule sla
• as _at_sla convert(bit, get_appcontext('demo',
  'lablea'))
• go
• create access rule slb
• as _at_slb convert(bit, get_appcontext('demo',
  'lableb'))
• go
• exec sp_bindrule sla, "CUSTOMER.sla"
• exec sp_bindrule slb, "CUSTOMER.slb"
• go

35
Securing ASIQ Demo
36
Data Explorer
37
Introducing Data Explorer

• The Vision . . . Why?
• What is Data Explorer?
• Data Explorer Architecture
• Data Explorer Demo

38
The Vision . . . Why?

• Develop a tool that provides a core set of OLAP
  and DSS analytical capabilities without the
  expense of higher-end tools such as Cognos,
  Microstrategy and Business Objects.
• Increase the ROI of Data Warehouses by making it
  more cost-effective for many individuals and
  groups to take advantage of Data Warehouse.
• Provide key business performance indicators and
  monitors through tools such as a dashboard.

39
Data Explorer The Users Perspective

• Ad-hoc analysis performed in a controlled, Safe
  environment
• It is highly unlikely that the user will get
  this wrong!

40
Data Explorer The Administrators Perspective

• Easy, wizard driven process
• Typically takes less than 1/2 hour to complete
• Majority of this time is import processing

One Time Process To Import Meta Data
One Time Process To Create the InfoCube
Adhoc Query Capability in a Controlled
Environment
PD9 Model
Create InfoCube From SubArea
Query/ Visualize with Data Explorer
Import to WCC
Identify Measures
Create Hierarchies
Validate Model (Optional)
Save InfoCube
41
Product Functionality

• Beta
• Associate dimensions with hierarchies
• InfoCube admin wizard
• Report Wizard - the process
• Dimension Browsing
• Drill down, dynamic matrix generation
• Visualization Basics 2D/3D Pie Chart, 2D/3D Bar
  Chart
• Show SQL/Hide SQL toggling
• Fine-tuning filtering on dimension browsing
• Fine-tuning filtering on drilling down

• Fine-tuning detecting end of hierarchy
• Graphical drilling image map
• Dimension ordering
• Drill up
• Calculated measures
• Slice Dice 
• Drill Across
• Save Reports
• Report Printing
• Generate PDF Report
• EP Portlet integration

42
Product Functionality

• Projected for Version 1.0
• Fine-tuning drilling w/ multi-dimensions
  selected
• Add more visual components Line, Scatter,
  Dash-dial,etc.
• HTML Form validation
• Add/Delete/Move dimension-level columns in the
  report
• Add/Delete/Move measures in the report
• Connection Manager with password encryption
• User log-in and session management
• EP Portlet integration

43
Data Explorer Architecture
Client Tier
App Server Tier
Data Tier
Product Support
Browser
RDBMS Sybase (ASE/IQ) Oracle Microsoft IBM

Servlets
JSPs
TagLibrary
HTML/XML/XSL
JFreeChart
Other Graphics
Client App
XML
Client Application Logic
XML/XSLT
JB/EJB
NVO
Other
File
44
Data Explorer Demo