Administrative Operations with ADS and Java

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 other 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 AdsJava 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 actionPerformed event of the Create Table and Grant Rights button (shown in Figure 13-1):

void createTableBtn_actionPerformed(ActionEvent e) {   boolean found = false;   Connection adminconn;   Statement adminstmt;   Statement grantstmt;   ResultSet rs;   String tn = tableNameText.getText();   //Check for semicolon hack   if (! (tn.indexOf(";") == -1)) {   JOptionPane.showMessageDialog(this,   "Table name may not contain a semicolon");   return;   }   if (tableNameText.getText().equals("")) {   JOptionPane.showMessageDialog(this,   "Please enter a table name");   return;   }   try {   adminconn = DriverManager.getConnection(   "jdbc:extendedsystems:advantage://server:6262/share"+   "/program files/extended systems/advantage/adsbook/" +   "demodictionary.add;user=adssys;password=password");   adminstmt = adminconn.createStatement();   rs =   adminstmt.executeQuery("SELECT NAME FROM system.tables");   String tabName;   if (! isRSEmpty(rs)) {   rs.first();   do {   tabName = rs.getString("Name");   if (tabName.equalsIgnoreCase(tn)) {   found = true;   break;   }   } while (rs.next());   }   if (found) {   JOptionPane.showMessageDialog(this,   "Table already exists. Cannot create");   return;   }   adminstmt.executeUpdate("CREATE TABLE " + tn +   "([Full Name] CHAR(30)," +   "[Date of Birth] DATE," +   "[Credit Limit] MONEY, " +   "Active LOGICAL)");   rs = adminstmt.executeQuery("SELECT * FROM system.usergroups");   if (isRSEmpty(rs)) {   JOptionPane.showMessageDialog(this,   "No groups to grant rights to");   return;   }   grantstmt = adminconn.createStatement();   rs.first();   do {   grantstmt.executeUpdate("GRANT ALL ON [" + tn + "]" +   " TO [" +  rs.getString("Name") + "]");   } while (rs.next());   JOptionPane.showMessageDialog(this, "The " + tn + " table " +   "has been created, with rights granted to all groups");   } catch (Exception e1) {   System.out.println(e1.getMessage());   } }

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

void changePasswordBtn_actionPerformed(ActionEvent e) {   String userName;   String oldPass;   String newPass1;   String newPass2;   try {   ResultSet rs = stmt.executeQuery("SELECT USER() as Name " +   "FROM system.iota");   rs.first();   userName = rs.getString("Name");   oldPass = JOptionPane.showInputDialog(this,   "Enter your current password");   if (oldPass.equals("")) {   return;   }   try {   Connection tempcon =   DriverManager.getConnection(   "jdbc:extendedsystems:advantage://server:6262/share" +   "/program files/extended systems/advantage/adsbook/" +   "demodictionary.add;user=" + userName +   ";password=" + oldPass);   }   catch (Exception e1) {   JOptionPane.showMessageDialog(this,   "Invalid password. Cannot change password");   return;   }   //Check for semicolon hack   newPass1 = JOptionPane.showInputDialog(this,   "Enter your new password");   if (! (newPass1.indexOf(";") == -1)) {   JOptionPane.showMessageDialog(this,   "Password may not contain a semicolon");   return;   }   newPass2 = JOptionPane.showInputDialog(this,   "Confirm your new password");   if (!newPass1.equals(newPass2)) {   JOptionPane.showMessageDialog(this,   "Passwords did not match. Cannot change password");   return;   }   stmt.executeUpdate("EXECUTE PROCEDURE sp_ModifyUserProperty('" +   userName + "', 'USER_PASSWORD', '" + newPass1 + "')");   JOptionPane.showMessageDialog(this,   "Password successfully changed. " +   "New password will be valid next time you connect");   } catch (Exception e1) {   System.out.println(e1.getMessage());   } }

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.

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. 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 hard-coded into the connection string.