2010 db security

1.
OS/400 SECURITY Data BaseSecurity Presented by Wayne O. Evans

2.
OS/400 Security DISCLAIMER The security recommendations and any program source are offered "AS IS" for your consideration. Wayne O Evans Consulting makes no warranties or representations as to the quality of the examples. ALL WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE, ARE SPECIFICALLY DISCLAIMED. REPRODUCTION Permission is granted to make Wayne O Evans a limited number of copies of 5677 W Circle Z St this material for non-commercial Tucson, AZ 85713 purposes provided this page and the title page are included Tel (520)-578-7785 with all copies WOEvans@aol.com iSeries, AS/400 and OS/400 are registered trademarks of the IBM Corporation. 2

3.
DB Security Outline Logical Files Column Level Security DB Exit Programs 3

4.
Protect Sensitive Data NAME SALARY DEPARTMENT ELLEN 50,000 Accounting NEIL 40,000 Legal TRACEY 20,000 Management TROY 45,000 Finance Limited access  Production files may contain sensitive and less sensitive data. 4

5.
Hide Sensitive Fields Table or Physical File NAME SALARY DEPARTMENT ELLEN 50,000 Accounting NEIL 40,000 Legal TRACEY 20,000 Management TROY 45,000 Finance View or Logical File NAME DEPARTMENT Logical file with sensitive fields removed 5

6.
Prevent Access toPhysical File Table or *PUBLIC Physical File *READ *ADD NAME SALARY DEPARTMENT *UPD ELLEN 50,000 Accounting *DLT NEIL 40,000 Legal TRACEY 20,000 Management TROY 45,000 Finance *OBJOPR authority is required to open a file. To prevent access to sensitive fields do not give users *OBJOPR to physical file 6

7.
Allow Access toLogical File *PUBLIC *OBJOPR View or *READ Logical File *ADD *UPD NAME DEPARTMENT *DLT *OBJOPR authority is required to open a file. To allow access to non-sensitive fields give users *OBJOPR to logical file 7

8.
Logical Files Logical filesuse system security to protect fields Protects all interfaces May require several logical files when different fields are give to different groups of users. 8

9.
DB Security Outline LogicalFiles Column Level Security DB Exit Programs 9

10.
Column Level Security NAME SALARY DEPARTMENT ELLEN 50,000 Accounting NEIL 40,000 Legal TRACEY 20,000 Management TROY 45,000 Finance Limited access  Column level security provides a way to limit update and reference access  Read level access not supported yet 10

11.
Column-Level Security Current Alternatives Application Program Code limits access Programs prevent access to prevent access to data base objects PROS: More flexible security checking CONS: Not enforced on all interfaces Extra programming required 11

12.
Column-Level Security Current Alternatives Logical Files or SQL views Files created “hide” sensitive columns PROS: System does work CONS: Not enforced on all interfaces Overhead of creating logical file for each different view 12

13.
Column-Level Security Current Alternatives Stored procedures for ODBC access Stored procedures use adopted authority to gain access PROS: More flexible security checking CONS: Not enforced on all interfaces Extra programming required 13

14.
Column Level Security SQLstatements GRANT and REVOKE define column level authorities CL command DSPOBJAUT is used to display column level authorities 14

15.
Column Level Security Database administrator enters STRSQL On the SQL entry screen enter CREATE TABLE myfile (name CHAR(40), salary INTEGER, department CHAR(50)) GRANT SELECT,UPDATE(name, department) ON TABLE myfile TO woepgmr GRANT SELECT,UPDATE(name) ON TABLE myfile TO public15

16.
DSPOBJAUT MYFILE *FILE F16 - shows field level authorities 16

17.
Field Authorities 17

18.
Column Level Security Columnlevel authorities are stored with the file Restoring user profiles will not restore column level authority Column level authority is enforced on the update operation (Update is rejected only when the column being restricted is modified) 18

19.
Objects Permissions 19

20.
Right Click CLICK CLICK 20

21.
21

22.
22

23.
Column Level Security V4R2 Point and click on checkbox to modify column security 23

24.
DB Security Outline LogicalFiles Column Level Security DB Exit Programs  Overview  Sample Exit Programs  iSeries Navigator (File Transfer and ODBC)  FTP 24

25.
Many Ways to Transfer Data  DDM -Distributed Data Management  FTP - File Transfer Protocol  Client Access File transfer ODBC 25

26.
Need to LimitUsers Access  Users are authorized to data because of existing applications  Need exists to prevent the user from using their access outside of applications EXIT PROGRAMS provide a way to screen user actions 26

27.
Exit Program Overview Exit Programs Registration Facility 27

28.
Identifying Exit Programs •Network • Registration attributes facility - DDMACC WRKREGINF - PCSACC Exit Program DDMACC or PCSACC QIBM_... Pgm EXIT1 QIBM_... Pgm QIBM_... Pgm Request Exit 1-ok 0-No server PGM Perform the request The exit program supplements existing object security 28

