IBM Informix Database Security

1. IBM Informix Database Security

2. Securing Your Data Database Security - protection from malicious attempts to view or modify data. Importance of data security: Security breaches are bad for you. Expensive to manage. Damages ‘good will’. Government Regulations and compliance.

3. Government Regulations USA HIPAA (Health Insurance Portability and Accountability Act), 1996 Sarbanes-Oxley (aka Sarbox or Sox), 2002 Gramm-Leach-Bliley Act (GLBA), 1999 California SB 1386 ‘Personal Information: Privacy’, 2002 Canada Personal Information Protection and Electronic Documents Act PIPEDA (2000) Europe European Union Directive on Data Protection Directive 95/46/EC (1995)

4. User Authentication Authentication is the process of verifying the identity of a user or application when connecting to a database. Informix supports: OS username/password authentication. PAM authentication framework. Single Sign-On (Kerberos) authentication. I am Sam Prove It $%^&*!@#@ Encrypted password

5. PAM authentication PAM framework provides a generic way to authenticate the user to system entry services. PAM can be used to integrate services with different authentication technologies, such as RSA, DCE, Kerberos, S/Key, and smart card based authentication.

6. Enable PAM in Informix Sample SQLHOSTS file: #server-name service machinename portno Options idsserver ontlitcp idc1ul14 3454 s=4,pam_serv=(pam_chal),pamauth=(challenge) Compile PAM module used for authentication and update the pam configuration file Example /etc/pam.conf: #service Module Control Module Options # Type Flag Path login auth required pam_unix_auth.so nowarn pam_chal auth required /usr/lib/security/pam_chal.so Developerworks Article http://www.ibm.com/developerworks/data/library/techarticle/dm-0704anbalagan/ http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/0306mathur/0306mathur.html

7. Single Sign-On Authentication How many login IDs and passwords do you have? Can you remember them all? People would like to login (sign-on) once: And then have appropriate access everywhere. Kerberos provides that functionality: Used in Microsoft Active Directory. See http://www.kerberos.org/

8. Enable Single Sign-On Authentication in Informix Informix servers configured to use Kerberos In sqlhosts file - server alias configured with GSS CSM: ol_ids_1150 onsoctcp toru ol_ids_1150 s=7,csm=(GSSCSM) Define a SSO GSSCSM concsm.cfg file defines the csm configuration information: GSSCSM("/work/informixdir/lib/csm/igsss11a.so", "", "c=1,i=1") Configure the Informix server principal in the KDC. Informix client programs: Configured via sqlhosts file. Both depend on a working Kerberos environment. Developerworks Article: http://www.ibm.com/developerworks/data/library/techarticle/dm-0809govindarajan

9. Agenda User Authentication Authorization and Access Control Auditing Data Encryption Mapped Users Trusted Context Row Level Auditing

10. Granting Privileges The authorization to use a database is called an access privilege: Example: An authorization to use a database is called the Connect privilege. Groups of privileges control the actions a user can perform on data and on database objects. Example: Database-level privileges. Ownership privileges. Table-level privileges. Column-level privileges. Use the GRANT / REVOKE statement to grant/ revoke privileges on a database, table, view, or procedure or to revoke a role from a user or from another role.

11. About Roles A role is a classification of access privileges that the DBA assigns, such as payroll. Most compliance rules require separation of roles: So distinct jobs are handled by different people. This means you must separate those groups: And the best method is by the use of roles. Default roles: DBSA – Database System Administrator Group. DBSSO – Database System Security Officer Group. AAO – Audit Analysis Officer Group. Bargroup is not officially a role. Role separation can be enabled during Install.

12. Access Control Requirements Access to the DBMS is a major part of compliance: It is far from the only issue. Only authorized users should be able to do anything: And even they should have minimum permissions. Do not grant RESOURCE or DBA to PUBLIC: Don’t even grant CONNECT to PUBLIC usually. Grant SELECT to PUBLIC on non-sensitive tables: Don’t even grant that on sensitive tables. Exploit roles to control permissions: Create a separate role for each class of user. Grant that role the necessary permissions for the job. Assign the permitted users the role. Write the application to set the correct role.

13. Why use LBAC? Will need to grant permissions to user: Specific users, not generic users. Need a finer grain of control than “public”. To take advantage of setting default authorization.

14. What is LBAC? Label-based access control (LBAC) is a form of Mandatory Access Control that enables you to control who has read access and who has write access to individual rows and columns of data: Data is Labeled. Users are granted labels. Based on Label comparison, users can access data. You get finer control than just the table level. Compliance rules dictate who can see what: Only certain users can see certain rows or columns. Developerworks Article: http://www.ibm.com/developerworks/data/library/techarticle/dm-0807mohan/

15. Agenda User Authentication Authorization and Access Control Auditing Data Encryption Mapped Users Trusted Context

16. Why Use Auditing? You need to know which data is modified by: Applications. Users. You need to monitor who uses each Informix utility. Sometimes you need to track who sees data: Identity theft takes a copy of information. It does not alter the information. Database auditing can track DBAs too. Auditing Requirements You need to be confident that you can track changes: Who changed what structurally. Only a very few staff can make schema changes. Row level auditing for all tables or selected tables.

