Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources]

Implementing Security with SQL

Access 2000 introduced the capability to implement security using SQL. This was accomplished by adding ANSI SQL-92 extensions to Jet 4.0. This section shows you how you can perform common security tasks using SQL.

Maintaining Users with SQL

Just as you can maintain users with ADOX code, you can also maintain them using SQL. The CREATE USER, ALTER USER, ADD USER TO, DROP USER FROM, and DROP USER statements will create users, add users to groups, remove users from groups, and remove users, respectively. These statements are all covered in the following sections.

Using SQL to Add a User

The CREATE USER statement is used to add a user to a workgroup. Listing 28.30 illustrates the process.

Listing 28.30 Using SQL to Create a User

Private Sub cmdAddUser_Click() Dim cnn As ADODB.Connection Set cnn = CurrentProject.Connection cnn.Execute "CREATE USER " & Me.txtUser Set cnn = Nothing End Sub

Notice that the Execute method of the Connection object is used to execute the CREATE USER statement. The code creates a user with the name designated in the txtUser text box. Two optional parameters are available with the CREATE USER statement: the password and the Personal ID (PID). Listing 28.31 illustrates the use of these optional parameters.

Listing 28.31 Using SQL's CREATE USER Statement

Private Sub cmdAddUser_Click() Dim cnn As ADODB.Connection Set cnn = CurrentProject.Connection cnn.Execute "CREATE USER ALEXIS GREATKID ABCDE" Set cnn = Nothing End Sub

The example creates a user named ALEXIS with a password of GREATKID and a PID of ABCDE.

Using SQL to Add a User to a Group

The ADD USER TO statement adds a user to a group. Listing 28.32 provides an example.

Listing 28.32 Using SQL to Assign a User to a Group

Dim cnn As ADODB.Connection Set cnn = CurrentProject.Connection cnn.Execute "ADD USER " & Me.txtUser & _ " TO " & Me.txtGroup Set cnn = Nothing End Sub

The ADD USER TO statement adds the user specified in the txtUser text box to the group designated in the txtGroup text box. Once again, the Execute method of the Connection object is used to execute the SQL statement.

Using SQL to Remove a User from a Group

The DROP USER FROM statement is used to remove a user from a group. Listing 28.33 provides an example.

Listing 28.33 Using SQL to Remove a User

Private Sub cmdRemoveFromGroup_Click() Dim cnn As ADODB.Connection Set cnn = CurrentProject.Connection cnn.Execute "DROP USER " & Me.txtUser & _ " FROM " & Me.txtGroup Set cnn = Nothing End Sub

The DROP USER FROM statement is provided with the name of the user you want to drop and the group he is to be dropped from. The statement is executed using the Execute method of the Connection object.

Using SQL to Remove a User

The DROP USER statement is used to remove a user from the workgroup. An example is provided in Listing 28.34.

Listing 28.34 Using SQL to Remove a User

Private Sub cmdRemoveUser_Click() Dim cnn As ADODB.Connection Set cnn = CurrentProject.Connection cnn.Execute "DROP USER " & Me.txtUser Set cnn = Nothing End Sub

Notice that the DROP USER statement with the keyword FROM removes a user from a group. Without the keyword FROM, the user is removed from the workgroup.

Using SQL to Change a Password

The ALTER USER statement is used to modify a password. The process is shown in Listing 28.35.

Listing 28.35 Using SQL to Modify a Password

Private Sub cmdChangePassword_Click() Dim cnn As ADODB.Connection Set cnn = CurrentProject.Connection cnn.Execute "ALTER USER " & Me.txtUser & _ " PASSWORD " & _ IIf(IsNull(Me.txtOldPassword), "''", _ Me.txtOldPassword) & " " & _ Me.txtNewPassword Set cnn = Nothing End Sub

Notice that the ALTER USER statement requires both the old password and the new password. If the old password is blank, you must pass a zero-length string as the parameter value for the old password.

Maintaining Groups with SQL

Just as you can maintain users with SQL code, you can also maintain groups. The CREATE GROUP and DROP GROUP statements are used to add and remove groups, respectively.

Using SQL to Add a Group

The CREATE GROUP statement is used to create a group. The statement receives an optional PID, a unique identifier for the group. Listing 28.36 provides an example.

Listing 28.36 Using SQL to Add a Group

Private Sub cmdAddGroup_Click() Dim cnn As ADODB.Connection Set cnn = CurrentProject.Connection cnn.Execute "CREATE GROUP " & Me.txtGroup & _ " " & Me.txtPID Set cnn = Nothing End Sub

Using SQL to Remove a Group

The DROP GROUP statement is used to remove a group. An example appears in Listing 28.37.

Listing 28.37 Using SQL to Remove a Group

Private Sub cmdRemoveGroup_Click() Dim cnn As ADODB.Connection Set cnn = CurrentProject.Connection cnn.Execute "DROP GROUP " & Me.txtGroup Set cnn = Nothing End Sub

Using SQL to Assign and Remove Permissions

The SQL GRANT and REVOKE statements are used to assign and remove permissions, respectively. Listing 28.38 provides an example of granting rights to an object.

Listing 28.38 Using SQL to Grant Rights to an Object

Private Sub cmdAssignPermissions_Click() Dim cnn As ADODB.Connection Set cnn = CurrentProject.Connection cnn.Execute "GRANT SELECT ON TABLE tblClients TO " & _ Me.txtGroup Set cnn = Nothing End Sub

The code in Listing 28.38 grants select rights to the tblClients table for the group designated in the txtGroup text box. Listing 28.39 shows how a REVOKE statement is used to remove user or group rights from an object.

Listing 28.39 Using SQL to Revoke Rights from an Object

Private Sub cmdRemovePermissions_Click() Dim cnn As ADODB.Connection Set cnn = CurrentProject.Connection cnn.Execute "REVOKE SELECT ON TABLE tblClients FROM " & _ Me.txtGroup Set cnn = Nothing End Sub

Listing 28.39 removes select rights for the tblClients table from the group designated in the txtGroup text box.