ASE139 Security and Privacy Infrastructure in Sybase ASE

1
ASE139 - Security and Privacy Infrastructure in
Sybase ASE
SethuDirector of Engineering (Architect)sethu_at_sy
base.com/925-236-5000August 2003
2
Agenda

• Why Security?
• Business Drivers for Security
• Database Security and Solutions
• Security Roadmap
• Open Discussion

3
Informations Price
6,571,000
4,632,000
Average Company Loss
283,000
Proprietary Information Theft
Financial Fraud
Viruses
Source 2002 CSI/FBI Computer Crime and Security
Survey
4
Why Security?

• 300 Y-O-Y increase in Internet related Cyber
  terrorism and fraud reported to FBI
• Technological advancements have increased
  accessibility of information leading to higher
  chances of information theft and greater business
  risk
• Growth in e-business is contingent on greater
  security and privacy of confidential data

5
Business Drivers for Security

• Need to meet regulations
• US government regulations
• Gramm Leach Bliley Act
• Healthcare Insurance Portability and
  Accountability Act (HIPAA)
• Homeland Security Act (new)
• Sarbanes-Oxley Act (SOA)
• Foreign government regulations
• EU Directive on Data Privacy
• Industry requirements
• VISA Cardholder Information Security Program
  (CISP)
• Need to protect clients privacy
• Need to protect company assets

6
Broad Levels of Security

• Network security management
• Anti-viral protection
• Intrusion detection
• Wire-level encryption
• Operating System security management
• Access control to secure files
• Application level security
• Database security (including Auditing)
• Email

7
Application Security Database Systems
8
Security Market Dynamics
IntegratedSolutions
2005

• API standards
• Strong Encryption
• Advanced Hashing/Keying
• Enterprise management

2003
Low Level Security

• SSL
• PKI
• Discrete solutions

Discrete Devices
9
Aspects of Data Security

• Data in flight security of data access
  retrieval
• Authentication
• Access Control
• Data Integrity
• Confidentiality
• Data at rest - security on production and archive
  data
• Confidentiality
• Security management
• Non-repudiation/auditing
• Enterprise User Management

10
Data in Flight - Authentication

• Sybase Solutions - Now
• User name with encrypted password
• CyberSafe Kerberos
• NT LAN Manager
• Sybase Solutions Future
• LDAP integration 12.5.1
• Solaris Password Compatibility on Linux ASE -
  12.5.1
• MIT Kerberos support on all platforms 12.5.1
• Integration with Microsoft Active Directory
  12.5.1
• Support for RSA SECURE ID
• Pluggable Authentication Module (PAM) 12.5.1

11
Data in Flight - Access Control

• Sybase solutions - Now
• Grant/revoke of permissions at database, table,
  column levels
• Row-level security for fine grained access
  control. This rule based systems allows access
  rules to be bound to columns of tables
• Sybase solutions Future
• Grant/Revoke on UPDSTAT, TRUNC TABLE (12.5.1)
• Enhanced Grant/Revoke

12
Access Control - Row Level Security

• Data security at row level with Access Rules on
  Columns or Data Types
• Works in conjunction with Application Context
  Facility and Login Triggers to provide automatic
  data filter
• Consolidate application/data security control
• Reduce developers hard-coding the where clause
• Reduce developers responsibility of coding with
  specific user profiles and application profiles
• Increase flexibility If users privileges
  change, administrator can change users groups,
  roles, or profiles to affect his/her data access
  accordingly

13
Data in Flight - Data Integrity

• Solutions in ASE
• ANSI check constraints, referential constraints
  support
• INSERT, UPDATE, DELETE triggers that can be coded
  in SQL or JAVA
• Sybase solutions Future
• Instead of Triggers 12.5.1

14
Data at Rest/Flight - Confidentiality

• Solutions in ASE - Now
• SSL support on network
• Encryption of stored data using Protegrity SDS
• Solutions in ASE Future
• Native Encrypt/Decrypt function 12.5.1
• Encryption of Archive 12.5.1
• Password protection for Archive 12.5.1
• Better integration of 3rd Party Encryption
  12.5.1

15
Security Management - Auditing

• Solutions in ASE - Now
• Comprehensive ASE audit trail
• Records all security-relevant activity across ASE
• Audit trail stored in system table
• Encrypted audit trail
• Records all access to and administrative actions
  on encrypted data
