ASE139

ASE139 - Security and Privacy Infrastructure in Sybase ASE: 

ASE139 - Security and Privacy Infrastructure in Sybase ASE Sethu Director of Engineering (Architect) sethu@sybase.com/925-236-5000 August 2003

Agenda: 

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

Information’s Price: 

Information’s Price Source: 2002 CSI/FBI Computer Crime and Security Survey Average Company Loss Proprietary Information Theft Financial Fraud Viruses $6,571,000 $4,632,000 $283,000

Why Security?: 

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

Business Drivers for Security: 

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 client’s privacy Need to protect company assets

Broad Levels of Security: 

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

Slide7: 

Application Security – Database Systems

Security Market Dynamics: 

Security Market Dynamics 2003 SSL PKI Discrete solutions Integrated Solutions Discrete Devices Low Level Security 2005 API standards Strong Encryption Advanced Hashing/Keying Enterprise management

Aspects of Data Security: 

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

Data in Flight - Authentication: 

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+

Data in Flight - Access Control: 

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

Access Control - Row Level Security: 

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 developer’s hard-coding the where clause Reduce developer’s responsibility of coding with specific user profiles and application profiles Increase flexibility: If user’s privileges change, administrator can change user’s groups, roles, or profiles to affect his/her data access accordingly

Data in Flight - Data Integrity: 

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+

Data at Rest/Flight - Confidentiality: 

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+

Security Management - Auditing: 

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

Access Control - Revisited: 

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

Sybase ASE Row-level Access Control: 

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.

Sybase ASE Row-level Access Control: 

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

RLAC Implementation - The Model: 

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

RLAC Implementation - Example: 

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 @clerk_id = suser_id() Create a default on clerk_id column create default clerk_default as suser_id()

RLAC Implementation - Example: 

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”

RLAC Implementation - Example: 

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

RLAC Implementation - AppServer caveat: 

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

RLAC Implementation - ACF: 

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

RLAC Implementation - ACF: 

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

RLAC Implementation - ACF: 

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.

RLAC Implementation - ACF: 

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

RLAC Implementation - ACF: 

RLAC Implementation - ACF Create an access rule clerk_rule as follows : create access rule clerk_rule as @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’)

RLAC Implementation – Grant/Revoke of ACF: 

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

RLAC Implementation - Login Trigger: 

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

RLAC Implementation - Catalog Changes: 

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)

RLAC Implementation - Summary : 

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

Summary: 

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

Open Discussion: 

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 ?

SDN Presents CodeXchange: 

SDN Presents CodeXchange 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 A New Benefit for Sybase Developers

SDN Presents CodeXchange: 

SDN Presents CodeXchange 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 Share ASE Scripts and Tools

Security and Privacy Infrastructure in Sybase ASE: 

Security and Privacy Infrastructure in Sybase ASE Sethu Director of Engineering (Architect) sethu@sybase.com/925-236-5000 August 2003

Title Arial 28 pt.: 

Title Arial 28 pt. Subhead: Arial Bold 20 pt. Body or secondary lines Arial16 pt. Body or secondary lines Arial16 pt. Secondary Title: Arial 16 pt.

Title Arial 28 pt.: 

Title Arial 28 pt. Secondary Title: Arial 16 pt.