Auditing in MVC EF Project

Recently I worked on a requirement to audit the web application for CRUD operations. Yes, all of CRUD that means read operation too. I had a MVC web application which used Entity Framework DB first option and was using Azure AD Authentication. The complete set of requirements (mentioned below) was bit different than normal auditing.

  1. Log one row per table change instead of per property (column) change.
  2. Log for read operation too
  3. Generate a checksum and log it to along with the audit trail
  4. Apart from that, have to log table name, logged in user name, timestamp and other generic stuff

We decided to just store the new (updated) values in the audit trail instead of both old and new ones. If needed, we can get history of that object from audit table so not storing old values was not going to be an issue.

To achieve this, I decided to overwrite the SaveChanges method of DbContext. Entity Framework DB first creates partial classes for everything including the DbContext. I just used partial classes and overrode the SaveChanges method. Before jumping to the code, below is the approach I followed for all of the above points, respectively:

  1. I decided to create JSON of the updated row by using JSON.NET library. I could have used XML too but the issue with XML is, it fails on out of the box generated DB classes due to circular references and use of ICollection and virtual properties. To do this with XML, I would have to create POCO objects, which was just increasing the development time and effort. Hence I decided to use JSON.NET to create a valid json of updated row and store it in varchar(max) field of audit table in SQL. You can go ahead with XML too
  2. By overriding SaveChanges I can audit for create, update and delete (CUD) operation but not for read. Therefore, to simplify the process I just manually added audit rows for every call to functions where I fetch any table’s details. It was straightforward and easy but there was a catch. As I was adding audit row while reading an object, SaveChanges was called and hence for every read operation, there was an entry in audit table which denotes that an entry is added in the audit table. Basically the audit table was getting audited which I did not want. Hence, I have to exclude the “Audit” table manually from code
  3. This was easy to implement. I just combined the complete row using StringBuilder and generated checksum using MD5. You can use any other algorithm too. Besides that I converted the generated checksum to hex to store it in DB, you can use Base64 too
  4. I was using three tier architecture in my project with web layer, service layer and one data layer. Other things are straightforward but for user id I did not want to store complete email address for every row. That’s why, what I did was, I entered logged in user’s email id in database once someone logs in to the application for the first time and at the same time stored that table’s primary key as custom claim. I then read this claim value and use it for audit purpose
  5. Last thing, as I need to audit create operations too and their id is not generated until the call to SaveChanges, I have to collect create entities in different collection and audit them after an initial call to SaveChanges 

So below is the code to achieve all this:

 

Advertisements

Standalone SharePoint Add-In

This post is about developing and deploying an application which works both as a provider hosted add-in as well as a standalone application depending on the URL from which it is launched. So, if a user logins to O365 and click on the app, it will open a provider hosted add-in and follow O365 authentication and if I launch the app directly using URL, then it will ask me to login using ASP.Net credentials and will follow ASP.Net Identity Framework.

It took me some time to decide on the approach as normal ASP.Net application uses AuthorizeAttribute while SharePoint uses ActionFilterAttribute and TokenHelper.cs class which comes by default while creating provider hosted add-in. This post assumes that the reader is aware about:

  • Provider Hosted Add-ins
  • ASP.Net Web Application projects using Identity Framework
  • Basics of MVC

Below are the steps I followed to achieve this.

  1. Create a provider hosted add-in using Visual Studio. You will end up with the below project structure:

    ProviderHostedAdd-In

  2. Install relevant nuget packages. Some of these will install other packages on which they are dependent. I installed the following:
    1. Microsoft ASP.NET Identity Core
    2. Microsoft ASP.NET Identity Owin
    3. Microsoft ASP.NET Identity EntityFramework
    4. Microsoft.Owin.Host.SystemWeb

      Nugetpackages

  3. Add relevant files in the project. When you create an ASP.Net MVC application, there are few files which gets added by default. Following is the list of such files which I added in the project:
    1. App_Data/IdentityConfig.cs
    2. App_Data/Startup.cs
    3. Controllers/AccountController
    4. Controllers/ManageController
    5. Models/AccountViewModel.cs
    6. Models/IdentityModel.cs
    7. Models/ManageViewModels.cs
    8. Views/Account -> complete folder
    9. Views/Manage -> complere folder
    10. Views/Shared/_LoginPartial.cshtml

      P.S. While copying all of these files; make sure the namespace of classes are correct

      NewClasses

  4. Create the tables to store user credentials in your database. You can find the script here or download from internet. Run this script in your relevant database and add connection string in web.config.
    <connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=AuthenticationProviderHosted;Integrated Security=True" providerName="System.Data.SqlClient" />
    </connectionStrings>
    

    Also add this connection string name in IdentityModel.cs file.

  5. The final step is to add a common filter attribute which will work with both Sharepoint authentication and asp.net authentication. For this I created a new class called CommonContextFilterAttribute which inherits from AuthorizeAttribute. The code is simple here, if request has host URL present then authenticate using SharePoint else move to asp.net.

    FilterAttribute

  6. In the HomeController.cs file change [SharePointContextFilter] to [CommonContextFilter]. Also, modify the Index action to handle the SP User class. I just added a host URL filter again to check if it is SP user or ASP.Net User. And you are done!