29.
EXIT PROGRAMS Exit ProgramsSupplement Object Level Security  Prevent specific operations  file transfer  remote commands  Restrict access to specific libraries  Monitor Use Record activity for usage analysis 29

30.
EXIT PROGRAMS Exit ProgramsSupplement Object Level Security  Prevent specific operations  file transfer  remote commands  Restrict access to specific libraries  Monitor Use Record activity for usage analysis 30

31.
Exit Program Flow SOURCE request SYSTEM TARGET SYSTEM 1. SOURCE system sends request to AS/400 TARGET 2. AS/400 calls exit program WRKREGINF named in network attribute DDMACC Exit Program or DDMACC or PSCACC or PCSACC QIBM_... Pgm QIBM_. EXIT1 Registration Facility EXIT1 QIBM_... Pgm 3. User exit program looks at request and sets return code 1= accept request t 0= reject request EXIT1 reques 31

32.
Exit Program Overview Exit Programs Registration Facility 32

33.
Network Attribute PCSACC NetworkAttribute values: Prior to V3R1 *OBJAUT Object authorizations are checked for this client request *REJECT Reject all server requests from clients PGM-name Exit program name called by all requests ALL Exit 1-ok REQUESTS PGM 0-No server • Every request invokes same exit program • Overhead of exit program for requests that are not restricted 33

34.
EXIT PROGRAM When network attribute PCSACC names a program, all requests are handled EXIT-PGM-Name by the same exit R Shared Folders E File Transfer Q Exit 1-ok server Remote Commands API's U PGM 0-No E Messages RCV/SND Printer Support S T S Performance overhead on all requests 34

35.
Network Attribute Use the system's PCSACC registration facility to *REGFAC determine which exit program to run. Multiple Exits Possible Exit 1-ok REQUESTS PGM 0-No server Exit 1-ok REQUESTS PGM 0-No server REQUESTS server No exit (overhead) for some servers 35

36.
Do I needto use registration facility? Comparison exit-pgm *REGFAC All Exit REQUESTS server REQUESTS server PGM REQUESTS Exit server PGM Overhead Overhead Reduced • All requests checked • Selected requests • More complex logic (larger program) checked • Program logic simpler RECOMMEND: Use registration facility ➤ Performance advantage ➤ Can check more request types 36

37.
Work with ExitPrograms WRKREGINF Work with Registration Information 5=Display exit point 8=Work with exit programs Exit Exit Point Regist Opt Point Format ered Text _ QIBM_QGW_NJEOUTBOUND NJEO0100 *YES Network Job Entry _ QIBM_QHQ_DTAQ DTAQ0100 *YES Original Data Queue _ QIBM_QLZP_LICENSE LICM0100 *YES Original License Mgmt _ QIBM_QMF_MESSAGE MESS0100 *YES Original Message _ QIBM_QNPS_ENTRY ENTR0100 *YES Network Print- entry _ QIBM_QNPS_SPLF SPLF0100 *YES Network Print- spool _ QIBM_QNS_CRADDACT ADDA0100 *YES Add CRQ description _ QIBM_QNS_CRCHGACT • Exit program for Change CRQfunction CHGA0100 *YES specific desc CRQ _ QIBM_QNS_CRDLTSBMCRQ DLTA0100 *YES Delete submitted • Multiple programs can be defined _ QIBM_QNS_CRDSPACT DSPA0100 *YES Display CRQ desc _ QIBM_QNS_CREXCACT EXCA0100 *YES Run CRQ activity More... Command ===> ____________________________________________ F3=Exit F4=Prompt F9=Retrieve F12=Cancel 37

38.
Work with ExitPrograms • Exit program for specific function • Multiple programs can be defined Work With Exit Programs Exit Point: QIBM_QZRC_RMT Format:CZRC0100 Type Options, Press Enter. 1=add 4=remove 5=display 10=replace Exit Program Exit Opt Number Program Library __ 1 __________ __________ EXIT1 MYLIB Command==>________________________________ F3=exit F4=prompt F5=refresh F9=retrieve 38

39.
DB Security Outline Logical Files Column Level Security DB Exit Programs  Overview  Sample Exit Programs  iSeries Navigator (File Transfer and ODBC)  FTP 39

40.
Exit Programs CALL EXIT (RTNCDE STRUCTURE) '0' NO Field Format Size '1' OK User profile name Char 10 Application name Char 10 Function Char 10 Object name Char 10 Library name Char 10 Object type Char 7 Format name Char 10 Variable data length Zoned 5, 0 Variable data Char * Format detail is described in  AS/400 Distributed Data Management SC41-5307  Client Access Server Concepts SC41-5740 40

