—
BMC Senior Technical Consultant
Data Management
Mar. 2017
Roberto Cason
Now you see it, Now you don’t
Implementing DB2 row access control
DB2 management
Database Administrator activities
Which is the most important ?
DBA
Mainframe survey 2016 : TOP Priorities
Direttiva Bankit 263
3. La sicurezza delle informazioni e delle risorse ICT
La sicurezza delle informazioni e delle risorse informatiche è garantita
attraverso misure di protezione a livello fisico e logico,........................
7. La disponibilità delle informazioni e delle risorse ICT
La disponibilità dell’accesso a dati e dei servizi telematici è garantita agli
utenti autorizzati in orari e con modalità conformi alle esigenze .................
..... in relazione alle esigenze di disponibilità delle singole applicazioni, sono
definite procedure di backup ..............
....... garantire l’alta disponibilità delle applicazioni maggiormente critiche, in
sinergia con le procedure e il sistema di disaster recovery
....... Il sistema di registrazione e reporting dei dati è deputato a tracciare
tempestivamente tutte le operazioni aziendali e i fatti di gestione al fine di
fornire informazioni complete e aggiornate sulla attività aziendali
PCI-DSS Certification
(Payment Card Application Data Security Standard)
The PCI Security Standards Council touches the lives of hundreds of millions of
people worldwide. A global organization, it maintains, evolves and promotes
Payment Card Industry standards for the safety of cardholder data across the
globe.
https://www.pcisecuritystandards.org/pci_security/
How DB2 treat security
Who & What access data ?
• Transactions
• Batch jobs
• Data Reporting
• SubSystems
• TSO
• Jdbc/odbc
• Database tools
Who access data ?
• Data segregation
• Need to know
• Access control
• Encryption
• Data Masking
• Audit features
How to keep the control ?
Why access DB2 data in Production ?
• Real time data inquiry
• Datawarehouse is 1 day later
• Fraud detections
• Bank branch closures
• Investigation on account operations ( Data discovery )
Data segregation requests
• Application must access only data that it owns
• Answer to Company organization issues
• Restrict users from storing documents in the wrong place
Which is the right choice to realize it ?
Physical segregation schema
A
A
B
B C
C
Logical segregation schema
Logic filter
A
A
B
B
C
C
Data segregation : which better ?
ISSUE Physical Logical
Multi schema structures
Number of objects
DBA effort
Custom cloning process
Database catalogs dimension
Error impact
Operative Risks
The goal
My company ask me to evaluate the control of table access at
row level instead of filter accesse throw logical view.
Two features :
Row Access Control - via DB2
Multi Level Security - via Racf
DB2 Row Access Control : Row Permission
A row permission is a database object that describes a specific
row access control rule for a table.
In the form of an SQL search condition, the rule specifies the
conditions under which a user, group, or role can access the rows
of data in the table.
User case example
BANK_CODE COLA COLB COLC
AA10 11111 11111 11111
BBBB 00000 00000 00000
AA10 22222 22222 22222
BBBB 00000 00000 00000
ZZZZ 00000 00000 00000
AA10 33333 33333 33333
BANK_CODE
AA10
User of bank AA10 can view only records AA10
USERAA10
DB2 Row Access Control implementation
1 - Internal Policy definition
2 - Create DB2 permission
3 - Activate permissions
4 - Test and measurements
Internal Policy definitions
• Define racf Group
• Association User to Group
• Assign Permit racf
• Grant DB2
Create db2 permission objects
CREATE PERMISSION ABCDAA10 ON tablename
FOR ROWS WHERE
VERIFY_GROUP_FOR_USER(SESSION_USER,‘GROUPAA10') = 1
AND BANK_CODE = 'AA10'
ENFORCED FOR ALL ACCESS ENABLE ;
In RACF®, you can define group profiles.
A group profile defines a group of users.
ADDGROUP GROUPAA10
CONNECT USERAA10 GROUP(GROUPAA10)
GROUPAA10
USERAA10
Activate Row Permission
ALTER TABLE tablename ACTIVATE ROW ACCESS CONTROL;
Immediate effects :
• Allow only accesses that satisfy existing permissions
• If NO permission defined NO access
• All dependent packages become invalid
• All future new permissions will invalid the packages
Activate Row Permission
The table must not be one of the following tables:
• A created temporary table
• A table that is directly or indirectly referenced in the definition of a
materialized query table
• A table that has a security label column
• A System-period temporal table
• A history table
• An archive-enabled table
• An archive table
... Continue pag 1324 DB2 V12 SQL Reference guide
Tests and measurements process
• Create sample database DBPERMIS ( 21 tables )
• Load data from Production
• Runstats
• Query for CREATE/DROP/ACTIVE massive Permission
• Create a sample workload
• Run and analyze workload before activate RAC
• Activate RAC
• Run and analyze workload after activate RAC
• Try access with other Userid ( SQLCODE -20471 )
Query for CREATE massive PermissionsBROWSE MVSRYC.SPUFI.OUTPUT Line 0000000000 Col 001 080
********************************* Top of Data **********************************
---------+---------+---------+---------+---------+---------+---------+---------+
SELECT
'CREATE PERMISSION ABCD'!!STRIP(NAME)!!' ON QUAL.'!!STRIP(NAME)!! 'QUAL.
'!!STRIP(NAME)!! 'FOR ROWS WHERE VERIFY_GROUP_FOR_USER(SESSION_USER,''GROUPAA10'') = 1
AND COD_EMPRESA = ''AA10'‘ENFORCED FOR ALL ACCESS ENABLE;'
FROM SYSIBM.SYSTABLES
WHERE TYPE = 'T' AND DBNAME = 'DBPERMIS' ;
---------+---------+---------+---------+---------+---------+---------+---------+
---------+---------+---------+---------+---------+---------+---------+---------+
CREATE PERMISSION ABCDTDBHIB04 ON QUAL.TDBHIB04QUAL.TDBHIB04FOR ROWS WHERE
VERIFY_GROUP_FOR_USER(SESSION_USER,''GROUPAA10'') = 1 AND COD_EMPRESA = ''AA10'' ENFORCED FOR ALL
ACCESS ENABLE;
CREATE PERMISSION ABCDTBCCH1 ON QUAL.TBCCH1QUAL.TBCCH1FOR ROWS WHERE
VERIFY_GROUP_FOR_USER(SESSION_USER,''GROUPAA10'') = 1 AND COD_EMPRESA = ''AA10'' ENFORCED FOR ALL
ACCESS ENABLE;
CREATE PERMISSION ABCDTBSAMPLE ON QUAL.TBSAMPLEQUAL.TBSAMPLEFOR ROWS WHERE
VERIFY_GROUP_FOR_USER(SESSION_USER,''GROUPAA10'') = 1 AND COD_EMPRESA = ''AA10'' ENFORCED FOR ALL
ACCESS ENABLE;
CREATE...............
Query for DROP massive Permission
BROWSE MVSRYC.SPUFI.OUTPUT Line 0000000000 Col 001 080
********************************* Top of Data *********************************
---------+---------+---------+---------+---------+---------+---------+---------
SELECT 'DROP PERMISSION ABCD'!!STRIP(NAME)!!' ;'
FROM SYSIBM.SYSTABLES
WHERE TYPE='T' AND DBNAME='DBPERMIS' ;
---------+---------+---------+---------+---------+---------+---------+---------
---------+---------+---------+---------+---------+---------+---------+---------
DROP PERMISSION ABCDINPUTTABLES ;
DROP PERMISSION ABCDTBCCH1 ;
DROP PERMISSION ABCDTBCCH2 ;
DROP PERMISSION ABCDTBCCH3 ;
DROP PERMISSION ABCDTBCCH4 ;
DROP PERMISSION ABCDTBCCH5 ;
DROP PERMISSION ABCDTBCCH6 ;
DROP PERMISSION ABCDTBPG1 ;
DROP PERMISSION ABCDTBPG2 ;
DROP...............
Query for ACTIVATE massive Permission
BROWSE MVSRYC.SPUFI.OUTPUT Line 0000000000 Col 001 080
********************************* Top of Data **********************************
---------+---------+---------+---------+---------+---------+---------+---------+
SELECT
'ALTER TABLE OWNER.'!!STRIP(NAME)!!' ACTIVATE ROW ACCESS CONTROL;'
FROM SYSIBM.SYSTABLES WHERE TYPE='T'
AND DBNAME = 'DBPERMIS'
WITH UR
---------+---------+---------+---------+---------+---------+---------+---------+
---------+---------+---------+---------+---------+---------+---------+---------+
ALTER TABLE OWNER.INPUTTABLES ACTIVATE ROW ACCESS CONTROL;
ALTER TABLE OWNER.TBCCH1 ACTIVATE ROW ACCESS CONTROL;
ALTER TABLE OWNER.TBCCH2 ACTIVATE ROW ACCESS CONTROL;
ALTER TABLE OWNER.TBCCH3 ACTIVATE ROW ACCESS CONTROL;
ALTER TABLE OWNER.TBCCH4 ACTIVATE ROW ACCESS CONTROL;
ALTER TABLE OWNER.TBCCH5 ACTIVATE ROW ACCESS CONTROL;
ALTER . . . . . . .
Rebind all dependents packages
Create Permission
If row access control is currently activated for the table, the row
permission becomes effective immediately and all packages and dynamic cached statements that reference the table are invalidated.
….
( Pag. 1628 - DB2 V12 SQL Reference )
SQLCODE -904 (resource not available)
Create sample worloads
//SUNDBP01 JOB MSGCLASS=8,CLASS=E
//STEP01 EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB DD DISP=SHR,DSN=....SDSNLOAD
//SYSTSIN DD *
DSN GROUPAA10(DBD)
RUN PROGRAM(DSNTEP4) PLAN(DSNTEP4) -
LIB('EDB.PUN.DB2LOAD')
END
//SYSIN DD DSN=A016891.PUN.SOURCE(SELECT),DISP=SHR
//SUNDBP02 JOB MSGCLASS=8,CLASS=E
//STEP01 EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB DD DISP=SHR,DSN=....SDSNLOAD
//SYSTSIN DD *
DSN GROUPAA10(DBD)
RUN PROGRAM(DSNTEP4) PLAN(DSNTEP4) -
LIB('EDB.PUN.DB2LOAD')
END
//SYSIN DD DSN=A016891.PUN.SOURCE(SELECT),DISP=SHR
//SUNDBP03 JOB MSGCLASS=8,CLASS=E
..............................................
JCL Source
Executes 50 jobs scheduled 5 TimesSELECT * FROM A016891.T04ANS72 WHERE COD_EMPRESA='AA10'
FETCH FIRST 1 ROWS ONLY WITH UR ;
SELECT * FROM A016891.T04ANA07 WHERE COD_EMPRESA='AA10'
FETCH FIRST 1 ROWS ONLY WITH UR ;
SELECT * FROM A016891.T04ATP04 WHERE COD_EMPRESA='AA10'
FETCH FIRST 1 ROWS ONLY WITH UR ;
SELECT * FROM A016891.T04CEC06 WHERE COD_EMPRESA='AA10'
FETCH FIRST 1 ROWS ONLY WITH UR ;
…………..
21 selects X 50 jobs X 5 times = 5.250 queries
Input dataset 1 row result set
SELECT * FROM A016891.T04ANS72 WHERE COD_EMPRESA='AA10'
FETCH FIRST 1000 ROWS ONLY WITH UR ;
SELECT * FROM A016891.T04ANA07 WHERE COD_EMPRESA='AA10'
FETCH FIRST 1000 ROWS ONLY WITH UR ;
SELECT * FROM A016891.T04ATP04 WHERE COD_EMPRESA='AA10'
FETCH FIRST 1000 ROWS ONLY WITH UR ;
SELECT * FROM A016891.T04CEC06 WHERE COD_EMPRESA='AA10'
FETCH FIRST 1000 ROWS ONLY WITH UR ;
…………..
Input dataset 1000 row result set
RAC test Results ( fetch first 1 row )Running 5250 queries with result set of 1 row simulating an online transaction workload.
Test TRACE file name Descrizione CPU db2 di Classe 2 in Sec.
1 IT41524.TRACE.RAC.NO RAC NOT active 0.005064
2 IT41524.TRACE.RAC.NO2 RAC ACTIVE and Permission defined 0.007555
3 IT41524.TRACE.RAC.NO3 RAC NOT active 0.005389
0.005064
0.007555
0.005389
RAC NOT ACTIVE RAC ACTIVE RAC NOT ACTIVE
1 2 3
Avg CPU (Sec.)
+50%
RAC test Results ( fetch first 1000 row )Running 5250 queries with result set of 1000 row simulating an online transaction workload.
Test TRACE file name Descrizione CPU db2 di Classe 2 in Sec.
1 IT41524.TRACE.RAC.YES1 RAC NOT active 0.75361
2 IT41524.TRACE.RAC.YES2RAC ACTIVE permission defined and
fetch 1000 rows0.769516
3 IT41524.TRACE.RAC.YES3 RAC NOT active 0.7531
+2,1%
From IBM labs
Labs estimate a workload throughput regression of 2.1% . More overhead during prepare
rather than runtime.
DB2 10 for z/OS
Performance Topics
Risk evaluations
• CPU overhead depends by• number of rows
• number of accesses
• number and complexity of the permissions defined
Notes :
If you decide to implement logical data segregation, row permissions must be defined on all
tables of an application.
Row.Access.Control final test evidences
• Single overhead for query 2-3 msec.
• Considering this cost for single TP transaction
• Suppose 1 Millions transactions per Hour
• Operative risks
• Enviroment considerations
Advantages Costs
Thank you !
Row Permission control
-20471
THE INSERT OR UPDATE IS NOT ALLOWED BECAUSE A RESULTING ROW DOES NOT SATISFY ROW
PERMISSIONS.
Explanation: Row access control is enforced for the object of the insert or update operation. Consequently, all
attempts to insert or update rows in that table are checked to ensure that the resulting rows conform to the
row permissions defined for that table.
GROUPAA10 action: The statement cannot be processed. No insert or update operations were performed, and
the
contents of the table remain unchanged.
Programmer response: Examine the definitions of the row permissions to determine why the requested insert
or update operation was not successful. This error might be a data-dependent condition.
Security strenghts
In V12 Unload introdotto controllo di auth sull unlaod come per select
Con zparm AUTH_COMPATIBILITY RETROFI IN v11 CON pi55706
Row.Access.Control test evidences
• Single overhead for query 2-3 msec.
• Considering this cost for single TP transaction
• Suppose 3 Millions transactions per Hour
Calculation of Mips overhead per hour with RAC :
Avg cpu 2 msec. * 3Mil. = 6000 sec/h
Then
Mips = 6000 sec.h / 3,25 = 1846 ??????
(3,25) internal coefficient to transform Seconds to Mips
DB2 Row Access Control implementation
1. Internal policy definition
2. Create db2 permission
(Using Built in Function integrated with Racf )
3. Activate permissions
4. Test and measurements
Top Related