Administrative Operations with ADS and ADO

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.

Creating a Table and Granting Rights to It

The VB_ADO.vbp 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 subprocedure, which is associated with the click event of the Create Table and Grant Rights button (shown in Figure 14-1):

Dim Found As Boolean If TableNameText.Text = "" Then   MsgBox "Please enter the name of the table to create"   Exit Sub End If 'Check for semicolon hack If InStr(1, TableNameText.Text, ";", vbTextCompare) <> 0 Then   MsgBox "Table name may not contain a semicolon"   Exit Sub End If If AdsRecordset.State = adStateOpen Then   AdsRecordset.Close End If AdminConnection.ConnectionString = _   "Provider=Advantage OLE DB Provider;" + _   "Data Source=" + DataPath + ";user ID=adssys;" + _   "password=password;" + _   "ServerType=ADS_LOCAL_SERVER | ADS_REMOTE_SERVER;" + _   "FilterOptions=RESPECT_WHEN_COUNTING;TrimTrailingSpaces=True" AdminConnection.Open Set AdminCommand.ActiveConnection = AdminConnection AdsRecordset.Open "SELECT * FROM system.tables", _   AdminConnection, adOpenDynamic, adLockPessimistic, adCmdText AdsRecordset.MoveFirst Found = False Do While Not AdsRecordset.EOF   If UCase(AdsRecordset.Fields(0).Value) = _   UCase(TableNameText.Text) Then   Found = True   Exit Do   End If   AdsRecordset.MoveNext Loop If Found Then   MsgBox "This table already exists. Cannot create"   Exit Sub End If AdminCommand.CommandText = "CREATE TABLE " + TableNameText.Text + _   "([Full Name] CHAR(30)," + _   "[Date of Birth] DATE," + _   "[Credit Limit] MONEY, " + _   "Active LOGICAL)" AdminCommand.Execute AdsRecordset.Close AdsRecordset.Open "SELECT * FROM system.usergroups", _   AdminConnection, adOpenDynamic, adLockPessimistic, adCmdText If AdsRecordset.BOF And AdsRecordset.EOF Then   MsgBox "No groups to grant rights to"   Exit Sub End If AdsRecordset.MoveFirst While Not AdsRecordset.EOF   AdminCommand.CommandText = "GRANT ALL ON " + _   TableNameText.Text + " TO """ + _   AdsRecordset.Fields(0).Value + """"   AdminCommand.Execute   AdsRecordset.MoveNext Wend AdminConnection.Close MsgBox "The " + TableNameText.Text + " table has been " + _   "created, with rights granted to all groups"

This subprocedure begins by verifying that the table name does not include a semicolon, which could be used to convert the subsequent GRANT SQL statement into a SQL script. Since this value represents a table name, a parameterized query is not an option.

Next, this code verifies that the table does not already exist in the data dictionary. Once that is done, a new connection is created using the data dictionary administrative account. This connection is then used to call CREATE TABLE to create the table, and then to call GRANT for each group returned in the system.usergroups table.

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 information so that it could not be retrieved from the executable.

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.

The following subprocedure, associated with the Change Password button (shown in Figure 14-1), demonstrates how you can permit a user to change their password from a client application:

Dim UserName As String Dim OldPass As String Dim NewPass1 As String Dim NewPass2 As String If AdsRecordset.State = adStateOpen Then   AdsRecordset.Close End If AdsRecordset.Open "SELECT USER() FROM system.iota", _   AdsConnection, adOpenDynamic, adLockPessimistic, adCmdText UserName = AdsRecordset.Fields(0).Value AdsRecordset.Close OldPass = InputBox("Enter your current password") If OldPass = "" Then Exit Sub If Not CheckPass(UserName, OldPass) Then   MsgBox "Cannot validate your current password. " + _   "Cannot change password"   Exit Sub End If 'Get new password NewPass1 = InputBox("Enter your new password") If NewPass1 = "" Then   MsgBox "Password cannot be blank. Cannot change password"   Exit Sub End If 'Check for semicolon hack If InStr(1, NewPass1, ";", vbTextCompare) <> 0 Then   MsgBox "Password may not contain a semicolon"   Exit Sub End If NewPass2 = InputBox("Confirm your new password") If NewPass1 <> NewPass2 Then   MsgBox "Passwords did not match. Cannot change password"   Exit Sub End If 'Green light to change password AdsCommand.CommandText = _   "EXECUTE PROCEDURE sp_ModifyUserProperty('" + UserName + _   "', 'USER_PASSWORD', '" + NewPass1 + "')" AdsCommand.Execute MsgBox "Password successfully changed. " + _   "New password will be valid next time you connect"

A number of interesting tricks are used in this code. First, the user name is obtained by requesting the USER scalar function from the system.iota table. USER returns the user name on the connection through which the query is executed. Next, the user is asked for their current password, and the user name and password are used to attempt a new connection, which, if successful, confirms that the user is valid. This validation occurs in a subfunction named CheckPass. The following code is found in this function:

Private Function CheckPass(UName As String, _   Pass As String) As Boolean   Dim TempConnection As ADODB.Connection   On Error GoTo ErrorHandler   'Try to make a new connection using this password   Set TempConnection = New ADODB.Connection   TempConnection.ConnectionString = _   "Provider=Advantage OLE DB Provider;" + _   "Data Source=" + DataPath + ";user ;" + _   "password=" + Pass + ";" + _   "ServerType=ADS_LOCAL_SERVER | ADS_REMOTE_SERVER;"   TempConnection.Open   'Password must be ok. Close TempConnection   TempConnection.Close   CheckPass = True   Exit Function ErrorHandler:   CheckPass = False End Function

Finally, the user is asked for their new password twice (for confirmation). If all is well, the sp_ModifyUserProperty stored procedure is called to change the user’s password. 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 hard-coded into the connection string.