row_access_control_example.sql

CREATE OR REPLACE TABLE LIB.ROW_ACCESS
( 
	FIRMA FOR COLUMN FIR CHAR(3) NOT NULL, 
	FAKTURIERSTELLE FOR COLUMN FAK CHAR(3) NOT NULL, 
	TABELLE FOR COLUMN TBL VARCHAR(128) ALLOCATE(10) DEFAULT NULL, 
	BENUTZER FOR COLUMN USR CHAR(10) NOT NULL, 
	CONSTRAINT LIB.ROW_ACCESS_PRIMARY_KEY PRIMARY KEY( FIRMA, FAKTURIERSTELLE, TABELLE, BENUTZER)
)   
RCDFMT RA00; 
  
LABEL ON TABLE LIB.ROW_ACCESS IS 'Berechtigungstabelle für RCAC'; 
  
LABEL ON COLUMN LIB.ROW_ACCESS 
(
	FIRMA IS 'Fir', 
	FAKTURIERSTELLE IS 'Fak', 
	TABELLE IS 'Tabelle             Name', 
	BENUTZER IS 'Benutzer'
); 
  
LABEL ON COLUMN LIB.ROW_ACCESS 
(
	FIRMA TEXT IS 'Firma',
	FAKTURIERSTELLE TEXT IS 'Fakturierstelle',
	TABELLE TEXT IS 'Tabellenname',
	BENUTZER TEXT IS 'Benutzername'
); 


CREATE OR REPLACE PERMISSION LIB/XXX_PERMISSION_1 ON LIB/XXX AS XXX 
	FOR ROWS WHERE EXISTS ( SELECT 1 FROM LIB.ROW_ACCESS RA WHERE RA.FIRMA IN (XXX.FIR , '*') AND RA.FAKTURIERSTELLE IN (XXX.FAK , '*') 
                                AND RA.TABELLE IN ('*' , 'XXX') AND RA.BENUTZER = SESSION_USER) 
                        OR QSYS2.VERIFY_GROUP_FOR_USER (SESSION_USER , 'GROUP')    
	ENFORCED FOR ALL ACCESS;

ALTER TABLE LIB.XXX ACTIVATE ROW ACCESS CONTROL;
--ALTER TABLE LIB.XXX DEACTIVATE ROW ACCESS CONTROL;