Informix 11.

7 Bootcamp
Informix Security
Information Management Technology Ecosystems

© 2010 IBM Corporation

 Agenda
• Database User Authentication
• Database User Authorization and Privileges
• Administrative Users and Roles
• Monitoring Database Activity
• Data Encryption
• Single Sign On (SSO)
• Trusted Context
• Pluggable Authentication Modules
• Mapped Users – Connections without host OS accounts
• Secure Socket Layer (SSL)
• Appendix

2 © 2010 IBM Corporation

 Agenda
• Database User Authentication
• Database User Authorization and Privileges
• Administrative Users and Roles
• Monitoring Database Activity
• Data Encryption
• Single Sign On (SSO)
• Trusted Context
• Pluggable Authentication Modules
• Mapped Users – Connections without host OS accounts
• Secure Socket Layer (SSL)
• Appendix

3 © 2010 IBM Corporation

Database Access
• OS Password Authentication
• If an Informix client application specifies a user name and password
at connection time, by default Informix does OS-level authentication
based on the password and shadow files
• Informix also supports trusted hosts defined by the hosts.equiv and
rhosts files
• Password Encryption
• Protects a password when it must be sent between the client and the
database server for authentication
• Communication support modules (CSMs) can be used to enable
password encryption
• Non OS Password Authentication
• PAM on Unix systems (more about that later)
• LDAP on Windows systems
© 2010 IBM Corporation
 Agenda
• Database User Authentication
• Database User Authorization and Privileges
• Administrative Users and Roles
• Monitoring Database Activity
• Data Encryption
• Single Sign On (SSO)
• Trusted Context
• Pluggable Authentication Modules
• Mapped Users – Connections without host OS accounts
• Secure Socket Layer (SSL)
• Appendix

5 © 2010 IBM Corporation

 Levels of Data Security

Database

Table
View Fragment in dbs1
LBAC
Column
Fragment in dbs2

Routine Fragment in dbs3

© 2010 IBM Corporation

Database-level Privileges

• The three levels of database access are:

• Connect
• If you have this privilege, you can query and modify data, and
modify the database schema if you own the database object
that you want to modify
• Resource
• Lets you extend the structure of the database such as create
tables, indexes, user-defined routines, etc.
• DBA
• Has all the capabilities of the Resource privilege and can
perform additional operations such as grant any database-
level privilege, to another user, create any database object,
grant a role to a user or to another role, and more.

© 2010 IBM Corporation

Table and Column Privileges

ALTER Add, delete, or modify columns

DELETE Remove rows from a table
INDEX Create indexes for a table
SELECT Retrieve information from the columns in a
table
UPDATE Modify information in the columns of a table
INSERT Insert rows into a table
REFERENCES Reference columns in referential constraints
ALL Perform any or all of the preceding
operations

© 2010 IBM Corporation

Granting and Revoking Privileges - Examples

• Database level privileges

• GRANT RESOURCE TO joe;
• GRANT CONNECT TO PUBLIC;
• GRANT DBA TO renee;
• REVOKE CONNECT FROM ted;

• Table/column level privileges

• GRANT UPDATE ON orders TO john WITH GRANT OPTION;
• GRANT ALL ON sales TO PUBLIC;
• GRANT INSERT, DELETE ON inventory TO mike AS kate;
• GRANT SELECT (company, addr) ON customer TO PUBLIC;
• REVOKE DELETE, UPDATE ON customer FROM ted;

© 2010 IBM Corporation

Routine and Datablade privileges

• Routine privileges
• GRANT EXECUTE ON square ( x INT ) TO laura;
• REVOKE EXECUTE ON cancel_orders FROM dan;

• DataBlade privileges
• GRANT EXTEND TO chris;
• REVOKE EXTEND FROM chris;

© 2010 IBM Corporation

Roles
• A mechanism to group privileges together
• Example
CREATE ROLE mkting;
GRANT INSERT, UPDATE, DELETE ON orders TO mkting;
GRANT mkting TO jon, lauren, nicole;
• A user can either inherit a default role, or specify a role to use in
their session (granted by the DBA)
GRANT ROLE slsadmin TO mark AS DEFAULT;
GRANT DEFAULT ROLE slsadmin TO mark;
• A user can set their own role
SET ROLE slsadmin;
SET ROLE DEFAULT;
• Default roles can be revoked with the REVOKE statement
REVOKE DEFAULT ROLE FROM jon;

© 2010 IBM Corporation

GRANT and REVOKE FRAGMENT

• Used to control access to fragments in dbspaces

• Example:

GRANT FRAGMENT INSERT,UPDATE,DELETE

ON customers(dbspace1) TO joy;

