Informix User Forum 2006

Transcript Informix User Forum 2006

IBM Information Management

Informix User Forum 2006 Auditing With IDS

Jonathan Leffler STSM, IDS Security Architect [email protected]

IBM Information Management – Informix Database Engineering

Agenda

 Overview of External Requirements – – – – – Payment Card Industry (PCI) standards Sarbanes-Oxley (SOX) Access Control Encryption – Where and Why Auditing  Features in IDS Meeting External Requirements – Auditing – – – Encrypted communications Column-level Encryption Non-IDS tools Informix User Forum 2006 | Auditing With IDS 2006-12-08 © 2006 IBM Corporation

IBM Information Management – Informix Database Engineering

PCI – Payment Card Industry

 CISP – Cardholder Information Security Program  PCI Data Security Standard – Version 1.1 (September 2006) – – https://www.pcisecuritystandards.org/ • Version 1.0 (January 2005) Not valid after 31 st December 2006 for new validations

IBM Information Management – Informix Database Engineering

PCI – Payment Card Industry

 12 requirements in 6 groups – – – – – Build and Maintain a Secure Network Protect Cardholder Data Maintain a Vulnerability Management Program Implement Strong Access Control Measures Regularly Monitor and Test Networks – Maintain an Information Security Policy

IBM Information Management – Informix Database Engineering

PCI Data Security Standard – Requirements



Build and Maintain a Secure Network

– – 1: Install and maintain a firewall configuration to protect data 2: Do not use vendor-supplied defaults for system passwords and other security parameters 

Protect Cardholder Data

– – 3: Protect stored data 4: Encrypt transmission of cardholder data and sensitive information across public networks 

Maintain a Vulnerability Management Program

IBM Information Management – Informix Database Engineering

PCI Data Security Standard – Requirements



Implement Strong Access Control Measures

– – – 7: Restrict access to data by business need-to-know 8: Assign a unique ID to each person with computer access 9: Restrict physical access to cardholder data 

Regularly Monitor and Test Networks

– 10: Track and monitor all access to network resources and cardholder data – 11: Regularly test security systems and processes.



Maintain an Information Security Policy

IBM Information Management – Informix Database Engineering

PCI Data Security Standard

 Many more details for each of these 12 requirements – 17 page PDF document (with 2 appendices)  For example, requirement 3 has 6 sub-sections – – – – – And 16 sub-sub-sections §3.1 Minimal information storage and retention §3.2 Do not store sensitive information after authentication §3.3 Mask account numbers when displayed §3.4 Render sensitive data unreadable when stored – – §3.5 Protect encryption keys against disclosure and misuse §3.6 Fully document and implement key management procedures

IBM Information Management – Informix Database Engineering

PCI – Payment Card Industry

 Is your DBA a Spy?

–

http://tinyurl.com/y8bahy

 User tricks, Security ‘Treats’ –

http://tinyurl.com/sfdj5 8

IBM Information Management – Informix Database Engineering

Sarbanes-Oxley

 Public Company Accounting Reform and Investor Protection Act of 2002 – – – Also known as Sarbanes-Oxley Act of 2002 Commonly called SOX or SarBox • See (amongst many others) http://www.sarbanes-oxley.com/ NB: I am not a lawyer!

 The most critical sections for IT are: – 302: Control (internal controls) – – 404: Evaluation (governance, measurement, records) 409: Disclosure (reporting, certification)  Plus the corresponding SEC rules and regulations Informix User Forum 2006 | Auditing With IDS 2006-12-08 © 2006 IBM Corporation

IBM Information Management – Informix Database Engineering

Sarbanes-Oxley

 CEO, CFO must certify that they: – – Are responsible for establishing and maintaining internal controls.

Have designed the internal controls so material information is made known to them by others in the organization.

 Internal controls are accounting terms (SEC rules): – A process designed to provide reasonable assurance about the reliability of financial reporting.

• • Maintenance of records that accurately reflect transactions Transactions are properly recorded and authorized to permit preparation of statements in line with the GAAP • Prevention or timely detection of unauthorized activity – That would materially affect the financial statements Informix User Forum 2006 | Auditing With IDS 2006-12-08 © 2006 IBM Corporation

IBM Information Management – Informix Database Engineering

Sarbanes-Oxley

 COSO (Treadway Commission) defines accounting rules – – – – – – http://www.coso.org/ Control Environment Risk Assessment Control Activities Information and Communication Monitoring  COBIT defines software rules related to: – – – – Planning and Organization Acquisition and Implementation Delivery and Support Monitoring and Evaluation

IBM Information Management – Informix Database Engineering

