Question:
How can I set Paradox table passwords in Delphi 2.0?

Answer:

Let Me Blow Off Some Steam About the BDE

Unfortunately, setting passwords on Paradox tables cannot be done using native Delphi calls. You have to use Borland Database Engine (BDE) calls. I’ve written a few examples of making BDE calls in the past, and I always complain about how difficult it is to use the BDE natively. The calls aren’t hard to make, but getting the right parameters to enter into the functions can be a trying experience to say the least. I’ll be truthful with you — poring through the BDE is not for the faint of heart.

One of the major drawbacks to programming with BDE calls is that the only hard copy documentation available from Borland for the BDE is the “Borland Database Engine” manual, which you can purchase from Borland International for about US$15.00. But that’s not the worst of it; this book is written for a C/C++ audience, so if you don’t know that language, it’s not easy to translate the C/C++ nomenclature to Delphi. Furthermore, while Delphi 2.0 includes a help file for the BDE32 (a great improvement over version 1.0), as with any help file, you have to know what to look for in order to use it effectively.

Further compounding the problem with the BDE documentation is the fact that it’s not written to address the object-oriented nature of the BDE. What do I mean by this?

With any object-oriented structure, you don’t just deal with functions, procedures and methods, but also with various data types, structures and properties over and above the procedural code. The BDE documentation only addresses the procedures in the BDE, so the would-be user is forced to search for the definitions of the proper constants, structures, and objects that may be required by a particular call.

That’s my biggest beef about the BDE documentation, and I wish Borland would fix it. Do you think I’m just sounding off? Maybe so, but consider this: the BDE is an integral part of the entire Borland Client/Server strategy. So if Borland wants wide acceptance of its use, the company must make the process of learning the BDE much easier.

That said, let’s move on to the subject at hand. I just felt that I needed to emphasize that making BDE calls, until you’re really used to the structure of the BDE, is no trivial matter. However, with regular use and reference to third party information resources, you can become proficient at it.

Oh yeah, we were talking about passwords…

There are two types of passwords you can create on Paradox tables: Master and Auxiliary. Master passwords are set for access to a table resource, while auxiliary passwords define security for field-level access. Depending upon the type of security you want to set, you will take specific steps. However, you’ll generally follow the proceeding steps in your code when performing security-related operations on a table:

1. Open up a table and set its Exclusive property to False.
2. Gather existing security information on the table with a Security Description structure variable of type SECDesc (For auxiliary passwords only).
3. Initialize a table description structure variable of type TBLDesc.
4. Set the security parameters for the TBLDesc variable.
5. Grab the database handle the table is using.
6. Close the table down, then call dbiDoRestructure to set the security information.
7. If the restructure is successful, make the password current for the current session by calling Session.AddPassword.

Listed below are the procedures I use to add and remove Master and Auxiliary passwords from Paradox tables. After listing each procedure, I’ll briefly discuss what it does.

Adding a Master Password

Below is the listing for the first procedure, which adds a master password to any Paradox table.

