Overview

The application now works well enough to meet the client needs but the database administrator (DBA) and Internal Audit (IA) have some concerns that will need to be addressed:

  1. The db_datareader and db_datawriter roles are too broad. They were handy for development purposes to make sure things worked but they give user AIST3410 read and write access to everything in the database. Although the application interface mediates some of that access the user can open tables and accidentally -- or purposefully -- see and change records that they have no business visiting.
  2. The current application links directly to tables in the DB. A much better architecture would be to link to views and execute sprocs which provides the DBA better management and control.
  3. IA would like an audit trail of the user who packs an order for shipping and indicates that it is shipped.
Remove User AIST3410 from DB Roles

This doesn't necessarily need to come first but the broad permissions for AIST3410 need to be dropped. In 'real life' executing these commands would mean AIST3410 couldn't use the current application until we deployed the update; in that case more care would be taken about the timing of these changes. Apply the following SQL:

ALTER ROLE db_datawriter DROP MEMBER [vm-1\AIST3410];

ALTER ROLE db_datareader DROP MEMBER [vm-1\AIST3410];

Create Views in Northwind

A SQL view is a 'virtual table' comprised of a SELECT query. It is accessed very much like a table but views can provide better documentation for data access, hide complexity of deriving results from users, and limit which columns and rows of the base tables which can be accessed by a user. Some views are even update-able (i.e., can support INSERT, UPDATE, or DELETE operations) but our use is simplifying and limiting data accessed by applications and their users. 

Our application will use two views: one for unshipped orders named vwUnShippedOrders and another named vwUnShippedProducts for the (unshipped) products from those orders.

CREATE VIEW [dbo].[vwUnShippedOrders] AS

SELECT        OrderID, CustomerID, OrderDate, RequiredDate, ShippedDate

FROM        dbo.Orders

WHERE        (ShippedDate IS NULL)


CREATE VIEW [dbo].[vwUnShippedProducts] AS

SELECT dbo.[Order Details].Quantity, dbo.[Order Details].ProductID, dbo.Products.ProductName,

dbo.[Order Details].OrderID, dbo.Orders.ShippedDate

FROM dbo.[Order Details]

INNER JOIN dbo.Products ON dbo.[Order Details].ProductID = dbo.Products.ProductID

INNER JOIN dbo.Orders ON dbo.[Order Details].OrderID = dbo.Orders.OrderID

WHERE (dbo.Orders.ShippedDate IS NULL)

Our user(s) must be able to read (i.e., SELECT from) these views (and nothing else):

GRANT SELECT ON vwUnShippedOrders to [vm-1\AIST3410];

GRANT SELECT ON vwUnShippedProducts to [vm-1\AIST3410];

Audit Trail for Order Packer

First, add a column PackedBy to the Orders table using varchar(50) as the type (since the contents will be a user ID) and allowing nulls (for unpacked orders or existing shipped orders where we don't know the packer). Note that using table design in SSMS lets you control where in the list of columns the new column is added but using SQL in SQL Server the new column's location in the list can't be controlled and comes at the end -- but remember that one of the principles of a relation is that column order does not matter.

ALTER TABLE Orders ADD PackedBy varchar(50);

Then modify the UPDATE to Orders statement in the spShipOrder sproc to set PackedBy to the current user name (and don't forget the comma needed after the set ShippedDate clause):

update Orders

set ShippedDate = GETDATE(),

           PackedBy = SUSER_SNAME (SUSER_SID())

       where OrderID = @prmOrderID;

It's important to test changes to the spShipOrder sproc to make sure we didn't regress (i.e., break something that formerly worked) and that our new features actually work. There is a spShipOrder Test Script v2 that helps with this.

Check User Permissions

We already (should) have given our user execute permission for the spShipOrder sproc in Part II. If not use

GRANT EXECUTE ON spShipOrder to [vm-1\AIST3410];

To verify user AIST3410 no longer has any DB role memberships (like db_datawriter and db_datareader) use the following SQL

SELECT DP1.name AS DatabaseRoleName,   

   isnull (DP2.name, 'No members') AS DatabaseUserName   

 FROM sys.database_role_members AS DRM  

 RIGHT OUTER JOIN sys.database_principals AS DP1  

   ON DRM.role_principal_id = DP1.principal_id  

 LEFT OUTER JOIN sys.database_principals AS DP2  

   ON DRM.member_principal_id = DP2.principal_id  

WHERE DP1.type = 'R' 

ORDER BY DP1.name;  

and check that AIST3410 does not appear in the DatabaseUserName column in results

To verify user AIST3410 has only the execute and select permissions we want use the following SQL

SELECT

   ISNULL(OBJECT_NAME(major_id),'') [Objects], USER_NAME(grantee_principal_id) as [UserName], permission_name as [PermissionName]

FROM

    sys.database_permissions p

WHERE grantee_principal_id>0 and USER_NAME(grantee_principal_id) = 'vm-1\AIST3410'

ORDER BY

    OBJECT_NAME(major_id), USER_NAME(grantee_principal_id), permission_name

and check that vm-1\AIST3410 has only the following permissions

Update the Application

Working in your Documents folder (or wherever your development copy of NorthwindApps.accdb is), make a backup copy of the current version of the application before making changes; this simply ensures we can get back to where we were if needed.

The application will now use views from the Northwind database rather than tables and queries within the application. So delete the existing linked tables and query (for tables this only deletes the linkages to the Northwind DB, it does not delete the tables in Northwind). Highlight the tables and query, right-click, and choose Delete and click Yes on the popup dialog

Using External Data as in Part I create linked tables to the vwUnshippedOrders and vwUnshippedProducts views in your Northwind database (use your existing Northwind connection or create a new one). You will get a popup for each view asking you to Select Unique Record Identifier; this would be important if the views were update-able but for our use selecting Cancel for each view will be ok. When complete you should see the views as (linked) tables in the Access project.

 

Use Database Tools | Relationships to 'join' the two (virtual) tables by OrderID being sure to exit and save the relationships definition window.

In form design on FulFillOrder change the Record Source for the form from the former (and now deleted) qryUnshippedOrders to dbo_vwUnShippedOrders.

Change the Record Source for the subform from the former SQL statement to dbo_vwUnShippedProducts

You might double-check that the linking field between the form and subform is still OrderID

That should be all that is needed; your application is production ready (assuming what you started with was). A key application architecture principle that's been applied is moving of a lot of 'hidden' SQL out of the Access project back into the SQL Server database where it can be better managed. We have also reduced the 'attack surface' of the application when used by our client AIST3410: only the two views and one stored procedure are allowed to the user, the minimum to get the job done (a principle called 'least privilege').

Deploy and Test

Exiting Access and copying NorthwindApps.accdb to your C:\DevApps folder places the revised version in production where user AIST3410 can run it. You should probably test that you can run the deployed application by shipping an order or two. Then from a command or PowerShell window use

runas /user:aist3410 "C:\Program Files\Microsoft Office\Office16\MSACCESS.EXE C:\DevApps\tschultz\NorthWindApps.accdb"

to run this as AIST3410 (all on one line but replacing tschultz with your login ID and recall that AIST3410 is the password also) and ship an order or two. It's kind of a thrill (to me, anyway, but I am weird I guess) to then check the Orders table in Northwind and see that the PackedBy field is tracking the user who indicated the order was shipped. Using

select OrderID, ShippedDate, PackedBy from Orders where ShippedDate > DATEADD(hour, -1, getdate())

will show any orders shipped within the last hour