IBM Informix Security functional overview

Luxembourg, October 2012

Eric Vercelletto, Begooden-IT Consulting

www.

Informix security: OS perspective (overview)

Informix security: database perspective (overview)

Roles: configuration et separation (detail)

Administration/Roles (detail)

Auditing (detail)

Performance considerations (overview)

2

Agenda

www.

Informix can authenticate users through ◦ os authentication: user must have a login on the system

◦ Trusted user: use OS trust capability if dbserver and app server are different systems

◦ PAM (pluggable authentication module: Informix supports the PAM framework, that can be used to develop company standards for authentication

◦ Lightweight Directory Access Protocol (LDAP): Informix also supports LDAP as an authentication method, only on Windows clients

Informix and users permissions ◦ Informix uses OS permissions to protect Informix utilities

◦ By default, user informix is the super user BUT

◦ DBSA, DBSSO, AAO and informix roles can be separated using OS built-in capabilities

3

OS security/1

www.

Informix uses standard network security capabilities

◦ ssh can be used to run Informix utilities in a secure way

◦ The Informix database server instance can(must) be placed behind a firewall to protect it from malicious external attacks

4

OS security/2

www.

Informix can secure data thru SQL commands in 2 ways

DAC: discretionary access control use of GRANT and REVOKE statements applied to users, roles, having effect on databases, tables, views, fragments, routines, UDT… The permission granted can be connect, resource, dba, create, alter, select,insert, update, delete, usage, execute etc…, according to the type of object impacted

5

Sql security/1

www.

Informix can also secure data thru SQL commands using LBAC: label-based access control

◦ Security can b defined at a row level or at a column level

Tables are protected by security POLICIES Rows and columns are protected by LABELS Policies and Labels are granted to users by the database

security administrator Labels can look like

◦ CREATE SECURITY LABEL COMPONENT classification ARRAY ['Top-Secret','Secret', 'Confidential', Unclassified'];

◦ CREATE SECURITY LABEL COMPONENT org_position ARRAY ['CEO', 'VP','Director', 'Manager','Staff'];

◦ CREATE SECURITY LABEL COMPONENT region TREE ( 'HeadQuarters' ROOT,’East' UNDER 'HeadQuarters','West' UNDER HeadQuarters','North' UNDER 'HeadQuarters','South' UNDER 'HeadQuarters','Georgia' UNDER 'East','Florida' UNDER 'East','Atlanta' UNDER 'Georgia','Texas' UNDER 'South','Dallas' UNDER Texas','Houston' UNDER 'Texas');

◦ Customer labels can be created

Policies can look like ◦ CREATE SECURITY POLICY sales_plcy COMPONENTS org_position, region;

Policies and labels are granted to users like this: ◦ GRANT SECURITY LABEL sales_plcy.sales_rep TO "usr3" FOR WRITE ACCESS;

◦ GRANT SECURITY LABEL sales_plcy.sales_rep_mgr TO "usr3" FOR READ

6

Sql security/2

www.

Informix IDS considers 7 distinct roles The DBSA (database system administrator)

is in charge of configuring, tuning and maintaining the IDS instances. Tasks include startup and shutdown instances, disk space management, performance tuning etc…

The DBSSO (database system security officer) is in charge of defining audit masks on a large possible range of audit targets

The AAO (audit analysis officer) configures, runs and analyzes the audit trail

The DBA (database administrator) manages databases (not necessarily instances)

the OSA (operating system administrator) handles user accounts, groups, sets permissions, handles system resource

The user runs database applications The privileged users « root » and « informix » are the default

privileged users defined by IDS

7

Roles separation

www.

The company can decide to use role separation or not

If not applied, the informix user has all the roles

At IDS install time, you must decide to use it or not ◦ You will be asked to enter the unix group names of DBSSO, AAO and

« regular » users.

To apply separation after installation, you must change group ownsership of $INFORMIXDIR/dbssodir and $INFORMIXDIR/aaodir ◦ You will rebounce the IDS instance to enable role separation

◦ You can switch back to no role separation by changing group ownership of those directories back to informix, and rebounce again

Security rules can then be set in a more detailed manner by editing the $INFORMIXDIR/dbssodir/seccfg file

8

Roles separation: When and how?

www.

9

IDS Audit

www.

The general configuration of audit is done in the $INFORMIXDIR/aaodir/adtcfg file

ADTMODE 0 # Auditing mode

ADTPATH /usr/informix/aaodir # Directory where audit trails will be written by IDS

ADTSIZE 50000 # Maximum size of any single audit trail file

ADTERR 0 # Error handling modes.

Possible modes are ◦ 0 audit off

◦ 1 audit on

◦ 3 audit dbsso operations

◦ 5 audit dbsso and dbsa operations

◦ 7 audit dbsso, dbsa operations and normal user operations

Rebounce the instance to validate config, or use onaudit command to set the configuration dynamically

10

Configure IDS audit

www.

audit dbsso and dbsa operations

After general configuration is set, audit policy is configured by specifying audit events

Audit events are instance and database operations identified by an audit mnemonic like CRTB,CRIX,DLRW,RDRW ….

You can request specific status for each even: ‘S’ for sucessful, ‘F’ for failed

If ‘S’ or ‘F’ is not specified, all the events will be audited Ex: SCRTB will audit only successful table creations FDLRW will audit only failed rows deletes CRVW will audit all the view creations

11

audit events

www.

CRLB Security Label, Create

CRLC Security Label Component, Create

CROC Operator Class, Create

CROP Optical Cluster, Create

CRPL Security Policy, Create

CRPT Encryption/Decryption

CRRL Create Role

CRRT Named Row Type, Create

CRSN Synonym, Create

CRSP SPL Routine, Create

CRSQ Sequence, Create

CRTB Table, Create

CRTR Trigger, Create

CRVW View, Create

DLRW Row, Delete

DNCK Chunk, Bring Off-line

DNDM Disk Mirroring, Disable

DRAM Audit Mask, Delete

DRBS Storage Space, Drop

DRCK Chunk, Drop

DRDB Database, Drop

12

audit events

www.

ACTB Access Table ADCK Chunk, Add ADLG Transaction Log, Add ALFR Alter Fragment ALIX Index, Alter ALLC Security Label Component, Alter ALME Access Method, Alter ALOC Operator Class, Alter ALOP Optical Cluster, Alter ALSQ Sequence, Alter ALTB Table, Alter BGTX Transaction, Begin CLDB Database, Close CMTX Transaction, Commit CRAG Aggregate, Create CRAM Audit Mask, Create CRBS Storage Space, Create CRBT Opaque Type, Create CRCT Cast, Create CRDB Database, Create CRDM Domain, Create CRDS Dbspace, Create CRDT Distinct Type, Create CRIX Index, Create

CRLB Security Label, Create

CRLC Security Label Component, Create

CROC Operator Class, Create

CROP Optical Cluster, Create

CRPL Security Policy, Create

CRPT Encryption/Decryption

CRRL Create Role

CRRT Named Row Type, Create

CRSN Synonym, Create

CRSP SPL Routine, Create

CRSQ Sequence, Create

CRTB Table, Create

CRTR Trigger, Create

CRVW View, Create

DLRW Row, Delete

DNCK Chunk, Bring Off-line

DNDM Disk Mirroring, Disable

DRAM Audit Mask, Delete

DRBS Storage Space, Drop

DRCK Chunk, Drop

DRDB Database, Drop

13

audit events

www.

ACTB Access Table ADCK Chunk, Add ADLG Trnsaction Log, Add ALFR Alter Fragment ALIX Index, Alter ALLC Security Label Component, Alter ALME Access Method, Alter ALOC Operator Class, Alter ALOP Optical Cluster, Alter ALSQ Sequence, Alter ALTB Table, Alter BGTX Transaction, Begin CLDB Database, Close CMTX Transaction, Commit CRAG Aggregate, Create CRAM Audit Mask, Create CRBS Storage Space, Create CRBT Opaque Type, Create CRCT Cast, Create CRDB Database, Create CRDM Domain, Create CRDS Dbspace, Create CRDT Distinct Type, Create CRIX Index, Create

RNIX Rename index

RNLB Security Label, Rename

RNLC Security Label Component, Rename

RNPL Security Policy, Rename

RNTC Table/ Column, Rename

RSOP Optical Cluster, Reserve

RVDB Revoke Database Access

RVDR Revoke Default Role

RVFR Revoke Fragment Access

RVLB Revoke Security Label

RVRL Revoke Role

RVSA Revoke DBSECADM

RVSS Revoke SETSESSIONAUTH

RVTB Revoke Table Access

RVXM Revoke Exemption

SCSP SPL Routine, System Command

STCO Collation®, Set

STCN Constraint, Set

STDF Set Debug File

STDP Set Database Password

STDS Set Dataskip

14

audit events

www.

GRTB Grant Table Access GRXM Grant Exemption INRW Row, Insert LGDB Database Log Mode, Change LKTB Table, Lock LSAM Audit Masks, List LSDB Databases, List MDLG Modify Transaction Logging ONAU onaudit ONBR onbar ONCH oncheck ONIN oninit ONLG onlog ONLO onload ONMN onmonitor ONMO onmode ONPA onparams ONPL onpload ONSP onspaces

The audit masks contain a list of events mnemonics to be audited

Events can be easily added or removed without affecting the ongoing configuration

Events can be included or excluded from auditing

There are 5 types of masks ◦ Template masks are self explanatory. Their names must begin with

a ‘_’ character

◦ User masks will define an events list for a specific user. Their name are made of the audited user ID. They are generally derivated from template masks.

◦ The _ default mask contains the default list of events to be audited, generally for all the users

◦ The _require mask contains the list of events that must be audited.

◦ The _exclude mask contains the list that must not be audited

◦ The order rule is: user masks, _default mask, _require mask and finally _exclude mask.

The masks are created using the onaudit command

15

audit masks

www.

The onaudit command is multipurpose: ◦ To set up and configure auditing

Ex: onaudit -l 3 onaudit -s 10000000

◦ To manipulate/change audit masks Ex: onaudit -a -u _user1 -e +CRTB,INRW onaudit -a -u _user1 -e –CRTB onaudit –f audit file

It is used only by the dbsso and the aao if roles are separated, else it can also be user by the informix user

To stop auditing onaudit -l 0

16

The onaudit command

www.

The audit log files are generated in the directory specified by the ADTPATH config parameter in the $INFORMIXDIR/aaodir/adtcfg file

The log file names are built this way: <value of onconfig DBSERVERNAME parameter>.sequence number. Ex bcv_boc9 .1

The log file have a size limited by the adtcfg ADTSIZE parameter. Once this size is reached, a new file is created, with an incremented sequence number.

The audit trail can grow consequently according to what events are audited. It is recommanded to put a regular archiving procedure in place.

Compression can also be applied

17

The audit log file

www.

The audit log file looks like this

18

The audit log file format

www.

First columns are self explanatory

The event specific colum is made of, and sepated by ‘:’ ◦ Error code

◦ Event mnemonic

◦ Database name

◦ Event specific fields, can be user name,table name,rowid etc… ie all relevant information used for auditing

This file is an ascii separated file, readable as is by any tool that can read this type of file

Results can also be loaded into a database

Formatted / Structured output is provided by the onshowaudit command

19

The audit log file output

www.

The onshowaudit command reads and formats the audit trail files in a structured readable way, read-only

A number of options allow the aao to filter the records by different criteria

Onshowaudit can also be used to generate a file to be loaded to database for further SQL analysis

Some scripts are provided to do so

20

The onshowaudit command

www.

Activating the audit will never enhance the Informix performance

It consists in Informix server threads that write system files, not directly IFMX buffers and tables

Important questions are: ◦ What events are audited

◦ How many events are audited

◦ How is Informix performance before auditing

◦ How many transactions are effectively audited

To be considered: ◦ Some events will generate huge amount of data (row read etc..)

◦ Define an archiving procedure, that may also filter out unrelevant data

21

Performance considerations

www.

We recommand the reading of these documentations

The IBM Informix Security guide, chapters 7,8,9,10,11,12 & 13, accessible on the Web http://publib.boulder.ibm.com/infocenter/idshelp/v117/index.jsp?topic=%2Fcom.ibm.sec.doc%2Fids_sec_019.htm

The Security and Compliance Solutions for IBM Informix Dynamic Server Redbook http://www.redbooks.ibm.com/abstracts/sg247556.html

22

Appendix

www.