Bid212 securing sybase iq and providing secure olap functionality
Download
1 / 44

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


  • 99 Views
  • Uploaded on

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?

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' BID212: Securing Sybase IQ and Providing Secure OLAP Functionality' - madra


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


Agenda
Agenda Functionality

  • 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


Overview
Overview Functionality


Business case for security
Business Case for Security Functionality

  • 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 Functionality

  • 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 Functionality

  • 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 Functionality

  • 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 Functionality

  • 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 Functionality

  • 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


ASIQ Functionality


Why asiq
Why ASIQ? Functionality

  • 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


Iq multiplex storage economy
IQ Multiplex FunctionalityStorage 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 Functionality

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 Functionality

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


Asiq out of the box security
ASIQ Out-Of-The-Box Security Functionality

  • 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 Functionality


Asiq security requirement gap analysis
ASIQ Security Requirement Gap Analysis Functionality

  • 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



Ase key security features
ASE Key Security Features Functionality

  • 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 S Functionalityolution 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

sp_addtype typeA, int Functionality

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 information from an LDAP server


Security solution requirements
Security solution requirements information from an LDAP server

  • 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 information from an LDAP server

Business Objects

COGNOS

Micro Strategy

Securing ASIQ - Architecture

ASE

LDAP

ASIQ

CIS

Users

WCC

Data Explorer

Warehouse data

Warehouse Control Center

&

Data Explorer


Securing asiq setup configuration
Securing ASIQ – Setup & Configuration information from an LDAP server

  • Securing ASIQ Architecture

  • Setting Up CIS

  • Creating ASE Proxy Tables to ASIQ

  • Login-Trigger

  • Access Rules


Setting up secure asiq
Setting up Secure ASIQ information from an LDAP server

  • 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 information from an LDAP 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


Creating the proxy tables
Creating the Proxy Tables information from an LDAP server

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'


Setup of app context table
Setup of App-Context Table information from an LDAP server

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


Setup of app context table1
Setup of App-Context Table information from an LDAP server

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” information from an LDAP server

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)

begin

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

fetch cur1 into @appname, @attr, @value

end

go


Binding a login trigger to user accounts
Binding a Login-Trigger to User-Accounts information from an LDAP server

grant execute on loginproc to public

go

sp_modifylogin test1,"login script","loginproc"

go


Creating access rules
Creating Access Rules information from an LDAP server

create access rule sla

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

go

create access rule slb

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

go

exec sp_bindrule sla, "CUSTOMER.sla"

exec sp_bindrule slb, "CUSTOMER.slb"

go


Securing asiq demo
Securing ASIQ Demo information from an LDAP server


Data explorer
Data Explorer information from an LDAP server


Introducing data explorer
Introducing Data Explorer information from an LDAP server

  • The Vision . . . Why?

  • What is Data Explorer?

  • Data Explorer Architecture

  • Data Explorer Demo


The vision why
The Vision . . . Why? information from an LDAP server

  • 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 information from an LDAP server

What is the best way to present the Measures and Dimensions

Save the report for later consumption

Printing and Deployment options for the report

ReportPrinting

Drilling

InfoCube

Slice & Dice

Save Report

GeneratingPDF

Select Dims and Measures

Visualization

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 information from an LDAP server

  • 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


Product functionality

Beta information from an LDAP server

*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 information from an LDAP server

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 information from an LDAP server

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


Data explorer demo
Data Explorer Demo information from an LDAP server


ad