Administrative Operations with ADS and Delphi

While ADS requires little in the way of periodic maintenance to keep it running smoothly, many applications need to provide administrative functionality related to the management of users, groups, and objects.

This section is designed to provide you with insight into exposing administrative functions in your client applications. Two related, yet different, operations are demonstrated here. In the first, a new table is added to the database and all groups are granted access rights to it. This operation requires that you establish an administrative connection. The second operation involves permitting individual users to modify their own passwords. Especially in the security-conscious world of modern database management, this feature is often considered an essential step to protecting data.

Like many of the other operations described in this chapter, Delphi provides a variety of different means for implementing these features. An obvious solution is to use SQL queries to perform these tasks. This approach is demonstrated in other chapters in this part of the book, and in many of those cases represents the only mechanism available.

Since the SQL approach is shown elsewhere, the following sections demonstrate how to implement these operations using methods of the AdsTable, AdsConnection, and AdsDictionary components. While this approach is sometimes more involved than the SQL approach, it is nonetheless valuable in that it demonstrates the utility of the various TDataSet descendant components. If you prefer to use the SQL approach in your Delphi applications, refer to the SQL statements used in Chapters 13–16.

Creating a Table and Granting Rights to It

The Delphi_TDataSet project permits a user to enter the name of a table that will be created in the data dictionary, after which all groups will be granted rights to the table. This operation is demonstrated in the following event handler, which is associated with the OnClick event of the Create Table and Grant Rights button (shown in Figure 12-1). Unlike most of the code shown in this chapter, several comment lines are retained here, due to its complexity:

procedure TForm1.CreateTableBtnClick(Sender: TObject); var   AdminConnection: TAdsConnection;   AdminTable: TAdsTable;   Strings: TStringList;   i: Integer; begin   if TableNameText.Text = '' then   begin   ShowMessage('Please enter the name of the table to create');   Exit;   end;   //Create Connection and Table objects   AdminConnection := TAdsConnection.Create(nil);   AdminConnection.Name := 'Admin';   AdminTable := TAdsTable.Create(AdminConnection);   AdminTable.DatabaseName := AdminConnection.Name;   Strings := TStringList.Create;   try   //Configure Connection and Table objects   AdminConnection.AliasName := AdsConnection1.AliasName;   AdminConnection.AdsServerTypes :=   AdsConnection1.AdsServerTypes;   AdminConnection.Username := 'adssys';   AdminConnection.Password := 'password';   AdminConnection.LoginPrompt := False;   AdminConnection.IsConnected := True;   AdminConnection.GetTableNames(Strings, TableNameText.Text);   for i := 0 to Pred(Strings.Count) do   if AnsiCompareText(Strings.Strings[i],   TableNameText.Text) = 0 then   begin   ShowMessage('This table already exists. Cannot create');   Exit;   end;   //Define new table structure and create it   with AdminTable.FieldDefs do   begin   Add('Full Name', ftString, 30);   Add('Date of Birth', ftDate);   with AddFieldDef do   begin   Name := 'Credit Limit';   DataType := ftBCD;   Precision := 20;   Size := 4;   end;   Add('Active', ftBoolean);   end;   AdminTable.TableType := ttAdsADT;   AdminTable.TableName := TableNameText.Text;   AdminTable.CreateTable;   //Configure and connect the AdsDictionary object   AdsDictionary1.AliasName := AdminConnection.AliasName;   AdsDictionary1.AdsServerTypes :=   AdminConnection.AdsServerTypes;   AdsDictionary1.UserName := AdminConnection.Username;   AdsDictionary1.Password := AdminConnection.Password;   AdsDictionary1.LoginPrompt := False;   AdsDictionary1.Connect;   Strings.Clear;   AdsDictionary1.GetGroupNames(Strings);   if Strings.Count = 0 then   begin   ShowMessage('No groups to grant rights to');   Exit;   end;   //Grant access rights to all groups   for i := 0 to Pred(Strings.Count) do   AdsDictionary1.SetObjectAccessRights(TableNameText.Text,   Strings.Strings[i], 'RW');   //cleanup   finally   AdsDictionary1.Disconnect;   Strings.Free;   AdminTable.Free;   AdminConnection.IsConnected := False;   AdminConnection.Free;   end;   ShowMessage('The ' + TableNameText.Text + ' table has been ' +   'created, with rights granted to all groups'); end; 