Sarbanes-Oxley

 Routine Re-evaluation of Internal Controls – CEO and CFO must evaluate the effectiveness of controls • Within 90 days prior to any report – • Present conclusions about the effectiveness of internal controls In the report – The company must annually assess the effectiveness too  Disclosure of any material problems found – All significant deficiencies in design or operation of internal controls – Any fraud, whether or not material, that involves management or employees who have a role in running the internal controls

IBM Information Management – Informix Database Engineering

Access Control Requirements

 Access to the DBMS is a major part of compliance – It is far from the only issue  Only authorized users should be able to do anything – And even they should have minimum permissions  Do not grant RESOURCE or DBA to PUBLIC – Don’t even grant CONNECT to PUBLIC usually  Grant SELECT to PUBLIC on non-sensitive tables – Don’t even grant that on sensitive tables

IBM Information Management – Informix Database Engineering

Access Control Requirements

 Exploit roles to control permissions – – – – Create a separate role for each class of user Grant that role the necessary permissions for the job Assign the permitted users the role Write the application to set the correct role  Note that this form of role-based security is imperfect – – It is a form of ‘security by obscurity’ If the user is assigned the role • • The user can use DB-Access to set the role And then modify the tables using the role’s permissions

IBM Information Management – Informix Database Engineering

Encryption of Data

 Some data must not be stored in the database – PCI says the CVV number cannot be stored after authentication  Other data must be stored encrypted – Typically, the credit card number and the social security number  If an old database uses SSN as a key for joins – • • Redesign your database with an arbitrary number for the joins Use a SERIAL column (employee number) Or a SEQUENCE  Data encryption slows things down – • It is a necessary evil Do not use it unless it is necessary Informix User Forum 2006 | Auditing With IDS 2006-12-08 © 2006 IBM Corporation

IBM Information Management – Informix Database Engineering

Auditing Requirements

 You need to be confident that you can track changes – Who changed what structurally • Only a very few staff can make schema changes  Sometimes you need to track who sees data – Identity theft takes a copy of information • It does not alter the information  Database auditing can track DBAs too  Database auditing is not sufficient – You need controls over backups etc

IBM Information Management – Informix Database Engineering

Test Database Requirements

 Developers need test databases  Companies must keep customer information safe  Developers cannot work on copies of production data – When that data contains sensitive information  There are companies with tools to transform data – – Take in valid data and randomize it While retaining referential integrity  The key point is to be sure that everyone is aware: – Developers may not work on live data!

IBM Information Management – Informix Database Engineering

Features in IDS to Address External Requirements

 IDS has features to help address these requirements: – – – Auditing Encrypted Communications Column-level Encryption  Other vendors can help – – Vormetric Protegrity  Future directions too – – Fine-Grained Auditing Label-Based Access Control

IBM Information Management – Informix Database Engineering

Basic Auditing

 IDS 7 and later has ON-Audit and ON-ShowAudit – – – ON-Audit controls what is audited ON-Audit also controls how the audit results are recorded ON-ShowAudit shows what auditable events occurred  Can be controlled by separate roles – • • DBSSO: Database Security Officer Controls who is audited Controls which events are audited – AAO: Audit Analysis Officer • • Controls whether auditing is in use or not Analyzes audit logs Informix User Forum 2006 | Auditing With IDS 2006-12-08 © 2006 IBM Corporation

IBM Information Management – Informix Database Engineering

Overview of Auditing in IDS

 Most operations can be audited.

– All auditable operations have a 4-letter mnemonic.

– CREATE DATABASE = CRDB – SELECT ROW = RDRW – generates voluminous output!

 Audit masks dictate which operations are monitored.

– Different masks for different people.

– Global masks – default, require, exclude.

– Template masks – User masks.

IBM Information Management – Informix Database Engineering

Overview of Auditing in IDS

 Audit logging can use operating system audit facilities.

– Only available on some Unix platforms.

– Requires server running as root.

 Audit logging can use Informix audit facilities.

– Available on all platforms.

– Available when running the server as informix.

– Generally the better choice.

IBM Information Management – Informix Database Engineering

Roles: DBSSO versus AAO

 DBSSO controls what is audited.

– – – Which users are audited.

Which operations are audited for each user.

Uses ON-Audit command.

• Setting auditing masks.

 AAO controls whether auditing is active or not.

– Uses ON-Audit command.

 AAO monitors the results of auditing.

– Uses ON-ShowAudit command.

 DBSA runs the server.

– Should not subvert the auditing set up by DBSSO and AAO.

IBM Information Management – Informix Database Engineering

