لطفا منتظر باشید ...
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.
