Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2005

    Unanswered: Scramble data fields


    I have a need to scramble account numbers in a table. Im using a Make Table query run on the original table with ScramNumber#:[CustomerNumber]*Rnd() in the query. I then delete the original table and rename the table made above to replace it. I dont need the actual account numbers, just the format of the table for what I need to do. Actually counting customers is one of the processes I would still need to do so deleting all the records is not an option. However there are a few problems. One, the account numbers change format i.e. from 0000-0000-0000 to say 000000.000000. Also its a pain to run the query, delete the old table, and rename the new table, then go into the recycle bin to delete the deleted table so that there is no original account numbers available. Can anyone see a better more efficient way of doing this? Basically Im trying to secure the account numbers in case the laptop should fall into the wrong hands. I have another table with social security numbers I do the same process with, so this becomes a real pain.


  2. #2
    Join Date
    Apr 2004
    outside the rim
    I assume you are placing data from some sort of secure file system onto your laptop and you want to work with customer data without the account number.

    Why not run a make table query that does not include the account number and place just that table into a seperate mdb and place that on your laptop? If you find a suitable format, you can use a bit of code to automate the creation of the seperate database file containing tables with do not include the sensitive information so it becomes a click and save operation when you want to update your laptop?


  3. #3
    Join Date
    Dec 2002
    Prverenges, Switzerland
    your *Rnd() process is not secure. it is also somewhere between inconvenient and unworkable when you have 000-000-000 format.

    tcace's make table or a DELETE/INSERT import combinatation together with a secure hash would preserve the uniqueness of each account number and be unhackable.
    SELECT myHash(accountNo) AS scrambled, this, that FROM secureTable

    demo of using capicom to produce SHA1 hash is attached.

    Attached Files Attached Files
    currently using SS 2008R2

  4. #4
    Join Date
    Aug 2005
    Thanks guys... I'll check it out. (tcase) - I need the account number data,it just doesn't have to be a real account number. Many of the reports and forms use the (an) account number to function and report properly. Accessing the database on the laptop is mearly for out of the office design changes and enhancements.
    (izyryder) - I looked at the hash demo... I assume your concept is to secure the real data in the entire database without the need to scramble the data contained therein? I'm not sure I have the knowledge to get that working. I'll give it a shot though. Thanks for the demo.


  5. #5
    Join Date
    Mar 2009
    Provided Answers: 14
    You can define a public function that performs some obfuscation on the data and use this function in your query :
    INSERT INTO Tbl_Destibation ( Field_1, Field_2 )
    SELECT obfuscate([Field_1]) AS Expr1, obfuscate([Field_2]) AS Expr2
    FROM Tbl_Source;
    Public Function Obfuscate(AnyData As Variant) As Variant
        ' Use with Text or Numeric data types.
        ' Does not work with Date/Time, Boolean, etc. 
        Dim strBuffer As String
        Dim intLBound As Integer
        Dim intUBound As Integer
        Dim i As Integer
        strBuffer = CStr(Nz(AnyData, ""))
        For i = 1 To Len(strBuffer)
            Select Case Mid(strBuffer, i, 1)
                Case "0" To "9"
                    intUBound = 57
                    intLBound = 48
                Case "A" To "Z"
                    intUBound = 65
                    intLBound = 90
                Case "a" To "z", "", "", "", "", "", "", "", "", "", "", ""
                    ' Common diacritics in French. Can be adapted for other languages.
                    intUBound = 122
                    intLBound = 97
                Case Else
                    intUBound = 0
            End Select
            If intUBound > 0 Then Mid(strBuffer, i, 1) = Chr(Int((intUBound - intLBound + 1) * Rnd + intLBound))
        Next i
        Obfuscate = strBuffer
    End Function
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts