Administrative Operations with ADS and ADO.NET

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 CS_ADONET 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 method, which is associated with the Create Table and Grant Rights button (shown in Figure 15-1):

private void addTableBtn_Click(object sender,   System.EventArgs e)   {   AdsConnection adminConnection;   IDbCommand adminCommand;   AdsDataAdapter adminAdapter;   DataSet ds;   DataTable dt;   DataRow dr;   String tabName;   tabName = tableNameText.Text;   if (tabName.Equals(""))   {   MessageBox.Show(this,   "Please enter the name of the table to create");   return;   }   //Check for semicolon hack   if ((tabName.IndexOf(";") >= 0))    {   MessageBox.Show(this,   "Table name may not contain a semicolon");   return;   }   try    {   adminConnection = new AdsConnection(   "Data Source=" + DataPath + ";user ID=adssys;" +   "password=password;" +    "ServerType=ADS_LOCAL_SERVER | ADS_REMOTE_SERVER;" +   "FilterOptions=RESPECT_WHEN_COUNTING;" +   "TrimTrailingSpaces=True");   adminConnection.Open();   adminAdapter = new AdsDataAdapter("SELECT Name FROM " +   "system.tables " +   "WHERE UCase(Name) = '" + tabName.ToUpper() + "'",   adminConnection);    ds = new DataSet();    adminAdapter.Fill(ds);   dt = ds.Tables[0];   if (dt.Rows.Count != 0)    {   MessageBox.Show(this,    "This table already exists. Cannot create");   return;   }    adminCommand = new AdsCommand("CREATE TABLE [" +   tabName + "] " +    "([Full Name] CHAR(30), [Date of Birth] DATE," +   "[Credit Limit] MONEY, Active LOGICAL)", adminConnection);   adminCommand.ExecuteNonQuery();   adminAdapter = new AdsDataAdapter("SELECT Name FROM " +   "system.usergroups", adminConnection);   ds = new DataSet();    adminAdapter.Fill(ds);   dt = ds.Tables[0];   if (dt.Rows.Count == 0)    {   MessageBox.Show(this, "No groups to grant rights to");   return;   }   adminCommand = adminConnection.CreateCommand();   for (int i=0; i <= dt.Rows.Count - 1 ; i++)    {   dr = dt.Rows[i];   adminCommand.CommandText = "GRANT ALL ON " +    tabName + " TO \"" + dr[0].ToString() + "\"";   adminCommand.ExecuteNonQuery();   }   }    catch (System.Exception ex)    {   Console.WriteLine("Exception", ex);   throw(ex);   }   MessageBox.Show(this,   "The " + tabName + " table has been " +    "created, with rights granted to all groups");   return;  } 

This method 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 you would either ask for this information from the user or you would obfuscate this information so that it could not be retrieved.

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 method, associated with the Change Password button (shown in Figure 15-1), demonstrates how you can permit a user to change their password from a client application:

private void changePasswordBtn_Click(object sender,   System.EventArgs e)  {   IDataReader dataReader;   String userName;   String oldPass, newPass, confirmPass;   oldPass = oldPassText.Text;   if (oldPass.Equals(""))   {   MessageBox.Show(this,   "Please enter your current password");   return;   }   newPass = newPassText.Text;   if (newPass.Equals(""))   {   MessageBox.Show(this,   "Please enter your new password");   return;   }   confirmPass = confirmPassText.Text;   if (confirmPass.Equals(""))   {   MessageBox.Show(this,   "Please confirm your new password");   return;   }   if (! newPass.Equals(confirmPass))   {   MessageBox.Show(this,   "New passwords do not match");   return;   }   if ((newPass.IndexOf(";") >= 0))    {   MessageBox.Show(this,   "Password may not contain a semicolon");   return;   }   //Get user name   command = connection.CreateCommand();   command.CommandText = "SELECT USER() FROM system.iota";   dataReader = command.ExecuteReader();   dataReader.Read();   userName = dataReader.GetString(0);   dataReader.Close();   //Verify current password   if (! CheckPassword(userName, oldPass))    {   MessageBox.Show("Cannot validate your current password. " +   "Cannot change password");   return;   }   try    {   command.CommandText =  "EXECUTE PROCEDURE sp_ModifyUserProperty('"   + userName + "', 'USER_PASSWORD', '" + newPass + "')";   command.ExecuteNonQuery();   }    catch (Exception ex)    {   MessageBox.Show(ex.Message);   return;   }   MessageBox.Show("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 is used to attempt a new connection, which if successful confirms that the user is valid. This password validation is performed using the custom CheckPassword method. The following is the implementation of this method:

private Boolean CheckPassword(String uName, String pass)  {   //Verify the current password   AdsConnection tempConnection;   tempConnection = new AdsConnection(   "Data Source=" + DataPath + ";user ;" +   "password=" + pass + ";" +    "ServerType=ADS_LOCAL_SERVER | ADS_REMOTE_SERVER;" +    "FilterOptions=RESPECT_WHEN_COUNTING;" +   "TrimTrailingSpaces=True");   try    {   tempConnection.Open();   tempConnection.Close();   return true;   }    catch (Exception)    {   return false;   } } //CheckPassword 

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.