41.
Operation code byFunction Applic function / operation ation *LMSR license management REQUEST RELEASE *VPRT virtual print EXTRACT CHECK OPEN *TFRFCL file transfer SELECT JOIN REPLACE EXTRACT AS/400 -> PC retrieve information SELECT AS/400 -> PC download file JOIN AS/400 -> PC download joined file REPLACE PC --> AS/400 UPLOAD file 41

42.
Operation code byFunction Applic function / operation ation *FLRSRV shared folders type 2 CHANGE CREATE DELETE EXTRACT MOVE OPEN RENAME *MSGFCL messages SEND RECEIVE *DDM distributed data management ADDMBR DELETE RENAME CHANGE EXTRACT RGZMBR CHGMBR INITIALIZE RMVMBR CLEAR LOAD RNMMBR COMMAND 42

43.
PREVENT REMOTE COMMANDS 1.Create CL program CRTCLPGM STOPCMDS SRCFILE( ) PGM PARM(&RTNCODE &DATA) DCL &DATA *CHAR 30 DCL &RTNCODE *CHAR 1 DCL &FUNC *CHAR 10 CHGVAR &FUNC (%SST(&DATA 21 10)) IF (&FUNC = 'COMMAND ') + THEN( CHGVAR &RTNCODE '0') ELSE CHGVAR &RTNCODE '1' 2. Change network attributes ENDPGM CHGNETA DDMACC(STOPCMDS) 43

44.
Prevent Remote Commands 1 of 2 and File Upload /****************************************************/ /* Installation instructions */ /* 1. Compile program */ /* CRTCLPGM PGM(LIB/EXIT1) */ /* SRCFILE( ) USRPRF(*OWNER) */ /* 2. Change owner of the program to user QSECOFR. */ /* Adopted authority allows the program sending */ /* to the audit journal */ /* CHGOBJOWN OBJ(LIB/EXIT1) */ /* OBJTYPE(*PGM) NEWOWN(QSECOFR) */ /* 3 Name the exit program in network attributes */ /* CHGNETA DDMACC(LIB/EXIT1) */ /* PCSACC(LIB/EXIT1) */ /* */ /* The audit journal QAUDJRN entries created are: */ /* 'X1' = Requests that are allowed */ /* 'X0' = Requests that are rejected */ /****************************************************/ PGM (&RC &STRU ) DCL &RC *CHAR 1 /*Return 1=allow */ /* 0=prevent*/ DCL &STRU *CHAR 200 /*Request description*/ DCL &USER *CHAR 10 /*User profile name */ DCL &APP1 *CHAR 10 /*Requested function */ DCL &APP2 *CHAR 10 /*Sub function */ DCL &TYPE *CHAR 2 /*Journal entry type */ 44

45.
Prevent Remote Commands 2 of 2 and File Upload MONMSG CPF0000 EXE(GOTO EXIT) /*If error exit*/ CHGVAR &RC '1' /*Allow request*/ CHGVAR &USER %SST(&STRU 1 10) /*Get user */ CHGVAR &APP1 %SST(&STRU 11 10) /*Get appl */ CHGVAR &APP2 %SST(&STRU 21 10) /*Get function/ /*Do not log IBM request to check license */ IF (&APP1 = '*LMSRV') GOTO EXIT IF &USER = 'XXXXXXXXX') GOTO LOG /* Prevent use of remote commands */ IF (&APP1 = '*DDM' *AND &APP2 = 'COMMAND') + CHGVAR &RC '0' /* Prevent the request */ ELSE /* Prevent file upload from PC users */ /* File download to PC is not prevented */ IF (&APP1 = '*TFRFCTL' *AND &APP2 = 'REPLACE') + CHGVAR &RC '0' /* Prevent the request */ /* Log request in the audit journal */ LOG:CHGVAR &TYPE ( 'X' *CAT &RC) SNDJRNE QAUDJRN TYPE(&TYPE) &ENTDTA(&STRU) EXIT:ENDPGM Good Way to Monitor Use 45

46.
The Exit WillDepend Upon the Operating Client Operating System File Transfer from following interfaces Operating Interactive PC5250 Rumba Interface ODBC System Emulation Emulation GUI API N/A EXIT1 EXIT1 N/A DOS N/A DOS extended EXIT1 N/A EXIT1 EXIT1 N/A Windows 3.x N/A EXIT1 EXIT1 EXIT1 N/A OS/2 EXIT2 EXIT1 EXIT1 EXIT1 EXIT2 OS/2 EXIT2 EXIT1 EXIT1 EXIT2 Optimized EXIT1 Windows 95/NT N/A Optimized EXIT2 EXIT2 EXIT1 EXIT2 Exit1 = Original File Transfer QIBM_QTF_TRANSFER Exit2 = Data Base Server QIBM_QZDA_NDB1 46

