Windows Security Primer
Walid Rjaibi IBM
Session Code: 205
May 16, 2012 9:45 AM – 10:45 AM | Platform: <DB2 for Linux, Unix, Windows>
Agenda
• Business Drivers
• Authentication
• Authorization
• Auditing
• Encryption
Database Security Business Drivers
• External threats
• Prevent data theft
• Prevent data loss
• Internal Threats
• Prevent data leakage
• Prevent unauthorized data and configuration changes
• Compliance
• Simplify the process
• Reduce the cost
Database Security Business Drivers (Cont.)
2010 Data Breach Report from Verizon Business RISK Team
http://www.verizonbusiness.com/resources/reports/rp_2010-data-breach-report_en_xg.pdf
“Although much angst and
security funding is given to offline data, mobile devices, and end- user systems, these assets are simply not a major point of
compromise.”
-2009 Data Breach Investigations Report
73% of security professionals
anticipate the volume of database security attacks will continue to increase
- Enterprise Strategy Group, Databases at Risk, September 2009
Authentication
Authentication Options
• Operating System
• User validation
• Group membership
• Kerberos
• User validation only
• LDAP
• User validation
• Group membership
• Central user and group membership management
• Custom Plug-ins
• User validation (e.g. GSS API)
• Group membership
DB2
Authentication - LDAP Options
• Requires separate LDAP configuration
• Requires all users to be defined in LDAP (cannot have some in OS and some in LDAP)
LDAP Security
Plugins LDAP
DB2 Default OS
Security Plugin Operating LDAP
System (OS)
Transparent LDAP Authentication*
* Available in DB2 9.7 FP 1 and DB2 9.5 FP5
• Integrates with existing OS LDAP configurations
• Requires minimal DB2 Configuration (1 Parameter: DB2AUTH=OSAUTHDB)
LDAP Security Plug-in Authentication
Authorization
What are the Most Common Authorization Questions?
1. How do I manage authorization (user vs role vs group)?
2. Where do I manage authorization (application vs database)?
3. How do I manage authorization at the row level?
4. How do I manage authorization to sensitive columns?
5. How do I limit authorization to certain contexts?
6. How do I manage authorization for end users in 3-tier environments?
7. How do I vest security administration and database administration into two non overlapping roles (i.e., separation of duties)?
8. How do I prevent the DBAs from accessing table data?
Database Roles
•
What is a database role?
• A database object that may group together one or more privileges or database authorities, and may be granted to users, groups, PUBLIC, or other roles
•
What are the advantages of database roles?
• Simplification of the administration and management of privileges in a database
•
When do roles take effect?
• All the roles assigned to a user are enabled when that user establishes a connection
• All the privileges and database authorities associated with those roles are taken into account when DB2 checks for authorization
Database Roles (Cont.)
• What can be granted to a role?
• LBAC security labels and exemptions
• All database privileges
• All database authorities
• Role membership
• Managed by SECADM and could be delegated to others using the WITH ADMIN option on role
Database Roles (Cont.)
• Roles vs Groups?
• Equivalent concepts that accomplish the same goal
• Role membership is managed within the database
• Group membership is managed externally, and thus can be managed centrally (e.g. in LDAP)
• Group privileges are not considered for creating views, MQTs, triggers, packages and SQL routines
• Roles and groups can work together by assigning roles to groups
Row and Column Access Control (RCAC)
• A table level authorization function fully implemented in the database that provides:
• Row level access control based on customer-supplied rules
• A doctor can see rows representing his patients only
• A manager can see rows representing his employees only
• Column level access control based on customer-supplied rules
• This is also called data masking
• A teller can see only the last 4 digits of the credit card number column
• Defined and managed by SECADM only (for separation of duties)
RCAC Usage Scenario
Account Name Income Branch
1111-2222-3333-4444 Alice 22,000 A
2222-3333-4444-5555 Bob 71,000 B
3333-4444-5555-6666 Carl 123,000 B
4444-5555-6666-7777 David 172,000 C
•
CUSTOMER table contains information about the bank customers• INTERNAL_INFO table contains information about bank employees
• The bank has a security policy for access to customer information
• Table: CUSTOMER
RCAC Usage Scenario (Cont.)
• Security policy
• Tellers can only see their own branch customers
• Customer service representatives and telemarketers can see all customers
• The account number is accessible by customer service representatives only when they are using the account update application. This application is identified through stored procedure ACCOUNTS.ACCTUPDATE
• Tellers, customer service representatives and telemarketers are members in roles TELLER, CSR and TELEMARKETER respectively
RCAC Usage Scenario (Cont.)
• The SECADM creates a row permission for the bank tellers
CREATE PERMISSION TELLER_ROW_ACCESS ON CUSTOMER FOR ROWS
WHERE
VERIFY_ROLE_FOR_USER (USER, ‘TELLER’) = 1 AND BRANCH = (SELECT HOME_BRANCH FROM
INTERNAL_INFO WHERE EMP_ID = USER) ENFORCED FOR ALL ACCESS
ENABLE
RCAC Usage Scenario (Cont.)
• The SECADM creates a row permission for customer service representatives and telemarketers
CREATE PERMISSION CSR_ROW_ACCESS ON CUSTOMER FOR ROWS
WHERE
VERIFY_ROLE_FOR_USER (USER, ‘CSR’) = 1 OR
VERIFY_ROLE_FOR_USER (USER, ‘TELEMARKETER’) = 1 ENFORCED FOR ALL ACCESS
ENABLE
RCAC Usage Scenario (Cont.)
• The SECADM creates a column mask for the column account number
CREATE MASK ACCOUNT_COL_MASK ON CUSTOMER FOR COLUMN ACCOUNT RETURN
CASE WHEN (VERIFY_ROLE_FOR_USER (USER, ‘CSR’) = 1 AND ROUTINE_SPECIFIC_NAME = ‘ACCTUPDATE’ AND ROUTINE_SCHEMA = ‘ACCOUNTS’ AND
ROUTINE_TYPE = ‘P’) THEN ACCOUNT
ELSE 'xxxx-xxxx-xxxx-‘ || SUBSTR(ACCOUNT,13,4) END
ENABLE
RCAC Usage Scenario (Cont.)
• The SECADM alters the CUSTOMER table to activate both row and column level access control
ALTER TABLE CUSTOMER
ACTIVATE ROW ACCESS CONTROL ACTIVATE COLUMN ACCESS CONTROL
RCAC Usage Scenario (Cont.)
Account Name Income Branch
XXXX-XXXX-XXXX-5555 Bob 71,000 B
XXXX-XXXX-XXXX-6666 Carl 123,000 B
• Amy, a bank teller from branch B, issues the query:
SELECT ACCOUNT, NAME, INCOME, BRANCH FROM CUSTOMER
• The result set returned includes only branch B customers
• The account number is masked out
RCAC Usage Scenario (Cont.)
Account Name Income Branch
XXXX-XXXX-XXXX-4444 Alice 22,000 A
XXXX-XXXX-XXXX-5555 Bob 71,000 B
XXXX-XXXX-XXXX-6666 Carl 123,000 B
XXXX-XXXX-XXXX-7777 David 172,000 C
• Pat, a bank customer service representative, issues the query:
SELECT ACCOUNT, NAME, INCOME, BRANCH FROM CUSTOMERS
• The account number is masked out in the result set returned
RCAC Usage Scenario (Cont.)
Account Name Income Branch
1111-2222-3333-4444 Alice 22,000 A
2222-3333-4444-5555 Bob 71,000 B
3333-4444-5555-6666 Carl 123,000 B
4444-5555-6666-7777 David 172,000 C
• Pat, a bank customer service representative using the account update application, issues the query:
SELECT ACCOUNT, NAME, INCOME, BRANCH FROM CUSTOMERS
• The account number is NOT masked out in the result set returned
Trusted Contexts
• A specification of a trust relationship between the database and an external application
• A connection that matches a trust relationship is called a trusted connection. There are 2 types:
• An implicit trusted connection
• An explicit trusted connection
• An implicit trusted connection allows a user to inherit a role that is not available to them outside the scope of that trusted connection
• Allows customers to gain more control on when a privilege or an authority can be exercised by a user
Conditional Authorization Via Trusted Contexts
9.26.52.193 DB2 Database
CREATE ROLE CONROLE
GRANT CONNECT ON DATABASE TO ROLE CONROLE
CREATE TRUSTED CTX1 BASED UPON CONNECTION USING SYSTEM AUTHID MILLER
ATTRIBUTES (ADDRESS 9.26.52.193) DEFAULT ROLE CONROLE
User: Miller
9.52.72.245
User: Miller
User Miller can connect
User Miller cannot connect
User1: privilege(p1) User2: privilege (p2) ………. Usern: privilege (pn)
Middle-tier application server
Application server user Privilege (p1,p2,…,pn)
DB2 Database
Who is connecting?
User1, User2, Usern?
Trusted Contexts (Cont.)
• An explicit trusted connection allows a user to switch the current user on the connection
• The user IDs to switch to are defined by the SECADM
• Switching can optionally require authentication
• An application server uses a trusted connection to change
the user id of the connection without re-authenticating the
new user at the database
User1: privilege(p1) User2: privilege (p2) ………. Usern: privilege (pn)
Middle-tier application server
Trusted connection AppServerTrustedID
No privileges
DB2 Database
- Offers a trusted connection as defined by SECADM - Authorize switching users as defined by SECADM
SECADM
CREATE TRUSTED CTX1 BASED UPON CONNECTION USING SYSTEM AUTHID AppServerTrustedID
ATTRIBUTES (ADDRESS 9.26.52.193) WITH USE FOR USER1, USER2, USERN
Separation of Duties
• SYSADM no longer includes DBADM
• DBADM no longer includes the ability to do grants and revokes
• New ACCESSCTRL authority is needed
• SECADM scope has been extended to fully manage security
• Customers can now vest database administration and security administration into 2 non overlapping roles
GRANT DBADM WITHOUT ACCESSCTRL ON DATABASE TO USER JOE GRANT SECADM ON DATABASE TO USER MARY
• DBADM no longer includes the ability to access data
• New DATAACCESS authority is needed
• Customers can now set up a DBADM with no access to data
GRANT DBADM WITHOUT DATAACCESS ON DATABASE TO USER ROB
• New EXPLAIN privilege to issue EXPLAIN statements
• No longer forced to grant actual table privileges, which allow data access
• New WLMADM authority to manage WLM objects
• No longer forced to grant DBADM, which gives other privileges
• New SQLADM authority to perform SQL tuning
• No longer forced to grant DBADM, which gives other privileges
• EXECUTE privilege is sufficient to run audit log management routines
• No longer forced to grant SECADM, which gives other privileges
Auditing
DDL = Data Definition Language (schema changes) DDL = Data Definition Language (schema changes)
DML = Data Manipulation Language (data value changes) DML = Data Manipulation Language (data value changes) DCL = Data Control Language
DCL = Data Control Language
Auditing
• Generates audit records for a series of predefined events
• Audit records provide insight on who did what, where, when and how
• Who: Authorization/User ID tracking
• What: SQL Statement text tracking
• Where: Application and IP address tracking
• When: Event timestamp tracking
• How: Authorization checks tracking
Category Name Description
Audit AUDIT Generates records when audit settings are changed or when the audit log is accessed
Authorization checking CHECKING Authorization checks done by DB2
User validation VALIDATE Authenticating users or retrieving system security information
Object Maintenance OBJMAINT When objects are created or dropped
Operation Context CONTEXT Shows the operation context when a database operation is performed
Execution of SQL EXECUTE Shows the execution of SQL statements
System Administration SYSADMIN Generates records when operations requiring SYSADM, SYSCTRL, SYSMAINT are performed
Security Mainteance SECMAINT Generates records for security related operations
Auditing Granularity
• Audit policies can be associated with a number of database objects to control what is audited
• The database itself
• Tables
• Authorities such as SYSADM, DBADM and SECADM
• Users and groups
• Roles
• Trusted Contexts
• This granularity allows a narrowed focus on exactly what needs to be audited
• Further reduction in amount of data that is logged
sensitive table
• Create an audit policy that captures EXECUTE audit events
CREATE AUDIT POLICY DMLCHANGES CATEGORIES EXECUTE STATUS BOTH ERROR TYPE AUDIT
• Associate the audit policy with the table
AUDIT TABLE MYTABLE
USING POLICY DMLCHANGES
Archived Audit Log File Archived
Audit Log File
Auditing Data Exploitation
DB2 Agent
Active Audit Log File
Archived Audit Log Files
Delimited Audit
File
Report (flat) Audit
File Archive
Command
Extract Command
DB2 Server Audit Tables
Load
Data In Transit Encryption
Data In Transit Encryption Options
• DB2 DRDA encryption
• Legacy
• DES with 56-bit keys
• Does not provide data integrity
• Secure Socket Level (SSL)
• Strategic direction
• AES, 3DES with 128-bit (or more) keys
• Provides both data encryption and data integrity
DB2 Server JCC Client
JCC Client Example
Digital certificates database Signer
certificate database
SSL (GSKit) SSL (JSSE)
TCP/IP TCP/IP Encrypted communication
gsk8capicmd tool gsk8capicmd tool
Data At Rest Encryption
• What are the requirements?
• Application transparency
• Database schema transparency
• Low performance overhead
• Protects both on-line and off-line data (e.g. backups)
• Addresses encryption key management issues
IBM Database Encryption Expert Architecture
DB2 backup DB2 backup
EE DB2 Backup Agent EE DB2 Backup
Agent
Security Server Security
Server Key, Policy, Log Store
Encryption Expert
Web Administration
soap/https
https DB2
Server DB2 Server
EE FS Agent EE FS Agent
online files
offline files
• Security Server
• Key and Policy Management
• Authenticates agent communication
• Separation of duties
• EE Agents
• Communicates with security server to enforce policy
• Encrypt data
