Geschäftslogik in der Datenbank Umstellung eines Kernbanksystems Michael Meyer Lead Database Architect November 2010 DOAG-Konferenz 2010, Nürnberg Geschäftslogik in der Datenbank – Umstellung eines Kernbanksystems Berenberg Bank auf einen Blick Das Projekt Geschäftslogik in der Datenbank Beispiele • Oracle Types (Grundideen, Beispiele: Kursversorgung, Kunde & Konto, Fulfilment, §24c KWG) • Standardfelder- und Trigger • Regelmäßige Reorganisation von Tabellen • Kapselung von PL/SQL (Zugriffe aus Cobolprogrammen) • Freigabesystem • Saldenermittlung • Reporterstellung (PDF und Excel aus der Datenbank) • Asynchrone Prozesse „Bonustrack“ • Data-Masking für Entwicklungsdatenbanken • Steuerung von Sichtbarkeiten mit der Virtual Private Database (Column-Level-Security) 2 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Berenberg Bank auf einen Blick 3 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Geschäftslogik in der Datenbank – Umstellung eines Kernbanksystems Das Projekt 4 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Das Projekt - Rahmendaten • Abzulösendes System • cobolbasiertes Altsystem • hierarchisches Datenbankmodell • Daten in der Oracle-Datenbank und in diversen sequentiellen und indizierten Dateien gespeichert • Batchorientierte Verarbeitung (kein STP vorhanden) • Terminalemulation als Frontend (4-GL) • Teilprojekte • Kernbank (KB) • Wertpapier (WP) • Staffing • Fachbereich • Organisatoren • Architekten (5), Entwickler (Datenbank: 20+, GUI (Java-Swing, .NET): ca. 15) • QS und Test • geplantes Ende des Projektes • 2012 • Anzahl Objekte (Stand: Juli 2010) • ca. 1600 Tabellen • ca. 1,6 Mio lines of code, davon 40% generiert 5 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Das Projekt – ein typische Teilprojektorganigramm LENKUNGSAUSSCHUSS Auftraggeber / Sponsor FB: xxxxx ORG/IT: xxxxx Projektleitung BBS -Kernbank Projektleitung / Architekturmanagemetn ORG/IT: xxxxx Fachbereichko ordinator QS Rechenzentrum Abhängigkeiten/ Konflikte, Architektur, technischen Paradigmen Projekt-Team Systemverantwortlicher (ORG/IT) PL/SQL xxxxxx Themenverantwortliche aus den Fachbereichen Thema Devisenhandel PL/SQL xxxx (GUI, GUI-Types) PL/SQL xxxx PL/SQL xxxx Erfassung u. Abwicklung gem. Statusmod online Limitausnutzung (FGS, SWIFT, Sync., Batch) SWIFT xxxxxx (XML für Belege, ext. Systeme) (SWIFT u. OBS Anbindung) Fulfillment xxxx (Beleg Erstellung u. Versand) Phase I (Details siehe Projektplan) PL/SQL xxxxx online Devisenposition Anbindung SWIFT u. OBS CLS-Abwicklung ZKV u. MKP Nostrodisposition 6 06.10.2010 ASP xxxxx (CRM und . Berechtigungen) Access xxxxx (Meldewesen u. Controlling) Schnittstellen P-Net, MarketM. etc. Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Phase II (Details siehe Projektplan) (Statusm., ext. Syst. Buchung, Deploy.) WP-Projekt COBOL Projektleitung + Teilprojektleitung + Fachbereichskoordinator + Themenverantwortlicher + Architekturmanagement + QS + weitere Fachbereiche (bei Bedarf) (Datenmigration, Limit, ZKV, Reports) externe Systeme Teilprojektleitung XX XX (ORG/IT) Review -Team Anbindung Handelssysteme Anbindung WP Confirmation Matching Online-Info-Center Das Projekt - Projektstatus Devisenhandel Nostro Limitsystem FB-Verantwortl.: xxxx IT-Unterstützung: xxxx FB-Verantwortl.: xxxx IT-Unterstützung: xxxx Devisenhandel II FB-Verantwortl.: xxxx IT-Unterstützung: xxxx FB-Verantwortl.: xxxx IT-Unterstützung: xxxx FX-Options Geldhandel II FB-Verantwortl.: xxxx IT-Unterstützung: xxxx FB-Verantwortl.: xxxx IT-Unterstützung: xxxx Collateral Geldhandel FB-Verantwortl.: xxxx IT-Unterstützung: xxxx FB-Verantwortl.: xxxx IT-Unterstützung: xxxx Kredit/Aval Kursversorgung FB-Verantwortl.: xxxx IT-Unterstützung: xxxx FB-Verantwortl.: xxxx IT-Unterstützung: xxxx Disposition Freigabesystem FB-Verantwortl.: xxxx IT-Unterstützung: xxxx FB-Verantwortl.: xxxx IT-Unterstützung: xxxx Internes Buchungssystem Zinsabschluss FB-Verantwortl.: xxxx IT-Unterstützung: xxxx FB-Verantwortl.: xxxx IT-Unterstützung: xxxx Umsatzerfassung Sperrensystem FB-Verantwortl.: xxxx IT-Unterstützung: xxxx 7 06.10.2010 FB-Verantwortl.: xxxx IT-Unterstützung: xxxx Infrastruktur Kleinere Themen FB-Verantwortl.: xxxx IT-Unterstützung: xxxx FB-Verantwortl.: xxxx IT-Unterstützung: xxxx Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Geschäftslogik in der Datenbank – Umstellung eines Kernbanksystems Geschäftslogik in der Datenbank 8 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Geschäftslogik in der Datenbank Geschäftslogik (engl. Business Logic, auch Anwendungslogik) ist ein abstrakter Begriff in der Softwaretechnik, der eine Abgrenzung der durch die Aufgabenstellung selbst motivierten Logik eines Softwaresystems von der technischen Implementierung zum Ziel hat. Allerdings ist der Begriff unscharf, da eine klare Trennung oft nicht möglich ist. .... • Mehrfache BL-Umsetzung • Datenbank Abhängigkeit ASP.NET Java Datenbank Daten-Import Cobol • Type Mapping (Java&.NET) FAT-Client 9 06.10.2010 • Oracle-DB ist mehr als eine Datenbank (Speicherort) • DBMS-Packages • Object Types Druckaufbereitung Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Geschäftslogik in der Datenbank – Umstellung eines Kernbanksystems Nutzung von Oracle Types – Grundideen 10 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Nutzung von Oracle Types – Grundideen (ROW_TYPES) Datenbank • ca. in dritter Normalform mit wenigen Redundanzen, die aus Performancegründen zugelassen werden • Generierung von ROW-Types, die die Standard-DMLs pro Tabelle abbilden • entspricht einem %ROWTYPE mit weiteren Attributen und Methoden (Table-API) • Vererbung über „UNDER xxxx“ möglich • polymorphe Prozeduren und Funktionen DH_MAKLER_ID 11 06.10.2010 SCHLUESSEL BESCHREIBUNG ERSTELLT_ID ERSTELLT_ZST GEAENDERT_ID GEAENDERT_ZST GEAENDERT_ZAEHLER GELOESCHT_ZST 1 1 acsdr 1469 21.03.2009 10:42:05 1469 14.06.2010 10:38:52 2 2 2 acdet 1469 21.03.2009 10:42:05 1469 14.06.2010 10:38:52 2 3 3 abghz 1469 21.03.2009 10:42:05 1469 14.06.2010 10:38:52 2 4 4 aaxsw 1469 11.11.2009 22:52:34 1469 14.06.2010 10:38:52 2 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Nutzung von Oracle Types – Grundideen (ROW_TYPES) generierter ROW_TYPE: CREATE OR REPLACE TYPE ROW_DH_MAKLER UNDER GLOBAL.TYPE_OBJECT ( -- object oriented ROWTYPE for DH_MAKLER table -- $Revision: 25013 $ -- created : 2008-01-14 09:54:57 -- attributes DH_MAKLER_ID , SCHLUESSEL , BESCHREIBUNG , ERSTELLT_ID , ERSTELLT_ZST , GEAENDERT_ID , GEAENDERT_ZST , GEAENDERT_ZAEHLER , GELOESCHT_ZST NUMBER(12) VARCHAR2(10) VARCHAR2(4000) NUMBER(12) DATE NUMBER(12) DATE NUMBER(12) DATE -- define constructors , CONSTRUCTOR FUNCTION ROW_DH_MAKLER RETURN SELF AS RESULT , CONSTRUCTOR FUNCTION ROW_DH_MAKLER(DH_MAKLER_ID NUMBER, SCHLUESSEL VARCHAR2, BESCHREIBUNG VARCHAR2, ERSTELLT_ID NUMBER, ERSTELLT_ZST DATE, GEAENDERT_ID NUMBER, GEAENDERT_ZST DATE, GEAENDERT_ZAEHLER NUMBER, GELOESCHT_ZST DATE) RETURN SELF AS RESULT , CONSTRUCTOR FUNCTION ROW_DH_MAKLER(IN_DH_MAKLER_ID NUMBER) RETURN SELF AS RESULT , CONSTRUCTOR FUNCTION ROW_DH_MAKLER(IN_SCHLUESSEL VARCHAR2, IN_GELOESCHT_ZST DATE) RETURN SELF AS RESULT , CONSTRUCTOR FUNCTION ROW_DH_MAKLER(IN_SCHLUESSEL VARCHAR2) RETURN SELF AS RESULT 12 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Nutzung von Oracle Types – Grundideen (ROW_TYPES) generierter ROW_TYPE (cont.): -- define member functions , MEMBER FUNCTION ROW_EXISTS(IN_DH_MAKLER_ID NUMBER) RETURN BOOLEAN , MEMBER FUNCTION ROW_EXISTS(IN_SCHLUESSEL VARCHAR2, IN_GELOESCHT_ZST DATE) RETURN BOOLEAN , MEMBER FUNCTION ROW_EXISTS(IN_SCHLUESSEL VARCHAR2) RETURN BOOLEAN , OVERRIDING MEMBER FUNCTION compare(in_type1 GLOBAL.TYPE_OBJECT,in_type2 GLOBAL.TYPE_OBJECT) RETURN INTEGER -- define member procedures , MEMBER PROCEDURE ROW_INSERT , MEMBER PROCEDURE ROW_UPDATE , MEMBER PROCEDURE ROW_MERGE , MEMBER PROCEDURE ROW_SAVE , MEMBER PROCEDURE ROW_DELETE , MEMBER PROCEDURE ROW_SELECT(IN_DH_MAKLER_ID NUMBER) , MEMBER PROCEDURE ROW_SELECT(IN_SCHLUESSEL VARCHAR2, IN_GELOESCHT_ZST DATE) , MEMBER PROCEDURE ROW_SELECT(IN_SCHLUESSEL VARCHAR2) , MEMBER PROCEDURE ROW_DEFAULT , MEMBER PROCEDURE ROW_LOCK , MEMBER PROCEDURE ROW_LOCK(IN_DH_MAKLER_ID NUMBER) , MEMBER PROCEDURE ROW_LOCK(IN_SCHLUESSEL VARCHAR2, IN_GELOESCHT_ZST DATE) , MEMBER PROCEDURE ROW_LOCK(IN_SCHLUESSEL VARCHAR2) ) NOT FINAL 13 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Nutzung von Oracle Types – Grundideen (ROW_TYPES) generierte Trigger (via ErWin) CREATE OR REPLACE TRIGGER TBI$DH_MAKLER BEFORE INSERT on BBS.DH_MAKLER for each row /* ------------------------------------------------------------------------------------Name : TBI$Trigger BEFORE-INSERT-TRIGGER AUF TABELLE BBS.DH_MAKLER Date : Montag, 20. Oktober 2008 08:20:50 ------------------------------------------------------------------------------------- */ when ( NEW.DH_MAKLER_ID IS NULL) DECLARE BEGIN SELECT DH_MAKLER_SEQ.NEXTVAL INTO :NEW.DH_MAKLER_ID FROM DUAL; END; CREATE OR REPLACE TRIGGER TBU$DH_MAKLER BEFORE UPDATE on BBS.DH_MAKLER for each row /* ------------------------------------------------------------------------------------Name : TBU$Trigger BEFORE-UPDATE-TRIGGER AUF TABELLE BBS.DH_MAKLER Date : Montag, 20. Oktober 2008 08:20:50 ------------------------------------------------------------------------------------- */ BEGIN :new.Geaendert_ID := SYS_CONTEXT('VPD', 'USERID'); :new.Geaendert_ZST := sysdate; :new.Geaendert_Zaehler := :old.Geaendert_Zaehler + 1; end; 14 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Nutzung von Oracle Types – Grundideen (ROW_TYPES) ein Blick ins Innere (SELECT) ... ONSTRUCTOR FUNCTION ROW_DH_MAKLER(IN_DH_MAKLER_ID NUMBER) RETURN SELF AS RESULT IS methodenname CONSTANT VARCHAR2(300) := 'BBS.ROW_DH_MAKLER.CONSTRUCTOR(IN_DH_MAKLER_ID NUMBER)'; BEGIN SELF.OBJECT_TYPE_NAME := 'BBS.ROW_DH_MAKLER'; SELF.ROW_SELECT(IN_DH_MAKLER_ID => IN_DH_MAKLER_ID); RETURN; EXCEPTION WHEN OTHERS THEN GLOBAL.PA_TRACE.TRACE( GLOBAL.PA_TRACE.TRACE_ERROR, methodenname, ' - Exception raised. ' ||SELF.TO_STRING() ||', SQLcode=' || to_char(SQLCODE) || ', ' || SQLERRM); RAISE; END; MEMBER PROCEDURE ROW_SELECT(IN_DH_MAKLER_ID NUMBER) IS methodenname CONSTANT VARCHAR2(300) := 'BBS.ROW_DH_MAKLER.ROW_SELECT(IN_DH_MAKLER_ID NUMBER)'; BEGIN SELECT DH_MAKLER_ID, SCHLUESSEL, BESCHREIBUNG, ERSTELLT_ID, ERSTELLT_ZST ,GEAENDERT_ID, GEAENDERT_ZST, GEAENDERT_ZAEHLER, GELOESCHT_ZST INTO SELF.DH_MAKLER_ID, SELF.SCHLUESSEL , SELF.BESCHREIBUNG ,SELF.ERSTELLT_ID, SELF.ERSTELLT_ZST, SELF.GEAENDERT_ID ,SELF.GEAENDERT_ZST, SELF.GEAENDERT_ZAEHLER, SELF.GELOESCHT_ZST FROM BBS.DH_MAKLER WHERE DH_MAKLER_ID = IN_DH_MAKLER_ID; EXCEPTION WHEN OTHERS THEN GLOBAL.PA_TRACE.TRACE( GLOBAL.PA_TRACE.TRACE_ERROR, methodenname, ' - Exception raised. ' ||'IN_DH_MAKLER_ID = '||IN_DH_MAKLER_ID||';' ||', SQLcode=' || to_char(SQLCODE) || ', ' || SQLERRM); RAISE; END; 15 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Nutzung von Oracle Types – Grundideen (ROW_TYPES) ein Blick ins Innere (UPDATE) ... MEMBER PROCEDURE ROW_UPDATE IS methodenname CONSTANT VARCHAR2(100) := 'BBS.ROW_DH_MAKLER.ROW_UPDATE'; BEGIN IF SYS_CONTEXT('TRACE','TRACE_LEVEL') >= GLOBAL.PA_TRACE.TRACE_TRACE THEN GLOBAL.PA_TRACE.TRACE( GLOBAL.PA_TRACE.TRACE_TRACE, methodenname, 'Parameters: '); END IF; UPDATE BBS.DH_MAKLER SET SCHLUESSEL = SELF.SCHLUESSEL ,BESCHREIBUNG = SELF.BESCHREIBUNG ,GELOESCHT_ZST = SELF.GELOESCHT_ZST WHERE DH_MAKLER_ID = SELF.DH_MAKLER_ID AND GEAENDERT_ZAEHLER = SELF.GEAENDERT_ZAEHLER RETURNING DH_MAKLER_ID,SCHLUESSEL,BESCHREIBUNG,ERSTELLT_ID,ERSTELLT_ZST,GEAENDERT_ID ,GEAENDERT_ZST,GEAENDERT_ZAEHLER,GELOESCHT_ZST INTO SELF.DH_MAKLER_ID,SELF.SCHLUESSEL,SELF.BESCHREIBUNG,SELF.ERSTELLT_ID ,SELF.ERSTELLT_ZST,SELF.GEAENDERT_ID,SELF.GEAENDERT_ZST ,SELF.GEAENDERT_ZAEHLER,SELF.GELOESCHT_ZST; IF SQL%ROWCOUNT <> 1 THEN GLOBAL.RAISE(-20999); END IF; EXCEPTION WHEN OTHERS THEN GLOBAL.PA_TRACE.TRACE( GLOBAL.PA_TRACE.TRACE_ERROR, methodenname,'-Exception raised.' ||SELF.TO_STRING() ||', SQLcode=' || to_char(SQLCODE) || ', ' || SQLERRM); RAISE; END; 16 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Nutzung von Oracle Types – Grundideen (ROW_TYPES) arbeiten mit einem ROW_TYPE declare r_dh bbs.row_dh_makler; begin r_dh := bbs.row_dh_makler(1); r_dh.dbms_output(); r_dh.BESCHREIBUNG := 'Name geändert'; r_dh.row_save(); r_dh.dbms_output(); end; <TYPE_OBJECT> <OBJECT_TYPE_NAME>BBS.ROW_DH_MAKLER</OBJECT_TYPE_NAME> <DH_MAKLER_ID>1</DH_MAKLER_ID> <SCHLUESSEL>1</SCHLUESSEL> <BESCHREIBUNG>xxxxxxxxxx</BESCHREIBUNG> <ERSTELLT_ID>1469</ERSTELLT_ID> <ERSTELLT_ZST>21.03.09</ERSTELLT_ZST> <GEAENDERT_ID>1469</GEAENDERT_ID> <GEAENDERT_ZST>14.06.10</GEAENDERT_ZST> <GEAENDERT_ZAEHLER>2</GEAENDERT_ZAEHLER> </TYPE_OBJECT> <TYPE_OBJECT> <OBJECT_TYPE_NAME>BBS.ROW_DH_MAKLER</OBJECT_TYPE_NAME> <DH_MAKLER_ID>1</DH_MAKLER_ID> <SCHLUESSEL>1</SCHLUESSEL> <BESCHREIBUNG>Name geändert</BESCHREIBUNG> <ERSTELLT_ID>1469</ERSTELLT_ID> <ERSTELLT_ZST>21.03.09</ERSTELLT_ZST> <GEAENDERT_ID>792</GEAENDERT_ID> <GEAENDERT_ZST>28.07.10</GEAENDERT_ZST> <GEAENDERT_ZAEHLER>3</GEAENDERT_ZAEHLER> </TYPE_OBJECT> 17 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Geschäftslogik in der Datenbank – Umstellung eines Kernbanksystems Nutzung von Oracle Types – Beispiele 18 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Nutzung von Oracle Types – Beispiel „Kursversorgung“ Überblick 19 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Nutzung von Oracle Types – Beispiel „Kursversorgung“ • ROW_TYPES stellen die Basiszugriffe auf eine Tabelle sicher • Entwickler arbeiten mit einem TYPE_XXX, die UNDER ROW_TYPES definiert werden • Beispiel einer Reutersmessage: „NOK Referenzkurs per 23 Juli 2010“ 340 XX EURNOKREF= 92 110 1 363 2 153 4 0 6 +7.9660 12 +0 13 +0 15 578 19 +0 21 +0 22 +7.9 660 23 +7.9750 24 +8.0135 25 +0 26 +0 27 +0 28 29 : 53 0 66 +0 67 78 79 104 37 115 2 118 0 131 0 196 0 197 0 259 209 270 27 271 27 272 27 275 +0 276 +0 277 +0 28 0 0 281 0 282 0 348 349 372 +0 374 0 375 : : 393 +7.9660 394 +7.9750 395 +8.0135 728 791 +0 792 +0 793 +0 800 801 802 820 0 821 0 822 0 825 0 826 827 828 831 ECB 832 833 836 FFT 837 838 841 ECB3 842 843 869 0 874 +0 875 23 JUL 2010 876 22 JUL 2010 877 21 JUL 2010 957 +0 958 0 959 +0 960 0 961 +0 962 0 963 +0 964 0 967 968 975 976 977 980 981 982 985 +0 986 +0 987 +0 990 0 991 0 992 0 995 996 +0 997 +0 998 +0 999 +0 1000 FIX 1001 EURNOK 1002 1003 1010 12:29:51 1011 12:30:10 1012 12:29:14 1021 +0 1029 +0 1030 +0 1031 +0 1032 +0 1033 +0 1034 +0 1035 1036 1037 1038 1039 1040 1276 1055 0 1056 1080 |@@ 1275 1277 1278 1354 1355 1356 1383 @hL 1709 0 1789 2129 0 2133 0 2134 0 2135 0 2136 0 2137 0 2138 0 2736 2737 2738 : : 2739 : : 2740 : : 3131 +0 3132 +0 3263 @@@ 3264 0 3364 0 3372 +0 3404 +0 3422 3694 3750 +0 3830 +0 3831 +0 4233 +0 4236 0 4237 0 4238 4305 +0 4400 4401 4402 1078 +0 4043 0 20 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Nutzung von Oracle Types – Beispiel „Kursversorgung“ • TYPE_REUTERS_MESSAGE UNDER ROW_REUTERS_MESSAGE CREATE OR REPLACE TYPE TYPE_REUTERS_MESSAGE UNDER EXT.ROW_REUTERS_MESSAGE ( /* -----------------------------------------------------------------------Parst eine Nachricht aus der REUTERS_MESSAGE Tabelle. %Version $Revision: 41291 $ ------------------------------------------------------------------------ */ -- Header Data aus der direkten Reuters Message RECORD_TYPE NUMBER(5) -- Type or Reuters Record (340=Snap, 316=Update, , TAG VARCHAR2(5) -- Tag, should be skipped, can be NULL , MSG_RIC VARCHAR2(100) -- RIC, should be skipped, can be NULL , FIELD_LIST_NO NUMBER(5) -- number, should be skipped, can be NULL , RTL NUMBER(6) -- lfd. Nummer, should be skipped, can be NULL , CONSTRUCTOR FUNCTION TYPE_REUTERS_MESSAGE RETURN SELF AS RESULT , CONSTRUCTOR FUNCTION TYPE_REUTERS_MESSAGE(IN_MESSAGE_ID NUMBER , IN_GET_HEADER_JN VARCHAR2 DEFAULT 'J' ) RETURN SELF AS RESULT , CONSTRUCTOR FUNCTION TYPE_REUTERS_MESSAGE(IN_MESSAGE VARCHAR2 , IN_GET_HEADER_JN VARCHAR2 DEFAULT 'J' ) RETURN SELF AS RESULT , CONSTRUCTOR FUNCTION TYPE_REUTERS_MESSAGE(IN_MESSAGE EXT.TYPE_AQ_ZKV_REUTERS_INBOX ) RETURN SELF AS RESULT , MEMBER FUNCTION Get_Data( IN_FID NUMBER , IN_GET_ORIGONLY_JN VARCHAR2 DEFAULT 'N' ) RETURN VARCHAR2 , MEMBER FUNCTION Get_Data_Tab RETURN EXT.TABLE_FID_VALUE PIPELINED ) 21 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Nutzung von Oracle Types – Beispiel „Kursversorgung“ • Select-Beispiel (der gesamte Datensatz) SELECT ext.type_reuters_message(3675002) FROM dual; • Select-Beispiel (ein einzelnes Attribut) SELECT ext.type_reuters_message(3675002).get_data('BID') FROM dual; 22 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Nutzung von Oracle Types – Beispiel „Kursversorgung“ • Select-Beispiel (alle Attribute einer Zeile) SELECT t.dde_acronym, t.wert, t.wert_original, t.* FROM TABLE(ext.type_reuters_message(3675002).get_Data_Tab()) t; 23 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Nutzung von Oracle Types – Beispiel „Kunde & Konto“ • Schachtelung von Types CREATE OR REPLACE TYPE TYPE_KUNDE UNDER BBS.ROW_KUNDE ( -- attributes DEPOT BBS.TABLE_DEPOT, KONTEN_BBS BBS.TABLE_KONTO_BBS, -- define constructors , CONSTRUCTOR FUNCTION TYPE_KUNDERETURN SELF AS RESULT , CONSTRUCTOR FUNCTION TYPE_KUNDE(IN_STAMMNR VARCHAR2) RETURN SELF AS RESULT , MEMBER FUNCTION is_bank -- ... RETURN BOOLEAN ) NOT FINAL CREATE OR REPLACE TYPE TABLE_KONTO_BBS AS TABLE OF BBS.TYPE_KONTO_BBS CREATE OR REPLACE TYPE BBS.TYPE_KONTO_BBS UNDER BBS.ROW_KONTO_BBS ( /*-------------------------------------------------------------------Erweitert den ROW-TYPE zur Tabelle BBS.KONTO_BBS. %Version $Revision: 54699 $ --------------------------------------------------------------------*/ t_konditionen BBS.TYPE_KONTO_BBS_KONDITIONEN, tab_gebuehrbetrag BBS.TABLE_GEBUEHRBETRAG, -- Constructor CONSTRUCTOR FUNCTION TYPE_KONTO_BBS(IN_KONTO_BBS_ID IN NUMBER) RETURN SELF AS RESULT, CONSTRUCTOR FUNCTION TYPE_KONTO_BBS(IN_KONTO_BBS_ID NUMBER, IN_FGS_INFO_JN CHAR) RETURN SELF AS RESULT, CONSTRUCTOR FUNCTION type_konto_bbs RETURN SELF AS RESULT, CONSTRUCTOR FUNCTION type_konto_bbs(in_kontonummer IN VARCHAR2) RETURN SELF AS RESULT, ) NOT FINAL 24 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Nutzung von Oracle Types – Beispiel „Fulfilment“ (AQ, XML) Fulfilment (Erzeugung von Output für Kunden: Brief, Fax, ...) Ablauf: Queue: AQ_ORA_STS_XML Oracle-DB Queue: AQ_STS_ORA_REPLY Statusmeldung; ggf. PDF-Datei als BLOB Reply-Out (Skript) J-InConnector (Java) XML-In (Konverter) Pre-Processor (Skript) J-OutConnector (Java) PDF-Out StreamServe 25 06.10.2010 XML Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Repository Poet-DB PDFDokumente Nutzung von Oracle Types – Beispiel „Fulfilment“ (AQ, XML) • Schachtelung von Types DECLARE t_270 bbs.type_fulfilment_270; -- abgeleitet von bbs.type_fulfilment (mit Basisroutinen zur -- Adressholung, Kommunikation mit StreamServe, ... BEGIN -- Instanz erzeugen t_270 := bbs.type_fulfilment_270(in_kundenid ,in_kontoid); t_270.Dokumentdetaildaten_erzeugen(...); t_270.sendToStreamServe; END; 26 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Nutzung von Oracle Types – Beispiel „§24c KWG“ (XML) KWG 24c - Meldungen • Tägliche Meldungen von Kunden-, Konto-, Bevollmächtigtendaten • Meldungen im XML-Format • Übermittlung der täglichen Änderungen • XML-Dateien werden an einen Dienstleister, der die Daten für die BaFin ( = Bundesanstalt für Finanzdienstleistungsaufsicht ) bereitstellt, übertragen 27 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Nutzung von Oracle Types – Beispiel „§24c KWG“ (XML) Pseudocode zur Meldungserstellung: BEGIN /* Delta ermitteln */ FOR /* alle melderelevanten Konten */ LOOP t_kto := TYPE_KWG24CKONTO(kundenid, Kontonummer, .. .); t_kto.addBBSInhaber(.. .); t_cmp_kto := getkwg24ckonto(Kontonummer, .. .); -- Vergleichskonto, letzte Version IF t_kto_compare IS NULL OR t_kto_compare <> t_kto THEN addkwg24ckonto(.. .); END IF; END LOOP; /* XML-Ausgabedatei erzeugen */ SELECT XMLConcat( -- Anfangs-Datensatz -XMLELEMENT ("anfangssatz", xmlattributes (verpflichtetenid AS "verpflichteten-id",...)), -- Konto-Datensaetze -- (liegen bereits als XML-Teildokument vor) (SELECT XMLAGG (XMLdokument) FROM kwgexportdateikonto p WHERE p.laufendedateinummer = in_laufendeDateiNummer), -- Ende-Datensatz -XMLELEMENT ("endesatz", xmlattributes (beschreibung AS "kommentar", anzahldatensatzeinexportdat AS "anzahl-kontodatensaetze" ) ) ) FROM ... END; 28 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Nutzung von Oracle Types – Fazit Vorteile / sinnvolle Einsatzgebiete • übersichtlicher, wartbarer Programmcode • OO-Mechanismen (Oracle sollte hier nachbessern: z. B. private Methoden und Attribute) • geeignet für Einzelsatzbearbeitung Nachteile / eher nicht sinnvolle Einsatzgebiete 29 • Massendatenänderungen ( DML) • bei UPDATEs werden immer alle Spalten aktualisiert 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Standards für Tabellen: Standardfelder- und Trigger TABELLE TABELLE_ID Eindeutige technische ID. Wird über Before-InsertTrigger ermittelt, wenn nicht übergeben ... ERSTELLT_ID ERSTELLT_ZST Erstellt-Felder. Werden über Spalten-Defaults (syscontext) belegt GEAENDERT_ID GEAENDERT_ZST GEAENDERT_ZAHLER GELOESCHT_ZST Letzte Änderungs-Felder. Werden über einen BeforeUpdate-Trigger ermittelt: :NEW.GEAENDERT_ZST := SYS_CONTEXT('VPD', 'USERID'); :NEW.GEAENDERT_ZST := SYSDATE; :NEW.GEAENDERT_ZAEHLER := :OLD.GEAENDERT_ZAEHLER + 1; Über den Gelöscht-Zst können einzelne Sätze logisch gelöscht werden 30 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Regelmäßige Reorganisation von Tabellen - Anforderungen • Operative Datenbank soll klein gehalten werden • Zentrale und übersichtliche Definition der Reorg-Vorgänge • Flexible und einfache Erweiterbarkeit, Anpassungsmöglichkeiten • Einsatzgebiete • Tracetabelle nach X-Tagen bereinigen • Alte, versandte E-Mails entfernen 31 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Regelmäßige Reorganisation von Tabellen - Tabellenaufbau Gruppierung der Reorg-Vorgänge REORG_SET NAME REORG Reihenfolge innerhalb des Reorg-Sets REORG_SET DELETE TRUNCATE SHRINK CALL REIHENFOLGE VARIANTE TABELLE LOESCH_BEDINGUNG AKTIV LETZTER_AUFRUF WHERE-Bedingung, die die zu löschenden Sätze beschreibt 32 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Zu reorganisierende Tabelle / Bei CALL steht hier der Name der aufzurufenden Prozedur Soll Reorg-Vorgang durchgeführt werden? Zeitpunkt des letzten Reorg-Laufs Regelmäßige Reorganisation von Tabellen - Ablauf alle Reorg-Sets durchlaufen alle Vorgänge des Reorg-Sets durchlaufen CASE VARIANTE WHEN 'DELETE' THEN EXECUTE IMMEDIATE 'DELETE FROM ' || TABELLE || ' WHERE ' || LOESCH_BEDINGUNG; WHEN 'TRUNCATE' THEN EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || TABELLE; WHEN 'SHRINK' THEN EXECUTE IMMEDIATE 'ALTER TABLE '|| TABELLE ||' SHRINK SPACE'; WHEN 'CALL' THEN EXECUTE IMMEDIATE 'BEGIN ' || TABELLE || '; END;'; END CASE; • Läuft seit Einführung 2007 fehlerfrei • Sehr flexibel bei kurzfristigen Änderungen • ABER: Dyn. SQL Freigabeverfahren ! 33 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Kapselung von PL/SQL - Problemstellung COBOL • Zugriff von „alten“ COBOLProgrammen auf die DB erfolgt über eine Zugriffschicht, die nur Select, Insert, Update und Delete unterstützt Zugriffsschicht • Der Aufruf von PL/SQL-Sourcen soll ermöglicht werden Nur Select, Insert, Update, Delete Datenbank 34 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Kapselung von PL/SQL - Lösung Spalten Funktion Parameter Ergebnis Fehlercode UPDATE COBOL Funktion Parameter View SELECT Instead-Of Trigger Package Ergebnis Fehlercode PL/SQL Source 1 PL/SQL Source n Fazit: Möglichkeit um PL/SQL für „alte“ Systeme ansprechbar zu machen 35 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Geschäftslogik in der Datenbank – Umstellung eines Kernbanksystems Freigabesystem 36 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Freigabesystem 3 Geschäft ist nicht konfiguert nicht konfiguriert (-1) Start Prüfung kein Limit überschritten alles OK Limit überschritten Erfasser hat Kompetenz Freigabe nicht notwendig (0) Freigabe erforderlich (3) n-1 Unterschriften wurden geleistet Eine Unterschrift wurde geleistet Teilfreigabe (2) n-te. Unterschriften wurden geleistet Legende Zurückweisung Standardablauf Zurückweisen (4) Option freigegeben (1) Startstatus 37 06.10.2010 ZwischenStatus Endestatus Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Freigabesystem Freigeben einer Freigabe declare t_fg TYPE_FGS_FREIGABE; begin t_fg := TYPE_FGS_FREIGABE (IN_ANY_ID => 3, IN_STAMMDATENTYP_NODE_ID IN_STAMMDATENTYP_ID IN_VORGANGSTYP_ID t_fg.approve_freigabe; end; 38 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 =>3, =>6, =>2); Freigabesystem • XML als allg. „Schnittstellensprache“ • Hinzufügen einer Freigabe: -- add mit xml declare i_xml sys.xmltype; t_fgs_freigabe bbs.type_fgs_freigabe := bbs.type_fgs_freigabe(); BEGIN i_xml := xmltype( '<?xml version="1.0" ?> <FREIGABE> <FGS> <ANY_ID>82238</ANY_ID> <DL_NODE_ID></DL_NODE_ID> <DIENSTLEISTUNG_ID></DIENSTLEISTUNG_ID> <TRANSAKTIONSTYP_ID></TRANSAKTIONSTYP_ID> <STAMMDATENTYP_NODE_ID>2</STAMMDATENTYP_NODE_ID> <STAMMDATENTYP_ID>3</STAMMDATENTYP_ID> <VORGANGSTYP_ID></VORGANGSTYP_ID> <VOLUMEN_EUR></VOLUMEN_EUR> <KUNDE_ID></KUNDE_ID> </FGS> <AENDERUNG> <DATENSATZ OWNER="BBS" TABLE="SPERRE" PKNAME="SPERRE_ID" PKWERT="84658"> <ATTRIBUT NAME="geloescht_zst"> <ALT></ALT> <NEU>04.08.2008</NEU> </ATTRIBUT> </DATENSATZ> </AENDERUNG> </FREIGABE>'); t_fgs_freigabe.add_freigabe(i_xml); end; 39 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Geschäftslogik in der Datenbank – Umstellung eines Kernbanksystems Saldenermittlung 40 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Saldenermittlung Saldo_per_Buchungstag := Summe der Umsatzbeträge bis zum Tag X Buchungstag Valutatag 25.07.2010 Betrag Saldo per Buchungstag Kommentar 0,00 0,00 Eröffnung 29.07.2010 29.07.2010 132,33 29.07.2010 15.12.2010 -254,00 -121,67 30.07.2010 2.08.2010 225,22 103,55 2.08.2010 2.08.2010 -15,98 2.08.2010 5.08.2010 -514,00 -426,43 Performance wird unzureichend bei vielen Umsätzen ... Aufbau von 2 Materialized Views MV_1) Saldo über alle Umsätze pro Valuta- und Buchungstag MV_2) Saldo über alle Umsätze (MV auf MV_1) 41 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Saldenermittlung Aufbau von 2 Materialized Views -- Gesamtsaldo pro Tag MV_1 CREATE MATERIALIZED VIEW MV_UMSATZ_BBS REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS SELECT u.konto_bbs_id, u.valuta, u.buchungsdatum, SUM((u.betrag + abs(u.betrag)) / 2) betrag_haben, COUNT((u.betrag + abs(u.betrag)) / 2) cnt_betrag_haben, SUM((abs(u.betrag) - u.betrag) / 2) betrag_soll, COUNT((abs(u.betrag) - u.betrag) / 2) cnt_betrag_soll, SUM(u.betrag) betrag, COUNT(u.betrag) cnt_betrag, COUNT(*) cnt FROM bbs.umsatz_bbs u GROUP BY u.konto_bbs_id, u.valuta, u.buchungsdatum; -- Gesamtsaldo MV_2 CREATE MATERIALIZED VIEW MV_UMSATZ_BBS_TOTAL REFRESH FAST ON COMMIT ENABLE QUERY REWRITE SELECT konto_bbs_id, COUNT(*) cnt_all, SUM(betrag) sum_betrag_total, COUNT(betrag) cnt_betrag_total, SUM(betrag_haben) sum_betrag_haben_total, COUNT(betrag_haben) cnt_betrag_haben_total, SUM(betrag_soll) sum_betrag_soll_total, COUNT(betrag_soll) cnt_betrag_soll_total FROM mv_umsatz_bbs GROUP BY konto_bbs_id 42 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Saldenermittlung Berechnung des Saldos SELECT /*+ rewrite */ nvl(SUM(betrag), 0) FROM (SELECT /*+ rewrite */ SUM(u.betrag) betrag FROM bbs.umsatz_bbs WHERE u.konto_bbs_id UNION ALL SELECT /*+ rewrite */ -- aktueller gesamtsaldo des Kontos (aus mv_umsatz_bbs_total) u = i_konto_bbs_id -- NACH dem gewünschten Datum -- aufgelaufene Umsätze abziehen (aus mv_umsatz_bbs) -SUM(u.betrag) betrag FROM bbs.umsatz_bbs u WHERE u.konto_bbs_id = i_konto_bbs_id AND u.buchungsdatum > i_stand); 43 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Reporterzeugung: PDF (mit PL/PDF) • Tägliche- oder AdHoc-Reports erstellen als pdf • Reports direkt aus der Datenbank heraus drucken CREATE OR REPLACE PROCEDURE HELLOWORLD IS l_blob BLOB; BEGIN /* Initialize, without parameters means: page orientation: portrait; page format: A4 */ plpdf.init; plpdf.NewPage; /* Sets the font and its properties */ plpdf.SetPrintFont( p_family => 'Arial', -- Font family: Arial p_size => 48 -- Font size: 12 pt ); /* Draws a rectangle cell with text inside. The rectangle may have a border and fill color specified. */ plpdf.PrintCell( p_w => 150, -- Rectangle width p_h => 20, -- Rectangle heigth p_txt => 'Hello World!' -- Text in rectangle ); /* Returns the generated PDF document. The document is closed and then returned in the OUT parameter. */ plpdf.SendDoc(p_blob => l_blob); -- The generated document INSERT INTO STORE_BLOB (blob_file, created_date) VALUES (l_blob, sysdate); END; 44 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 PL/PDF - Fazit Vorteile • PL/PDF ist ausschließlich in PL/SQL geschrieben • Erzeugung von PDF-Dokumenten direkt in der Datenbank • Geringe Kosten • (einfache Charts möglich) Nachteile • kein WYSIWYG • keine automatischen Spaltensummen oder Gruppierungen 45 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Reporterzeugung: Excel • Excel im XML-Format erstellen (ab Excel 2003 lesbar) declare t global.type_Excel_Document := global.type_Excel_Document(); begin t.document_Open; -- minimale Seite t.worksheet_Open('DOAG-Test-Tab'); t.add_SQL_XSL(in_query => 'SELECT kundenid, inhaber FROM bbs.kunde WHERE ROWNUM<=10'); t.row_Open(); t.add_cell(p_data=> 'Anzahl='); t.add_cell(p_formula => 'count(R2C1:R11C1)'); t.row_Close; t.worksheet_Close; t.document_Close; t.document_Save('REPORTS','DH_Makler.xls'); end; 46 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Asynchrone Prozesse Verschiedene Situationen erfordern es Aufrufe asynchron zu verarbeiten. z.B.: • Performance • „ORA-04091 mutating table“ • Transaktionsicherer eMail-Versand (resp. alle utl*-Packages) 47 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Asynchrone Prozesse - Umsetzung Unterteilung in serielle und parallele Verarbeitung Standardattribute ID und IDENT zum Aufrufen der Funktionen Dispatcher-Package zum Verteilen der Aufrufe Queues seriell DBMS_SCHEDULER Aufruf parallel 48 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Dispatcher Package Asynchrone Prozesse - Beispiel PROCEDURE APPLY_MESSAGE(IN_MSG IN SYS.XMLTYPE) IS v_ident VARCHAR2(50); v_id NUMBER(12); BEGIN v_ident := IN_MSG.EXTRACT('/async/ident//text()'); v_id := IN_MSG.EXTRACT('/async/parameter/id//text()'); CASE v_ident WHEN 'GLOBAL.EXPORT' THEN -- Export anstossen GLOBAL.PA_EXPORT.EXPORT_USER(IN_USER_ID => v_id); WHEN 'GLOBAL.VERARBEITE_ORDER' THEN -- Order einarbeiten GLOBAL.PA_ORDER.VERARBEITEN(IN_ORDER_ID => v_id); WHEN 'GLOBAL.MAIL' THEN -- eMail verschicken. GLOBAL.PA_MAIL.SEND_MAIL(IN_MAIL_ID => v_id); WHEN 'GLOBAL.MAIL_MESSAGE' THEN -- eMail verschicken. GLOBAL.PA_MAIL.SEND_MAIL(IN_MESSAGE => IN_MSG); WHEN 'ORDER.GATTUNG_LOESCHUNG' THEN -- Löschen einer Gattung ORDER.PA_GATTUNG.LOESCHE_GATTUNG(IN_GATTUNG_ID => v_id); ELSE RAISE_APPLICATION_ERROR(-20000,'QUEUE_DISPATCHER : Unbekannter Message Typ.'); END CASE; END; 49 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Asynchrone Prozesse - Beispiel 50 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Asynchrone Prozesse - Fazit • „Bremsen“ im seriellen Ablauf verzögern nachfolgende Aufrufe • Zustand der Datenbank ändert sich bis asynchrone Verarbeitung startet 51 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Zentralisierung der Geschäftslogik Vorteile • single point of thruth • Wartbarkeit („wo muss ich überall ändern, wenn ...“) Nachteile • 52 06.10.2010 single point of thruth Fehler haben sehr weitreichende Auswirkungen QS (automatisierte Regressionstests) Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Geschäftslogik in der Datenbank – Umstellung eines Kernbanksystems „Bonustrack“ • Data-Masking für Entwicklungsdatenbanken • Steuerung von Sichtbarkeiten mit der Virtual Private Database (Column-Level-Security) 53 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Data-Masking für Entwicklungsdatenbanken Problem: • Bereitstellung von (Fullsize-) Entwicklungsdatenbanken • mit allen Geschäftsvarianten, aber ohne Kundennamen, Adressen, ... Lösungsvariante: • Oracle Data-Masking • Bestimmung des relevanten Tabellen + Spalten • Beim Aufbau der Entwicklungsdatenbanken erfolgt die Anonymisierung der Daten • zertifiziert • ABER: • Initiale Probleme bei der Skripterzeugung im EM (verlorene Objekte, Trigger) • Laufzeit beim maskieren (ca. 60 Tabellen, 250 Spalten 2 h) 54 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Sichtbarkeiten mit Virtual Private Database (Column-Level-Security) Problem: • Teilweise muss der DB-Support auf nicht anonymisierte Datenbank zugreifen (bei Produktionsproblemen). • Kundennamen, Adressen dürfen aber nicht sichtbar werden Lösungsvariante: • Oracle VPD mit Column-Level-Security • Bestimmung des relevanten Tabellen und Spalten • Zugriff für DB-Support über einen bestimmten User • einzelne Spalten von Tabellen werden ausgeblendet • Abfrage geben NULL für diese Spalten zurück 55 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Sichtbarkeiten mit Virtual Private Database (Column-Level-Security) begin DBMS_RLS.ADD_POLICY (object_schema=>'BBS', object_name=>'KUNDE', policy_name=>'BBS_KUNDE', function_schema=>'BV', policy_function=>'pa_vpd_reader.fu_policy', sec_relevant_cols=>‘VORNAME,NACHNAME', sec_relevant_cols_opt=>dbms_rls.ALL_ROWS); end; / begin DBMS_RLS.DROP_POLICY ('BBS', 'KUNDE', 'BBS_KUNDE'); end; / FUNCTION fu_policy( obj_schema VARCHAR2, obj_name VARCHAR2 ) RETURN VARCHAR2 IS BEGIN v_retVal := '0=0'; IF USER = 'READER' THEN CASE WHEN SYS_CONTEXT('USERENV','DB_NAME') LIKE 'INFO%' or SYS_CONTEXT('USERENV','DB_NAME') LIKE 'BBS%‘ THEN v_retVal := '1=0'; END CASE; END IF; RETURN v_retVal; END; keine merkbaren Performanceeinbußen (ca. 60 Tabellen, 250 Spalten) 56 06.10.2010 Geschäftslogik in der Datenbank - Umstellung eines Kernbanksystems - DOAG Konferenz 2010 Geschäftslogik in der Datenbank – Umstellung eines Kernbanksystems Fragen? Anregungen? Michael Meyer Lead Database Architect November 2010