1. No category

Virtual Boot Camp – Day 1
Guardium Database Activity Monitor (DAM) & DB2 for i
Scott Forstie
forstie@us.ibm.com
DB2 for i Business Architect
1
© 2014 IBM Corporation
Guardium DAM & DB2 for i
DB2 for i
• Standard compliant
• Secure
• Scalable
• Functionally Advanced
• Excellent Performance
• Easier to use
• Easier to maintain
Value Proposition
In
d
an
7.1
More Built-in Global
Variables
XMLTABLE
More SQL Scalar
functions
OLAP Extensions
Omnifind
Encryption
enhancements
(FIELDPROCs)
V5R4
MySQL storage
engine
Result set support in
embedded SQL
WebQuery
DECFLOAT
SSD Memory
Preference
Grouping sets
/super groups
CURRENTLY
COMMITTED
INSERT in FROM
Global variables
Partitioned tables
On Demand
Performance Center
Named arguments
and defaults for
parameters
VALUES in FROM
UFT-8 and UTF-16
Health Center
Obfuscation of SQL
routines
ICU sort sequence
Extended Indicator
Variables
Array support in
procedures
e
nt
e
stm
SQE Stage 1
MQTs
Completion of SQL
Core
IASPs
Sequences
Scalar fullselect
Implicit char/numeric
Recursive CTE
6.1
Expression in
Indexes
ROW CHANGE
TIMESTAMP
BINARY/VARBINARY INSTEAD OF triggers Statistics catalog
Savepoints
views
GET DIAGNOSTICS Descriptor area
UNION in views
XA over DRDA
CLIENT special
DRDA Alias
Scalar subselect
registers
DDM
2-phase
DECIMAL(63)
UDTFs
SQE Stage 6
Scrollable cursor
DECLARE GLOBAL SQE Stage 3
DDM and DRDA
2M SQL statement
TEMPORARY
Ragged SWA
IPv6
TABLE
1000
tables
in
a
QDBRPLAY
Deferred Restore of
query
Catalog views
MQT and Logicals
Online Reorganize
2
© 2014 IBM Corporation
Environmental limits
MERGE
Three-part names
and aliases
requests
TRANSFER
OWNERSHIP
Array support in
UDFs
Timestamp precision
SQE Adaptive Query
Processing
Multiple-action
Triggers
EVI enhancements
Built-in Global
Variables
CREATE OR
REPLACE
TR-timed
enhancements
More Services
System Limits for IFS
Regression
Functions/Covarianc
High priority customer
e/Correlation
SQE Logical file
support
Inline functions
Pipeline
Row and Column
Access Control
CONNECT BY
ua
n
i
nt V5R3
o
C
Identity columns
v
o
n
on
i
t
a
7.2
XML Support
v
l In
V5R2
Next?
1.7 Terabyte Indexes
Navigator Graphing
and Charting
Journal_Info catalog
And more…
DB2 Family Products
InfoSphere Data Architect
Enterprise data modeling and
management
Optim Data Studio
DB2 Connect
.NET Plug ins - Entity Framework
Graphical development, deployment, and
debug of SQL procedures and functions
Design
Optimize Policies Develop
Models
Metadata
Operate
Deploy
InfoSphere Optim Test
Data Management
OmniFind Text Search Server
OmniFind
Text indexes and text search
Cognos
Business Intelligence software
• Streamlined test data management
Database
Change Data Capture
Database
Database
..
..
InfoSphere Change Data
Capture Enterprise replication
3
© 2014 IBM Corporation
…
Database
InfoSphere Guardium
Real-time Database Protection and Compliance
InfoSphere Guardium
Vulnerability Assessment
Helps secure data by recommending
best security practices
Previous Guardium IBM i Support
•
•
Guardium
Collector
GUI
IBM i
Scheduled
Guardium JDBC
Upload Process
Audit data in
DB2 for upload
to collector
Audit Data
captured via
journals
4
© 2014 IBM Corporation
Journaling is the primary vehicle for capturing audit data (for all vendors)
Network monitoring also provided
Journaling
DB2
Audited
Tables
Audited
Database
System Audit
Journaling
Database
changes
System
Activity
Guardium STAP for IBM i
Both QAUDJRN and SQL information is streamed to the Guardium collector
•
Global SQL Monitor using a view and instead of trigger captures SQL information and
puts them on a Unix queue
•
Audit server job
– Runs in batch
– Receives QAUDJRN audit entries and puts them on a Unix queue
– Runs a Guardium AIX executable in PASE to receive entries from the queue and
send them to the collector
– On an IPL we will restart automatically (you may have to start the subsystem)
5
© 2014 IBM Corporation
What’s special about Guardium V9.0 & DB2 for i?
Comprehensive database monitoring solution
Audit Journal, inbound and host-based SQL activity and Data Journals
SQL Statement Text with Bind Variables
Ability to reconstruct the complete SQL statement
Beyond Bind Variables, contextual detail an auditor needs is there
Real time monitoring
Enables real time action and reaction
Integrated IBM i solution
SWMA and strategic investment area
Extensive filtering capability
6
© 2014 IBM Corporation
Guardium STAP for IBM i
SQL monitoring based on INSTEAD OF triggers on a view.
Journaling over a view significantly reduces overhead and storage requirements
Elapsed Time Comparison
Storage Used Compatison
140000000
30
120000000
25
100000000
20
80000000
15
60000000
10
40000000
5
20000000
0
0
Traditional
Database
Monitor
View with
only 23
columns
View with
Instead of
Trigger
Traditional
Database
Monitor
View with
only 23
columns
View with
Instead of
Trigger
CPU overhead will be reduced even lower with IBM i
TR9 (GA – November 11, 2014
7
© 2014 IBM Corporation
Filtering Which Data to Capture
There are several places where audit data is filtered:
• Normal auditing controls filter what data goes into QSYS/QAUDJRN
• Filtering only specific Journal Entry Types can be configured
• Only certain journal entry types are processed and sent to Guardium
• Only certain attributes of the journal entries are processed and sent to Guardium
• The database monitor can filter data
• The same filtering available on STRDBMON can be configured
• Only certain monitor entries are processed and sent to Guardium
• Only certain attributes of the monitor entries are captured and sent to Guardium
• The Guardium collector can perform additional filtering via Policies.
For example:
• Only capture security failures
• Only capture failures for certain users or objects
• Etc. Etc. Etc.
8
© 2014 IBM Corporation
Database Monitor Filtering
Filtering option
Description
FILTER_USER
The specified user or group user profile filter, if any.
Only one user name or generic user name can be specified.
With TR9 (7.1 & 7.2) clients can provide from 1-10 filter names
The specified job filter, if any.
Only one job name or generic job name can be specified.
FILTER_JOB
FILTER_TCPIP
The specified TCP/IP filter, if any.
Only one TCP/IP address can be specified.
FILTER_TABLE
The specified table filter, if any.
Up to ten file names or generic file names can be specified. The specified library name must be the system schema name (10
character name). The file name can be either the system table name or table name (long or short name).
FILTER_PORT
The specified port filter, if any.
Only one port filter can be specified. Filtering by port is only supported in release 7.1 and above.
FILTER_CLIENT_ACCTING
The specified client accounting filter, if any.
Only one client accounting filter can be specified. Filtering by client accounting is only supported in release 7.1 and above.
FILTER_CLIENT_APPLNAME
The specified client application filter, if any.
Only one client application filter can be specified. Filtering by client application is only supported in release 7.1 and above.
FILTER_CLIENT_PROGRAMID
The specified client program filter, if any.
Only one client program filter can be specified. Filtering by client program is only supported in release 7.1 and above.
FILTER_CLIENT_USERID
The specified client user filter, if any.
Only one client user filter can be specified. Filtering by client user is only supported in release 7.1 and above.
FILTER_CLIENT_WRKSTNNAME
The specified client workstation filter, if any.
Only one client workstation filter can be specified. Filtering by client workstation is only supported in release 7.1 and above.
FILTER_RDB
The specified relational database filter, if any.
Up to 10 relational database names can be specified.
FILTER_SYSTEM_SQL
The specified system SQL statement filter.
Specifies whether system SQL statements should be audited (Y or N) . The default is Y.
FILTER_AUDIT_ENTRY_TYPES
The specified QAUDJRN audit entry filter, if any.
Specifies which audit journal entry types should be processed.
The default is 'AD AF CA CO CP DO GD OM OR OW PG PW RA RO RZ ZC ZR'
9
© 2014 IBM Corporation
Monitor Entry Type Filtering
Some of the entries are strictly of interest for isolating performance and response time problems.
These entries are omitted: (not sent to the Guardium appliance)
CH CLOSE (Hard)
HC CLOSE (Hard)
HH CLOSE (Hard)
JR Recycle Prestart Job
PD PREPARE DESCRIBE (except authorization errors are captured)
PR PREPARE (except authorization errors are captured)
ZD HOST SERVER ONLY
Some of the DBMON entries are not useful from an audit standpoint.
These entries are omitted: (not sent to the Guardium appliance)
AD ALLOCATE DESCRIPTOR
HL HOLD LOCATOR
CL CLOSE
RE RELEASE
DA DEALLOCATE DESCRIPTOR
RG RESIGNAL
FE FETCH
SC SET CONNECTION
FL FREE LOCATOR
SD SET DESCRIPTOR
GD GET DIAGNOSTICS
SG SIGNAL
GS GET DESCRIPTOR
Xx Entries (-primarily X/Open)
Some statements are sub-statements of another statement, these are also omitted: (not sent)
IN INSERT that is part of a CREATE TABLE or DECLARE GLOBAL TEMPORARY TABLE AS ... WITH DATA
DL DELETE that is part of a MERGE
IN INSERT that is part of a MERGE
UP UPDATE that is part of a MERGE
10
© 2014 IBM Corporation
QAUDJRN Filtering
The audit entries that we capture can be controlled by configuring which entry types should be captured.
By default, the following are returned:
ZR Read object
ZC Change object
CA Authority change
CP Change Profile
AD Auditing change
AF Authority failure
CO Create object
DO Delete object
GR General purpose audit record
OM Object moved or renamed
PG Primary group change
PW Invalid password or user ID
OW Change owner
OR Object restored
RA Restore authority change
RO Restore owner change
RZ Restore primary group change
SV System Value change
Note: CD Command string is supported, but not by default
Since the focus is primarily on database changes, we will only return entries related to objects that are database specific:
*FILE (a table, view, index, logical file, alias, or device file)
*SQLUDT (an SQL user-defined type)
*SQLPKG (an SQL package)
*PGM (a procedure, function, or program)
*SRVPGM (a procedure, function, global variable, or service program)
*DTAARA (an SQL sequence)
For entries that identify an object, the statement text part of the message will be constructed as follows:
XX - 30-byte-text 10-byte-library 10-byte-object-name 8-byte-object-type
For example:
ZC - Change object
11
© 2014 IBM Corporation
<library-name> <file-name>
*FILE
Attribute filtering
Audit Data
SQL Monitor
Audit Journal
Job name
Job user
Job number
Start time
End time
SQLSTATE
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Always the same as the Start time
Yes
08001 for invalid password (PW) and audit records (GR)
42501 for authority failure (AF)
00000 everything else
SQLCODE
Yes
-30080 for invalid password (PW) and audit records (GR)
-551 for authority failure. (AF)
0 everything else
SQL statement
SQL variables
Interface
Client application name
Client user ID
Client workstation
Client accounting
Client program
Current user
Thread ID
Program schema
Program name
Client IP Address
Local or server port number
RDB name
Number of rows
Yes – limited to 60K
No - basic journal entry description instead
Yes - limited to 1000 bytes
No
Yes
Always QAUDJRN
Yes,
No
Yes
No
Yes
No
Yes
No
Yes
No
Yes
Yes
Yes
Yes
Yes, if the statement is executed from a PGM or SRVPGM
Yes, if the statement is executed from a PGM or SRVPGM
Yes, if the statement is executed from a PGM or SRVPGM
Yes, if the statement is executed from a PGM or SRVPGM
Yes
Yes
Yes
Yes
Yes
Yes
Yes, only for INSERT, DELETE, UPDATE, MERGE, OPEN*, VALUES
INTO, CREATE TABLE AS, DECLARE GLOBAL TEMPORARY
TABLE AS, and SET VARIABLE
No
12
© 2014 IBM Corporation
Audit Server Status information
STATUS_TIME
Timestamp of this request for status
SERVER_STARTED
Indicates whether the server is currently started or not (YES or NO)
START_TIME
Timestamp of the last time the server was started
SERVER_JOB
Job name of the server
NUMBER_JOBS_AUDITED_USING_SQL
Number of jobs that have processed an SQL statements since the server was started
NUMBER_PROCESSED_SQL_STATEMENTS
Number of SQL statements that have been processed since the server was started. This does not include SQL
statements filtered out by STRDBMON
NUMBER_ENQUEUED_SQL_STATEMENTS
Number of SQL statements that have been enqueued since the server was started. This does not include SQL statements
filtered out.
NUMBER_SKIPPED_SQL_STATEMENTS
Each job will attempt to put an SQL statement on the queue up to three times. This indicates that some number of
SQL statements could not be audited. Rare…typically indicates a queue problem.
NUMBER_PROCESSED_VARIABLE_SETS
Number of SQL statements that have variables since the server was started. Some SQL statements have variables and some
not. This does not include SQL statements filtered out.
NUMBER_SKIPPED_VARIABLE_SETS
Number of SQL statements whose variable values were discarded. Since the variable values are written to the
monitor PRIOR to the actual SQL statement, It is possible that several sets of variables will have to be saved until
the SQL statement shows up. Up to 300 sets of variables are saved so this is extremely unlikely.
NUMBER_PROCESSED_QAUDJRN_ENTRIES
Number of journal audit entries that have been processed since the server was started. This does not include audit entries
filtered out.
NUMBER_ENQUEUED_QAUDJRN_ENTRIES
Number of journal audit entries that have been enqueued since the server was started. This does not include
entries filtered out.
NUMBER_SKIPPED_QAUDJRN_ENTRIES
Number of journal audit entries that were discarded. This indicates that some number of audit journal entries could
not be audited. Rare…typically indicates a queue problem.
QUEUE_DAMAGED
Indicates whether or not the queue is damaged. (YES or NO)
NUMBER_MESSAGES_ON_QUEUE
Number of messages currently on the queue
SIZE_OF_MESSAGES_ON_QUEUE
Size of the queue
MAXIMUM_SIZE_OF_QUEUE
Maximum size of the queue (this is always 16 meg)
TOTAL_ENQUEUING_THREADS
Total number of threads enqueuing messages
LAST_DEQUEUE_TIME
Timestamp of the last dequeue operation
LAST_ENQUEUE_TIME
Timestamp of the last enqueue operation
QUEUE_OWNER
Owner of the queue. This is the user that started the server
LAST_END_MONITOR_JOB
Job of the user that last ended the monitor
LAST_END_MONITOR_USER
User that last ended the monitor
13
© 2014 IBM Corporation
Guardium Database Activity Monitoring & DB2 for i
IBM i
TR5
IBM i
TR6
2013
IBM i
TR7
SF99701
Level 18
SF99601
Level 28
2014
SF99701
Level 26
SF99601
Level 31
SF99701
Level 24
SF99601
Level 30
SF99702
Level 1
•
Tracing Support Added
•
Serviceability Guide created
Product GA
• InfoSphere Guardium V9.0
DAM & DB2 for i
• White Paper published
• Promotional video on YouTube
IBM i
TR8
7.2 – GA
•
Add support for:
PREVENT_SKIPPED_ENTRIES
Enhanced Audit
Server status
Enhanced CP
journal detail
•
•
•
Guardium DAM
Supported with IBM i 7.2
Add AX audit entry
Enhanced PW
journal detail
Add micro-seconds
to Activity entity
reports
Tech Talk Videos –
Implementing a data protection
and compliance solution for IBM i
Guardium Data Monitoring - DB2 for i fact page
https://ibm.biz/GuardiumDAMonIBMi
14
© 2014 IBM Corporation
Best Practices
•
•
•
•
Capture only what you need by using filtering
Filtering at the IBM i is better than filtering at the Guardium collector
Create a new user profile and a unique job description for the audit server batch job
Create a unique group profile and use it to audit your highly privileged users
15
© 2014 IBM Corporation
Alert On Failed Login (Exception Rule)
APPUSER is
generic service
account
Exception Type
= Failed Login
Min Count =
How often
Reset Interval =
Between what
time period
Action = Alert
So… Alert on 3
Failed Login
attempts from
the same user if
they occur 3
times within a 5
minute interval
You should not have 3 failed logins on a production system!
16
© 2014 IBM Corporation
Guardium Database Activity Monitoring & DB2 for i
2014
IBM i
TR8
SF99701
Level 29
SF99601
Level 32
Guardium DAM for i improvements:
• Reduced CPU overhead for aggressive
monitoring of SQL
• Multiple User Name SQL Filtering
• Guardium on i Technical article
• Other High Priority feature requests
17
© 2014 IBM Corporation
IBM i
7.1 TR9
7.2 TR1
SF99702
Level 3
SF99701
Level 32
SF99601
Level 33
2015
???
Guardium for i – Possible Future Enhancements
• Guardium Vulnerability Assessment (VA) support for IBM i
• Guardium Classifier support for IBM i
• Add micro-seconds to exception entity reports
• Improved “out of the box” IBM i Activity and Exception
reports
• Enhancements requested by PoC or clients
Guardium Data Monitoring - DB2 for i fact page
https://ibm.biz/GuardiumDAMonIBMi
Contrasting DB2 for i - Data Compliance
Technology
Guardium Activity
Monitor & DB2 for I
Audit Journal
Data Journal
6.1, 7.1, 7.2
6.1, 7.1, 7.2
6.1, 7.1, 7.2
InfoSphere Guardium
Security ISVs
&
InfoSphere Guardium
Security ISVs
Solution infrastructure
beyond IBM i
Yes
No
No
Capture SQL statements
Yes
No
No
Capture SQL host variable
values and environment
Yes
No
No
Capture database specific
Audit Journal details
Yes
Yes
No
Capture before and after
images of data
No
No
Yes
Able to track which rows
are seen by users
No
No
No
Success factors include:
Strategy, Tuning &
Consulting
Yes
Yes
Yes
Use case
Supported IBM i OS
releases
Analysis & Reporting
18
© 2014 IBM Corporation
Contrasting DB2 for i - Data Security
Technology
Field Procedures
Column
Masks
Row
Permissions
Views &
Logical Files
Use case
Supported IBM i OS
releases
7.1, 7.2
7.2
7.2
6.1, 7.1, 7.2
Limit access to some/all
data within a column
Yes
Yes
No
Yes
Limit access to rows
No
No
Yes
Yes
External program
(complex)
SQL rule
(simple)
SQL rule
(simple)
DDS or SQL
(varies)
• Townsend Security
• Linoma
• Enforcive
None at this
time
None at this time
N/A
Data encrypted at rest
Yes
No
No
No
Data encrypted in journal
Yes
No
No
No
Masked values apply to
selection criteria
Yes
No
N/A
N/A
Data-Centric Solution
Yes
Yes
Yes
No
Success factors include:
Strategy, Tuning &
Consulting
Yes
Yes
Yes
No
Security logic payload
(customer experience)
Software Vendor
component
19
© 2014 IBM Corporation
Contrasting DB2 for i - Data Security
DB2 for i Data Security options go well beyond the data model.
Open Database File (QIBM_QDB_OPEN) exit program is called when a
when a job is opening a database file. Use this interface to deploy blocking
and more.
Query Governor protects against over consumption of CPU or Storage
http://www-01.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_72/apis/xqrygovr.htm
IBM i Function usage IDs provide a granular role based security
authorization (allow or deny) based upon users or groups.
https://www.ibm.com/developerworks/community/wikis/home?lang=es#!/wiki/IBM%20i%20Technology%20Updates/p
age/DB2%20for%20i%20Security%20Enhancements
IBM i has exit programs for connection interfaces and commands. The exit
program can block or log or more.
20
© 2014 IBM Corporation
Field Procedures and Data Encryption
Create a CUSTOMER table where the CCNBR column has a FIELDPROC.
Allows for transparent
encryption or
encoding/decoding of data
on SQL or native interfaces
CREATE TABLE CUSTOMER (
NAME VARCHAR(50),
ADDRESS VARCHAR(100),
CCNBR CHAR(16) FIELDPROC ENCRYPTLIB.ENCRYPTPGM1 )
Alter an existing CUSTOMER table to add a FIELDPROC to the CCNBR column.
ALTER TABLE CUSTOMER
ALTER COLUMN CCNBR SET FIELDPROC ENCRYPTLIB.ENCRYPTPGM1
White Paper: Protecting IBM i data with encryption
http://www-304.ibm.com/partnerworld/wps/servlet/ContentHandler/whitepaper/ibmi/db2/encryption
Articles:
Enable Transparent Encryption with DB2 Field Procedures
www.mcpressonline.com/database/db2/enable-transparent-encryption-with-db2-field-procedures.html
DB2 Field Procedures Finally Support Conditional Masking
www.mcpressonline.com/rpg/db2-field-procedures-finally-support-conditional-masking.html
21
© 2014 IBM Corporation
Guardium STAP for IBM i
Both QAUDJRN and SQL information is streamed to the Guardium collector
Global SQL Monitor using a view and instead of trigger captures SQL information
and puts them on a Unix queue
Audit server job
– Runs in QBATCH (or your choice of subsystem)
– Receives QAUDJRN audit entries and puts them on a Unix queue
– Runs a Guardium UNIX executable in PASE to receive entries from the queue
and send them to the collector
– On an IPL it will restart automatically (you may have to start the subsystem)
Se
cu r
e lo
ggi
ng
22 22
© 2014 IBM Corporation
InfoSphere Guardium
Database Activity
Monitor (DAM) &
DB2 for i
Leading Medical Practice,
Research, and Education
Institution supports data
security and compliance
Need
• Requirement to demonstrate SOX compliance to
ensure the integrity of financial reporting data
stored in DB2 for i
• Previous vendor tool was unable to monitor local
connections to the database, exposing sensitive
data to privileged user access
Benefits
• Provides comprehensive activity monitoring for
both SQL and native access to DB2 for i data
• Monitors privileged users, embracing separation of
duties
• Centralizes and automates audit controls and
regulatory reporting
23
23
© 2014 IBM Corporation
• Provides proactive security via real-time alerts for
critical events without affecting performance or
requiring changes to databases or applications
DB2 for i - 7.2 Enhancements by role - Security
New Boss Option
New SQL Statements for security
–
–
–
–
–
–
IBM Advanced Data Security for i
CREATE PERMISSION
ALTER PERMISSION
CREATE MASK
ALTER MASK
ALTER TRIGGER
TRANSFER OWNERSHIP
(Boss option 47)
No Charge
New tools in the toolbox…
New Built-in Function
– VERIFY_GROUP_FOR_USER()
New Function Usage ID
– QIBM_DB_SECADM
New Catalogs
– QSYS2/SYSCONTROLS
– QSYS2/SYSCONTROLSDEP
24
© 2014 IBM Corporation
New Journal Entry Types
For journal code D - Database file:
M1, M2, M3 for create/drop/alter mask
P1, P2, P3 for create/drop/alter permission
For journal code T – Audit trail:
AX for Row and Column Access Control
X2 for Query manager profile changes
DB2 for i RCAC Redpaper
www.redbooks.ibm.com/redpieces/abstracts/redp5110.html
25
© 2014 IBM Corporation
© Copyright IBM Corporation 2014
25
DB2 for i RCAC Workshop
• New offering from the STG Lab Services team
• Four day facilitated workshop led by the DB2 for i Center of Excellence
including the following:
– Review of the current state, current requirements, and future
requirements for managing data access
– Education on possible solutions and related best practices for
their implementation
– Discussion and formulation of a strategic roadmap for
implementation
• For more information, contact mcain@us.ibm.com
26
© 2014 IBM Corporation
© Copyright IBM Corporation 2014
26
Competition at a glance…
Guardium Database Activity Monitor vs Competition**
Audit Journal and Data Journal coverage (tie)
ODBC requests vs All SQL activity (win)
SQL Statement Text vs
SQL Statement Text with Bind Variables (win)
Integrated IBM i solution vs External monitoring (win)
Extensive filtering capability (win)
One software product to handle all databases vs IBM i specific solution (win)
** - several competitors and they are all unique in what they provide and how they provide it
27
© 2014 IBM Corporation