procedure AddMasterPword(dbName,        //database alias                         tblName,       //table name                         mastPword:     //password to set                         string);var  tbl    : TTable;  tblDesc: CRTblDesc;  hDb    : hDBIDb;begin  {Create the table and see if we can open and set Exclusive rights   to the resource. Abort if we can’t do both}  tbl := TTable.Create(Application);  with tbl do  try    Active        := False;    DatabaseName  := dbName;    TableName     := tblName;    TableType     := ttParadox;    {The following section taken out of the example in the help file     for setting exclusive property on a table.}    repeat { until successful or Cancel button is pressed }      try        Exclusive := True; { See if it will open }        Active := True;        Break; { If no error, exit the loop }      except on EDatabaseError do        if MessageDlg(‘Could not open Table1 exclusively – OK to retry?’, mtError,            [mbOK, mbCancel], 0) <> mrOK then raise; { If not, reraise to abort }      end;    until      False;    {Initialize and populate the table description structure and fill in     name, type, password, and set Protection to true}    FillChar(tblDesc, SizeOf(CRTblDesc), 0);    with tblDesc do    begin      StrPCopy(szTblName, tbl.TableName);      StrCopy(szTblType, szParadox);      StrPCopy(szPassword, mastPword);      bProtected := True;    end;    {Get the database handle from the table, then shut the table down}    Check(DbiGetObjFromObj(hDBIObj(Handle), objDATABASE, hDBIObj(hDb)));    Close;    Check(DbiDoRestructure(hDb, 1, @TblDesc, nil, nil, nil, FALSE));    Session.AddPassword(mastPword);  finally    Free;  end;end;

There’s one thing you should note whether you’re adding a master or an auxiliary password. You cannot set the Exclusive property on a table that’s active. This makes sense because you will want to know if you can have it exclusively before you open it anyway. Regarding the above procedure, the code comments explain fairly well what happens at each major step along the way. But let’s look at the steps in detail because they form the basis for the rest of the procedures.

First, notice that I enclosed all the functionality within a try..finally statement using the tbl instance as the major context (the with..do statement). While seemingly trivial, it conveys a very important point in that in object-oriented programming, most of what you do is within the context of some object. For our purposes here, our operations are within the context of the TTable to which we want to add a password. My point is that wherever possible, encapsulate your code within the context of the instance variable you’re using. It’ll save you the type of having to do dot notation, plus it forces you to arrange your code in a very orderly fashion, ultimately making it easier to read.

Now let’s move on to the specific steps in the code.

The first step: A table instance is created based upon the table name supplied as a formal parameter. The appropriate database, table name and type are set and the Active property is set to False.

  tbl := TTable.Create(Application);  with tbl do  try    Active        := False;    DatabaseName  := dbName;    TableName     := tblName;    TableType     := ttParadox;    {The following section taken out of the example in the help file     for setting exclusive property on a table.}    repeat { until successful or Cancel button is pressed }      try        Exclusive := True; { See if it will open }        Active := True;        Break; { If no error, exit the loop }      except on EDatabaseError do        if MessageDlg(‘Could not open Table1 exclusively – OK to retry?’, mtError,            [mbOK, mbCancel], 0) <> mrOK then raise; { If not, reraise to abort }      end;    until      False;

Then a repeat..until loop is started to set the Exclusive property to True. In order to set a password, we must restructure the table and that requires that you have exclusive use of it. I took the example out of the BDE32 help file to demonstrate how you can provide a method for interacting with the user should the program be unable to get exclusive rights to the table. However, if you can, rewrite that section to be just a try..except block which will exit the program entirely if exclusive rights cannot be set. Notice that at this point, the table is not active. You can’t set exclusive rights on a table once you open it for viewing or editing; besides, it makes sense to have it this way because you need to know whether you can have exclusive access to the resource before you open it.

The next thing that happens in the code is that the table description structure is initialized. I’ll leave it up to you to look up the structure in the BDE32.HLP file. I’ve left it out here because it’s a fairly large structure. But let me make an important point. You can’t just declare BDE structures in your var statement and then use them; you have to allocate memory for the entire structure with a call to FillChar (you’ll see it in the code below). The only indication you have of this requirement (and I learned it the hard way) is that the documentation describes this structure as client-allocated. That means all the work for filling up the structure is left to you, even if you don’t use all of the elements in the structure.

    {Initialize and populate the table description structure and fill in     name, type, password, and set Protection to true}    FillChar(tblDesc, SizeOf(CRTblDesc), 0);    with tblDesc do    begin      StrPCopy(szTblName, tbl.TableName);      StrCopy(szTblType, szParadox);      StrPCopy(szPassword, mastPword);      bProtected := True;    end;

Once the structure is initialized and allocated, to add a master password we’re only interested in modifying a few elements in the structure: the table name, type, password to add, and setting the protection status to true. Setting protection to true says I’m encrypting this table! Notice that setting the table name and master password is done with a StrPCopy while setting the table type is done with StrCopy. This is because the table name and password are string variables which must be converted to null-terminated strings, while the szParadox value is a string constant which will be implicitly converted to a null-terminated string when copied into an element or var of that type.

Finally, in order to do a restructure, we first have to get the database handle that’s being used by the table and that’s done by the call to DbiGetObjFromObj. We could have just as easily done this by opening up a database var that pointed to the same database as the table, but that would have required much more code. Instead, I make single call to DbiGetObjFromObj to extract the database handle from the table and set the handle’s value to the variable hDb that I declared in the var section of the procedure.

    {Get the database handle from the table, then shut the table down}    Check(DbiGetObjFromObj(hDBIObj(Handle), objDATABASE, hDBIObj(hDb)));    Close;    Check(DbiDoRestructure(hDb, 1, @TblDesc, nil, nil, nil, FALSE));    Session.AddPassword(mastPword);  finally    Free;

Once I’ve got the handle, I make the call to DbiDoRestructure passing the handle, the number of table descriptors (which is always 1), then set the SaveAs, KeyViol table name, and Problems table to nil, then set the AnalyzeOnly (which is used to get structure information) parameter to False. Finally, I add the password to the current session to make it valid for the session I’m working in, free the table, and exit.

Notice that I’ve enclosed all my native BDE calls in the Check procedure. Before I explain what Check does, let me explain what happens behind the scenes when you call a BDE function.

Whenever you make a call to a BDE function, if successful it will return DBIERR_NONE, which is a constant name for a zero integer as its return value; conversely, it will return a non-zero value that is equivalent to a BDE error constant describing the problem. Each BDE function has a number of error constants associated with it, and the number varies depending upon the function you call. Traditionally, in the old days of BDE programming, you would have declared a variable with type DbiResult and set your function equal to that variable to get its result code, then you’d evaluate the result code and take appropriate action.

For example, let’s look at the following code snippet from a procedure I wrote using the old method of evaluation:

  dbRes := DBIOpenDatabase(nil, nil, dbiREADWRITE, dbiOPENSHARED,                            nil, 0, @pOptFldDesc, @pOptParams, hDb);  case dbRes of    DBIERR_UNKNOWNDB    : msg := ‘Database specified is unknown. Check your drivers.’;    DBIERR_NOCONFIGFILE : msg := ‘No IDAPI.CFG file for this machine. Install BDE.’;    DBIERR_DBLIMIT      : msg := ‘Maximum number of databases have been opened.’ +                                 ‘ Close down one or more databases and retry’;  end;  if (dbRes <> DBIERR_NONE) then  begin    raise Exception.Create(msg);    Exit;  end;

For every BDE call you made back then, you would have had to write some code similar to the code above. Kind of a pain, but necessary to trap errors. Furthermore, it required that you really understand the nature of the problem.

The Check procedure does all of this for you. Check takes the return value of a BDE function and makes a call to the DBIErr BDE error-checking procedure which evaluates the value. If it’s non-zero, it raises an exception and pops up the error message associated with the error value. The nice thing about this is that DBIErr has all the error codes, error text and the smarts to raise the exception. Now, all BDE calls are one-liners as opposed to the several lines that we listed above. So use Check whenever you make a BDE function call — you’ll save yourself a lot of time.

Adding an Auxiliary Password

Here’s the code listing for adding an auxiliary password on a table. I’ve already covered most of the details of what’s going on in the code for setting a master table, so I won’t go into much detail here other than discussing the security descriptor structure. Let’s look at the code…

{=======================================================================================   Here are the prvType values which define auxiliary security on a table.  prvType = (prvNONE, prvREADONLY, prvMODIFY, prvINSERT, prvINSDEL, prvFULL, prvUNKNOWN) =======================================================================================}procedure AddAuxPword(dbName,             //database alias                      tblName,            //table name                      mastPword,          //master password name                      auxPword: string;   //aux. password name                      auxRights: PRVType);//aux. table rightsvar  hDb     : hDBIDb;  tbl     : TTable;  tblDesc : CRTblDesc;  secDsc  : SECDesc;  opType  : CROpType;  I       : Word;begin  //initialize vars  opType := crAdd;  Session.AddPassword(mastPword);  //Pretty much do the same thing as in adding a master password  //to start out with.  tbl := TTable.Create(Application);  with tbl do  try    Active        := False;    DatabaseName  := dbName;    TableName     := tblName;    repeat { until successful or Cancel button is pressed }      try        Exclusive := True; { See if it will open }        Active := True;        Break; { If no error, exit the loop }      except on EDatabaseError do        if MessageDlg(‘Could not open Table1 exclusively – OK to retry?’, mtError,            [mbOK, mbCancel], 0) <> mrOK then raise; { If not, reraise to abort }      end;    until      False;    FillChar(secDsc, SizeOf(secDsc), 0);    with secDsc do    begin      eprvTable   := auxRights;   //set auxiliary table priveleges      iFamRights  := NoFamRights; //set family rights      for I := 0 to tbl.FieldCount – 1 do        aprvFld[I] := prvFULL;      StrPCopy(szPassword, auxPword);//finally set the password    end;    FillChar(tblDesc, SizeOf(CRTblDesc), 0);    with tblDesc do    begin      StrPCopy(szTblName, tbl.TableName);      StrCopy(szTblType, szPARADOX);      StrPCopy(szPassword, mastPWord);      iSecRecCount  := 1;      pecrSecOp     := @opType;      psecDesc      := @secDsc;      bProtected    := True;    end;    Check(DbiGetObjFromObj(hDBIObj(Handle), objDATABASE, hDBIObj(hDb)));    Close;    Check(DbiDoRestructure(hDb, 1, @TblDesc, nil, nil, nil, FALSE));  finally    Free;  end;end;

The only difference between adding an auxiliary as opposed to a master password to a Paradox table is the definition of a security descriptor, the subsequent assignment of that structure and the operation type to the table descriptor. Setting the security descriptor is handled by the following code:

    FillChar(secDsc, SizeOf(secDsc), 0);    with secDsc do    begin      eprvTable   := auxRights;   //set auxiliary table priveleges      iFamRights  := NoFamRights; //set family rights      for I := 0 to tbl.FieldCount – 1 do        aprvFld[I] := prvFULL;      StrPCopy(szPassword, auxPword);//finally set the password    end;

Above, notice the for loop for setting field privileges above. To keep the code simple, I’ve set the field privileges to prvFull. However, it is entirely possible to set different privileges for different fields. I provided a generic routine that assumes you want to have all field privileges for a given type of auxiliary table right. Having said this, it begs the question of why you’d want to use auxiliary passwords in the first place.

You’d use them to set up varying degrees of access to your tables. For instance, let’s say user Mary has access to all fields of a table because she is the supervisor of the group, so she has a certain auxiliary password assigned to her. However, user John, who answers to Mary, shouldn’t have access to all the fields because there is sensitive information about other employees in the group. So he uses another auxiliary password that doesn’t permit him to access those fields. The point about auxiliary passwords is this: They afford you the ability to define specific field-level security per password, a powerful way to manage data in a large group setting.

Removing Master and Auxiliary Passwords

Removing passwords is almost exactly like adding passwords with just some alterations or additions. Let’s look at the code below for removing both master and auxiliary passwords, then have a discussion about them afterwards:

Removing a Master Password

The procedure for removing a master password is almost identical to the code for adding one, with just a couple of differences: You don’t set the table descriptor’s password property, and you set the protected property to false. It’s that simple. So instead of writing an entirely new procedure to do roughly the same thing, let’s rename the AddMasterPword to SetMasterPword so we can add or remove master passwords from the same procedure. Here’s the modified listing:

procedure SetMasterPword(dbName,              //database alias                         tblName,             //table name                         mastPword : string;  //password to set                         AddPword  : Boolean);//Add = True, Remove = False;var  tbl    : TTable;  tblDesc: CRTblDesc;  hDb    : hDBIDb;begin  {Create the table and see if we can open and set Exclusive rights   to the resource. Abort if we can’t do both}  tbl := TTable.Create(Application);  with tbl do  try    Active        := False;    DatabaseName  := dbName;    TableName     := tblName;    {The following section taken out of the example in the help file     for setting exclusive property on a table.}    repeat { until successful or Cancel button is pressed }      try        Exclusive := True; { See if it will open }        Active := True;        Break; { If no error, exit the loop }      except on EDatabaseError do        if MessageDlg(‘Could not open Table1 exclusively – OK to retry?’, mtError,            [mbOK, mbCancel], 0) <> mrOK then raise; { If not, reraise to abort }      end;    until      False;    {Initialize and table description structure and fill in     name, type, password, and set Protection. If we’re adding     a password, then set the password, if not, just ignore mastPword     and set protection to false}    FillChar(tblDesc, SizeOf(CRTblDesc), 0);    with tblDesc do    begin      StrPCopy(szTblName, tbl.TableName);      StrCopy(szTblType, szPARADOX);      if AddPword then        StrPCopy(szPassword, mastPword);      bProtected := AddPword;    end;    {Get the database handle from the table, then shut the table down}    Check(DbiGetObjFromObj(hDBIObj(Handle), objDATABASE, hDBIObj(hDb)));    Close;    Check(DbiDoRestructure(hDb, 1, @TblDesc, nil, nil, nil, FALSE));    if AddPword then      Session.AddPassword(mastPword);  finally    Free;  end;end;

That’s what I call a clean implementation! With that single procedure we can easily add or remove master passwords with a single call. However, it’s not as easy to do with auxiliary passwords, as we’ll see below.

Removing Auxiliary Passwords

Similar to removing master passwords, removing an auxiliary password is pretty much the same process as adding one. However, there’s one fundamental process that must be performed when removing an auxiliary password that doesn’t easily lend itself to be included in one procedure. The problem is that a table can potentially have several auxiliary passwords that are stored in an array of security descriptors. In order to remove an auxiliary password, we have to iterate through the array of security descriptors to find the one that has a password that is the same as the one we want to remove. It’s not a difficult process, but it changes the behavior of the passwording enough to merit the definition of another procedure. Here it is:

procedure RemoveAuxPword(dbName, tblName, mastPword, auxPword: string);var  tbl       : TTable;  tblDesc   : CRTblDesc;  secDsc    : SECDesc;  hDb       : hDBIDb;  opType    : CROpType;  hCur      : hDBICur;begin  //initialize vars  opType := crDrop; //set auxiliary password option  tbl := TTable.Create(Application);  with tbl do  try    Active        := False;    DatabaseName  := dbName;    TableName     := tblName;    repeat { until successful or Cancel button is pressed }      try        Exclusive := True; { See if it will open }        Active := True;        Break; { If no error, exit the loop }      except on EDatabaseError do        if MessageDlg(‘Could not open Table1 exclusively – OK to retry?’, mtError,            [mbOK, mbCancel], 0) <> mrOK then raise; { If not, reraise to abort }      end;    until      False;    {In this section, we have to get the security number for the security descriptor     number that matches our auxiliary password. That way we know what to remove}    FillChar(secDsc, SizeOf(SECDesc), 0);    {Get security information for the list and load it into the hCur record}    Check(DbiOpenSecurityList(DBHandle, PChar(TableName), nil, hCur));    Check(DbiSetToBegin(hCur));    {Now iterate through the structure and at each record load the security     information into the secDsc buffer. Once you find the right record,     break, and the secDsc structure will be filled.}    while (DbiGetNextRecord(hCur, dbiNOLOCK, @secDsc, nil) = DBIERR_NONE) do      if (secDsc.szPassword = auxPword) then        Break      else        secDsc.iSecNum := 0; //set it to “not found”    Check(DbiCloseCursor(hCur));    if secDsc.iSecNum = 0 then      raise Exception.Create(‘Specified auxiliary password does not exist for this table’);    {Now, fill up the table descriptor structure.}    FillChar(TblDesc, SizeOf(CRTblDesc), 0);    with TblDesc do    begin      StrPCopy(szTblName, tbl.TableName);      StrCopy(szTblType, szPARADOX);      StrPCopy(szPassword, mastPword);      iSecRecCount  := 1;      pecrSecOp     := @opType; //previously set to crDrop      psecDesc      := @secDsc;      bProtected    := True;    //unlike master password, retain overall protection status    end;    Check(DbiGetObjFromObj(hDBIObj(Handle), objDATABASE, hDBIObj(hDb)));    Close;    Check(DbiDoRestructure(hDb, 1, @TblDesc, nil, nil, nil, FALSE));  finally    Free;  end;end;

The fundamental difference between adding and removing an auxiliary password is contained in the code snippet below:

    {Get security information of the table and load it into the hCur list}    Check(DbiOpenSecurityList(DBHandle, PChar(TableName), nil, hCur));    Check(DbiSetToBegin(hCur));    {Now iterate through the structure and at each record load the security     information into the secDsc buffer. Once you find the right record,     break, and the secDsc structure will be filled.}    while (DbiGetNextRecord(hCur, dbiNOLOCK, @secDsc, nil) = DBIERR_NONE) do      if (secDsc.szPassword = auxPword) then        Break      else        secDsc.iSecNum := 0; //set it to “not found”    Check(DbiCloseCursor(hCur));    if secDsc.iSecNum = 0 then      raise Exception.Create(‘Specified auxiliary password does not exist for this table’);

You’ll notice that we’re now using an hDBICur or cursor variable that’s set as a cursor to the security descriptors. This was the wrench in the machinery that made me put the code in a separate procedure. I didn’t want to declare a variable and then use it only conditionally; that’s not a very good practice.

Let’s go back to the topic at hand. As the program iterates through the security descriptors, if the password for the security descriptor is the same as the entered auxiliary password, then the program breaks and the iSecNum element would be set to a non-zero value. The only time the security descriptor iSecNum element value would be zero was if the security descriptor with the specified auxiliary password wasn’t found. In that case, an exception is raised and program exits.

This is a lot to swallow for simple operations, and I thank you for bearing with me through the article. However, I hope I was able to provide you with some deeper insight into the BDE, insight that could help propel you along in using it. Mind you, you don’t want to supplant the native Delphi calls, but there are some things you’ve got to be able to do that native Delphi calls just can’t handle. And when it comes to data, the BDE is the only way to go.