REVOKE FRAGMENT ALL ON customers FROM joy;

© 2010 IBM Corporation

 Label Based Access Control – High level Overview

Compare labels
and only allow access if they match

User

Data
Label Label

© 2010 IBM Corporation

 Why would you use LBAC?

• Access can be controlled declaratively

• By granting or revoking labels to rows/columns/users
• By granting or revoking exemptions
• Provides extra protection for sensitive data
• Credit card numbers
• Social security numbers
• Provides Mandatory Access Control (MAC)
• Orange Book (B1) style label-based security
• Similar to MLS – multi-level security
• Intended for certification versus Common Criteria

14 © 2010 IBM Corporation

 Agenda
• Database User Authentication
• Database User Authorization and Privileges
• Administrative Users and Roles
• Monitoring Database Activity
• Data Encryption
• Single Sign On (SSO)
• Trusted Context
• Pluggable Authentication Modules
• Mapped Users – Connections without host OS accounts
• Secure Socket Layer (SSL)
• Appendix

15 © 2010 IBM Corporation

Administrative Users and Roles

• Informix User

• Database Security Administrator (DBSECADM)

• With role separation

• Database Server Administrator (DBSA)

• Audit audministrator roles

• Database System Security Officer (DBSSO)
• Audit Analysis Officer (AAO)

© 2010 IBM Corporation

Informix User

• User account with main authority over an Informix instance

• Owns the major files and directories of an instance

• Used for all major administrative task

• Unless you are using role separation

• Use onsecurity tool to validate the permissions for

important files and directories

© 2010 IBM Corporation

Database Security Administrator (DBSECADM)

• New role with Informix 11

• Only user INFORMIX can assign this role

• Cannot be granted to self

• Responsible for managing

• Label Based Access Control (LBAC)
• The SETSESSIONAUTH privilege
• Trusted contexts

© 2010 IBM Corporation

Role Separation

• Allows you to have one account for each person who performs a
role

• Based on the principle of separation of duties

• Enabled during installation

• Can be changed later on Unix systems only

• Additional roles when using role separation

• Database Server Administrator (DBSA)

• Audit audministrator roles:

• Database System Security Officer (DBSSO)
• Audit Analysis Officer (AAO)

© 2010 IBM Corporation

Role Separation - DBSA

• Database Server Administrator (DBSA)

• Configures, maintains and tunes the Database Server

• User informix and all users who belongs to the group

informix

• Have as few DBSAs as possible

• Same for other privileged roles

© 2010 IBM Corporation

Role Separation – Audit Administrator Roles

• Database System Security Officer (DBSSO)

• Maintains the audit masks (onaudit utility)
• Defines what to audit
• Every user who belongs to the group that owns
$INFORMIXDIR/dbssodir

• Audit Analysis Officer (AAO)

• Reads and analyzes the audit trail (onaudit, onshowaudit)
• Every user who belongs to the group that owns
$INFORMIXDIR/aaodir

© 2010 IBM Corporation

 Agenda
• Database User Authentication
• Database User Authorization and Privileges
• Administrative Users and Roles
• Monitoring Database Activity
• Data Encryption
• Single Sign On (SSO)
• Trusted Context
• Pluggable Authentication Modules
• Mapped Users – Connections without host OS accounts
• Secure Socket Layer (SSL)
• Appendix

22 © 2010 IBM Corporation

Monitoring Database Activity - Auditing

• The database system security officer (DBSSO) can

configure the system to audit certain user activities and
periodically analyze the audit trail
• The audit event implemented using audit masks
• The onaudit command is used to create, modify, and
maintain the audit masks and configuration
• Auditing can also be used for diagnostic purposes
• Example:
onaudit -l 1
onaudit -p /tmp/audit
onaudit -a -u usr1 -e +CRTB,DRTB

© 2010 IBM Corporation

Selective Row-Level Auditing (SRLA) – 11.70

• Informix can be configured to only audit selected tables

• Improve auditing performance

• Make it easier to find relevant audit entries

• Enable on table level:

ALTER TABLE ... ADD AUDIT;
CREATE TABLE ... WITH AUDIT;

• And turn on using onaudit

onaudit –R 1

© 2010 IBM Corporation

onshowaudit Utility

• Used to display and Filter Audit Trails

• Can filter audit trail based on

• User
• Database

• Can also unload to delimited file which can be loaded into table and
queried with SQL

• Done by AAO if role separation is enabled

• Example to filter for user jane and server cheetah1

onshowaudit –I –f /my/auditfile.7 –u jane
–s cheetah1

© 2010 IBM Corporation

 Agenda
