IBM GLOBAL SERVICES

1. IBM GLOBAL SERVICES G08 Is Your DBA Paranoid Enough? IDS 9.4 Recommended Security Practices Jonathan Leffler, STSM, IBM Informix Database Engineering IBMDB2 Information Management Technical Conference Sept. 20-24, 2004 Las Vegas, NV Revised for Kansas City Informix User Group – 27th October 2004 © IBM Corporation 2004

2. Agenda • Planning for IDS • Installing IDS • Configuring IDS • Role Separation • Encrypted Communications • PAM-based Authentication • Running IDS • Monitoring IDS • Upgrading IDS

3. Planning for IDS • Separating client and server • Insulating server from changes • Who can you trust? • Unix setup

4. Planning – Separating Client and Server • A paranoid DBSA keeps client and server separate. • One INFORMIXDIR for clients: • Contains either I-Connect or ClientSDK. • Maybe other tools – I4GL, ISQL, etc. • The sqlhosts file is a symlink to the central one. • Might contain DB-Access or an equivalent program • But probably no other DBA tools • One INFORMIXDIR for server: • Contains IDS and administrative tools: • ISA, SSJE, I-Spy, etc. • Permissions on directories can be restricted.

5. Planning – Insulating Server from Changes • A paranoid DBSA assumes IDS will be upgraded. • Because of new major releases • Because of bug fix releases • Possibly for customer-specific patch releases • A paranoid DBSA ensures upgrades are easy. • By ensuring that all device names are version neutral • By ensuring that log files are suitably located • By standardizing the contents of ONCONFIG • By setting INFORMIXDIR to a symlink

6. Planning – Insulating Server from Changes • A paranoid DBSA usually manages multiple servers. • Use standard naming and layout conventions. • Create and use templates for the servers. • Standard naming conventions. • Keep things that stay constant out of INFORMIXDIR. • Device files. • Log files. • Ensure that file permissions are set appropriately. • Always deny public write access. • Usually deny public read access.

7. Planning – Insulating a Server from Changes • Server names are durable across upgrades. • Version numbers are not durable. • Optionally, embed server number into server name. • On machine ‘anubis’, server number 23 is ‘anubis_23’. • Separate INFORMIXDIR from data storage. • Use one directory for INFORMIXDIR. • Use a different directory for chunk device names. • Use an official name for INFORMIXDIR. • /usr/informix/anubis_23 • A symbolic link to where the software is installed. • ln –s /work/informix/9.40.UC4 /usr/informix/anubis_23

8. Planning – Insulating a Server from Changes • Build the server name into file names. • INFORMIXDIR: /usr/informix/anubis_23 • ONCONFIG: onconfig.anubis_23 • Chunks: /informix/dev/anubis_23.rootdbs.c0 • Often a symbolic link to a device under /dev • Log files: /informix/log/anubis_23.log • Or $INFORMIXDIR/log or $INFORMIXDIR/tmp • UDRs: /informix/lib/anubis_23/localudrs.so • Temporary files: $INFORMIXDIR/tmp • HDR lost+found: $INFORMIXDIR/tmp/hdr • The object of the exercise is to be boring! • And 100% predictable.

9. Planning – Who can you trust? • You must be able to trust the system administrators. • No UNIX-like system is secure if you cannot trust root. • No Windows system is secure if you cannot trust Administrator. • You must be able to trust user informix. • User informix must have group informix as its primary group. • Only user informix should belong to group informix (at all). • No UNIX-like system is secure if you cannot trust informix. • No Windows system is secure if you cannot trust informix. • You must be able to trust group ‘bargroup’.

10. Protecting UDRs • C code UDRs (shared libraries) should be stored in a secure directory, possibly under $INFORMIXDIR, more likely near where the device file links are. • The permissions on the shared object should be the minimum necessary – probably 664 or 775. • The directory where they are placed should be 775, though you can argue that 770 would be sufficient. • You should control who creates UDRs. • In IDS 9.50, you should be able to do this with the new ‘extend’ role.

11. Planning – Unix Setup • A paranoid DBSA doesn’t like having to trust root. • But they don’t have much choice. • A paranoid DBSA ensures that the path leading to INFORMIXDIR is secure. • No public or inappropriate group write access on: • Any directory leading to INFORMIXDIR. • Any directory leading to device files. • Any directory leading to log files.

12. Avoid Using /tmp for Anything • Insecure Temporary File Creation • Major class of security bugs • Intruder determines file name in /tmp • /tmp/bar_dbug.log • Intruder creates a symlink to a file of their choosing: • ln –s /etc/passwd /tmp/bar_dbug.log • Non-paranoid DBSA runs ON-Bar • Writes log data to password file! • Or the kernel, or some other key file. • Or creates a new file where none should exist.

