|
|
Roles are an easy way to manage permissions for logins and users. You can programmatically create custom roles and assign permissions to your custom roles. Then logins and users can inherit permissions from your custom roles just as they do when you add logins to fixed server roles and users to fixed database roles. This section presents three samples to illustrate techniques for managing role membership and permissions.
Two system stored procedures remind you about the different fixed server roles and their capabilities. The sp_helpsrvrole system stored procedure returns a list of the fixed server roles along with a brief description of each role. The sp_srvrolepermission stored procedure can return the server permissions associated with all the fixed server roles or with a specific one. You can run either stored procedure from the Create Text Stored Procedure template in an Access project or from a VBA procedure. It may be necessary to save and then open the procedures that you create before you can view the results sets generated by sp_helpsrvrole and sp_srvrolepermission. When you use a VBA procedure instead of the Create Text Stored Procedure template, you can run these two stored procedures from the Command object and then pass the result set to a Recordset object for display in the Immediate window or elsewhere in an Access application.
The following sample demonstrates the syntax for running both the sp_helpsrvrole and sp_srvrolepermission system stored procedures in a single VBA procedure. The output from this procedure is a convenient, top-level, detailed report documenting the functionality provided by each fixed server role. The procedure instantiates a command and then uses it to run a T-SQL statement that invokes the sp_helpsrvrole system stored procedure. After transferring the result to a recordset, the procedure loops through the recordset's rows to enumerate the fixed server names and descriptions. Next, the VBA procedure reuses the command and recordset to enumerate detailed permissions for the fixed server roles.
Sub SummarizeFixedServerRoles() Dim cmd1 As ADODB.Command Dim rst1 As ADODB.Recordset 'Instantiate a Command object and let it use 'the connection for the current project Set cmd1 = New ADODB.Command cmd1.ActiveConnection = CurrentProject.Connection 'Summarize fixed server roles cmd1.CommandText = "Exec sp_helpsrvrole" Set rst1 = New ADODB.Recordset Set rst1 = cmd1.Execute Debug.Print "Print Fixed Server Roles and Descriptions" Do Until rst1.EOF Debug.Print rst1(0), rst1(1) rst1.MoveNext Loop Debug.Print String(2, vbCr) 'Provide detailed permissions cmd1.CommandText = "EXEC sp_srvrolepermission" Set rst1 = cmd1.Execute Debug.Print "Print Fixed Server Roles and Permissions" Do Until rst1.EOF Debug.Print rst1(0), rst1(1) rst1.MoveNext Loop 'Clean up objects rst1.Close Set rst1 = Nothing Set cmd1 = Nothing End Sub
The next sample shows application of the same basic logic for documenting the fixed database roles. In this case, the sp_helpdbfixedrole system stored procedure enumerates the individual fixed database roles along with brief descriptions. The sp_dbfixedrolepermission system stored procedure lists the individual permission descriptions for each fixed database role. Because the names for the fixed database roles vary more in length than they do for fixed server roles, the code sample pads the end of the names with spaces so that the second column of descriptions aligns evenly across all fixed database role names.
Sub SummarizeFixedDBRoles() Dim cmd1 As ADODB.Command Dim rst1 As ADODB.Recordset 'Instantiate a Command object and let it use 'the connection for the current project Set cmd1 = New ADODB.Command cmd1.ActiveConnection = CurrentProject.Connection 'Summarize fixed database roles cmd1.CommandText = "Exec sp_helpdbfixedrole" Set rst1 = New ADODB.Recordset Set rst1 = cmd1.Execute Debug.Print "Print Fixed Database Roles and Descriptions" Do Until rst1.EOF Debug.Print rst1(0) & _ String(18 - Len(rst1(0)), " ") & rst1(1) rst1.MoveNext Loop Debug.Print String(2, vbCr) 'Provide detailed permissions cmd1.CommandText = "EXEC sp_dbfixedrolepermission" Set rst1 = cmd1.Execute Debug.Print "Print Fixed Database Roles and Permissions" Do Until rst1.EOF Debug.Print rst1(0) & _ String(18 - Len(rst1(0)), " ") & rst1(1) rst1.MoveNext Loop End Sub
Instead of sending a user to the DBA to get permission, you can dynamically add permissions to a user account when your application discovers that the account has insufficient permissions to perform some task. Of course, you need a login with appropriate fixed server role status to process the permissions you wish to assign. The next sample procedure, DynamicallyAddUpdatePermission, offers an approach to handling the dynamic assignment of a permission.
This sample starts by setting up the login and user accounts for lgnName and usrName. These accounts are endowed with SELECT permission for the tables in the SecurityDemo1 database. To this point, this code sample is similar to the TestSelectPermissionLogin procedure from the "Testing User Accounts" section in this chapter. The two procedures diverge after the invocation of the Open method for the recordset. This sample's code assigns a new value to the recordset value with the statement rst1(1) = "foo". At this point in the procedure, the change is still local. However, the rst1.Update statement alerts the server to the client application's desire to modify a value on the server.
The attempt to invoke the Update method generates a run-time error. This run-time error occurs because the usrCanSelectOnly user belongs to just one fixed database role, db_datareader. This role conveys SELECT permission for any table or view in a database, but the role does not authorize its members to update any record source. On the other hand, the db_datawriter role does grant the ability to modify any updatable record source tied to a SQL Server database. The remainder of the procedure lets the user know about the problem and provides the user with a way to dynamically add the usrCanSelectOnly user to the db_datawriter role.
This approach to dynamically adding a User object to a new database role starts just before the line of code that triggers the run-time error rst1.Update. Notice that the preceding line opens an inline error trap with an On Error Resume Next statement. The line after the attempt to update the record source is merely an If…Then…Else…End If statement. This statement permits the execution of the code within the block when the Number property value for the Err object matches the value of a run-time error resulting from an attempt to update a value without UPDATE permission. The code within the Then clause of the If…Then…Else…End If statement first presents an error message briefly explaining the problem in a message box. Then the code uses an InputBox function to ask for the secret word that will upgrade the user account to include the UPDATE permission. Users responding with the secret word update have the usrCanSelectOnly User object upgraded to include membership in the db_datawriter role. After this upgrade occurs, the procedure executes the Update method a second time just before printing a message about the success of the update. If a user does not reply to the prompt from the InputBox function with the correct secret word, the procedure tells him to get help from the DBA.
Sub DynamicallyAddUpdatePermission()
Dim cnn1 As ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim str1 As String
Dim srv1 As SQLDMO.SQLServer
Dim srvName As String
Dim dbsName As String
Dim lgnName As String
Dim usrName As String
Dim dbrName As String
Dim str2 As String
'Create lgnName on srvName and usrName in dbsName
'with dbrName database role
srvName = "(local)"
dbsName = "SecurityDemo1"
lgnName = "lgnCanSelectOnly"
usrName = "usrCanSelectOnly"
dbrName = "db_datareader"
AddLoginAndUserToDBRole srvName, dbsName, _
lgnName, usrName, dbrName
'Connect using lgnName login to dbsName database with
'password equal to password
Set cnn1 = New ADODB.Connection
cnn1.Open "Provider=sqloledb;Data Source=" & srvName & ";" & _
"Initial Catalog=" & dbsName & "; " & _
"User Id=" & lgnName & ";" & _
"Password=password;"
'Attempt to update a table with a user account that has
'only SELECT permission
Set rst1 = New ADODB.Recordset
rst1.Open "SELECT * FROM Pic_Addresses", cnn1, _
adOpenKeyset, adLockOptimistic, adCmdText
str1 = rst1(1)
Debug.Print rst1(1)
rst1(1) = "foo"
'Setup to process error from attempt to update a table
'with a user account that has only SELECT permission
On Error Resume Next
rst1.Update
If Err.Number = -2147217911 Then
MsgBox "No UPDATE permission for Pic_Addresses " & _
"table.", vbCritical, _
"Programming Microsoft Access Version 2003"
str2 = InputBox("Do you know the secret word for UPDATE " & _
"permission?", _
"Programming Microsoft Access 2003", _
"I don't know.")
If str2 = "update" Then
'If user knows secret word, add permission to user account
'immediately and perform update
srvName = "(local)"
Set srv1 = New SQLDMO.SQLServer
srv1.LoginSecure = True
srv1.Connect srvName
srv1.Databases("SecurityDemo1"). _
DatabaseRoles("db_datawriter").AddMember _
"usrCanSelectOnly"
srv1.DisConnect
Set srv1 = Nothing
rst1.Update
str2 = "Update from " & """" & str1 & """" & " to " & _
"""" & rst1(1) & """" & " succeeded."
MsgBox str2, vbInformation, _
"Programming Microsoft Access 2003"
Else
'If user does not know the secret word, refer to DBA
str2 = "Wrong secret word; see your DBA for " & _
"UPDATE permission."
MsgBox str2, vbCritical, _
"Programming Microsoft Access 2003"
End If
End If
End Sub
The db_datareader and db_datawriter roles, along with the other fixed database roles, apply permissions indiscriminately to all database objects. However, it's common for security needs to selectively apply to database objects. For example, a typical requirement is for users to be able to select from all tables—except those containing salaries and other sensitive information. To accommodate this need, you must create user-defined roles with custom permissions for individual database objects.
The code in the next sample demonstrates how to set up a user-defined role and then add a user to it. This may or may not be sufficient for your security requirements. This is because user accounts can belong to multiple database roles. Therefore, if a user cannot accomplish a task with the permissions for one database role membership, that person might be able to succeed with membership in another database role. The syntax for creating a user-defined database role and assigning a new user will be the focus as I walk you through the sample code. You also will learn how to drop a user from a database role. The sample concludes by conditionally executing one of two SELECT query statements for a recordset. When the user takes the path associated with a value of True for the UseWrongSelect compiler constant, the program generates a run-time error but the other path leads to a normal exit. This sample demonstrates the behavior of user accounts based on roles and adds to the spectacle value of the outcome by not building an error trap for taking the wrong path.
The procedure starts by pointing a SQLServer object at the local server. Then the code instantiates a DatabaseRole object, gives it a name, and assigns the object to the DatabaseRoles collection for the database at which the dbsName string points. The new role's name is SelectEmployeesNotPics. After adding the DatabaseRole object, the procedure invokes the Grant method for the Employees table. With this method, the procedure adds a SELECT permission for the table to the SelectEmployeesNotPics database role. The sample references this role with dbr1. Next, the sample adds the usrCanSelectOnly user, represented by usrName, to the SelectEmployeesNotPics user-defined role. Notice that the dbr1 role has no permission for the Pic_Addresses table.
Now the procedure is ready to start testing the security of the database. First, it opens a connection to the SecurityDemo1 database for the usrCanSelectOnly user. Then it uses the Connection object to open a recordset based on a SELECT query for the Pic_Addresses table. Although the SelectEmployeesNotPics database role grants SELECT permission for just the Employees table, the recordset for the Pic_Addresses table still opens. This is because the usrCanSelectOnly user belongs to the db_datareader role, which grants SELECT permission for all tables and views.
After successfully executing the SELECT query, the sample performs one more database definition. The next statement drops usrCanSelectOnly from the db_datareader role. This completes the steps necessary to restrict usrCanSelectOnly SELECT privileges to the Employees table. The final statement in the sample is a #If…Then…#Else…#End If statement that runs one of two recordset Open method statements based on the value of the UseWrongSelect compiler constant. When the constant's value is False, as in the next sample, the procedure ends normally.
When you change the constant's value to True, the procedure can fail in a couple of ways that do not illustrate the impact of the compiler constant setting. For this reason, run the DropSelectEmployeesNotPics procedure after each execution of the following procedure. The DropSelectEmployeesNotPics procedure—the last sample procedure in the chapter—removes the SelectEmployeesNotPics role and restores the membership of the usrCanSelectOnly user in the db_datareader role. With these two fixes, the following procedure is ready to run again and show the effects of a changed compiler constant setting. When you run the procedure with a compiler constant of True, the procedure fails in the Then clause of the #If…Then…#Else…#End If statement because the code tries to run a SELECT query against the Pic_Addresses table.
'Run after DynamicallyAddUpdatePermission procedure so that 'usrCanSelectOnly has db_datareader and db_datawriter 'role memberships Sub CreateDBRoleAndGrantSelectPermission() Dim srvName As String Dim dbsName As String Dim lgnName As String Dim usrName As String Dim srv1 As SQLDMO.SQLServer Dim dbr1 As SQLDMO.DatabaseRole #Const UseWrongSelect = False 'Assign selected string names srvName = "(local)" dbsName = "SecurityDemo1" lgnName = "lgnCanSelectOnly" usrName = "usrCanSelectOnly" 'Connect to server Set srv1 = New SQLDMO.SQLServer srv1.LoginSecure = True srv1.Connect srvName 'Instantiate, name, and add new custom database role Set dbr1 = New SQLDMO.DatabaseRole dbr1.Name = "SelectEmployeesNotPics" srv1.Databases(dbsName).DatabaseRoles.Add dbr1 'Grant SELECT permission to the Employees table but not 'the Pic_Addresses table to dbr1 role, and add user 'account with usrName to dbr1 database role srv1.Databases(dbsName).Tables("Employees"). _ Grant SQLDMOPriv_Select, dbr1.Name srv1.Database