security and encryption in ids v10 dick snoke sr. consulting it specialist / ibm informix user forum...

Security and Encryption in IDS v10

Dick SnokeDick SnokeSr. Consulting IT Specialist / Sr. Consulting IT Specialist /

IBMIBM

Informix User Forum 2005 Moving Forward With Informix

Atlanta, Georgia December 8-9, 2005

IBM Software Group | DB2 information management software

Topics

Opening Comments

Encryption of Client-Server traffic

Encryption of data in the database

User authentication

DBMS and database privileges

Database object privileges

Roles

Q&A


Comments

Data and database security must be part of an overall security and privacy policy.

Security and privacy are choices are about risk assessment and mitigating risks.

No computer system can be completely secure.

This talk does NOT cover all aspects of security or privacy.

Basic practice: Least required privilege for any activity


Client-Server Traffic Security

Available in IDS 9.40 and later

Prevents easy data capture by sniffing/snooping

Is not completely unbreakable, but is not easily broken

Is independent of other encryption operations

Uses the OpenSSL libraries to encrypt and decrypt

Configured in the sqlhosts and other files

Is not selectable among sessions of the same $INFORMIXSEVER


Client-Server Security

sqlhosts file:

sevenoaks onsoctcp rhel.workshop.com sqlturbo s=0

eightoaks onsoctcp localhost sqlturbo2 s=1

s=0 Disables both hosts.equiv and rhosts lookup from the database server side (only

incoming connections with passwords are accepted).

s=1 Enables only the hosts.equiv lookup from the database server side.

Other options exist. Study the IDS Administrator’s Guide, chapter 3


Encrypted Application-DBMS Traffic

Passwords or All Traffic, not both at once

Configuration File $INFORMIXDIR/etc/concsm.cfg No standard or default file provided See the IDS Administrator’s Guide, chapter 3 for details

Password Encryption Example SPWDCSM("/opt/IBM/ids10/csm/libixspw.so","","p=0")