Using ON-Audit as DBSSO

 Define which events are audited for specific users.

 Various aids: – – – – _default – events audited if no other list specified for a user.

_required – events always audited, even if not listed.

_excluded – events always excluded, even if listed.

_templates – lists of events defined by the DBSSO.

 Basic usages: –

onaudit -a -u gandalf -r _guest -e –CRDB

– – –

onaudit -m -u _default -e +UPAM,DRAM onaudit –d –u gandalf onaudit –o –y

–

onaudit –o –u gandalf 23

IBM Information Management – Informix Database Engineering

Audit Masks

 Set the _require mask to the Informix recommended events: – – –

onaudit -a -u _require –e +OPDB,GRDB,RVDB,GRTB,RVTB onaudit –m –u _require –e +CRRL,STRL,STSA,STOM onaudit –m –u _require –e +GRRL,RVRL,GRFR,RVFR

 Add the main DDL operations: – – – –

onaudit –m –u _require –e +CRDB,DRDB,CRTB,DRTB,ALTB onaudit –m –u _require –e +CRIX,DRIX,ALIX,CRSN,DRSN onaudit –m –u _require –e +CRSP,DRSP,CRTR,DRTR onaudit –m –u _require –e +CRVW,DRVW

 Add the utilities: – – –

onaudit –m –u _require –e +ONAU,ONBR,ONCH,ONIN onaudit –m –u _require –e +ONLG,ONLO,ONMN,ONMO,ONPA onaudit –m –u _require –e +ONPL,ONSP,ONST,ONTP,ONUL 24

IBM Information Management – Informix Database Engineering

Audit Masks

 The high-intensity audit events are candidates for _exclude mask: – ACTB – Access table – INRW – Insert row – UPRW – Update row – DLRW – Delete row – RDRW – Read row

IBM Information Management – Informix Database Engineering

Using ON-Audit as AAO

 Defines whether auditing occurs, and auditing behaviour.

 Auditing mode – – 0 – Auditing is turned off.

1,3,5,7 • • • Increasingly complete auditing of users, including DBSSO and DBSA.

Using server-managed auditing.

Add 1 on Unix to have operating system managed auditing.

 Auditing error action – When an audit message cannot be written • 0 continue • • 1 suspend server until audit message can be written 3 halt server because audit message cannot be written

IBM Information Management – Informix Database Engineering

Using ON-Audit as AAO

 Auditing directory – – Default is /tmp – which is

awful

and should never be used.

Make sure that the directory is: • • • Owned by user informix Belongs to the AAO group No public access (no write access) – 770 permissions.

 Audit file size – How big audit files get before switching to a new one.

 Basic usages: – – –

onaudit –c onaudit –l mode –e errmode –p path –s size onaudit -n

IBM Information Management – Informix Database Engineering

Audit Information Stored in SysMaster

 The sysadtinfo table reflects the configuration in adtcfg.

– One row of data.

– Five columns.

 The sysaudit table contains encoded audit masks.

– Bit-encoded numeric values spread across two sets of four columns.

• • • One set for failed operations.

One set for successful operations.

But there’s no documented way to set these differently.

 Access to these tables is restricted.

IBM Information Management – Informix Database Engineering

Contents of Audit Log Files

 All audit records have a common structure: – – 7 pipe-separated fields Last field contains variable colon-separated fields  – – – – – – Common preamble is: – • ONLN To distinguish IDS (OnLine) records from other systems Timestamp to milliseconds (USEOSTIME=1) Host name Session ID IDS server name User name

IBM Information Management – Informix Database Engineering

Contents of Audit Log Files

        

Last field contains:

– – – Error code Operation mnemonic • Auxilliary information for operation Varies by mnemonic

9305|anubis_17|jleffler|0:OPDB:stores:0:-

database owner table name tabid

rowid

9305|anubis_17|jleffler|0:RDRW:stores:16:1049029:2817

IBM Information Management – Informix Database Engineering

Contents of Audit Log Files – Example Errors

             

-239:CRAM:_exclude -26008:CRPT:stores:select decrypt_char(v, 'XXXXXXX') from t; -8301:CRSQ:stores:0:jleffler:s9:0: -511:ALIX:stores:17:informix:procbody:1 -212:CRIX:stores:1990:c1:jleffler:1:dbspace_16 -19803:GRRL:stores:root :extend:jleffler -218:DRSN:stores:0:-:dbd_ix_public01 -9628:DRTY:stores: - :dbd_ix_distofi8 -350:CRIX:new_stores:108:idxmsgs_frfr:jleffler:1:rootdbs -1:ONSP:-c -d nfs_disk -p /nfs/anubis_17.nfs_disk.c0 -s 10240 -o 0 -705:DRSP:stores:1237:-:^Ly ☼ ~T -661:EXSP:stores:1242