• Audit trail itself encrypted for protection
• Solutions in ASE Future
• Provide Who did What and When 12.5.1
• Audit Datamart with optimized storage and
  comprehensive reports
• Regulatory Requirements Common Criteria, FIPS
  140-2

16
Access Control - Revisited

• Access Control is done at various levels
• Physical level
• Security of the Computer System facilities and
  other accessories
• Personnel level
• Personnel handling the facilities needs to be
  authorized (Card Keys)
• DBA/SA level
• Handling the database/systems
• End Users
• End Users who use the database systems through
  local or remote networks need to be identified
  and authorized

17
Sybase ASE Row-level Access Control

• Database Owner (DBO) and Table Owner (TBO), at
  their discretion, can control the access to their
  data (DAC)
• DBO control the access to users of the database
  to create tables, procedures, rules, and defaults
• TBO can control the access
• To users, to select, insert, update, delete data
  from the table
• To columns on tables from which users can select
• To the data that is inserted into the columns by
  adding rules and defaults
• Row-level Access Control (RLAC) enables table
  owners to control access to individual rows in
  the table
• RLAC enables physical database to be virtualized
  by
• Creating rules using Application Context Facility
  and,
• Setting the ACF using Auto Login Script/Login
  Triggers based on application user profiles.

18
Sybase ASE Row-level Access Control

• Access Rules (AR) defined to control access to
  data at the levels of individual users, groups or
  specific roles
• Application Context Facility (ACF) useful
  application profiles or user profiles for
  developing tightly secured applications
• Login Trigger (LT) stored procedure executed at
  login time
• They can be used independently or together
• RLAC is implemented using the existing, server
  enforced integrity rules mechanism
• ASE enforces RLAC for all DMLs , preventing any
  means of using covert/overt means to bypass the
  access control to get to the data

Client
Query
19
RLAC Implementation - The Model

• A new type of rule (access rule) is introduced in
  ASE
• The domain integrity rule is enforced on UPDATE,
  INSERT statements
• The access rule is enforced on SELECT, UPDATE,
  DELETE operations on the table
• The access rule is enforced on all the columns of
  a table even if they are not in the select list
  of the query

20
RLAC Implementation - Example

• The TRAN table (clerk_id int, txn_info
  char(8192)) stores transactions done by store
  clerks. The business rule is that the clerks
  should see only their transactions in this table.
  This can be enforced with a simple access rule.
• Create an access rule clerk_rule.
• create access rule clerk_rule as _at_clerk_id
  suser_id()
• Create a default on clerk_id column
• create default clerk_default assuser_id()

21
RLAC Implementation - Example

• Bind the clerk_rule to clerk_id column using
  sp_bindrule sp_bindrule clerk_rule,
  TRAN.clerk_id
• The clerk_rule can be bound to a data type too.
  ASE will enforce this access rule on all the
  tables that uses the data type.
• Bind the clerk_default to the clerk_id column
  using sp_bindefault sp_bindefault
  clerk_default, TRAN.clerk_id

22
RLAC Implementation - Example

• Inserts will have default values of the
  suser_id() values.
• If Clerk1 executes an ad-hoc query (select from
  TRAN), ASE will attach the access rule in the
  where clause, normalize, optimize, compile and
  execute the query.
• The final query that gets executed is
• select from TRAN where clerk_id suser_id()

23
RLAC Implementation - AppServer caveat

• What happens if an application is using an
  AppServer and connection pooling ?
• Answer Application Context Facility (ACF)
  functionality augments connection pooling in
  AppServer

24
RLAC Implementation - ACF

• Application Context Facility (ACF) provides the
  necessary support to set, query and remove task
  specific context
• ACF provides built-ins to SET, GET, REMOVE task-
  specific contexts
• There can be multiple contexts set for a given
  task
• Each context can have any number of attrib/value
  pairs
• ACF can be used as global variables

25
RLAC Implementation - ACF

• All attribute/value pairs are stored as strings.
  User should do the appropriate type conversions
• The Application Context can be set through a Auto
  Login Script or through a Stored Proc. or through
  the middle-tier application

26
RLAC Implementation - ACF

• Examples
• select set_appcontext(CTX1,ATTR1,VAL1)
• select set_appcontext(CTX1,ATTR2,3006)
• select set_appcontext(CTX2,ATTR1,VAL1)
• select get_appcontext(CTX1,ATTR1,VAL1)
• select convert(int, get_apppcontext(CTX1,ATTR2
  ))
