Bid212 securing sybase iq and providing secure olap functionality
This presentation is the property of its rightful owner.
Sponsored Links
1 / 44

BID212: Securing Sybase IQ and Providing Secure OLAP Functionality PowerPoint PPT Presentation

  • Uploaded on
  • Presentation posted in: General

BID212: Securing Sybase IQ and Providing Secure OLAP Functionality. Jim Campbell Principal Sales Consultant [email protected] / 301-896-1231 August 6, 2003. Agenda. Overview – why secure ASIQ? Business Case for Security Common-Criteria and Protection-Levels Why ASIQ?

Download Presentation

BID212: Securing Sybase IQ and Providing Secure OLAP Functionality

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript

Bid212 securing sybase iq and providing secure olap functionality

BID212: Securing Sybase IQ and Providing Secure OLAP Functionality

Jim CampbellPrincipal Sales [email protected] / 301-896-1231

August 6, 2003



  • 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



Business case for security

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

Common criteria

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”

Evaluation assurance level

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

Protection profile and security target evaluation

Protection Profile and Security Target Evaluation

  • TOE – Target operating environment

  • Protection profile – An implementation-independent set of security requirements for a category of TOE’s 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

Protection levels

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!

Levels of concern and protection levels

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

Bid212 securing sybase iq and providing secure olap functionality


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


      • Adding traditional RDBMS servers is not a simple scalable solution

      • Traditional RDBMS systems start to loose servers as the amount of data grows


Iq multiplex storage economy

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 (?)

Example using traditional database

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 <1%) 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

Asiq vertical partitioning of data

ASIQ - Vertical Partitioning of Data

ASIQ - Columns

are stored


  • Benefits:

  • Consistent data types are easy to compress

  • Resulting storage size is typically less than 50% the size of the raw data

Asiq out of the box security

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

Asiq security gaps

ASIQ Security Gaps

Asiq security requirement gap analysis

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

Adaptive server enterprise ase

Adaptive Server Enterprise (ASE)

Ase key security features

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

Security services in ase s olution in ase 12 5

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

Security services in ase solution in ase 12 5


create access rule ruleA as

@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()

Row level security

uses user defined rules for constraints

powerful constraints can be built using Java

association done at login time

Security Services in ASE Solution in ASE 12.5

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

Directory Services in ASE Solution in ASE 12.5

Securing asiq

Securing ASIQ

Security solution requirements

Security solution requirements

  • Encrypted authentication

  • Encrypted network connection

  • Row-level access control

  • Ability to use user’s security-profile to enforce access-rights

  • Auditing

Securing asiq architecture

User Usable Data Cubes

Business Objects


Micro Strategy

Securing ASIQ - Architecture







Data Explorer

Warehouse data

Warehouse Control Center


Data Explorer

Securing asiq setup configuration

Securing ASIQ – Setup & Configuration

  • Securing ASIQ Architecture

  • Setting Up CIS

  • Creating ASE Proxy Tables to ASIQ

  • Login-Trigger

  • Access Rules

Setting up secure asiq

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

Adding the remote server

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


Creating the proxy tables

Creating the Proxy Tables

create proxy_table CUSTOMER

at 'snowflake..DBA.CUSTOMER'

create proxy_table CUSTOMER_CATEGORY





create proxy_table GROCERY_TRANSACTION


create proxy_table TIME_PERIOD

at 'snowflake..DBA.TIME_PERIOD'

Setup of app context table

Setup of App-Context Table

create table app_context (

userid varchar(30),

appname varchar(30),

attr varchar(30),

value char(1))


grant select on app_context to public


Setup of app context table1

Setup of App-Context Table

insert into app_context values ('test1','demo','labela','1')

insert into app_context values ('test1','demo','labelb','0')

insert into app_context values ('test1','demo','labelc','0')

insert into app_context values ('test1','demo','labeld','0')

insert into app_context values ('test2','demo','labela','1')

insert into app_context values ('test2','demo','labelb','1')

insert into app_context values ('test2','demo','labelc','0')

insert into app_context values ('test2','demo','labeld','0')

Creating a login trigger

Creating a “Login Trigger”

create proc loginproc as

declare @appname varchar(30), @attr varchar(30), @value char(1), @retval int

declare cur1 cursor for

select appname, attr, value from app_context where userid = suser_name()

open cur1

fetch cur1 into @appname, @attr, @value

while (@@sqlstatus = 0)


select @retval = set_appcontext(rtrim(@appname),rtrim(@attr),@value)

fetch cur1 into @appname, @attr, @value



Binding a login trigger to user accounts

Binding a Login-Trigger to User-Accounts

grant execute on loginproc to public


sp_modifylogin test1,"login script","loginproc"


Creating access rules

Creating Access Rules

create access rule sla

as @sla = convert(bit, get_appcontext('demo', 'lablea'))


create access rule slb

as @slb = convert(bit, get_appcontext('demo', 'lableb'))


exec sp_bindrule sla, "CUSTOMER.sla"

exec sp_bindrule slb, "CUSTOMER.slb"


Securing asiq demo

Securing ASIQ Demo

Data explorer

Data Explorer

Introducing data explorer

Introducing Data Explorer

  • The Vision . . . Why?

  • What is Data Explorer?

  • Data Explorer Architecture

  • Data Explorer Demo

The vision why

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 Warehouse’s 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’.

Data explorer the user s perspective

What Measures are important by what dimensions

What is the best way to present the Measures and Dimensions

Save the report for later consumption

Printing and Deployment options for the report




Slice & Dice

Save Report


Select Dims and Measures


EP Portlet

Data Explorer – The User’s Perspective

  • Ad-hoc analysis performed in a controlled, “Safe” environment

  • It is highly unlikely that the user will get this wrong!

Data explorer the administrator s perspective

Data Explorer – The Administrator’s Perspective

  • Easy, wizard driven process

  • Typically takes less than 1/2 hour to complete

  • Majority of this time is import processing

One Time Process


Import Meta Data

One Time Process


Create the InfoCube

Adhoc Query Capability

in a

Controlled Environment

PD9 Model

Create InfoCube

From SubArea


Visualize with

Data Explorer

Import to WCC





Validate Model


Save InfoCube

Product functionality


*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

Product Functionality

Product functionality1

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

Product Functionality

Data explorer architecture

JVisuals Web Container

Data Explorer Architecture

Client Tier

App Server Tier

Data Tier





Sybase (ASE/IQ)









Other Graphics

Client App


Client Application Logic






Data explorer demo

Data Explorer Demo

  • Login