13. Installing IDS • Basic Installation • Post-install Check-up • Removing Unwanted Material

14. Install – Basic Installation • A paranoid DBSA reads the release notes and install instructions before starting. • The details change periodically. • The core pre-requisites remain unaltered. • The Bundle Installer needs prodigious amounts of disk space. • This is mostly a bug – being addressed. • The Bundle Installer is run with root privileges. • It can deal with issues that separate product installs cannot. • Use it.

15. Install – Post-Install Checkup • A paranoid DBSA verifies the installation: • Scrutinize install log files. • And remove them. • Check for world-writable files. • Turn off public write permission. • Check for world-writable directories. • Turn off public write permission. • Establish a checksum for each file. • Record it somewhere secure. • Periodically verify that the files are still unchanged. • Add scripts to boot the server automatically. • But provide a simple way to override the automatic start.

16. Install – Removing Unwanted Material • A paranoid DBSA ensures that: • They know what the various programs do. • They know which programs their system needs. • The programs that aren’t there can’t be abused. • Do you use ON-Bar? • Then why keep ON-Tape around? • Possibly because you use HDR. • HDR needs ON-Tape. • Do you use ON-Tape? • Then why keep ON-Bar around? • If you don’t use either, • You’re not a paranoid DBSA!

17. Install – Removing Unwanted Material • Do you use SNMP to monitor IDS? • If not, remove or disable the SNMP programs. • Do you use ON-Perf to monitor IDS? • If not, remove or disable the ON-Perf programs. • Do you use ER? • If not, remove the ER administration program. • BEWARE! • There are programs which you should not remove. • dbaccess • There are directories and files which you should not remove. • dbssodir/seccfg

18. Configuring IDS • Much of the groundwork is already done. • After you’ve created or copied the ONCONFIG file: • Back-up and truncate any log files. • Bring up the server: • oninit –ivy — new server, one time only • oninit –v — upgrade • For new servers: • Add extra dbspaces • Move physical log and logical log if desired • Perform a Level 0 archive. • It’s what a paranoid DBA does reflexively.

19. Configuring – Role Separation • Understanding the roles • DBSA group – database system administrator. • DBSSO group – database system security officer. • AAO group – audit analysis officer. • User informix – the omnipotent. • Group informix – no-one except informix in this group. • User root – the other deity. • Group bargroup – ON-Bar users.

20. DBSA – Database System Administrator Group • Determined by group of $INFORMIXDIR/etc. • Default group is ‘informix’. • DBSA can run the server-level controls. • Not specially privileged within a database. • If the DBSA group is not ‘informix’: • The utilities treat members of this group like user ‘informix’. • Consider changing permissions on ‘oninit’ to 6755: • Permits DBSA group to start the server. • Group ‘informix’ still has elevated privileges.

21. DBSSO – Database System Security Officer Group • Determined by group of $INFORMIXDIR/dbssodir. • Default group is ‘informix’. • Controls which user group can connect to the server. • $INFORMIXDIR/dbssodir/seccfg • Default entry: IXUSERS=* • Alternative: IXUSERS=dbusers • Controls what is audited. • Using onaudit to set audit masks.

22. AAO – Audit Analysis Officer Group • Determined by group of $INFORMIXDIR/aaodir. • Default group is ‘informix’. • Controls auditing: • Whether auditing is enabled. • Where audit records are logged. • Using onaudit. • Analyzes audit data: • Using onshowaudit.

23. User ‘informix’, Group ‘informix’ • User ‘informix’ • A.k.a ‘God’. • Must be protected as carefully as ‘root’. • Owns most materials under $INFORMIXDIR. • Can access any data in the system. • Group ‘informix’ • Owns most materials under $INFORMIXDIR. • Only user ‘informix’ should belong to this group.

24. User ‘root’, Group ‘bargroup’ • User ‘root’ • A.k.a OSA (Operating System Administrator) • Must be trusted – of course. • Has limited access to the database via ON-Monitor, or programs. • Can run most utilities and administer the database. • But should not do so! • Group ‘bargroup’ • Fixed (non-configurable) name. • Members of this group can run ON-Bar.

25. Should the DBSA Group have OSA Privileges? • There is no single correct answer to this. • Some industries have legal requirements to separate them. • Smaller organizations cannot spare the manpower • During normal operation: • There is no need for root privileges. • When altering a configuration: • Adding or replacing disks, or altering network configuration, • Then you need root privileges (and training). • Is there an OSA on call at 03:00? • Can they get into the office quickly enough? • A DBA seldom needs root privileges.