47.
Exit Program Usage 1.Two programs are required because parameters are different EXIT1 EXIT2 Original File Windows 95 and Transfer NT File Transfer 2. Limit all file transfer based upon library name Up load from UP_LIB Down load from DOWN_LIB Note: Not possible to determine the type of request for EXIT2. Allow transfer (UP and DOWN) in either library. 3. Log requests in audit journal 47

48.
Installation Instructions 1. Compileprograms CRTCLPGM PGM(XXX/EXIT1) SRCFILE(XXX/QCLSRC) + USRPRF(*OWNER) CRTCLPGM PGM(XXX/EXIT2) SRCFILE(XXX/QCLSRC) + USRPRF(*OWNER) 2. Change owner CHGOBJOWN OBJ(XXX/EXIT1) OBJTYPE(*PGM) + NEWOWN(QSECOFR) CHGOBJOWN OBJ(XXX/EXIT2) OBJTYPE(*PGM) + NEWOWN(QSECOFR) Adopt owners authority to allow the programs to send to the audit journal 48

49.
Installation Instructions 3. Registerthe exit programs ADDEXITPGM EXITPNT(QIBM_QTF_TRANSFER) + FORMAT(TRAN0100) PGMNBR(1)+ PGM(XXX/EXIT1) + TEXT('Limit to specific Libraries') ADDEXITPGM EXITPNT(QIBM_QZDA_NBR1 ) + FORMAT(ZDAD0100) PGMNBR(1)+ PGM(XXX/EXIT2) REPLACE(*NO) + TEXT('Limit to specific libraries') 4. Update the network attribute CHGNETA PCSACC(*REGFAC) 49

50.
EXIT1 - OriginalFile Transfer Exit 1 of 3 /*==========================================================*/ /* To compile: */ /* CRTCLPGM PGM(XXX/EXIT1) SRCFILE(XXX/QCLSRC) +*/ /* USRPRF(*OWNER) */ /* installation instructions: */ /* 1. Compile program */ /* 2. Change owner of the program to user QSECOFR. */ /* Adopted authority allows the program sending */ /* to the audit journal */ /* CHGOBJOWN OBJ(XXX/EXIT1) OBJTYPE(*PGM) + */ /* NEWOWN(QSECOFR) */ /* 3. Name the exit program in registration facility */ /* ADDEXITPGM EXITPNT(QIBM_QTF_TRANSFER) + */ /* FORMAT(TRAN0100) PGMNBR(1)+ */ /* PGM(XXX/EXIT1) + */ /* text('limit to specific libraries') */ /* 4. Set registration facility in the network attribute */ /* CHGNETA PCSACC(*REGFAC) */ /* The request is recorded in the audit journal */ /* The audit journal QAUDJRN entries created are: */ /* 'X1' = requests that are allowed */ /* 'X0' = requests that are rejected */ /*==========================================================*/ PGM PARM(&RC &STRU) DCL VAR(&RC) TYPE(*CHAR) LEN(1) /*1=allow 0=prevent*/ DCL VAR(&STRU) TYPE(*CHAR) LEN(80) /* request description */ 50

51.
EXIT1 - OriginalFile Transfer Exit 2 of 3 DCL VAR(&USER) TYPE(*CHAR) LEN(10) /* user profile */ DCL VAR(&APP1) TYPE(*CHAR) LEN(10) /* function */ DCL VAR(&APP2) TYPE(*CHAR) LEN(10) /* sub function */ DCL VAR(&TFOBJ) TYPE(*CHAR) LEN(10) /* file name */ DCL VAR(&TFLIB) TYPE(*CHAR) LEN(10) /*library */ DCL VAR(&TFMBR) TYPE(*CHAR) LEN(10) /* member */ DCL VAR(&TFFMT) TYPE(*CHAR) LEN(10) /* format */ DCL VAR(&TYPE) TYPE(*CHAR) LEN(2) /* journal type */ MONMSG MSGID(CPF0000) EXEC(GOTO CMDLBL(EXIT)) CHGVAR VAR(&RC) VALUE('1') /* set return code to + allow request unless rejected by program */ CHGVAR VAR(&USER) VALUE(%SST(&STRU 1 10)) /* get + user */ CHGVAR VAR(&APP2) VALUE(%SST(&STRU 21 10)) /* get + function */ 51