Network Traffic Encryption ENCCSM(“/opt/IBM/ids10/lib/cms/iencs10a.so", "config=/usr/lib/encrypt.txt") Encryption Parameter File:

ENCCSM_CIPHERS all

ENCCSM_SWITCH 120,60

ENCCSM_MAC medium

ENCCSM_MACFILE /opt/IBM/ids10/etc/mackey


Encrypting Data in the Database

New in IDS v10

Only for character or BLOB data, not for other data types

Column granularity, not row or table-based

Is selected for each SQL operation Not in the table definition

Can select data without decryption

Choice of algorithm AES

Triple DES

This does cost: Extra virtual processors

Additional CPU overhead

Additional disk space


Encryption Examples (1)

Add encrypted columns to the stores database

alter table call_type add enc_code char(99);

alter table call_type add enc_descr char(119);

Put encrypted data in the new columns

SET ENCRYPTION PASSWORD 'erewhon' with hint 'gulliver';

UPDATE call_type SET enc_code =

CASE

WHEN call_code = 'B' then encrypt_aes('B')

WHEN call_code = 'D' then encrypt_aes('D')

WHEN call_code = 'I' then encrypt_aes('I')

WHEN call_code = 'L' then encrypt_aes('L')

WHEN call_code = 'O' then encrypt_aes('O')

END;



The Query

SET ENCRYPTION PASSWORD 'erewhon';

SELECT * FROM call_type;

The Resultscall_code B

code_descr billing error

enc_code 01ikQAAAAEA4maJ+h/vbt/IQwDfrlajpEiOmMaVI1QHCUq9ngvJkEP+/BzwFhGSYw==

enc_descr 0sE8QAAAAEAKsigJRAD++AEVnYnD5lWt7npsATwqe2UCUq9ngvJkEP+/BzwFhGSYw==



The Query

SELECT call_code

, decrypt_char(enc_code)

, code_descr

, decrypt_char(enc_descr)

FROM call_type;

The Results

call_code B

(expression) B

code_descr billing error

(expression) billing error



A Query that returns no rows

SELECT decrypt_char(enc_descr, 'erewhon') as description

FROM call_type

WHERE enc_code = encrypt_aes("B", 'erewhon', 'gulliver')

;

The encryption is not the same every time. This is GOOD

Prevents probing with dictionaries or other trial data


Warnings: Encryption and SQL Processing

Encrypted columns ought not be primary keys Indexing is difficult if not impossible

Don’t join on encrypted columns Comparisons are expensive

Don’t index encrypted columns See the previous slide on comparison of encrypted column data

Get the size right! If not The data is truncated

Its impossible to retrieve the data after its been truncated

In general, encrypted columns in predicates slow down the processing. So do that with great care.


Encryption Passwords

Passwords and Password Management Not all rows have to be encrypted

Not all rows have to have the same password

A password clue is optional

There is no way to get the data back without the correct password!

Think carefully about who chooses passwords and where the passwords are recorded. Are they in files that are not encrypted?

Are they embedded in the application source code?


Sizing

Encrypted data is larger than un-encrypted data

How much larger varies with the row

There is no easy way to know how large is large enough for variable-length columns.

The chart of expected sizes is in the IDS Administrator’s Guide


Security and Connections to the DBMS

Note that connections are to the server, not the database A database is opened AFTER a connection is established

Users are authenticated but not verified

Choice of authentication method OS: /etc/passwd or equivalent

Pluggable Authentication Modules (PAM)

LDAP

Trust


Security and Connections to the DBMS

OS-based; /etc/passwd or User Accounts On all platforms The conventional technique Requires OS account administration

A Pluggable Authentication Module (PAM) Not on all platforms. Check for your platform Various algorithms are available

LDAP Only on Windows, not on UNIX or Linux Centralized identity control Requires a PAM

Trust On all platforms, Based solely on the origin of the request



Platforms: Linux and Solaris: 32-bit and 64-bit AIX and HP/UX: 32-bit only

Configuration in /etc/pam.conf or /etc/pam.d sqlhosts - field 6 of each line

Execute within the oninit processes separate from other threads typically from /usr/lib

Methods vary password only challenge-response multiple challenge-responses

You can write your own!



Warnings: HDR and ER do not work with challenge-response PAM authentication

Must use a separate port for replication

Applications must be ready for the challenge-response protocol

Pay attention to PAM_STACKSIZE in the onconfig file


Using LDAP on Windows

Similar to configuring a PAM

Build the LDAP client for your choice of LDAP server Servers: IBM Directory Server, openLDAP,…

$INFORMIXDIR\demo\authentication has templates and samples

Configure the sqlhosts file Column 6: s=4, pam_serv=(pam_chal), pamauth=(challenge) OR

s=4, pam_serv=(pam_pass), pamauth=(password)

Beware of challenge mode: Replication can not work since no challenge response is possible

Applications must be prepared to respond

Beware of password mode Implicit DBMS connections can not work since no password is provided


Trusted Connections

Accepting connections based on the source system alone Configured in

.rhosts

/etc/hosts.equiv

No checking of passwords or other authentication

Risky, but required for distributed SQL (synonyms)


Database Privileges and Controls

Control who may grant privileges Limit the delegation of authority

Keep records: use scripts, not ad-hoc SQL

The nefarious user: public

Do NOT grant anything to public!!


Categories of Privileges

Database Connect, Resource, DBA

Table Insert, update, delete, select, alter, references, index, under

View Insert, update, delete, select

Types usage, under

Routines Execute

Sequences select, alter


Managing Privileges

NODEFDAC environment variable Controls the initial privileges for new objects

Set in the environment of the user

non-ANSI databases

Public gets ALL unless NODEFDAC is set

ANSI databases

only the owner has any initial privileges

Study the Guide to SQL: Syntax for all the details on privileges


Database Privileges and Controls

New in v10: permission to create a database (330/388 errors if not allowed)

permission to register external UDRs.

Basic privileges Connect

Select, insert, update, delete

Execution of UDRs and UDFs

All these granted on either the entire database or table by table

Privileges for Views Similar to table privileges

Does not allow greater access to underlying tables


Database Privileges and Controls - Examples

Users informix in group informix dsnoke in group dsnoke; DBA privileges granted by user informix idsuser1 in group dsnoke; resource privilege granted by user informix idsuser2 in group dsnoke; connect privilege granted by user informix

In the onconfig file DBCREATE_PERMISSION informix

As user informix CREATE DATABASE privileges IN sevenoaks; -- succeeds Any CREATE DATABASE statement by any other user fails

As user idsuser2 CREATE TABLE tab1 (col1 CHAR(1)); -- fails w 388 (no resource permission)

As user idsuser1 The table is successfully created


Database Privileges - Grant and Revoke

GRANT Allows specified operations

GRANT … WITH GRANT OPTION Allows the user to pass privileges on to other users

Use this with great care

REVOKE Withdraws permission of specified operations


Roles and Managing Privileges

Roles are just groups of privileges Set by default or with SET ROLE statement

Allocated to users by GRANT ROLE statement

New in v10: Default roles

Can help manage ad-hoc users

Simplifying object privilege management Don’t grant privileges to users; grant privileges to roles

Don’t publicize the role names!


Roles and Managing Privileges - Examples

CREATE ROLE ordinary;

CREATE ROLE special;

GRANT SELECT ON employee(name) to ordinary;

GRANT SELECT, UPDATE, INSERT ON employee TO special;

REVOKE ALL ON employee FROM PUBLIC;

GRANT DEFAULT ROLE ordinary TO ellison;

GRANT DEFAULT ROLE special TO dsnoke;

SELECT * FROM employee WHERE name = “Snoke”; Fails for ellison Succeeds for dsnoke

SELECT name FROM employee; Succeeds for everyone in either role


Questions?

Security and Encryption in Security and Encryption in

IDS v10IDS v10

Dick SnokeDick [email protected]@us.ibm.com

Informix User Forum 2005 Moving Forward With Informix

Atlanta, Georgia December 8-9, 2005

security and encryption in ids v10 dick snoke sr. consulting it specialist / ibm informix user forum...

Documents

database security

database server

blob data

code char99alter table

encryption parameter

encryption operationsuses

detailspassword encryption

easy data capture