BID212: Securing Sybase IQ and Providing Secure OLAP Functionality

1. BID212: Securing Sybase IQ and Providing Secure OLAP Functionality Jim CampbellPrincipal Sales Consultantjames.campbell@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 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

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

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. sp_addtype typeA, int 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

22. 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

23. Securing ASIQ

24. Security solution requirements • Encrypted authentication • Encrypted network connection • Row-level access control • Ability to use user’s security-profile to enforce access-rights • Auditing

25. User Usable Data Cubes Business Objects COGNOS Micro Strategy 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','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')

32. 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) begin select @retval = set_appcontext(rtrim(@appname),rtrim(@attr),@value) fetch cur1 into @appname, @attr, @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 @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

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

39. 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 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!

40. 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 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. 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 Product Functionality

42. 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

43. JVisuals Web Container 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