52.
EXIT1 - OriginalFile Transfer Exit 3 of 3 /*******************************************/ /* Prevent file upload from PC users */ /* except in the UP_LIB library */ /* prevent download to PC */ /* except in the DOWN_LIB library */ /*******************************************/ IF COND(&APP2 *EQ 'REPLACE') THEN(DO) IF COND(&TFLIB *NE 'UP_LIB ') THEN( + CHGVAR &RC) '0') /*prevent request*/ ENDDO IF COND(&APP2 *EQ 'SELECT') THEN(DO) IF COND(&TFLIB *NE ’DOWN_LIB ') THEN( + CHGVAR &RC) '0') /*prevent request*/ ENDDO /*******************************************/ /* Log request in the audit journal */ /*******************************************/ LOG: CHGVAR VAR(&TYPE) VALUE('X' *CAT &RC) SNDJRNE JRN(QAUDJRN) TYPE(&TYPE)+ Good Way to ENTDTA(&STRU) Monitor Use Exit:ENDPGM 52

53.
EXIT2 - Windows95/NT File Transfer Exit 1 of 3 /*===============================================================* / /* To compile: */ /* */ /* CRTCLPGM PGM(XXX/EXIT2) SRCFILE(XXX/QCLSRC) + */ /* USRPRF(*OWNER) */ /* */ /* installation instructions: */ /* 1. Compile program */ /* */ /* 2. Change owner of the program to user QSECOFR. */ /* Adopted authority allows the program sending */ /* to the audit journal */ /* */ /* CHGOBJOWN OBJ(XXX/EXIT2) OBJTYPE(*PGM) + */ /* NEWOWN(QSECOFR) */ /* */ /* 3. Name the exit program in registration facility */ /* */ /* ADDEXITPGM EXITPNT(QIBM_QZDA_NBR1 ) + */ /* FORMAT(ZDAD0100) PGMNBR(1)+ */ /* PGM(XXX/EXIT2) REPLACE(*NO) + */ /* text('limit to specific libraries') */ /* */ /* The request is recorded in the audit journal */ /* The audit journal QAUDJRN entries created are: */ /* 'Z1' = requests that are allowed */ /* 'Z0' = requests that are rejected */ /*==============================================================*/ PGM PARM(&RC &REQUEST) DCL VAR(&RC) TYPE(*CHAR) LEN(1) /* 1=allow 0=prevent*/ DCL VAR(&REQUEST) TYPE(*CHAR) LEN(700) /*request desc */ DCL 53 VAR(&TYPE) TYPE(*CHAR) LEN(2) /*journal entry type */

54.
EXIT2 - Windows95/NT File Transfer Exit 2 of 3 DCL var(&x1800) type(*char) len(4) + value(x'00001800') /* create database file*/ DCL var(&x1801) type(*char) len(4) + value(x'00001801') /* create source file*/ DCL var(&x1802) type(*char) len(4) + value(x'00001802') /* add member */ DCL var(&x1803) type(*char) len(4) + value(x'00001803') /* clear member */ DCL var(&x1804) type(*char) len(4) + value(x'00001804') /* delete member */ DCL var(&x1805) type(*char) len(4) + value(x'00001805') /* file override */ DCL var(&x1806) type(*char) len(4) + value(x'00001806') /* delete file override*/ DCL var(&x1807) type(*char) len(4) + value(x'00001807') /* create save file */ DCL var(&x1808) type(*char) len(4) + value(x'00001808') /* clear save file */ DCL var(&x1809) type(*char) len(4) + value(x'00001809') /* delete file */ /* OPTIMIZED DATABASE SERVER DECLARES */ DCL VAR(&DBFMT) TYPE(*CHAR) LEN(8) /* format name */ DCL VAR(&DBFID) TYPE(*CHAR) LEN(4) /* function identifier */ /* THE FOLLOWING PARAMETERS ADDITIONAL FOR FORMAT ZDAD0100 */ DCL VAR(&DBFILE) TYPE(*CHAR) LEN(128)/* file name */ DCL VAR(&DBLIB) TYPE(*CHAR) LEN(10) /* library name */ DCL VAR(&DBMBR) TYPE(*CHAR) LEN(10) /* member name */ DCL VAR(&DBAUT) TYPE(*CHAR) LEN(10) /* authority to file */ DCL VAR(&DBBFIL) TYPE(*CHAR) LEN(128) /* based on file name */ DCL VAR(&DBBLIB) TYPE(*CHAR) LEN(10) /* based on library name */ DCL VAR(&DBOFIL) TYPE(*CHAR) LEN(10) /* override file name */ DCL VAR(&DBOLIB) TYPE(*CHAR) LEN(10) /* override library name */ DCL VAR(&DBOMBR) TYPE(*CHAR) LEN(10) /* override member name */ 54

55.
EXIT2 - Windows95/NT File Transfer Exit 3 of 3 MONMSG MSGID(CPF0000) EXEC(GOTO CMDLBL(EXIT)) * allow request unless rejected by program */ CHGVAR VAR(&RC) VALUE('1') /* set variables from request description */ CHGVAR VAR(&DBFMT) VALUE(%SST(&REQUEST 21 8)) CHGVAR VAR(&DBFID) VALUE(%SST(&REQUEST 29 4)) CHGVAR VAR(&DBFILE) VALUE(%SST(&REQUEST 33 128)) CHGVAR VAR(&DBLIB) VALUE(%SST(&REQUEST 161 10)) CHGVAR VAR(&DBMBR) VALUE(%SST(&REQUEST 171 10)) CHGVAR VAR(&DBOFIL) VALUE(%SST(&REQUEST 329 10)) CHGVAR VAR(&DBOLIB) VALUE(%SST(&REQUEST 339 10)) CHGVAR VAR(&DBOMBR) VALUE(%SST(&REQUEST 349 10)) IF COND((&DBFID = &X1805)) THEN(DO) /* OVERRIDE */ IF COND(&DBOLIB = 'UP_LIB') THEN(GOTO LOG) IF COND(&DBOLIB = 'DOWN_LIB') THEN(GOTO LOG) CHGVAR VAR(&RC) VALUE('0') ENDDO /* log request in the audit journal */ LOG: CHGVAR VAR(&TYPE) VALUE('Z' *CAT &RC) SNDJRNE JRN(QAUDJRN) TYPE(&TYPE) ENTDTA(&REQUEST) EXIT: ENDPGM 55

56.
Allowing Specific UsersAccess IF &USER = ’User 1 ') GOTO LOG IF &USER = ’User 2 ') GOTO LOG Could check the name of the user in exit program + Good performance on exit program - To change users requires program to be modified - Security specification uses a different technique 56

