Security is a very BIG word in database programming. How can we password protect our data and put some form of security into our programs. dBASE has the ability to provide powerful data protection, but this only provides protection at the table level. Sometimes we need to programmatically control what the user can access as far as menu items and forms.

From the dbase help file:

“Setting up security

Visual dBASE provides built-in levels of security against unauthorized access to encrypted databases and tables. This table-level security depends on data encryption.

Sensitive tables should always be encrypted by using the database vendor’s administration software. Visual dBASE’s password dialog is presented whenever a user tries to access a form linked to an encrypted table or database. The user’s response to the password dialog is passed to the encrypted table or database for verification before Visual dBASE will display the form. See your database vendor’s documentation about security administration for SQL, ODBC, or non-Standard systems.

The DBF and DB tables you create within Visual dBASE have built-in encryption. Visual dBASE provides direct database administration security access to set passwords for BDE-Standard DB and DBF tables, as well as the extensive user-access and privilege-level security features of DBF tables.”

So then how do we employ an application with the ability to control the access level of the user and at the same time provide some level of data encryption so that users cannot come across other users' id and password.

Answer: write our own security program, which we can refer to in our menu's forms, etc.

Setting up the user table

The first thing we need to do is set up a table to contain the user information. What we need is a user id, user name, password, and access levels. We also need to be able to encrypt this table so that no one else can understand what is in the table if they happen to come across it. We must also be able to decrypt the table so that this information can be read with ease under some kind of program supervision.

So we create a table with the following structure, which can change according to need

Structure for table User.dbf
Table type DBASE
Version 4
Number of rows 6
Last update 05/28/1999

The Security and Access Form

The security form is where all our user access will be determined. It is a simple form with two entryfields on it and a bunch of checkboxes corresponding to the amount of user options we put into our table. By checking the checkbox we give that particular user access, and unchecked access is then denied.

I have just separated the different option to correspond with the menu items and tabs within forms.

When the user id is entered, the form will seek for that user id. If none is found, then  the plus button will be enabled. By clicking it you can then add that user to the table and apply the restriction concerning that user. The only problem is that our table is encrypted.

Now the question is how do we encrypt and decrypt the data. Well with the use of the ASCII table and a simple algorithm we can accomplish this.

do while mCount <= len(trim(_app.mUser))
   MCHUCK = (chr(asc(substr(trim(_app.mUser),mCount,1))+128))
   _app.mID := _app.mID + mChuck
   mCount := mCount + 1
enddo
IF form.rowset.findkey(_app.mID)

We loop through each letter of the entryfield until we reach the end

Once we have the encrypted value in our memory variable we can then replace the record we created with the BeginAppend() function with the contents of the variable.

FORM.ROWSET.FIELDS["user_id"].VALUE = _app.mID

By adding an extra character value to each letter in the entryfield or table field we can accomplish what might seem to be some kind of encryption.. I know that this isn't mind blowing, but keep a few things in mind. First of all, by looking at it, it does look good and any normal user would not even chance their luck or knowledge trying to decrypt it, secondly, you could further encrypt the table with dbase protect, although I have not tried this so cannot say whether it will work or not.

The letter A has an ASCII Value of 65 (keep in mind that uppercase and lower case have different values). By adding 128 to 65 I get 193. The printable character for 193 is (depending on your language driver, but keep in mind that you want to return a printable character) which is Á. To test this out, within MS Word holding down the alt key and with the number pad key in 193. And so on we go with B being 194 which would equate to Â, C would be Ã, and so on. Have a look at any ASCII table to see the possibilities. To decrypt all we do is reverse the process. We find out the ASCII value of the extended character and instead of adding 128 we subtract 128 so 193 -128 would give 65 and that would then represent the letter A. We store the character equivalent of a numeric value with the chr() function. So chr(65) would return the letter A. And to get the numeric value of a character we would use the asc() function. So asc("g") would return 103.

Giving The User Access

How do we then give the user access or deny access. Well, simple. All we have to do is be on the correct record for the user in the user table and then refer to each option (user_opt1) to see whether it is checked, allow access, or unchecked, deny access. We do this by using a login screen.

Changing The Password

What happens if the user needs to change their password?  Well, by double clicking on the password entryfield of the login form you initiate a password change form. I suppose I could have used a pushbutton. This password change form checks to see if a valid password for that user exists and then allows them to change their password. It double checks to see if the new password entered is correct by using a second entryfield and requests the user to confirm the new password.

The same technique is used as discussed earlier to encrypt and decrypt the password. This is also a good time to mention that I make use of the “Password.cc” custom class for version 5.6. The reason I still use this version is because of backward computability. Explanation of the custom control is beyond the scope of this article and so I would suggest that you read the custom control code. But I will Insert a snippet for the sake of clarification here.

* USAGE: When creating a form, select the "Set Up Custom Controls"
* menu from the "File" menu. Then select "Add" button on the
* "dBASE Custom Controls" page of the "Set Up Custom Controls"
* dialog. Select this file from the file selection dialog
* that comes up. The controls in this file will be available
* on the "Custom" page of the "Controls" window.
*
* You must assign a correctPassword property in your code
* by using the SetCorrectPassword() method. After the password
* is entered, you can verify that it was correct by using the
* IsEnteredPasswordOk() method.

A Back Door

Oh no! I have lost my password. Haven't you heard that before? Sure you have, but then the supervisor can just go and change it can't he. But what happens if the administrator has lost his/her password. Well Microsoft and many other application houses say that your password is lost, and cannot be retrieved for security reasons. Well I don't believe this. If those programmers are anything like me, they would have provided a back door. This is what we do. Create a form that when activated does all the decrypting of the user table and displays the information in such a way that we can read it.Also, have the ability to scan the table to view the other user information. This is done simply by creating a secret key stroke that nobody else knows about, so that you can then rescue yourself and the users from an embarrassing situation. In your startup program use the on key command to assign a key sequence that would display your decrypted password form.

on key LABEL alt+X DO PASS.WFM

This indeed is a very simply solution. Perhaps one could even make it a lot more secure or difficult to get too. For instance, what if the users in the user.dbf were either lost or corrupt, one would still need to get into the program. As suggested, a fixed password, perhaps, the programmers name or some one close, spelled backwards or spelled randomly could be inserted into the code, so that, when entered our app would then bypass the login screen and enable us to change or rectify the user table. I have used the user id as “REBOR” and the password as “ElOhIm”. Note the usage of alternating capitalization. In the Login screen:

if upper(trim(this.value)) == "REBOR" // The first five letters of my name spelled backward
form.passwordentry1.enabled = TRUE
_app.mpassword:= "ElOhIm" // The password is a Jewish word with alternating letter
                          // changed in case
return
else

Then, in the menu, we would just look for this password and allow access to the form that enables us to change or correct the user table. By doing this you would need to comment out the line in the startup app that enables the previous form to show eg. // on key LABEL alt+X DO PASS.WFM. This would then disable the Alt-X key combination.

Conclusion

This is not the be all and end all on this subject but just an example of how a task can be accomplished and how when putting your mind to it you can accomplish almost anything.

Special thanks to William DuPlessis