• Database User Authentication
• Database User Authorization and Privileges
• Administrative Users and Roles
• Monitoring Database Activity
• Data Encryption
• Single Sign On (SSO)
• Trusted Context
• Pluggable Authentication Modules
• Mapped Users – Connections without host OS accounts
• Secure Socket Layer (SSL)
• Appendix

26 © 2010 IBM Corporation

Data Encryption

• Stores sensitive data in an encrypted format

• SQL built-in functions can be used to implement data encryption
• Column-level encryption to encrypt all values in a given column
with the same password
• Cell-level encryption to encrypt data within the column with
different passwords
• Only those users who have the correct password will be able to
read, copy, or modify the data

• Encrypt and Decrypt Functions:

• ENCRYPT_AES and ENCRYPT_TDES
• Return an encrypted_data value that encrypts the data argument.
• DECRYPT_CHAR and DECRYPT_BINARY
• Return a plain-text data value from the encrypted_data argument.

© 2010 IBM Corporation

Cell Level Encryption - Example

• Different passwords for different rows

• Need a WHERE clause when SELECTing data
• Otherwise encounter the following error when the password does
not match
26008: The internal decryption function failed

SET ENCRYPTION PASSWORD ‘secretpwd1‘ WITH HINT ‘pwd 1‘;

INSERT INTO mytab VALUES(123, ENCRYPT_AES(‘secret value1‘));

SET ENCRYPTION PASSWORD ‘secretpwd2‘ WITH HINT ‘pwd 2‘;

INSERT INTO mytab VALUES(456, ENCRYPT_AES(‘secret value2‘));

SELECT column1, decrypt_char(column2) from mytab WHERE

column1=456;
SELECT getHint(column2) from mytab;

© 2010 IBM Corporation

Column Level Encryption - Example

• Same password for all rows

• Recommended to not use “WITH HINT“ because that requires
additional storage for each row
• Rather store the hint in another table

SET ENCRYPTION PASSWORD ‘secretpwd1‘;

INSERT INTO mytab VALUES(123, ENCRYPT_AES(‘secret value1‘));
INSERT INTO mytab VALUES(456, ENCRYPT_AES(‘secret value2‘));
INSERT INTO mytab VALUES(789, ENCRYPT_AES(‘secret value3‘));

SELECT column1, decrypt_char(column2) from mytab;

© 2010 IBM Corporation

 Agenda
• Database User Authentication
• Database User Authorization and Privileges
• Administrative Users and Roles
• Monitoring Database Activity
• Data Encryption
• Single Sign On (SSO)
• Trusted Context
• Pluggable Authentication Modules
• Mapped Users – Connections without host OS accounts
• Secure Socket Layer (SSL)
• Appendix

30 © 2010 IBM Corporation

 What is Single Sign On (SSO)?

• Users enter their password once to gain access to

resources

• Password entered during login

• Password need not be entered again to authenticate to

the Informix

• Authentication is invisible to the user

31 © 2010 IBM Corporation

Single Sign On Support (SSO) in Informix

• Implemented as a Communication Support Module (CSM)

• General Security Services CSM (GSSCSM)

• Single Sign On (SSO) supported via Kerberos

• developerWorks Article that describes the setup:

www.ibm.com/developerworks/data/library/techarticle/d
m-0809govindarajan/

© 2010 IBM Corporation

Kerberos

• Relies on Trusted Third Party

• Called the Key Distribution Center (KDC)

• Each host trusts the KDC

• The KDC shares a secret key with each host

• Uses Tickets to authorize users/hosts

© 2010 IBM Corporation

Kerberos Procedure to Access a Server

1. A client requests a Ticket Granting Ticket (TGT) from the KDC

2. The client receives a Ticket Granting Ticket (TGT) from the
KDC
• The TGT allows the client to request further tickets
3. The client wants to use a service on a server
4. The client requests another ticket for that service
5. The KDC send back a ticket for that service if the user is
authorized to use that service
6. The client sends the `service ticket` to the server
7. The client is authorized because it has a `service ticket`
8. Access is granted to the server

© 2010 IBM Corporation

Kerberos Example

1. R
equ
est
2. TG
Gr T
an
3. R tT
e que GT
4. G st ti
ran cke
t tic t for
ket serv
if au ice
tho
rize KDC
d to
5. Authorize with service ticket use
se rvic
e

Service

© 2010 IBM Corporation

Informix Configuration

• Kerberos and client configuration is also necessary

• Requires the Informix Communication Support Module
• Entry in $INFORMIXDIR/etc/concsm.cfg
• Entry in SQLHOSTS file

Example concsm.cfg
# csmname("client=clientlib, server=serverlib, "global_opts", "conn_opts")
GSSCSM("/work/informixdir/lib/csm/igsss11a.so", "", "c=1,i=1")

Example SQLHOSTS entry

cheetah1 onsoctcp ids1150srvr 9089 s=7,csm=(GSSCSM)

© 2010 IBM Corporation

 Agenda
• Database User Authentication
• Database User Authorization and Privileges
• Administrative Users and Roles
• Monitoring Database Activity
• Data Encryption
• Single Sign On (SSO)
• Trusted Context
• Pluggable Authentication Modules
• Mapped Users – Connections without host OS accounts
• Secure Socket Layer (SSL)
• Appendix

37 © 2010 IBM Corporation

Current Problem – 3 Tier Environment

User 1

App server
Application Server User id
User 2

ƒ Problem
User 3
ƒ Often only one user id from the application server
ƒ Physical reconnects for each user would give bad
performance
ƒ Database auditing does not show which user
initiated an action
© 2010 IBM Corporation
Solution - Trusted Context

• Typically an application server has to connect to the database

as the “application user“

• Trusted Connection allows connection reuse under a different

userid to avoid overhead of creating a new connection
• With or without athentication
• Better performance than a new physical connection

• Prevents loss of identity in 3-Tier environment

• Allows auditing to show which user initiated database activity

instead of having only one user from the application server

© 2010 IBM Corporation

 Solution - Trusted Context

Trusted Context
User 1

User 1
User 2
Application Server User 3
User 2

User 3

ƒ Benefit:
ƒ Application server can switch the user id over one physical connection
ƒ Performance improvement
ƒ Database auditing shows the right user
© 2010 IBM Corporation
Trusted Context

• A database object created by the database security

administrator (DBSECADM)
• Defines certain criteria to allow a “trusted connection“
• Defines to which user ids a “trusted connection“ can switch
• Defines further priviliges and properties

• Criteria for a “trusted connection“

• The connection must be established by a specific user
• The connection must come from a trusted client machine
• The port over which the connection is made must have the
required encryption

© 2010 IBM Corporation

Trusted Context – Steps

CREATE TRUSTED CONTEXT CTX1

BASED UPON CONNECTION USING SYSTEM AUTHID BOB
DEFAULT ROLE MANAGER
ENABLE
ATTRIBUTES (ADDRESS '9.26.113.204')
WITH USE FOR JOE, MARY WITHOUT AUTHENTICATION

• Creates an Trusted Context object named CTX1

• Will allow connections from 9.26.113.204

• Can switch to user Joe or Mary once Trusted Connection

established
© 2010 IBM Corporation
Trusted Context – Switching Users

• Switch to any user defined in the Trusted Context Object scope

• Perform database operations

• Audit records will show the switched user as the originator of the
operations

• If using transactions, commit or rollback before switching to a new

user

© 2010 IBM Corporation

 Agenda
• Database User Authentication
• Database User Authorization and Privileges
• Administrative Users and Roles
• Monitoring Database Activity
• Data Encryption
• Single Sign On (SSO)
• Trusted Context
• Pluggable Authentication Modules
• Mapped Users – Connections without host OS accounts
• Secure Socket Layer (SSL)
• Appendix

44 © 2010 IBM Corporation

Pluggable Authentication Module (PAM)

• Framework to handle user authentication and management

• Uses Modules
• Modules can be combined via stacking
• Implement your own modules
• Great flexibility
• Supported on (32 and 64 bit)
• Solaris
• Linux
• AIX
• Supported with: ODBC, JDBC and ESQL/C
© 2010 IBM Corporation
 How to setup PAM

1. Install and configure PAM on your Operating System

2. Configure Informix to use PAM
3. Configure your application*

* Only necessary when using authentication mode `challenge` to make your

Application answer the challenge

© 2010 IBM Corporation

How to setup PAM

• Write your own module if desired or use existing one

• Existing ones like LDAP, UNIX authentication, etc
• Configure PAM
• Solaris: /etc/pam.conf
• Linux: /etc/pam.d/<servicename>
• Configure Informix
• As simple as adding/changing an SQLHOSTS file entry
• Configure Client
• Only necessary when using authentication mode
“challenge“ to make your Application answer the challenge

© 2010 IBM Corporation

Sample PAM configuration file using UNIX authentication

• Linux example: /etc/pam.d/ids_pam_service

<module_type> <controlflag>
<module_path>
auth required
pam_unix.so
account required
pam_unix.so
password required
pam_unix.so

© 2010 IBM Corporation

Configure Informix to use PAM

• Add a PAM enabled DBSERVERALIAS in the ONCONFIG file:

DBSERVERALIAS cheetah2pam

• Add an SQLHOSTS file entry:

cheetah2pam onsoctcp localhost 9089
s=4,pam_serv=(ids_pam_service),pamauth=(challenge)

