Programming Roles and Permissions

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.

T-SQL Help for Roles

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.

 SubSummarizeFixedServerRoles()  Dimcmd1AsADODB.Command Dimrst1AsADODB.Recordset     'InstantiateaCommandobjectandletituse 'theconnectionforthecurrentproject Setcmd1=NewADODB.Command cmd1.ActiveConnection=CurrentProject.Connection     'Summarizefixedserverroles cmd1.CommandText= "Execsp_helpsrvrole" Setrst1=NewADODB.Recordset Setrst1=cmd1.Execute Debug.Print "PrintFixedServerRolesandDescriptions" DoUntilrst1.EOF Debug.Printrst1(0),rst1(1) rst1.MoveNext Loop Debug.PrintString(2,vbCr)     'Providedetailedpermissions cmd1.CommandText= "EXECsp_srvrolepermission" Setrst1=cmd1.Execute Debug.Print "PrintFixedServerRolesandPermissions" DoUntilrst1.EOF Debug.Printrst1(0),rst1(1) rst1.MoveNext Loop     'Cleanupobjects rst1.Close Setrst1=Nothing Setcmd1=Nothing     EndSub 

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.

 SubSummarizeFixedDBRoles()  Dimcmd1AsADODB.Command Dimrst1AsADODB.Recordset     'InstantiateaCommandobjectandletituse 'theconnectionforthecurrentproject Setcmd1=NewADODB.Command cmd1.ActiveConnection=CurrentProject.Connection     'Summarizefixeddatabaseroles cmd1.CommandText= "Execsp_helpdbfixedrole" Setrst1=NewADODB.Recordset Setrst1=cmd1.Execute Debug.Print "PrintFixedDatabaseRolesandDescriptions" DoUntilrst1.EOF Debug.Printrst1(0)&_ String(18-Len(rst1(0)), " ")&rst1(1) rst1.MoveNext Loop Debug.PrintString(2,vbCr)     'Providedetailedpermissions cmd1.CommandText= "EXECsp_dbfixedrolepermission" Setrst1=cmd1.Execute Debug.Print "PrintFixedDatabaseRolesandPermissions" DoUntilrst1.EOF Debug.Printrst1(0)&_ String(18-Len(rst1(0)), " ")&rst1(1) rst1.MoveNext Loop     EndSub 

Dynamically Adding Permissions to a User Account

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.

 SubDynamicallyAddUpdatePermission() Dimcnn1AsADODB.Connection Dimrst1AsADODB.Recordset Dimstr1AsString Dimsrv1AsSQLDMO.SQLServer DimsrvNameAsString DimdbsNameAsString DimlgnNameAsString DimusrNameAsString DimdbrNameAsString Dimstr2AsString     'CreatelgnNameonsrvNameandusrNameindbsName 'withdbrNamedatabaserole srvName= "(local)" dbsName= "SecurityDemo1" lgnName= "lgnCanSelectOnly" usrName= "usrCanSelectOnly" dbrName= "db_datareader" AddLoginAndUserToDBRolesrvName,dbsName,_ lgnName,usrName,dbrName     'ConnectusinglgnNamelogintodbsNamedatabasewith 'passwordequaltopassword Setcnn1=NewADODB.Connection cnn1.Open "Provider=sqloledb;DataSource=" &srvName& ";" &_  "InitialCatalog=" &dbsName& "; " &_  "UserId=" &lgnName& ";" &_  "Password=password;"     'Attempttoupdateatablewithauseraccountthathas 'onlySELECTpermission Setrst1=NewADODB.Recordset rst1.Open "SELECT*FROMPic_Addresses",cnn1,_ adOpenKeyset,adLockOptimistic,adCmdText str1=rst1(1) Debug.Printrst1(1) rst1(1)= "foo"     'Setuptoprocesserrorfromattempttoupdateatable 'withauseraccountthathasonlySELECTpermission OnErrorResumeNext rst1.Update IfErr.Number=-2147217911Then MsgBox "NoUPDATEpermissionforPic_Addresses " &_  "table.",vbCritical,_  "ProgrammingMicrosoftAccessVersion2003" str2=InputBox("DoyouknowthesecretwordforUPDATE " &_  "permission?",_  "ProgrammingMicrosoftAccess2003",_  "Idon'tknow.") Ifstr2= "update" Then 'Ifuserknowssecretword,addpermissiontouseraccount 'immediatelyandperformupdate srvName= "(local)" Setsrv1=NewSQLDMO.SQLServer srv1.LoginSecure=True srv1.ConnectsrvName srv1.Databases("SecurityDemo1")._ DatabaseRoles("db_datawriter").AddMember_  "usrCanSelectOnly" srv1.DisConnect Setsrv1=Nothing rst1.Update str2= "Updatefrom " & """" &str1& """" & " to " &_  """" &rst1(1)& """" & " succeeded." MsgBoxstr2,vbInformation,_  "ProgrammingMicrosoftAccess2003" Else 'Ifuserdoesnotknowthesecretword,refertoDBA str2= "Wrongsecretword;seeyour DBA for " &_  "UPDATE permission." MsgBoxstr2,vbCritical,_  "ProgrammingMicrosoftAccess2003" EndIf EndIf     EndSub 

Selectively Applying Object Permissions

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.

 'RunafterDynamicallyAddUpdatePermissionproceduresothat 'usrCanSelectOnlyhasdb_datareaderanddb_datawriter 'rolememberships SubCreateDBRoleAndGrantSelectPermission()  DimsrvNameAsString DimdbsNameAsString DimlgnNameAsString DimusrNameAsString Dimsrv1AsSQLDMO.SQLServer Dimdbr1AsSQLDMO.DatabaseRole #ConstUseWrongSelect=False     'Assignselectedstringnames srvName= "(local)" dbsName= "SecurityDemo1" lgnName= "lgnCanSelectOnly" usrName= "usrCanSelectOnly"     'Connecttoserver Setsrv1=NewSQLDMO.SQLServer srv1.LoginSecure=True srv1.ConnectsrvName     'Instantiate,name,andaddnewcustomdatabaserole Setdbr1=NewSQLDMO.DatabaseRole dbr1.Name= "SelectEmployeesNotPics" srv1.Databases(dbsName).DatabaseRoles.Adddbr1     'GrantSELECTpermissiontotheEmployeestablebutnot 'thePic_Addressestabletodbr1role,andadduser 'accountwithusrNametodbr1databaserole srv1.Databases(dbsName).Tables("Employees")._ GrantSQLDMOPriv_Select,dbr1.Name srv1.Database