[[https://www.revelation.com/|Sign up on the Revelation Software website to have access to the most current content, and to be able to ask questions and get answers from the Revelation community]] ==== Encryption via MFS on a Btree Indexed Field (AREV Specific) ==== === At 26 JAN 2005 04:20:46PM R Johler wrote: === {{tag>"AREV Specific"}} Our encryption MFS encrypts the confidentail data field upon Writes and decrypts it upon Reads, so all of our application logic is unchanged. It is just an extra layer of security over the physical media (harddrive and backup tapes) that we need, not extra in application security. This works great in one table where the confidential field has no btree index applied to it. In the second table, the btree is applied to the confidential data field. This results in the !file containing encrypted values, such that, users cannot query the table with un-encrypted data. For example, we want to lookup the record using the confidential data because the person who owns this data is on the telephone with us, and requires customer service and so he/she tells us the confidential data and we look it up. I have added our encryption mfs both before and after the SI.MFS on this table, and get the same result. Is the solution to apply a decryption MFS to the !file? ---- === At 26 JAN 2005 04:46PM Matt Sorrell wrote: === Ralph, What if you add a synonym field that is indexed? This synonym field would have an ICONV associated with it that applies the same encryption algorithm. This should, I believe, allow you to enter a plain-text value that the ICONV converts to the encrypted value for teh BTREE search. Either that, or you can create a small window in which you type the plain-text value. This window would apply the encryption algorithm, yielding the encrypted string, and then query the BTREE for the encrypted value. It could then return an active select list of the matching records. Just some food for thought, msorrel@greyhound.com [url=http://www.greyhound.com]Greyhound Lines, Inc.[/url] ---- === At 26 JAN 2005 08:19PM dsig _at_ sigafoos.org wrote: === Matt you have hit the nail on the head .. I too would suggest a window which handles the encrypts the data (hopefully the same routine the mfs is using) and then query btree and display results for selection. Just as an aside .. this will be shown at my very exciting talk at the next conference which deals with Mfs and BFS .. oh way cool dsig _at_ sigafoos.org.com onmouseover=window.status=the new revelation technology .. a refreshing change;return(true)" David Tod Sigafoos ~ SigSolutions ---- === At 27 JAN 2005 03:27PM R Johler wrote: === FYI - Here is how I fixed this. 1. Prepare the mfs to decrypt on Reads and encrypt on Writes and place it after the SI.MFS call. Don't write the encrypted data from your mfs just save it into memory variables and set a flag that means 'encrypted data pending'. Let the plain text data be written. 2. Then add after the "CALL @nextfs(code...." IF the code is 'Write.record' and there is 'encrypted data pending' an RTP57 subroutine call is made that writes out the encrypted data. The SI.MFS never sees the encrypted data, and so the index stays in plain text. Since the index is in plain text, no application level code need be changed. ---- === At 27 JAN 2005 03:57PM Matt Sorrell wrote: === My only concern with this is that you now have information, apparently important enough to be encrypted, stored in a table in an unencrypted format. To me, this seems to break the confidentiality levels you achieved with the MFS. The '!' tables are readable, can be queried, and data can be extracted from them. Also, record keys are associated with the data, so its not much of a stretch to then get the baseline data record. It seems to me that you have possibly defeated your encryption scheme with this approach. Just my (unsolicited) $0.02. msorrel@greyhound.com [url=http://www.greyhound.com]Greyhound Lines, Inc.[/url] ---- === At 27 JAN 2005 08:49PM dsig _at_ sigafoos.org wrote: === you are right IF you want the data to be in 'visible' to users. Since the btree isn't encrypted it isn't secure But as I said .. you are right .. one of the tricks of the mfs is positioning them to do what you need just depends on the level on encryption/security you need dsig _at_ sigafoos.org.com onmouseover=window.status=the new revelation technology .. a refreshing change;return(true)" David Tod Sigafoos ~ SigSolutions ---- === At 01 FEB 2005 04:35AM Hippo wrote: === It depends on application ... Raplhp's solution has the advantage that interval queries can be performed, what is why the B-Tree index is mostly used. ... Hashing is better for searching an exact value, so other methods may be considered in that case. Matt's solution for exact value search using hashed B-tree is nice, but needs a bit of recodding for each exact value search, and there is no way to extend it to interval searching. I vote for Ralph's one ... I suppose, that the indexed values itself are not so confidential, the confidential is their connection to other info and it's hidden for non AREV users. (At least it's sufficient for CONVERT NOPERM TO PERM IN @RECORD codding:)) ---- === At 01 FEB 2005 10:48AM dsig _at_ sigafoos.org wrote: === Not sure what you are getting at? Are you saying it is better to have data, that has been deemed needing encryption, should only be encrypted in the physical record but not in the indexes? If so then ok I guess. I just always figured that data needing encryption .. should be encrypted. dsig _at_ sigafoos.org.com onmouseover=window.status=the new revelation technology .. a refreshing change;return(true)" David Tod Sigafoos ~ SigSolutions ---- === At 01 FEB 2005 10:56AM support@sprezzatura.com wrote: === We read Hippo's response as saying that if you store the index node data unencrypted you can perform "From- To", "Greater Than", "Less than" etc but that if you encrypt the index node data you can only do=or #. There is a concomitant assertion that this is unlikely to be a problem as the unencrypted data is not in situ. HTH ;) support@sprezzatura.com The Sprezzatura Group Web Site [i]World Leaders in all things RevSoft[/i] [img]http://www.sprezzatura.com/zz.gif[/img] ---- === At 01 FEB 2005 11:23AM Hippo wrote: === OK, if the indexed values should be encrypted even when they are isolated from the records, there is solution with MFS on index table. (It can use independent crypting method, but what is important, the method should be used both on keys and records (of the index file)) ... Yes, I have meant <= queries. I tought about the case, when whole records are crypted, not only "secure parts". The Ralph's solution needs no recodding and this is why I recomend it. ---- === At 03 FEB 2005 03:56PM R Johler wrote: === The data being encrypted is really only a portion of the SET of data that is confidential. Having one part and not the other is useless. So if leave the !file plain text (without the rest of the confidential data set) and encrypt the main file (with all the confidential data fields) we feel safe. Not ideal, but an ok trade-off. The trade off being easy and reasonably fast access by our authorized arev users, versus securing the data on the hard-drive and backup tapes from bit-wise reading if someone gets say a tape. If however, someone knows how to make the !file be encrypted but allow an arev user to run queries as if it isn't - please let me know! We would like encryption all around, but can't afford to make security a road-block to doing the job. Good discussion! Thanks. ---- === At 03 FEB 2005 04:16PM Hippo wrote: === Ralph, I hope the post you are responding to described the method how to crypt !file. The system does not know the actual records and Id's stored in the table it knows only decrypted ones so everything goes unchanged. Just for security reasons it's better to encrypt both @RECORDS and @ID's in the !file. (May be You are doing so even for data file. I only say in !file it is much more important) I didn't test the idea (and don't plan to do that), but with @ID crypting one should take care not only to reads and writes, but also to every key manipulation ... e.a. readnexting returns set of keys ... @FM, @VM ... should be avoided in encrypted keys ... I thing these are just technical details;) ---- === At 03 FEB 2005 04:42PM Matt Sorrell wrote: === It all depends on whether or not you want to do substring or greater-than, less-than matching. If all of your queries will be of the straight equality type, then you can leave the index in an encrypted form. All you have to do is provide a collector window that drives the query. The user would enter their plain-text search value and then press, presumably, to run the query. The process that runs after the window would take the entered value, apply the same encryption algorithm used by the MFS, and then query the index using the encrypted value. It would then return the appropriate records. Again, without knowing the encryption algorithm being used, I am working under the assumption this only works for equality queries. If the algorithm is as simple as a ROT-13 type algorithm, then this would also work for queries where you are looking for sub-string matches as well. msorrel@greyhound.com [url=http://www.greyhound.com]Greyhound Lines, Inc.[/url] ---- === At 03 FEB 2005 05:00PM Hippo wrote: === This one window needed to translate the searched value is the bit of programing needed for each table you decide to crypt. Suppose you have a huge application, you know only a little about it and you are asked to encrypt all the data in the application. I don't thing your solution is feasible for such a task but Ralph's one is. ---- === At 04 FEB 2005 04:40AM Hippo wrote: === Ralph, now I am reading the first post marked as "RESOLVED" ... do I understand it well, your original MFS tried doing WRITE command? I would expect this: Crypting MFS should be called from SI.MFS, it traps READ, READO and WRITE calls and in this case changes RECORD variable (the 6th one) and calls @NEXT.FS. Did You test such codding? It seems to be easier to be called from SI then to call SI from it... P.S.: In the case of keys codding ... the NAME (the 4th one) should be changed, too and the set of trapped calls should be extended ... DELETE, LOCK, UNLOCK in the easy way, READNEXT is more complicated as it returns a set of encrypted keys. I don't thing SELECT , CLEARSELECT should be modified. (You must set STATUS 1 for UNLOCK.ALL, FLUSH, INSTALL, FLUSH.CACHE calls) ---- === At 04 FEB 2005 10:12AM Matt Sorrell wrote: === Hippo, I would disagree. If the same MFS or algorithm is used throughout, and the entire record is encrypted, then it is quite a simple prospect to have the window have several fields. FMC 1 is the table to search. This can have an option on it that reads SYSTABLES for any tables with the encryption MFS. Your list is now dynamic and updates automatically if you add encryption to another table. FMC 2 is field name. It can provide a popup of the dictionary fields for the nominated table. FMC 3 is simply the unencrypted search value. This type of robust, self-updating search facility is quite easy to develop as long as the application is consistent in its development. msorrel@greyhound.com [url=http://www.greyhound.com]Greyhound Lines, Inc.[/url] ---- === At 04 FEB 2005 11:37AM Hippo wrote: === OK, OK, I can see you can easily create "locate engine (for =)" which will work on all tables of your application (and codding is done just once for that purpose). What I cannot imagine is how you can modify hundreds of application windows to maintain their functionality. Similarly for programs modifying the tables originaly without encryption. (There is big simillarity with the AREV -] IO conversion problem. The approach to rewrite all application windows is not feasible for a lot of us and therefore we either look for automatic AREV-]OI conversion or simply stay with AREV) ---- === At 11 FEB 2005 12:22PM R Johler wrote: === Fasinating! For some reason this is really interesting to you all. Thanks. Our encryption method works given our list of requirements: 1. No changes to business logic. 2. Transparent to all users. 3. Protect the data set on the hard-drive and back-up tapes. 4. With 18+ million records, initialization and changes in encryption method will occur while users are on-line (and we run 24/7). So yes, we do have PART of our data set in plain text in the btree index data, but the first two requirements pretty much made that essential. Our MFS upon reading the record, checks to see if the data is encrypted, if so it decrypts the data, using the appropriate decrypt cipher. Our data can reside on the disk then in plain text or any known encryption method varying from record to record. Our user always sees plain text data. When writing, the MFS encrypts the data AFTER the @callnext which performs the Write, and writes again the encrypted data to the file using an RTP57 call. This is so the SI.MFS cannot trap the encrypted data and then index on that encrypted data. ---- === At 09 FEB 2007 09:54AM Simon G Wilmot wrote: === Ralph, I am doing something very similar at the moment. However, when doing the final RTP57 call I am getting an FS231 error. Could you post some of the code so that I can see if I am doing something stupid !! Thanks, Simon ---- === At 09 FEB 2007 10:38AM Ralph Johler wrote: === Simon I don't know what the fs231 error is telling you. It might be a different problem than the mfs. See this post from Kevin http://www.revelation.com/__85256DC1002A4A9E.nsf/0/12346EF28BC1802A852570E70049A681?OpenDocument However I think (it's been awhile) that the trick I used was to NOT actually encrypt the mfs's passed in argument record, but to assign record to another variable that is encrypted. Then the "tell" variable in the WRITE.RECORD check below allows me to be lazy and encrypt each record's column as time allowed, versus shutting down and while all users are kicked-off and encrypt the entire table. If the record's column we read was already encrypted, we don't encrypt it again and so "tell" is true. Also I have two columns in the table that are encrypted, so to save time (the encrypt/decrypt cycle), I first concatenate them together and encrypt the combined string. Then at decrypt time after decrypting them the mfs de-concatenates them and assigns each part to it's appropriate column. CODE FRAGEMENT encrypt_rec=record ... do some encrypting here ... * Getting ready to leave the mfs * 'record' in the arg list below is not encrypted CALL @nextfs(code, fs, handle, name, fmc, record, status) IF code EQ WRITE.RECORD THEN IF tell THEN RTP57(WRITE.RECORD, "RTP57", encrypt_handle, encrypt_name, --] encrypt_fmc, encrypt_rec, encrypt_status) END END ---- === At 09 FEB 2007 10:58AM Simon G Wilmot wrote: === Thanks Ralph, That is basically what I have done ... are the 'encrypted_' vars in the RTP57 call the same as those above except for the Record ?? Simon ---- === At 09 FEB 2007 11:02AM Simon G Wilmot wrote: === ... and I havent got a works subscription anymore !! Any chance cut and paste to swilly_mdhc@yahoo.co.uk please, Thanks, Simon ---- === At 09 FEB 2007 03:39PM Ralph Johler wrote: === Yes you are correct. Here is more code too...fwiw! *****************************************************************8 0054 $INSERT SYSINCLUDE, LOGICAL 0055 $INSERT SYSINCLUDE, FILE.SYSTEM.EQUATES 0056 0057 fs=DELETE(bfs, 1, 1, 1) 0058 nextfs=fs 0059 @FILE.ERROR=" 0060 0061 $INSERT SYSINCLUDE, FILE.SYSTEM.ONGOSUB 0062 0063 RETURN 0064 0065 ****************************************************************************** 0066 0067 READ.RECORD: 0068 READO.RECORD: 0069 * Until we use the bfs to actually read the id in 'name', we don't have the 0070 * associated @record for that id in 'record'. So place our code, after the 0071 * call @nextfs. 0072 0073 CALL @nextfs(code, fs, handle, name, fmc, record, status) 0074 0075 IF UNASSIGNED(record) ELSE 0076 | 0077 | * 4 IS data1 0078 | * 29 IS data2 0079 | * The 'tell' is on data2, but is used for ALL encryption on the record. 0080 | * To save processing time, the data1 & 2 are combined and then encrypted 0081 | * as one field, with the resulting cipher being stored in the data2 field. 0082 | * The pipe "|" is used as the delimiter. 0083 | 0084 | tell=record1,1 0085 | 0086 | BEGIN CASE 0087 | CASE tell EQ 'Z' 0088 | | * We have encrypted. Note that the 'tells' cannot be hex characters 0089 | | * or for methodX, the letters M, N, or O (which have brought you this msg). 0090 | | * First method is arbitrarily coded "Z", and it is named method1. 0091 | | d2=record2,999 0092 | | val=DECODE1(d2) 0093 | | record =FIELD(val,"|",1,1) 0094 | | record=FIELD(val,"|",2,1) 0095 | | 0096 | CASE tell EQ 'Y' 0097 | | * The tell 'Y' has been reserved for the method2 encryption method. 0098 | | d2=record2,999 0099 | | val=DECODE2(d2) 0100 | | record =FIELD(val,"|",1,1) 0101 | | record=FIELD(val,"|",2,1) 0102 | | 0103 | END CASE 0104 END 0105 0106 DELETE.RECORD: 0107 0108 RETURN 0109 0110 ****************************************************************************** 0111 0112 WRITE.RECORD: 0113 0114 * Write is simpler, add the encoding method 'tell' or code starting with that special char 0115 * NOTE: The 'tells' cannot be hex characters or M, N, O. 0116 * Z=method1. 0117 * Y=method2. 0118 0119 tell=record1,1 0120 BEGIN CASE 0121 CASE tell EQ 'Z' 0122 | * The data is already encoded, so do NOT encode it again 0123 | tell=' 0124 CASE tell EQ 'Y' 0125 | * The data is already encoded, so do NOT encode it again 0126 | tell=' 0127 CASE 1 0128 | * Not encoded, we place the current encoding method logic here, with 0129 | * the appropriate 'tell' for that method - not non-hex and not M,N, or 0130 | * O for method1. 0131 | 0132 | encrypt_rec=record 0133 | * We started encypting in Jan 2005, with the tell 'Z' and method1. 0134 | d2=encrypt_rec 0135 | IF LEN(encrypt_rec) THEN 0136 | | d2 := "|":encrypt_rec 0137 | | encrypt_rec=' 0138 | END 0139 | d2=ENCODE1(d2) 0140 | encrypt_rec=Z":d2 0141 | 0142 | /* 0143 | * Here we can switch to method2 encryption 0144 | d2=encrypt_rec 0145 | IF LEN(encrypt_rec) THEN 0146 | d2 := "|":encrypt_rec 0147 | encrypt_rec=' 0148 | END 0149 | d2=ENCODE2(d2) 0150 | encrypt_rec=Y":d2 0151 | */ 0152 | 0153 | encrypt_handle=handle 0154 | encrypt_name =name 0155 | encrypt_fmc =fmc 0156 | encrypt_status=' 0157 END CASE 0158 0159 0160 LOCK.RECORD: 0161 UNLOCK.RECORD: 0162 SELECT: 0163 READNEXT: 0164 CLEARSELECT: 0165 CLEARFILE: 0166 0167 0168 CALL @nextfs(code, fs, handle, name, fmc, record, status) 0169 0170 IF code EQ WRITE.RECORD THEN 0171 | IF tell THEN 0172 | | RTP57(WRITE.RECORD, "RTP57", encrypt_handle, encrypt_name, encrypt_fmc, encrypt_rec, encrypt_status) 0173 | END 0174 END 0175 0176 RETURN 0177 0178 ****************************************************************************** 0179 0180 OPEN.FILE: 0181 CREATE.FILE: 0182 RENAME.FILE: 0183 MOVE.FILE: 0184 DELETE.FILE: 0185 OPEN.MEDIA: 0186 READ.MEDIA: 0187 CREATE.MEDIA: 0188 WRITE.MEDIA: 0189 RESERVED: 0190 OMNI.SCRIPT: 0191 CLOSE.MEDIA: 0192 RECORD.COUNT: 0193 REMAKE.FILE: 0194 CREATE.INDEX: 0195 DELETE.INDEX: 0196 UPDATE.INDEX: 0197 SELECT.INDEX: 0198 READNEXT.INDEX: 0199 0200 CALL @nextfs(code, fs, handle, name, fmc, record, status) 0201 0202 RETURN 0203 0204 ****************************************************************************** 0205 0206 UNLOCK.ALL: 0207 FLUSH: 0208 INSTALL: 0209 0210 STATUS=TRUE$ 0211 0212 RETURN ---- === At 12 FEB 2007 06:55AM Simon G Wilmot wrote: === Hi Ralph, Thanks for that, pretty much as I already have things ... I have traced the problem back to the file handle including the indexing bits as well. The question I now have (for anybody !!), is there a simple way to strip out that info programmatically. There are ways that I can do this, but I dont see re-inventing the wheel if it is not necessary ... TIA, Simon ---- === At 23 FEB 2007 09:51AM Vladan Majerech wrote: === I am not sure it is good technique to call RTP57 directly from MFS. This prohibits adding further MFS's later. [[https://www.revelation.com/revweb/oecgi4p.php/O4W_HANDOFF?DESTN=O4W_RUN_FORM&INQID=NONWORKS_READ&SUMMARY=1&KEY=393ADDA34103BA8885256F95007541FD|View this thread on the forum...]]