• pam_serv is the PAM service/module name

• pamauth can be:
• challenge the application must provide the correct answer to a challenge
• password the user is authenticated using the explicit connection password

That‘s all from the Informix side!

© 2010 IBM Corporation
PAM Sequence
PAM Service in sqlhosts
S=4,pam_serv=(ids_pam_service),pamauth=(challenge)

2. Check Pamservice 3. PAM service =

Ids_pam_service /etc/pam.d/ids_pam_service
auth required
pam_unix.so
4. Check PAM
module
1. Request for connection 5. PAM module pam_unix.so
7. Challenge Informix
pam_unix.so
Database Server 6. Load pam_unix.so
8. Response

9. Authenticated 10. Database access

Database

© 2010 IBM Corporation

ESQL/C Example for Challenge Authentication Mode
Excerpt from $CSDKDIR/demo/esqlc/pamdemo.ec

© 2010 IBM Corporation

 Agenda
• Database User Authentication
• Database User Authorization and Privileges
• Administrative Users and Roles
• Monitoring Database Activity
• Data Encryption
• Single Sign On (SSO)
• Trusted Context
• Pluggable Authentication Modules
• Mapped Users – Connections without host OS accounts
• Secure Socket Layer (SSL)
• Appendix

52 © 2010 IBM Corporation

Mapped Users

• Allows connections without host operating system accounts

• An externally authenticated user can be mapped to

• A different operating system user
• An internal Informix UID/GID pair

• This local user to which the external user is mapped is called

“surrogate“

• The user will use the priviliges of the user to which it has been
mapped (surrogate user priviliges)

• Windows support pending

© 2010 IBM Corporation

Mapped Users - Setup

• Enable by setting ONCONFIG parameter USERMAPPING

• BASIC
• Allows mapping to regular users
• ADMIN
• Allows mapped users to have informix administrative priviliges

• Setup PAM or SSO

• Use GRANT ACCESS TO to setup mapped users

• The following sets up user bob to be mapped to the local
user dbuser
GRANT ACCESS TO bob PROPERTIES USER dbuser

© 2010 IBM Corporation

Mapped Users – GID/UID

• Surrogate user can also be informix internal

• Specify UID and GID for users that do NOT exists on the
system

• The following maps user bob to the Informix internal user with
ID 101 and Group ID 10011

GRANT ACCESS TO bob PROPERTIES UID 101, GROUP (10011)

• It uses $INFORMIXDIR/users/uid_101 as home directory

should the user create any files etc.

© 2010 IBM Corporation

Mapped User Tables - Monitor

• Information about mapped users located in three tables of the

sysuser database

• sysusermap
• Maps an external user id to the local id to which it is mapped
(surrogate)

• syssurrogates
• Stores the local ids (surrogates) to which external users are
mapped

• syssurrogategroups
• Stores information about the local groups that are used

© 2010 IBM Corporation

 Agenda
• Database User Authentication
• Database User Authorization and Privileges
• Administrative Users and Roles
• Monitoring Database Activity
• Data Encryption
• Single Sign On (SSO)
• Trusted Context
• Pluggable Authentication Modules
• Mapped Users – Connections without host OS accounts
• Secure Socket Layer (SSL)
• Appendix

57 © 2010 IBM Corporation

 What is Secure Socket Layer (SSL)?

• SSL is a communication protocol that uses encryption

• Provides privacy and integrity for data communication between two
points over a network
• SSL uses digital certificates to exchange keys for encryption and
server authentication.
• Digital Certificates are electronic ID cards issued by a trusted party
known as Certificate Authority (e.g. VeriSign).
• Digital certificates are stored in a key database (also known as a
keystore).
• IBM’s Global Security Kit bundled with Informix and CSDK provides
an iKeyman utility that can be used to create keystores and
manage digital certificates.

58 © 2010 IBM Corporation

 Why SSL and Where can it be Used?

• SSL is a more widely used alternative to the Informix

communication support modules (CSMs).
• You can use SSL for encrypted communication with both DRDA and
SQLI clients. You can use CSMs with only SQLI clients.
• You can use SSL for the following Informix connections:
• JDBC, SQLJ, ESQL/C, ODBC, and DRDA connections
• Enterprise Replication connections
• High-availability data replication (HDR) connections between an HDR primary
server and one or more secondary servers of any type (HDR secondary, SD
secondary, or RS secondary)
• Distributed transaction connections, which span multiple database servers
• The dbexport, dbimport, dbschema, and dbload utility connections
• Connection Manager connections between servers in a cluster

59 © 2010 IBM Corporation

60 © 2010 IBM Corporation
 Agenda