17. Basic Auditing Informix has the ON-Audit and ON-ShowAudit utilities: ON-Audit controls what is audited. ON-Audit also controls how the audit results are recorded. ON-ShowAudit shows what auditable events occurred. Can be controlled by separate roles DBSSO: Database Security Officer: Controls who is audited. Controls which events are audited. AAO: Audit Analysis Officer: Controls whether auditing is in use or not. Analyzes audit logs.

18. Agenda User Authentication Authorization and Access Control Auditing Data Encryption Mapped Users Trusted Context

19. Encryption of Data Some data must not be stored in the database: PCI says the CVV number cannot be stored after authentication. Other data must be stored encrypted: Typically, the credit card number and the social security number. If an old database uses SSN as a key for joins Redesign your database with an arbitrary number for the joins: Use a SERIAL column (employee number). Or a SEQUENCE. Data encryption slows things down: It is a necessary evil. Do not use it unless it is necessary.

20. Encryption of Data • Encrypted Communications • Passwords encryption: • Simple Password encryption. • Fully encrypted communications: • ENCCSM • SSL • Data Encryption: • Column Level Encryption. • Encrypt Data at Rest. • Full encryption for all Informix data storage via Vormetric Encryption Expert for Informix.

21. Encrypted Communications Encrypts communications between client and server: Using standard encryption techniques to establish session keys. Also used for distributed database access – I-Star. ER (Enterprise Replication) can be encrypted: Often replicating over WAN. HDR (Heterogeneous Data Replication) will support encryption.

22. Enable Encrypted Communications Create or modify server entry in sqlhosts file: server_1_enc olsoctcp host 9089 csm=(s1_enc) Create or modify concsm.cfg file: s1_enc("/usr/informix/lib/csm/libixenc.so","cipher[aes:cbc], timeout[cipher:1440,key=60], mac[levels:<high,medium>,files:<builtin>]") Developerworks Article: http://www.ibm.com/developerworks/data/library/techarticle/dm-0401dandekar Enabling Simple Password Encryption is similar to ENCCSM.

23. Secure Socket Layer (SSL) Communication protocol that provides privacy and integrity for data communication over the network. Uses encryption to provide end-to-end secure connection. SSL feature in Informix uses digital certificates to exchange keys for encryption and server authentication. Digital certificates are stored in a key database (also known as keystore). IBM’s Global Security Kit bundled with Informix server and client provides an iKeyman utility that can be used to create keystores and manage digital certificates. Both client and server must have keystore for housing digital certificates.

24. Enable SSL in Informix sqlhosts for client and server: menlo_on onsocssl pinchy menlo_serv onconfig for server: e.g. SSL_KEYSTORE_LABEL ids_label conssl.cfg for client: SSL_KEYSTORE_FILE SSL_KEYSTORE_STH Create keystores and digital certificates for client and server: gsk7cmd -keydb -create -db menlo_on.kdb -pw snoopy -type cms -stash gsk7cmd -cert -create -db menlo_on.kdb -pw snoopy -label ids_label -dn "CN=menlo.ibm.com,O=ibm,C=US" -size 1024 -default_cert yes gsk7cmd -cert -extract -db menlo_on.kdb -format ascii -label ids_label -pw snoopy -target ids_label.cert

25. Column-Level Encryption Data can be stored in encrypted format: Using SQL functions ENCRYPT and DECRYPT. Data encrypted using either Triple-DES or AES. Data encrypted under application control. DBMS is not aware that data is encrypted. Assists in legislative compliance: HIPAA (Health Insurance Portability and Accountability Act), 1996. Sarbanes-Oxley (aka Sarbox or Sox), 2002. Basel II, 2001. Gramm-Leach-Bliley Act (GLBA), 1999. California SB 1386 ‘Personal Information: Privacy’, 2002. Developerworks Article: http://www.ibm.com/developerworks/data/library/techarticle/dm-0711mohan

26. Agenda User Authentication Authorization and Access Control Auditing Data Encryption Mapped Users Trusted Context

27. As a user without Host Operating System Accounts, I should be able to connect to Informix. As a DBSA, I should be able to grant Dynamic Server access to externally authenticated users by mapping them to the appropriate user and group privileges, regardless of whether these users have operating system accounts on the Dynamic Server host computer. Overview

28. Enabling Mapped Users When a DBSA turns on the USERMAPPING parameter of the onconfig file. Onconfig variable: USERMAPPING OFF|ADMIN|BASIC OFF is the default. ADMIN can grant administrative privileges to mapped users. BASIC is what it says, basic access. DBSA should verify that the users whom you want to map to surrogate user properties for Informix access can externally authenticate with single sign-on (SSO) or a pluggable authentication module (PAM).

29. Granting Informix Access to Mapped Users GRANT ACCESS TO statement: Use the GRANT statement with the ACCESS TO clause to map users to user properties required for access to Informix resources. User mapping tables: The following system catalog tables in the SYSUSER database map users to OS-level properties that enable Informix access and control level of privileges: sysusermap syssurrogates syssurrogategroups Open Admin Tool: Server Administration  User Privileges -> Mapped Users