57.
Allowing Specific UsersAccess Exit Program Read Could check the name of the user in the exit program + More flexible change users - used frequently Potential performance concern if - exit Additional file open each job with - different technique Security specification uses a 57

58.
Allowing Specific UsersAccess Exit Authorization Program CHKOBJ List Could check the authorization list for user access + More flexible change users + Minimal performance overhead + Does not require file open + Uses standard security interfaces 58

59.
Check an Authorization List Exit Authorization Program CHKOBJ List IF COND(………………. ) THEN(DO) CHKOBJ OBJ(QSYS/FILEREAD) + OBJTYPE(*AUTL) AUT(*USE) MONMSG MSGID(CPF9800) + EXEC(CHGVAR &RC '0') GOTO LOG Possible to check for different authorities ENDDO *USE for Read actions *CHANGE for Update actions 59

60.
Check an Authorization List Exit Authorization Program CHKOBJ List  See end of handout for an example program using authorization lists 60

61.
DB Security Outline LogicalFiles Column Level Security DB Exit Programs  Overview  Sample Exit Programs  iSeries Navigator (File Transfer and ODBC)  FTP 61

62.
FTP Server ExitPrograms FTP server logon exit program Permit or deny users to log on based on: User ID Password Client IP address  Establish an anonymous FTP server FTP request validation exit program Permit or deny FTP operation based on User profile Remote IP address of FTP client or server Directory, library, files (path names) CL commands 62

63.
Exit Program Flow OS/400 FTP Server User Exit 2 1 Program PARAMETERS 4 3 ❶ TCP/IP calls exit passing parameters ❷ Exit program processes parameters ❸ Exit program sets return code ❹ TCP/IP application performs operation based on exit program response 63

64.
TCP/IP Application Request Validation Exit Point Interface FTP Parameters User Exit Server Program Application identifier Input Binary(4) 0 FTP client program 1 FTP server program Operation identifier Input Binary(4) 0 Session initialization 1 Directory/library create 2 Directory/library deletion 3 Set current directory 4 List files 5 File deletion 6 Sending file 7 Receiving file 8 Renaming file 9 Execute CL command User profile Input Char(10) Remote IP address Input Char(*) Length of remote IP address Input Binary(4) Operation-specific information Input Char(*) Length of operation-specific information Input Binary(4) Return Code Output Binary(4) -1 Never allow this operation identifier 0 Reject the operation 1 Allow the operation 2 Always allow this operation identifier 64

65.
FTP Logon Interface FTP Parameters User Exit Logon Program Application identifier Input Binary(4) 1 FTP server program User identifier Input Char(*) Length of user identifier Input Binary(4) Authentication string Input Char(*) Length of authentication string Input Binary(4) Client IP address Input Char(*) Length of client IP address Input Binary(4) Return code Output Binary(4) 0 Reject the logon operation. 1 Continue the logon operation 2 Continue the logon operation with the specified user identifier and authentication string, and override the initial current library with exit program values 3 Continue the logon operation. Override the user profile and password with exit program values User profile Output Char(10) Password Output Char(10) Initial current library Output Char(10) 65

66.
FTP Server Request 1of 4 Validation /**********************************************************************/ /*Sample FTP server request validation exit program for anonymous FTP */ /*Notes: */ /*1.When the application id is 1 (ftp server) and the operation id is */ / 0 (session initialization), the job is running under the QTCP */ /* User profile when the exit program is called. In all other cases, */ /* The job is running under the user's profile. */ /*2. Create the exit program in a library with public authority */ /* *Exclude. The exit program itself be given a *EXCLUDE public */ /* The FTP server adopts the authority necessary to call the exit */ /*3. It is possible to use the same exit program for both the ftp */ /* Client And server request validation exit points. */ /**********************************************************************/ Tstreqcl: pgm parm(&appidin &opidin &usrprf &ipaddrin + &Iplenin &opinfoin &oplenin &allowop) Example from TCP/IP Configuration and Reference SC41-5420 66