• Appendix
• Setting up SSL
• Setting up SSO
• More detailed LBAC

61 © 2010 IBM Corporation

 Setting up SSL - onconfig

• Located in $INFORMIXDIR/etc
• Configure server name and aliases
• DBSERVERNAME lenexa_on
• DBSERVERALIASES menlo_on,portland_on

• All SSL encryption/decryption operations are

performed on encrypt VP. Encrypt VPs can be
configured via VPCLASS parameter
• VPCLASS encrypt,num=3
• If VPCLASS is not configured, server will start one
encrypt VP

62 © 2010 IBM Corporation

 Setting up SSL - onconfig

• Configure poll threads for SSL connection

• NETTYPE socssl,3,50,NET
• If poll threads are not configured, server will start
one poll thread

• Configure label name for server digital

certificate in keystore
• SSL_KEYSTORE_LABEL ssltestlabel

63 © 2010 IBM Corporation

 Setting up SSL - sqlhosts

• Located in $INFORMIXDIR/etc
lenexa_on onsoctcp <hostname> lenexa_serv
menlo_on onsocssl <hostname> menlo_serv
portland_on drsocssl <hostname> portland_serv

• drsocssl - protocol for supporting SSL

communication with DRDA clients
• onsocssl - protocol for supporting SSL
communication with SQLI clients and between
servers in ISTAR, HDR, ER, SDS/RSS

64 © 2010 IBM Corporation

 Setting up SSL - services

• Located in /etc
lenexa_serv 1001/tcp
menlo_serv 1002/tcp
portland_serv 1003/tcp

• Ensure that the port numbers are unused.

65 © 2010 IBM Corporation

 Setting up SSL – conssl.cfg

• Located in $INFORMIXDIR/etc
• Configure fully qualified filename of client keystore
• SSL_KEYSTORE_FILE <local dir>/clikeydb.kdb
• Configure fully qualified filename of client stash file
• SSL_KEYSTORE_STH <local dir>/clikeydb.sth
• If conssl.cfg does not exist or if any of above
parameters are not configured, the client keystore
and stash file will default to:
$INFORMIXDIR/etc/client.kdb and
$INFORMIXDIR/etc/client.sth

66 © 2010 IBM Corporation

 Setting up SSL – server keystore

• Location and name of server keystore and its password

stash file is predefined

$INFORMIXDIR/ssl/<servername>.kdb
$INFORMIXDIR/ssl/<servername>.sth
servername is value of DBSERVERNAME onconfig
parameter

67 © 2010 IBM Corporation

 Setting up SSL – server keystore
• Create keystore and self-signed test certificate
gsk7capicmd -keydb -create -db lenexa_on.kdb
-pw snoopy -type cms –stash
gsk7capicmd -cert -create -db lenexa_on.kdb
-pw snoopy -label ssltestlabel -dn
"CN=lenexa.ibm.com,O=ibm,C=US" -size 1024 -
default_cert yes
• Export certificate to ascii file (to be imported to client
keystore):
gsk7capicmd -cert -extract -db lenexa_on.kdb
-format ascii -label ssltestlabel -pw snoopy
-target ssltestlabel.cert
• Change permissions on keystore and stash file to
600/informix:informix

68 © 2010 IBM Corporation

 Setting up SSL – client keystore

• Create keystore and import server certificate

gsk7capicmd -keydb -create -db clikeydb.kdb
-pw snoopy -type cms -stash

gsk7capicmd -cert -add -db clikeydb.kdb -pw

snoopy -label ssltestlabel -file
ssltestlabel.cert -format ascii

• Change the permissions on the keystore and stash

files to 664/informix:informix

69 © 2010 IBM Corporation

 Agenda

• Appendix
• Setting up SSL
• Setting up SSO
• More detailed LBAC

70 © 2010 IBM Corporation

 Setting up SSO
• The process in deploying Kerberos SSO for Informix
involves:
1. Configure the computers on the network to function with the Kerberos 5
authentication protocol. This involves setup of a secured computer to host the
Key Distribution Center (KDC).
2. Create client user principals and the Informix service principal in the KDC
3. Configure the SQLHOSTS information and Generic Security Services
communications support module (GSSCSM) on the computer hosting the
database server
4. Configure the Informix service principal key and ensuring it is on the
computer hosting the database server.
5. Configure a database client program that functions with GSSCSM

Refer to the Informix, Version 11.50 SECURITY manual for more information
http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sec.doc/SEC_wrapper.htm

71 © 2010 IBM Corporation

 Agenda

• Appendix
• Setting up SSL
• Setting up SSO
• More detailed LBAC

72 © 2010 IBM Corporation

 LBAC Demonstration

User Label – Public