26. Configuring – Encrypted Communications • Primary configuration via sqlhosts file: • On client and on server. • Secondary configuration via concsm.cfg file: • Also on client and server. • $INFORMIXCONCSMCFG environment variable. • sqlhosts file option: • dbserver olsoctcp hostname sqlexec csm=(ENCCSM) • Client and server configuration files must match: • Some permitted cipher:mode pair and mac level must match. • Some key must be available on both client and server.

27. Encrypted Communications – concsm.cfg file • Format is: • csmname(“library paths”, “encryption options”) • Library paths: • Either single pathname • Or space-separated entries for client and server • client=$INFORMIXDIR/lib/csm/iencs09a.so • server=$INFORMIXDIR/lib/iencs09a.so • Encryption options • cipher • mac • switch • config

28. Encryption Options - cipher • cipher[values] • Comma-separated ‘cipher:mode’ pairs: • ‘cipher[des3:cbc]’ – triple-DES in cipher-block chaining mode • Available ciphers include: • des, des3, desx • Variations on Digital Encryption Standard. • bf-1, bf-2, bf-3 • Variations on Blowfish algorithm. • Available modes include: • ECB electronic code book (do NOT use this!) • OCB • OFB output feedback • CBC cipher block chaining

29. Encryption Options – mac • mac[<values>] • Levels:<levels> • high – always use secure MAC. • medium – use secure MAC on long messages; XOR on short. • low – use XOR MAC on all messages. • off – do not use any MAC. • Files:<filenames> • Key files – using GenMacKey • Or ‘builtin’ – use a built-in key.

30. Encryption Options – switch • switch • Timeouts on cipher and key renegotiation in minutes • switch[cipher:20, key:4]

31. Encryption Options – config • In 9.40.xC2 and above: • config=/informix/etc/csm-des3.cfg • Absolute name of CSM parameter file. • CSM Parameter file: • ENCCSM_CIPHERS: <ciphers> • ENCCSM_MAC: <MAC level> • ENCCSM_MACFILES: <MAC file location(s)> • ENCCSM_SWITCH: <key change intervals> • ENCCSM_DEBUG: <filename:flags>

32. Encrypted Communications – concsm.cfg file • Hence: • ENCCSM(“server=$INFORMIXDIR/lib/iencs09a.so client=$INFORMIXDIR/lib/client/csm/iencs09a.so”, “cipher[des3:cbc,bf-2:ofb], mac[levels:<high,medium>, files:<builtin>], switch[cipher:20, key:4]”) • You have to manually expand $INFORMIXDIR above. • Ensure the concsm.cfg file is secure. • And any CSM parameter files. • Remember – you’re a paranoid DBSA!

33. Configuring Password Controls – sqlhosts options • Extra sqlhosts file options • r – client-side security control (ignored by server). • s – server-side security control (ignored by client).

34. Configuring – PAM Authentication • PAM – Pluggable Authentication Modules. • Allows OSA to configure authentication methods. • Available on Linux, AIX, Solaris, HP-UX and others. • Configure PAM at the operating system. • PAM modules located in /usr/lib/security • Configuration file is normally /etc/pam.conf • login auth required /usr/lib/security/pam_unix.so.1 • The login service uses pam_unix.so.1, etc. • PAM supports challenge-response protocols: • In response to initial authentication request, • PAM issues a challenge, • And waits for response from application.

35. Configuring – PAM Authentication • Another set of sqlhosts options: • s=4,pam_serv=<pam-servicename>,pamauth=(challenge) • s=4 • Indicates that PAM is to be used. • pam_serv=<pam-servicename> • Designates an entry in the PAM configuration file • pamauth=(challenge) • This PAM service can issue challenge-response. • Configure ESQL/C with callback function before connecting. • No callback, guaranteed failure of challenge-response. • Alternative is ‘password’.

36. Dedicated Ports for ER and HDR • Configured by an option in sqlhosts • s=6 • This service is only for ER and HDR. • Also can use encryption with ER (but not HDR). • Parameter in ONCONFIG file. • ENCRYPT_CDR [012] • 0 – no encryption. • 1 – try to encrypt. • 2 – must encrypt.

37. Configuring – Server Security • A paranoid DBSA controls database creation: • Set DBCREATE_PERMISSION in ONCONFIG file. • List users who can create databases. • Default is everybody. • One possible value is ‘informix’. • Only user ‘informix’ can create databases.