Try running the app as provider hosted add-in, just click on “Start” at the top and you will get the below screen

ProviderAddInRun

And if you run as standalone app then you will get below screen:

Stanalone

You can register a new user here and login to get the desired screen:

Stanalone-LoggedIn

Notice the user name here i.e. garima@outlook.com. I used this login to register a user and then logged in to the site.

Similarly, you can modify the code to integrate login through Facebook, Twitter or any other website using OWIN.

You can download the complete source code from here.

Note: To debug the add-in as a standalone application, please make the following changes:

  • Set the web project as start-up project
  • In the “Properties” of web project -> “Web” tab -> select “Current Page” as “Start Action”.
  • Revert these changes to debug as SP add-in.
  • You can deploy this project as a normal provider hosted add-in and it should work both ways. I tested by deploying the web project in IIS. Skipping the steps here as it is straightforward.

Hope this helps. Happy coding!

O365 OneNote API in Web Application

Microsoft recently announced OneNote API for O365 notebooks. I decided to give it a try but most of the examples/documentation I found was for native client applications which is obviously not working for web apps. After spending a lot of time on it and getting help from Microsoft OneNote API team, I was able to resolve it. Below are the steps you need to follow to access your notebooks using OneNote API for O365. Please note that the below code is for single tenant app and OneNote API are in preview.

  1. Register your application in Azure AD: We need to register the app in Azure AD as well as give OneNote API proper permissions. The steps are as follows:
    • Go to Azure AD related to your O365 account

      Azure AD
    • Navigate to Active Directory and click on Applications tab. Add a new application and select “Add an application my organization is developing”NewApplication
    • Give proper name to the app and select Web Application/OR Web API optionWebAppORWebAPI
    • Provide a Sign-On URL and a unique App ID URI.
      • Sign-On URL: https://localhost:44327/
      • APP ID URI: https://<tenantname>.sharepoint.com/SingleTenantOneNoteAPIDemo, replace <tenantname> with the name of your Office365 tenant.
    • Now the new application is ready. Go to “Configure” tab and at the bottom you will get the option to manage permissions. Azure AD already has “Enable sign-on and read user’s profiles” permission.ExistingPermissions
    • Click on “Add application” and select “OneNote” and give appropriate permission.OneNotePermission
    • In “Keys” area select 2 years. You will get a message that “The Key value will be displayed after you save it”. Make sure to copy this key value as this is client secret for your application. Also, copy the client id.Keys/ClientSecret
    • Now go to “Reply URL” and add a new one which will correspond to the address in which you will write the code to handle the return flow.ReplyURL
    • Click on “Save” and DON’T FORGET TO COPY KEY VALUE AND CLIENT ID. That’s it, your application is configured in Azure AD now.
  2. Access OneNote API in code
    • Open Visual Studio -> Click on “New Project” -> Select ASP.NET Web Application and give a proper name to your application. Click on OK.
    • In the next screen, select “Change Authentication” and click on “Organization Accounts”. Provide domain name of your O365 account as well as APP ID URI which you used while configuring the app in Azure AD.ChnageAuthentication
    • Click on “OK” and you will be asked to login to the O365 account. Login using the account you used while creating the entry in Azure AD and hit “OK”.
    • Your project will be set up. Open the web.config and you will notice tenant related entries. In the “appsettings” section enter the other details like client id and app key. You can directly use these in code or add them in web.config. I usually prefer web.configweb.config
    • Add nuget for ADAL in your project.ADAL
    • The code for accessing the OneNote API is given below. Please note, that here I am using AcquireTokenByAuthorizationCodeAsync method for authorization.

This is just basic code which will return all your notebooks. You can also create/edit the notebooks based on the permissions you provided to the application in Azure AD. You can change these permissions later.

My next step is to achieve this in multi-tenant app. As apparent from the code, the authorization URL contains the tenant URL which is not possible in multi-tenant apps. I will soon update the post for this scenario.

Lastly, I would like to thank Microsoft OneNote API team for their help while I was stuck in these issues. Apart from that, please go through Using ADAL’s AcquireTokenBy AuthorizationCode to Call a Web API From a Web App post, it explains the process in more detail.

Hope this helps. Happy coding!

Update Aug 4, 2015: For using OneNote API in multi-tenant app, go through WebApp-WebAPI-MultiTenant-OpenIdConnect-DotNet example. This is for graph API but works as it is for OneNote API too. A very good and self-explanatory sample.