Security Label Name Rank Task
Public John Smith CEO Run Company
Public James Talbot CFO Run Accounts
Public Malcolm Knight CIO Run IT

• SELECT * FROM people

73 © 2010 IBM Corporation

 LBAC Demonstration

User Label – Confidential

Security Label Name Rank Task
Public John Smith CEO Run Company
Confidential Heinrich Messier Accountant SEC Relations
Public James Talbot CFO Run Accounts
Confidential Jessica McHenry IT Specialist Networks
Confidential Melissa Williams IT Specialist Databases
Public Malcolm Knight CIO Run IT

• SELECT * FROM people

74 © 2010 IBM Corporation

 LBAC Demonstration

User Label – Secret

Security Label Name Rank Task

Secret Verity Dolittle M&A Buy Google
Public John Smith CEO Run Company
Confidential Heinrich Messier Accountant SEC Relations
Public James Talbot CFO Run Accounts
Confidential Jessica McHenry IT Specialist Networks
Secret Jane Ferguson M&A Buy Microsoft
Confidential Melissa Williams IT Specialist Databases
Secret Kate Ball M&A Buy Oracle
Public Malcolm Knight CIO Run IT

• SELECT * FROM people

75 © 2010 IBM Corporation

 Multi-Component Security Policy

Secret Marketing Entire Region

Confidential Product
Development

Public Quality West East

Assurance
• Each label will have one of Secret, Confidential, Public
• Each label will have zero or more of Marketing, Product
Development, Quality Assurance
• Each label will have zero of more of Entire Region, East, West

76 © 2010 IBM Corporation

 Why would you use LBAC?

• Access can be controlled declaratively

• By granting or revoking labels to rows/columns/users
• By granting or revoking exemptions
• Provides extra protection for sensitive data
• Credit card numbers
• Social security numbers
• Provides Mandatory Access Control (MAC)
• Orange Book (B1) style label-based security
• Similar to MLS – multi-level security
• Intended for certification versus Common Criteria

77 © 2010 IBM Corporation

Label Based Access Control
Security Components
Array Set Tree

Policy

Label

© 2010 IBM Corporation

Label Based Access Control
Security Components
Array Set Tree

Policy

Label

© 2010 IBM Corporation

 LBAC - Security Components
• Security Components
• Array
• Ordered list of elements
• First one is the highest
• One element allowed for each array component
• Set
• Non-ordered set of elements
• Zero or more elements in a label for a component
• Tree
• Hierarchical set of elements
• You can have one or more elements in a label

© 2010 IBM Corporation

 Security Label Component – Array (from prev. example)

• If your read label is Secret

Secret • You can read anything
• Your write label must be Secret
• If your read label is Confidential
Confidential
• You can read Confidential or Public data
• Your write label must be Confidential

Public • If your read label is Public

• You can only read Public
• Your write label must be Public
• Closest to the Bell-LaPadula model

81 © 2010 IBM Corporation

 Security Label Component – Set (from prev. example)

• If your read label has

Marketing { ‘Marketing’ }
• You can read anything that is
marked just ‘Marketing’
Product • Or has an empty Department
Development label component
• But not anything marked
Quality ‘Quality Assurance’ or ‘Product
Development’
Assurance

82 © 2010 IBM Corporation

 Security Label Component – Set (from prev. example)

• If your read label is

Marketing {‘Product Development’,
‘Quality Assurance’ }
• You can read items marked
Product ‘Product Development’
Development • You can read items marked
‘Quality Assurance’
Quality • Or both
Assurance • Or with an empty label
component

83 © 2010 IBM Corporation

 Security Label Component – Tree (from prev. example)

Entire Region • If your read label is ‘Entire Region’

• You can read anything

• If your read label is ‘West’

• You can only read ‘West’ or empty

• If your read label is (‘West’, ‘East’)

West East • You can only read ‘East’ or ‘West’
• You cannot read items labeled ‘Entire
Region’

84 © 2010 IBM Corporation

Creating Security Label Components

• CREATE SECURITY LABEL COMPONENT level ARRAY

['Secret', 'Confidential', 'Public'];

• CREATE SECURITY LABEL COMPONENT department

SET {'Marketing', 'Product Development',
'Quality Assurance'};

• CREATE SECURITY LABEL COMPONENT region TREE

(‘Entire Region‘ ROOT, ‘East’ UNDER ‘Entire
Region’, ‘West’ UNDER ‘Entire Region’);

© 2010 IBM Corporation

Label Based Access Control
Security Components
Array Set Tree

Policy

Label

© 2010 IBM Corporation

 Multi-Component Security Policy

Secret Marketing Entire Region

Confidential Product
Development