This event handler demonstrates a number of interesting techniques. First, while it uses an AdsDictionary component that was placed at design time onto the main form (shown in Figure 12-1), the AdsConnection and AdsTable used by the administrative connection are created and then discarded at runtime. This approach is always valid, and could have been used by many of the other event handlers listed in this chapter. In most cases, however, components that are placed and configured at design time are easier to maintain.

After verifying that the requested table does not already exist in the data dictionary, the administrative connection is configured and opened, and a new AdsTable is configured to use it. Next, the table’s structure is defined (using both the Add and AddFieldDefs methods of the AdsTable), and the table is created with a call to CreateTable.

Note

The administrative user name and passwords are represented by string literals in this code segment. This was done for convenience, but in a real application, either you would ask for this information from the user or you would scramble this data to prevent its discovery.

After creating the table, the AdsDictionary component is configured and opened. Finally, the list of groups is retrieved and used to grant read and write access to the table.

Changing a User Password

A user can change the password on their own connection, if you permit this. In most cases, only when every user has a distinct user name would you expose this functionality in a client application. When multiple users share a user name, this operation is usually reserved for an application administrator.

As was done in the preceding section, this code demonstrates changing a user password using an AdsDictionary component. For an example of changing a password using the sp_ModifyUserProperty stored procedure, refer to Chapter 13, 14, or 15.

procedure TForm1.ChangePasswordBtnClick(Sender: TObject); var   UserName: String;   OldPass: String;   NewPass1: String;   NewPass2: String;   {$HINTS OFF}   function CheckPass(UName, UPass: String): Boolean;   var   TempConnection: TAdsConnection;   begin   result := False;   TempConnection := TAdsConnection.Create(nil);   try   TempConnection.AliasName := AdsConnection1.AliasName;   TempConnection.AdsServerTypes :=   AdsConnection1.AdsServerTypes;   TempConnection.Username := UName;   TempConnection.Password := UPass;   TempConnection.LoginPrompt := False;   try   TempConnection.IsConnected := True;   except   result := False;   end;   result := True;   finally   TempConnection.IsConnected := False;   TempConnection.Free;   end;   end; //CheckPass   {$HINTS ON} begin   UserName := AdsConnection1.Username;   OldPass := '';   InputBox('Password', 'Enter your current password', OldPass);   if OldPass = '' then Exit;   if not CheckPass(UserName, OldPass) then   begin   ShowMessage('Cannot validate your current password. ' +   'Cannot change password');   Exit;   end;   NewPass1 := '';   NewPass2 := '';   InputBox('Password', 'Enter your new password', NewPass1);   if NewPass1 = '' then   begin   ShowMessage('Password cannot be blank. ' +   'Cannot change password');   Exit;   end;   InputBox('Password', 'Confirm your new password', NewPass2);   if NewPass1 <> NewPass2 then   begin   ShowMessage('Passwords did not match. ' +   'Cannot change password');   Exit;   end;   //Connect AdsDictionary1 and change password   AdsDictionary1.AliasName := AdsConnection1.AliasName;   AdsDictionary1.AdsServerTypes :=   AdsConnection1.AdsServerTypes;   AdsDictionary1.UserName := AdsConnection1.Username;   AdsDictionary1.Password := AdsConnection1.Password;   AdsDictionary1.LoginPrompt := False;   AdsDictionary1.Connect;   AdsDictionary1.SetUserProperty(AdsConnection1.Username,   ADS_DD_USER_PASSWORD, PChar(NewPass1),   (StrLen(PChar(NewPass1)) + 1));   AdsDictionary1.Disconnect;   ShowMessage('Password successfully changed. ' +   'New password will be valid next time you connect'); end;

This code segment is a bit simpler than the preceding one. After verifying that the user knows the current password with which they are connected, they are prompted for their new password twice, for confirmation purposes. Next, the AdsDictionary component is configured to connect with the user account, after which its SetUserProperty method is invoked to change the user’s password. As the final dialog box displayed by this event handler indicates, this password will be valid once the user terminates all connections on this user account.

Note

If you run this code, and change the password of the adsuser account, you should use the Advantage Data Architect to change the password back to password. Otherwise, you will not be able to run this project again, since the password is assigned to the Password property of the AdsConnection component.