• select rm_appcontext(CTX1,ATTR1)
• select list_appcontext()
• All ACF built-ins return 0 for success and 1 for
  failure.

27
RLAC Implementation - ACF

• The connection pooling caveat in the first
  example can be solved by changing the access rule
  to get the clerk id from the ACF (after setting
  it) instead of getting through the suser_id()
  built-in

28
RLAC Implementation - ACF

• Create an access rule clerk_rule as follows
• create access rule clerk_rule as _at_clerk_id
  convert(int, get_appcontext(HRAPP,SUSERID))
• The AppServer can set the SUSERID attribute in
  HRAPP context after identifying the clerk
• select set_appcontext(HRAPP, SUSERID, 3006)
• The final query that gets executed is
• select from TRAN where clerk_id convert(int,
  get_appcontext(HRAPP,SUSERID)

29
RLAC Implementation Grant/Revoke of ACF

• Access Rules and ACF need to be security safe
• If the end-user knows what Application Context is
  set and how they are used in the access rules the
  end-user can set the appropriate context
  attribute and security will be compromised
• Answer
• ACF built-ins cannot be executed unless
  permissions are granted on the ACF built-ins.
• The grant/revoke feature for built-ins is
  available only for ACF built-ins
• To build a safe access control mechanism,
  permission should be revoked (default) from the
  users on set_appcontext built-in and permission
  should be granted on get_appcontext built-in
• The DBO/TBO along with DBA should create a auto
  login script and bind the auto login script to
  each end-user of the application. All
  user/application specific context should be set
  inside this login trigger

30
RLAC Implementation - Login Trigger

• TBO/DBO can also create stored procedures that
  are executable by end-users. These stored
  procedures should validate the end-user and can
  query an application meta-data table and set
  appropriate Application Contexts
• The owner of a auto login script should be
  granted the privilege to set_appcontext()
• A auto login script is a regular stored procedure

31
RLAC Implementation - Catalog Changes

• There are several new columns added to the system
  catalogues
• SYSCOLUMNS
• accessrule (objid_t)
• SYSTYPES
• accessrule (objid_t)
• SYSLOGINS
• procid (objid_t)

32
RLAC Implementation - Summary

• Row-level Access Control, Application Context
  Facility and Auto Login Script together provide a
  fail-safe, fine grained, row-level access control
  functionality

33
Summary

• Sybase ASE supports a comprehensive set of
  leading-edge capabilities to secure data that is
  stored and retrieved
• Compliance of federal regulations is possible
  today with solutions from Sybase and its partners
• Future enhancements will enable enterprise-wide
  security administration

34
Open Discussion

• What are the essential components of a generic
  security architecture?
• What security policies are being followed and
  what are some of the key security requirements
  that would follow from that?
• What are the critical challenges faced in
  administering security policies in ASE
  environments?
• What additional capabilities, if any, need to be
  supported in ASE, Replication Server, ASIQ ?

35
SDN Presents CodeXchange
A New Benefit for Sybase Developers

• Forum for exchanging samples, tools, scripts,
  etc.
• New features enable community collaboration
• Download samples created by Sybase or external
  users
• Leverage contributions of others to extend Sybase
  tools
• Contribute code or start your own collaborative /
  open source project with input from other product
  experts
• Any SDN member can participate
• Log in using your MySybase account via SDN
• Join the collaboration already underway
• http//www.codexchange.sybase.com or via SDN at
  www.sybase.com/developer

36
SDN Presents CodeXchange
Share ASE Scripts and Tools

• New SDN feature enables community collaboration
• Download tools created by Sybase
• Leverage contributions of others to help
  administer and monitor your servers
• Contribute your own code or start your own
  collaborative project with input from other ASE
  experts
• Any SDN member can participate
• Log in using your MySybase account via SDN
• Join the collaboration already underway
• http//ase.codexchange.sybase.com or via SDN at
  www.sybase.com/developer

37
Security and Privacy Infrastructure in Sybase ASE
SethuDirector of Engineering (Architect)sethu_at_sy
base.com/925-236-5000August 2003
38
Title Arial 28 pt.
Secondary Title Arial 16 pt.

• Subhead Arial Bold 20 pt.
• Body or secondary lines Arial16 pt.
• Body or secondary lines Arial16 pt.

39
Title Arial 28 pt.
Secondary Title Arial 16 pt.