Transcript Security and Encryption in IDS v10 - Washington Area Informix User

Informix User Forum 2005
Moving Forward With Informix
Security and Encryption
in IDS v10
Dick Snoke
Sr. Consulting IT Specialist / IBM
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
IBM Software Group | DB2 information management software
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
IBM Software Group | DB2 information management software
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
IBM Software Group | DB2 information management software
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
IBM Software Group | DB2 information management software
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
IBM Software Group | DB2 information management software
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
IBM Software Group | DB2 information management software
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;
IBM Software Group | DB2 information management software
Encryption Examples (2)
 The Query
SET ENCRYPTION PASSWORD 'erewhon';
SELECT
* FROM call_type;
 The Results
call_code
B
code_descr
billing error
enc_code
01ikQAAAAEA4maJ+h/vbt/IQwDfrlajpEiOmMaVI1QHCUq9ngvJkEP+/BzwFhGSYw==
enc_descr
0sE8QAAAAEAKsigJRAD++AEVnYnD5lWt7npsATwqe2UCUq9ngvJkEP+/BzwFhGSYw==
IBM Software Group | DB2 information management software
Encryption Examples (3)
 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
IBM Software Group | DB2 information management software
Encryption Examples (4)
 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
IBM Software Group | DB2 information management software
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.
IBM Software Group | DB2 information management software
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?
IBM Software Group | DB2 information management software
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 variablelength columns.
 The chart of expected sizes is in the IDS Administrator’s Guide
IBM Software Group | DB2 information management software
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
IBM Software Group | DB2 information management software
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
IBM Software Group | DB2 information management software
Pluggable Authentication Modules (PAM)
 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!
IBM Software Group | DB2 information management software
Pluggable Authentication Modules (PAM)
 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
IBM Software Group | DB2 information management software
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
IBM Software Group | DB2 information management software
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)
IBM Software Group | DB2 information management software
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!!
IBM Software Group | DB2 information management software
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
IBM Software Group | DB2 information management software
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
IBM Software Group | DB2 information management software
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
IBM Software Group | DB2 information management software
Database Privileges and Controls - Examples
 Users
 informix
 dsnoke
 idsuser1
 idsuser2
in group informix
in group dsnoke; DBA privileges granted by user informix
in group dsnoke; resource privilege granted by user informix
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
IBM Software Group | DB2 information management software
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
IBM Software Group | DB2 information management software
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!
IBM Software Group | DB2 information management software
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
IBM Software Group | DB2 information management software
Questions?
Informix User Forum 2005
Moving Forward With Informix
Security and Encryption in
IDS v10
Dick Snoke
[email protected]
Atlanta, Georgia
December 8-9, 2005