Setting and Verifying Permissions to the Stored Procedures
Once the stored procedures have been created, we need to grant the appropriate permissions to them (see Listing 6). With the HomeLending database we will be operating on the assumption that the Sensitive_high database role will have access to the information that will be inserted and updated to the Borrower_Identification table.
The ability to search the data that is in the Borrower_Identification table will be granted to the Sensitive_high and Sensitive_medium database roles, due to our use of the Identification_Value_HT column that contains a hash value of the last four digits of our original plain text.
Now that our stored procedures have been created we will want to verify that the permissions are effective, using of EXECUTEASUSER to impersonate a member of the various database roles. The use of REVERT terminates the impersonation and returns us to our original user account.
Listing 7 executes the Search_Borrower_Identification
stored procedure with the plain text value of "0143", which is the last
four digits of a known Social Security Number, being passed as its
argument.
The result of this verification will reflect that rows were returned for the queries for the Sensitive_high and Sensitive_medium members; but since permissions did not exist for the Sensitive_low members the actual rows will not be returned. Instead the following will appear:
To view the actual rows returned, execute each batch in this script individually.