30. Examples GRANT ACCESS TO bob PROPERTIES USER fred; This means that when 'bob' connects to Informix, as far as the operating system access is concerned, Informix will use the UID, GID(s) and home directory for user 'fred' (which must be a user name known to the O/S). GRANT ACCESS TO bob PROPERTIES UID 101, GROUP 10011; This means that 'bob' will use the anonymous UID 101 and the anonymous group 10011 when an O/S identity is required.

31. Examples GRANT ACCESS TO PUBLIC PROPERTIES USER dbuser; Anyone who can authenticate but does not have an explicit entry designating the mapped (surrogate) user will use the identity of dbuser. REVOKE ACCESS FROM bob; This means that 'bob' no longer has access to the machine via user mapping unless user PUBLIC is given mapped access, in which case 'bob' now uses the same privileges that PUBLIC uses. Alternatively, 'bob' may have been created as an O/S user, in which case those privileges override anything set in sysusermap and syssurrogates.

32. Agenda User Authentication Authorization and Access Control Auditing Data Encryption Mapped Users Trusted Context

33. Trusted Context – What is it? Connection reuse is allowed with a different userid with authentication: Avoids the overhead of establishing a new connection. Accommodate application servers needing to connect on behalf of an end-user but lack access to that end-user’s password to establish a new connection on their behalf. Typically used in 3 tier Client/Server environments. Allow users to gain additional privileges when their connection satisfies certain database server defined conditions.

34. Current State without Trusted Context (1) Loss of user identity: Some enterprises need to know the identity of the actual user accessing the database for access control purposes. Diminished user accountability: Accountability through auditing is a basic principle in database security. Not knowing the user’s identity makes it difficult to distinguish the transactions performed by the middle tier for its own purpose from those performed by the middle tier on behalf of some user.

35. Current State without Trusted Context (2) Over granting of privileges to the middle tier’s userid: The middle tier’s userid must have all the privileges needed to execute all the requests from all the users. This has the security issue of enabling users who do not need access to certain information to obtain access to them. Weakened security: The current approach requires that the userid used by the middle tier to connect must be granted privileges on all resources that might be accessed by user requests. If that middle-tier userid is ever compromised, then all those resources will be exposed.

36. Trusted Context Features Typically an application server has to connect to the database server as the “application user”. This gives the application all the privileges associated with that user – usually everything. Control the machine(s) a trusted connection can be established from. With trusted context, application users can access the database with their own level of privilege. Discretionary Access Control (DAC) applies to the current userid. Audit records apply to the current user. Different levels of privilege (roles) can be given to different users.

37. What is a Trusted Context? A Trusted Context is a database object created by the database security administrator (DBSECADM) that defines a set of properties for a connection that when met, allow that connection to be a “trusted connection” with special properties. The connection must be established by a specific user. The connection must come from a trusted client machine. The connection connecting port must have required encryption. If these criteria are met, the connection will allow changes in userid and privileges as defined in the trusted context.

38. Typical Usage Scenario Step 1: Create Trusted Context Objects: Created at database level. Must be created by DBSECADM before Trusted Connections can be established. Can use O/S users or Mapped Users. Step 2: Establish Trusted Connections: Must satisfy criteria defined in Trusted Context. Provision to Switch User. Use transactions within switched user session.

39. Creating Trusted Context Objects

40. Create Trusted Context 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.

41. Creating Trusted Connections API Support in ESQL/C, JDBC and ODBC ESQL/C Example: EXEC SQL CONNECT TO "dbname@online1" TRUSTED A trusted connection is possible only when the application specifically invokes an API designed to make such a connection (known as an explicit connection). The connection request attributes must match those of a trusted context defined on the DBMS as follows: System authorization ID: Represents the user that establishes a database connection. IP address (or domain name): Represents the host from which a database connection is established. Data stream encryption: Represents the encryption setting (if any) for the data communication between the database server and the database client.

42. 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.

43. Summary IBM aims to avoid security alert headlines: Bad for business confidence. To do so, IBM Informix: Provides a securable system. Installs it securely by default. Provides guidance and training on security.

44. Resources • The Online Informix Information Center • http://publib.boulder.ibm.com/infocenter/idshelp/v117/index.jsp • One-stop shop for Informix product documentation. • Supports book marking favorite topics, narrowing the scope to refine searches, printing subsets of topics. • IBM Informix DeveloperWorks Technical Articles • http://www.ibm.com/developerworks/db2/products/informix/index.html • Premium technical resource site for DBAs and developers. • Features explained with examples/sample code. • Contributions from IBM experts as well as customers. • IBM DeveloperWorks Informix Blogs • http://www-128.ibm.com/developerworks/blogs/page/roundrep (Informix Replication) • http://www-128.ibm.com/developerworks/blogs/page/gbowerman(Informix Application Development) • http://www-128.ibm.com/developerworks/blogs/page/idsteam(Informix Experts Blog)