Public Quality West East

Assurance
• Each label will have one of Secret, Confidential, Public
• Each label will have zero or more of Marketing, Product
Development, Quality Assurance
• Each label will have zero of more of Entire Region, East, West

87 © 2010 IBM Corporation

 Creating a Security Policy

• A Security Policy is created from Security Label

Components
• Up to 16 components
• Typically using 1 to at most 3

• CREATE SECURITY POLICY company COMPONENTS

level, department, region;

88 © 2010 IBM Corporation

Label Based Access Control
Security Components
Array Set Tree

Policy

Label

© 2010 IBM Corporation

 Creating Security Labels

• A Security Label specifies the value for each

component in a Security Policy

• CREATE SECURITY LABEL company.director

COMPONENT level 'Secret’,
COMPONENT department 'Product Development',
'Quality
Assurance',
COMPONENT region 'Entire Region';

• Single element for components level and region

• Multiple elements for component department

90 © 2010 IBM Corporation

 Putting it all Together
• CREATE SECURITY LABEL COMPONENT level ARRAY ['Secret',
'Confidential', 'Public'];

• CREATE SECURITY LABEL COMPONENT department SET {'Marketing',

'Product Development', 'Quality Assurance'};

• CREATE SECURITY LABEL COMPONENT region TREE (‘Entire Region‘ ROOT,

‘East’ UNDER ‘Entire Region’, ‘West’ UNDER ‘Entire Region’);

• CREATE SECURITY POLICY company COMPONENTS level, department, region;

• CREATE SECURITY LABEL company.director

COMPONENT level 'Secret’,
COMPONENT department 'Product Development', 'Quality
Assurance',
COMPONENT region 'Entire Region';

© 2010 IBM Corporation

Label Based Access Control
Security Components
Array Set Tree

Policy

Label

© 2010 IBM Corporation

 Protecting a Table
Row Level Security

• CREATE TABLE T1
( C1 IDSSECURITYLABEL, { Always NOT NULL }
C2 INTEGER NOT NULL,
C3 CHAR(10) NOT NULL
COLUMN SECURED WITH director
) SECURITY POLICY company; Column Level Security
Security Policy for Table

ALTER TABLE T1
ADD(C1 IDSSECURITYLABEL),
MODIFY(C3 INTEGER NOT NULL
COLUMN SECURED WITH director),
ADD SECURITY POLICY company;

93 © 2010 IBM Corporation

 Inserting a Protected Row

• The same INSERT statement as without the protected column

• The label is specified for the whole column, at the time the column is
protected

INSERT INTO EMP (empno, firstname, lastname)

VALUES ( '12345', 'John', 'Doe');
CREATE TABLE EMP (
SL IDSSECURITYLABEL,
EMPNO CHARACTER(6),
FIRSTNAME VARCHAR(12),
LASTNAME VARCHAR(15))
SECURITY POLICY
access_employee_policy;

• Uses the default write label for the user

© 2010 IBM Corporation
 Inserting a protected Row – explicit label Security
label
INSERT INTO EMP
VALUES (
SECLABEL_BY_NAME('access_employee_policy', 'confidential')
, '1001', ‘Jane', ‘Doe');
CREATE TABLE EMP (
SL IDSSECURITYLABEL,
EMPNO CHARACTER(6),
FIRSTNAME VARCHAR(12),
LASTNAME VARCHAR(15))
SECURITY POLICY
access_employee_policy;

INSERT INTO EMP (empno, firstname, lastname) VALUES( '12345', 'John', 'Doe');

© 2010 IBM Corporation

 Retrieving Row Security Labels
• If the labels “match” a column can be selected, if not, an error is generated
• Select * from protected_table gives an error if the user is not allowed to
see all columns!

SELECT substr(seclabel_to_char('access_employee_policy',
SL),1,30), empno, firstname, lastname
FROM emp;

CREATE TABLE EMP (

SL IDSSECURITYLABEL,
EMPNO CHARACTER(6),
FIRSTNAME VARCHAR(12),
LASTNAME VARCHAR(15))
SECURITY POLICY
access_employee_policy;

© 2010 IBM Corporation

Label Based Access Control
Security Components
Array Set Tree

Policy

Label

© 2010 IBM Corporation

Assigning Labels to a User

GRANT SECURITY LABEL company.director TO mr_ceo FOR ALL ACCESS;

GRANT SECURITY LABEL company.director TO mr_ceo FOR READ ACCESS;
GRANT SECURITY LABEL company.director TO mr_ceo FOR WRITE ACCESS;

98 © 2010 IBM Corporation

Informix 11.7 Bootcamp
Security
Information Management Technology Ecosystem

© 2010 IBM Corporation