67.
FTP Server Request 2of 4 Validation /* Declare input parameters */ DCL &APPIDIN *CHAR LEN(4) /* Application ID */ DCL &OPIDIN *CHAR LEN(4) /* Operation ID */ DCL &USRPRF *CHAR LEN(10) /* User profile */ DCL &IPADDRIN *CHAR /* Remote IP address */ DCL &IPLENIN *CHAR LEN(4) /* Length of IP address */ DCL &OPLENIN *CHAR LEN(4) /* Length of operation-spec info*/ DCL &OPINFOIN *CHAR LEN(9999) /*Operation-specific info */ DCL &ALLOWOP *CHAR LEN(4) /* allow (output) */ /* Declare local copies of parameters (in format usable by CL) */ DCL &APPID TYPE(*DEC) LEN(1 0) DCL &OPID TYPE(*DEC) LEN(1 0) DCL &IPLEN TYPE(*DEC) LEN(5 0) DCL &IPADDR *CHAR DCL &OPLEN) TYPE(*DEC) LEN(5 0) DCL &OPINFO *CHAR LEN(9999) DCL &PATHNAME *CHAR LEN(9999) /* Uppercase path name */ /* Declare values for allow(1) and no allow(0) */ DCL &ALLOW TYPE(*DEC) LEN(1 0) VALUE(1) DCL &NOALLOW TYPE(*DEC) LEN(1 0) VALUE(0) /* Declare request control block for QLGCNVCS (convert case) API*/ /* convert to uppercase based on job CCSID */ DCL &CASEREQ *CHAR LEN(22) + VALUE(X'00000001000000000000000000000000000000000000') DCL &ERROR *CHAR LEN(4) VALUE(X'00000000') 67

68.
FTP Server Request 3of 4 Validation /* Assign input parameters to local copies */ CHGVAR VAR(&APPID) VALUE(%BINARY(&APPIDIN)) CHGVAR VAR(&OPID) VALUE(%BINARY(&OPIDIN)) CHGVAR VAR(&IPLEN) VALUE(%BINARY(&IPLENIN)) CHGVAR VAR(&IPADDR) VALUE(%SUBSTRING(&IPADDRIN 1 &IPLEN)) CHGVAR VAR(&OPLEN) VALUE(%BINARY(&OPLENIN)) /* Handle operation specific information field (which is var Len */ IF COND(&OPLEN = 0) THEN(CHGVAR VAR(&OPINFO) VALUE(' ')) ELSE CMD(CHGVAR VAR(&OPINFO) VALUE(%SST(&OPINFOIN 1 &OPLEN))) /* Operation ID 0 (incoming connection): reject if connection is */ /* through interface 9.8.7.6, accept otherwise. */ /* example.) This capability could be used to only allow incoming */ /* connections from an internal network and reject them from the */ /* "real" Internet, if the connection to the Internet */ /* NOTE: For FTP server, operation 0 is ALWAYS under QTCP profile */ IF COND(&OPID = 0) THEN(DO) IF COND(&OPINFO = '9.8.7.6') THEN(CHGVAR + VAR(%BINARY(&ALLOWOP)) VALUE(&NOALLOW)) ELSE CMD(CHGVAR VAR(%BINARY(&ALLOWOP)) + VALUE(&ALLOW)) GOTO CMDLBL(END) ENDDO 68

69.
FTP Server Request 4of 4 IF Validation COND(&USRPRF = 'ANONYMOUS ') THEN(DO) /* Do not allow the following operations for ANONYMOUS user: */ IF &OPID = 2 | /*Directory/library deletion */ + &OPID = 5 | /* File deletion */ + &OPID = 7 | /* Receive file */ + &OPID = 8 | /* Rename file */ + &OPID = 9 /* Execute cmd */ + THEN(CHGVAR VAR(%BINARY(&ALLOWOP)) VALUE(&NOALLOW)) ELSE CMD(DO) IF COND(&OPID = 3 | /* Change directory */ + &OPID = 4 | /* List directory */ + &OPID = 6 ) DO /* Send file */ /* Convert path name to uppercase (since names in "root” and */ /* library file systems are not case sensitive */ CALL PGM(QLGCNVCS) PARM(&CASEREQ &OPINFO + &PATHNAME &OPLENIN &ERROR) /* Note: must check for "/public" directory by itself and */ /* path names starting with "/public/". */ IF COND((%SUBSTRING(&PATHNAME 1 20) *NE + '/QSYS.LIB/PUBLIC.LIB') *AND + (&PATHNAME *NE '/PUBLIC') *AND + (%SUBSTRING(&PATHNAME 1 8) *NE '/PUBLIC/')) + THEN(CHGVAR VAR(%BINARY(&ALLOWOP)) VALUE(&NOALLOW)) ELSE CMD(CHGVAR VAR(%BINARY(&ALLOWOP)) VALUE(&ALLOW)) ENDDO ENDDO ENDDO /* Not ANONYMOUS user: allow everything */ ELSE CMD(CHGVAR VAR(%BINARY(&ALLOWOP)) VALUE(&ALLOW)) END: ENDPGM 69