Fixed bug

-1:ONTP:-s -L 0 -388:CRSP:stores:0:username:exitfail 31

IBM Information Management – Informix Database Engineering

Example Trace



sqlcmd –d stores –e ‘update elements set atomic_number = atomic_number + 0’

– – – – – – – –

0:OPDB:stores:0: 0:STSN 0:ACTB:stores:jleffler:elements:101 0:RDRW:stores:101:1049066:257 0:UPRW:stores:101:1049066:257:1049066:257 0:RDRW:stores:101:1049066:258 0:UPRW:stores:101:1049066:258:1049066:258 …

 Even for small tables, output is voluminous – And tedious

IBM Information Management – Informix Database Engineering

Audit Log File Management

 Use a different audit log directory for each server – – Based on server name or number E.g. $INFORMIXDIR/tmp/anubis_17  Backup and compress logs – – Compression ratio 25:1 possible • • Into sub-sub-directory: $INFORMIXDIR/tmp/anubis_17/backup $INFORMIXDIR/tmp/anubis_17/00000-00999 • $INFORMIXDIR/tmp/anubis_17/01000-01999  Leave empty files to avoid reuse – Otherwise, IDS reuses log files when restarting Informix User Forum 2006 | Auditing With IDS 2006-12-08 © 2006 IBM Corporation

IBM Information Management – Informix Database Engineering

Weirdnesses Associated with Auditing

 All servers use same adtcfg file – Hard to have different servers auditing to different directories  Changes to audit configuration written to private adtcfg – – adtcfg.NN for server NN But file is not used at start-up!

 Servers should keep track of last audit log file used

IBM Information Management – Informix Database Engineering

Using ON-ShowAudit

 ON-ShowAudit command selects data from audit logs.

 On Unix, you specify where to pull the audit data from: – – The operating system managed audit log, or The database server managed audit log.

 You can specify which user to select the information for: – Default – all users.

 You can specify which database server to select the information for: – Default – all servers.

 It shows you all the data for all users and all servers – Using the information indicated by the adtcfg file.

IBM Information Management – Informix Database Engineering

Managing Auditing

 Beware of the non-technical requirements!

– Publish security policies documenting that actions are audited.

– Document that activities may be recorded and analyzed.

 Auditing is pointless unless you analyze the logs.

– It reduces the performance of the system • How much depends on what you audit.

– You need two sorts of analysis • Post-incident forensic analysis – Something went wrong; who, what, when, how?

• Routine usage pattern tracking – – Fred’s on a hiking vacation in the Sierras Why was he logged in at 03:00 on Sunday morning?

IBM Information Management – Informix Database Engineering

Forensic Auditing

 More typically, audit logs are used forensically.

– To analyze, after the event, who did it, and when.

 Since it may be a while before stolen data is noticed, – It is important to keep (compressed) audit logs.

– And to monitor all the relevant events.

 Note that you can audit every row that is read.

– But the volume of data will be enormous.

 Although you may know the user ID that did it, – It is hard to prove that the bona fide owner of that user ID did it.

IBM Information Management – Informix Database Engineering

Encrypted Communications for IDS

 Available in IDS 9.40 and later  Encrypts communications between client and server – Using standard encryption techniques to establish session keys  Also used for distributed database access – I-Star  ER (Enterprise Replication) can be encrypted – Often replicating over WAN  With effect from Cheetah – – HDR (Heterogeneous Data Replication) will support encryption Previously assumed HDR only over LAN

IBM Information Management – Informix Database Engineering

Encrypted Communications

 Applications communicate with IDS unencrypted – – – – This is safe enough for local connections Shared memory – usually OK – just about IPC Streams or equivalent – OK Loopback connection – OK (does not hit network)  This may be safe enough on intranet – – But it may allow too many people to snoop Consider all PCs as potential problems – Do not expose your IDS server on the internet!

IBM Information Management – Informix Database Engineering

Column-Level Encryption

 Available in IDS 10  Permits encryption of data in selected fields in tables – But requires schema changes – And modifications to applications • Unless you use INSTEAD OF triggers on views  Uses AES 128-bit encryption – Or Triple-DES  See session M08 Column-Level Encryption in IDS – From IDUG North America 2006

IBM Information Management – Informix Database Engineering

