DB2 Row access control - DUGIdugi.molaro.be/wp-content/uploads/2017/03/DB2-Row-access-control… ·...

—

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


ACCESS ENABLE;

CREATE PERMISSION ABCDTBSAMPLE ON QUAL.TBSAMPLEQUAL.TBSAMPLEFOR ROWS WHERE


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

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


//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


..............................................

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'


SELECT * FROM A016891.T04ATP04 WHERE COD_EMPRESA='AA10'


SELECT * FROM A016891.T04CEC06 WHERE COD_EMPRESA='AA10'


…………..

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'


SELECT * FROM A016891.T04ANA07 WHERE COD_EMPRESA='AA10'


SELECT * FROM A016891.T04ATP04 WHERE COD_EMPRESA='AA10'


SELECT * FROM A016891.T04CEC06 WHERE COD_EMPRESA='AA10'


…………..

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

DB2 Row access control - DUGIdugi.molaro.be/wp-content/uploads/2017/03/DB2-Row-access-control… ·...

Documents

Transcript of DB2 Row access control - DUGIdugi.molaro.be/wp-content/uploads/2017/03/DB2-Row-access-control… ·...