70.
Logon Exit Programfor Anonymous FTP 1of 2 /********************************************************************/ /* Sample FTP server logon exit program. */ /* Note: This program is a sample only and has not undergone any */ /* review or testing. */ /* Additional notes: */ /* 1. When the FTP server logon exit is called, the FTP server job */ /* is running under the QTCP user profile. */ /* 2. For the ANONYMOUS case, users can add logging capability (for */ /* example, write the E-mail address entered for the password and*/ /* the client IP address to a log file). */ /* 3. IBM recommends that you create the exit program in a library */ /* with *PUBLIC authority of *EXCLUDE,and give the exit program */ /* itself a *PUBLIC authority of *EXCLUDE. The FTP server adopts*/ /* authority when it is necessary call the exit program. */ /********************************************************************/ TSTLOGCL:PGM PARM(&APPIDIN &USRIN &USRLENIN &AUTIN &AUTLENIN + &IPADDRIN &IPLENIN &RETCDOUT &USRPRFOUT &PASSWDOUT &CURLIBOUT) /* Declare input parameters */ DCL &APPIDIN *CHAR LEN(4) /* Application identifier */ DCL &USRIN *CHAR LEN(999)/* User ID */ DCL &USRLENIN *CHAR LEN(4) /* Length of user ID */ DCL &AUTIN *CHAR LEN(999)/* Authentication string */ DCL &AUTLENIN *CHAR LEN(4) /* Length of auth. string */ DCL &IPADDRIN *CHAR LEN(15) /* Client IP address */ DCL &IPLENIN *CHAR LEN(4) /* IP address length */ DCL &RETCDOUT *CHAR LEN(4) /* return code (out) */ DCL &USRPRFOUT *CHAR LEN(10) /* user profile (out) */ DCL &PASSWDOUT *CHAR LEN(10) /* password (out) */ DCL &CURLIBOUT *CHAR LEN(10) /* current library (out) */ 70

71.
Logon Exit Programfor Anonymous FTP 2of 2 /* Declare local copies of parameters (in format usable by CL) */ DCL VAR(&APPID) TYPE(*DEC) LEN(1 0) DCL VAR(&USRLEN) TYPE(*DEC) LEN(5 0) DCL VAR(&AUTLEN) TYPE(*DEC) LEN(5 0) DCL VAR(&IPLEN) TYPE(*DEC) LEN(5 0) /* Assign input parameters to local copies */ CHGVAR VAR(&APPID) VALUE(%BINARY(&APPIDIN)) CHGVAR VAR(&USRLEN) VALUE(%BINARY(&USRLENIN)) CHGVAR VAR(&AUTLEN) VALUE(%BINARY(&AUTLENIN)) CHGVAR VAR(&IPLEN) VALUE(%BINARY(&IPLENIN)) CHGVAR VAR(%BINARY(&RETCDOUT)) VALUE(1)) /* Check for ANONYMOUS user. Allow for ANONYMOUS,etc. as */ /* regular user profile. */ IF COND(&USRLEN = 9) THEN(DO) IF COND(%SST(&USRIN 1 9) = 'ANONYMOUS') THEN(DO) /* For anonymous user:force user profile ANONYMOUS */ /* current library to PUBLIC. */ CHGVAR VAR(%BINARY(&RETCDOUT)) VALUE(6) CHGVAR VAR(&USRPRFOUT) VALUE('ANONYMOUS ') CHGVAR VAR(&CURLIBOUT) VALUE('PUBLIC ') ENDDO ENDDO /* Any other user: proceed with normal logon processing. */ END: ENDPGM Example from TCP/IP Configuration and Reference SC41-5420 71

73.
SUMMARY Menu security is not adequate to limit a user. You must protect data from access via the other Client Access servers: • FILE TRANSFER • REMOTE COMMANDS • FOLDER ACCESS Use exit programs to block use of remote commands 73

74.
SUMMARY Specifying exit program using network attributes is not recommended  Increase overhead  Network attributes a limited set of exits Use Registration Facility to specify exit programs 74

75.
 If youhave additional questions or want more information please contact me Wayne O. Evans Phone (520) 578-7785 WOEvans@AOL.com www.WOEvans-security.com 75

2010 db security

More Related Content

What's hot

Viewers also liked

Similar to 2010 db security

Recently uploaded

2010 db security