Vormetric CoreGuard – Disk Encryption

 Available 2007Q2 for IDS and DB2 – http://www.vormetric.com/  Works with any version of IDS – – Works with dbspaces in cooked files • Access controlled by PEM Privacy Enforcement Module – • Linked into operating system kernel Mediates all access to disk • Decrypts or encrypts data for authorized uses  Encryption is transparent to IDS – IDS only sees unencrypted data Informix User Forum 2006 | Auditing With IDS 2006-12-08 © 2006 IBM Corporation

IBM Information Management – Informix Database Engineering

Policy Enforcement Modules



Authenticated Users Applications DBMS File System PEM Software Policy Enforcement Modules (PEM)

 File Encryption / Access Control   Host Protection Control System Administrators

1 – N Gigabit Ethernet 42 Volume Manager DAS/ ETC.

SAN/ NAS

Informix User Forum 2006 | Auditing With IDS   

CoreGuard Security Servers

IBM Information Management – Informix Database Engineering

Label-Based Access Control

 Major feature in Cheetah release  Parallel to, and based upon, DB2 Viper implementation  Complex structure – But achieves complex result  CREATE SECURITY LABEL COMPONENTS – Defines things like ‘top secret’, ‘secret’ etc.

 CREATE SECURITY POLICY – Uses one or more security label components  CREATE SECURITY LABEL – Specifies one set of values for a security policy Informix User Forum 2006 | Auditing With IDS 2006-12-08 © 2006 IBM Corporation

IBM Information Management – Informix Database Engineering

Label-Based Access Control

 Users are granted certain labels for each policy – – This controls which rows they can read and write Other rows are simply not shown to the unprivileged user  Users can be granted exemptions to policy – – But this should happen rarely And under extreme supervision  New database role – DBSECADM – – Responsible for LBAC administration Creating security policies and assigning labels

IBM Information Management – Informix Database Engineering

Fine-Grained Auditing

 Existing auditing facilities do not record SQL statements – Cheetah can log all SQL statements • • Originally for performance analysis Extended for auditing too  Fine-grained auditing to provide selective recording – – – Some databases Some users Some statements – probably – Some tables – probably

IBM Information Management – Informix Database Engineering

External Auditing of IDS

 Guardium – – Monitors Informix SQL traffic on the network • Can be used to detect unauthorized activity http://www.guardium.com/  Princeton Softech • http://www.princetonsoftech.com/  sudo + sudosh – – Controls over who has access to user informix • Logs all activity http://www.courtesan.com/sudo/ • http://sudosh.sourceforge.net/ Informix User Forum 2006 | Auditing With IDS 2006-12-08 © 2006 IBM Corporation

IBM Information Management – Informix Database Engineering

Other Possible Future Features

 Server-managed table- or column-level encryption – – IDS consciously in charge of encryption • Leads to key management issues Key management is where encryption usually breaks down  Encrypted back-ups – – Shipping back-ups off-site unencrypted leads to embarrassment • Key management is the crucial issue As ever with encryption

IBM Information Management – Informix Database Engineering

48 http://www.ibm.com/software/data/informix http://www.iiug.org/

IBM Information Management – Informix Database Engineering

49 http://www.ibm.com/software/data/informix http://www.iiug.org/

Informix User Forum 2006

Transcript Informix User Forum 2006

Informix User Forum 2006 Auditing With IDS

Agenda

PCI – Payment Card Industry

PCI – Payment Card Industry

PCI Data Security Standard – Requirements

PCI Data Security Standard – Requirements

PCI Data Security Standard

PCI – Payment Card Industry

Sarbanes-Oxley

Sarbanes-Oxley

Sarbanes-Oxley

Sarbanes-Oxley

Access Control Requirements

Access Control Requirements

Encryption of Data

Auditing Requirements

Test Database Requirements

Features in IDS to Address External Requirements

Basic Auditing

Overview of Auditing in IDS

Overview of Auditing in IDS

Roles: DBSSO versus AAO

Using ON-Audit as DBSSO

Audit Masks

Audit Masks

Using ON-Audit as AAO

Using ON-Audit as AAO

Audit Information Stored in SysMaster

Contents of Audit Log Files

Contents of Audit Log Files

Last field contains:

Contents of Audit Log Files – Example Errors

Example Trace

Audit Log File Management

Weirdnesses Associated with Auditing

Using ON-ShowAudit

Managing Auditing

Forensic Auditing

Encrypted Communications for IDS

Encrypted Communications

Column-Level Encryption

Vormetric CoreGuard – Disk Encryption

Policy Enforcement Modules

Label-Based Access Control

Label-Based Access Control

Fine-Grained Auditing

External Auditing of IDS

Other Possible Future Features

Directory