38. Configuring – Internal Database Security • A paranoid DBA controls who can connect: • Probably does not grant PUBLIC connect privilege. • Definitely does not grant PUBLIC resource privilege. • Let alone DBA privilege! • Sets and enforces rules on table owners. • Probably requires NODEFDAC=yes in environment. • So there is no default PUBLIC access to tables, etc. • Often grants PUBLIC a default of SELECT privilege (only). • INSERT, DELETE, UPDATE privileges granted sparingly. • Uses ROLES to simplify permission management. • Beware: applications must know about and set roles. • Beware: only one role can be active at a time.

39. Running IDS • A paranoid DBSA controls the IDS environment. • Do you know what the PATH setting is for oninit? • What is its time zone? • Was it last restarted by someone in Singapore? • Which other environment variables did it get? • Why did it need it? • And ensures that it is consistent. • Every time the server is started.

40. Starting IDS IXD=/usr/informix/9.40.UC4 IXS=anubis_29 cd $IXD asroot –u informix –C –g informix -- \ env –i INFORMIXDIR=$IXD \ INFORMIXSERVER=$IXS \ ONCONFIG=onconfig.$IXS \ TZ=UTC0 \ PATH=$IXD/bin:/usr/bin \ HOME=$IXD \ SHELL=/usr/bin/ksh \ -- $IXD/bin/oninit “$@”

41. System Start and Stop IXD=/usr/informix/anubis_23 IXS=anubis_23 IXP=“IBM Informix IDS 9.40.UC4 ($IXS)” export ONCONFIG=onconfig.$IXS export INFORMIXSERVER=$IXS export INFORMIXDIR=$IXD mode=“${1:-start}” shift case “$mode” in start) echo “Starting $IXP”; $IXD/bin/boot.$IXS;; stop) echo “Stopping $IXP”; $IXD/bin/onmode –ky;; restart) $0 stop; $0 start;; *) echo “Usage: $0 [start|stop|…]” 1>&2 exit 1;; esac

42. Alarm Program • The version in IDS 9.30 only handles log full. • The version in IDS 9.40 handles everything. • Send emails or pages to all and sundry. • Requires configuration for your environment. • How about a simpler, logging-only version? • Invoked with up to 5 arguments. • Severity, class, message, text, file. • But arguments 3 and 4 are often the same. • Argument 5 can be missing. • If the log file does not exist, create it. • And record the server’s environment. • Timestamp the entry, and record the INFORMIXSERVER.

43. Alarm Program (1 of 2) EV_SEV=“sev=$1”; EV_CLS=“class=$2”; EV_MSG=“msg=‘$3’”; EV_TXT=“$4”; EV_FIL=“$5”; IXS=${INFORMIXSERVER:=unknown_server} log=${INFORMIXDIR:-/usr/informix}/log/$IXS date=`TZ=UTC0 date +”%Y-%m-%d %H:%M:%S +00:00”` if [ ! –f $log ] then echo “$date: Starting log for $IXS” > $log chmod 664 $log fi

44. Alarm Program (2 of 2) if [ “$EV_TXT” != “$EV_MSG” ] then text=“:text=‘$EV_TXT’” else text=“” fi if [ -n “$EV_FIL” ] then file=“:file=$EV_FIL” else file=“” fi echo “$date:$IXS:$EV_SEV:$EV_CLS:$EV_MSG$text$file” \ >>$log exit 0

45. Monitoring IDS • A paranoid DBSA establishes normal behaviour. • And keeps a record of what is normal. • Which permits the tracking of trends and anomalies. • A paranoid DBSA sets various threshold levels that fire warning messages to the appropriate people. • For example, when dbspaces reach a certain percentage of fullness. • Monitoring for these levels is done at appropriate intervals. • Which vary depending on your requirements.

46. Upgrading IDS • A paranoid DBSA never installs an upgrade over anything! • The upgrade goes into a new directory. • Copy the configuration from the current directory. • Validate everything. • When ready to cut to new version: • Turn off old version. • Switch symbolic links. • Turn on new version. • It is also easy to revert to the old version.

47. Decommissioning IDS • A paranoid DBSA knows that a machine may be reused by someone else after it is retired. • So, before releasing it, they clean it up. • They remove IDS and ClientSDK or I-Connect. • Possibly using a secure remove program • They overwrite all the dbspaces several times with junk data. • They remove log files. • They remove mail files. • They remove user informix and group informix. • And any other specialized Informix-related users or groups. • They clean out the startup and crontab code. • They reformat all the drives.

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

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