CRUD

How do you secure your data using CRUD?

19 October 2020 • 8 minutes

Written by Eban Escott

image for 'How do you secure your data using CRUD?'

There is a lot of data in the world. Current estimates put it somewhere between 4 and 5 zettabyte’s (1 ZB = 1,000,000,000,000,000,000,000 bytes). Geez! That is a lot. No doubt, you have been collecting some as well. Possibly in a database or even in a spreadsheet. In this article, we are going to look at how you can make this data available securely using modern software technologies.


Create, Read, Update, and Delete (CRUD) are the four basic operations that can be performed on data. So when talking about securing your data, talking about CRUD is the way to go. However, security can be tricky and it is important to get it right; if left unattended, all sorts of exposure can lead to unnecessary risks. Securing your data using CRUD is an excellent way to approaching a solution.

A use case we are seeing more and more, are people wanting to take an existing data set and make it available online. Data scientists and researchers want to do this to make their data more accessible. Internet of Things (IoT) providers want to do this to help connect their different applications. Actually, there are many different scenarios, and the drive for big data in the business world is fuelling this.

To make this data available securely should be top of mind. In this article, we are going to talk about how this can be achieved. But for us to answer this meaningfully we are going to start with the following more basic questions and build up:

We also going to continue using the movie application as a running example. For you reference, it is a pretty simple application and the following screenshot shows the entity diagram (data schema). There are movies, categories, and actors in the example.

Codebots movies application entity diagram

What is CRUD?

CRUD is worth stopping and thinking about because it is something you will find across many different parts of software. Given the following four statements; can you think of any other operations that can be performed on data?

I would suggest not, and if you can, it would be a combination of CRUD operations. The reason there is only these four operations, is that we are bound to the underlying hardware, and this is what we are provided with. Personally, I like it, as it is a way to orientate your understanding of what smart people are doing within their respective domains.

What is AAA?

Authentication, Authorisation, and Accounting (AAA) is an acronym used to broadly describe a security framework for controlling access to a software application’s data.

Authentication is the process of determining if someone or something is who they declare themselves to be. For example, you might authenticate yourself with a username and a password.

Authorisation is the process of determining if someone or something has access to a specific resource or data. For example, are you authorised to update your own personal information.

Accounting is the process of recording who has done what at a specific time. This creates an audit trail of information that can be used in computer forensics.

When it comes to software application security, this creates a nice framework that you can think about and ensure that you have your bases covered.

How do you implement AAA?

Most modern software frameworks will have security built in that you can take advantage of. For example, Java developers can use Spring Security for authentication and authorisation and then use Hibernate Envers for accounting. This is how we implemented AAA for SpringBot. However, for the rest of this section we are going to look closer at C#Bot and some features of the ASP.NET Core framework. You can still look at doing a manual implementation like this in your application, but C#Bot writes this code for you.

For authentication in C#Bot, we use ASP.NET authentication. This is the standard approach and we recommend using this is your application. In the following code snippet, you can see we use cookie-based authentication for login and logout functionality. When searching in your git repository, look inside the startup.cs file.

services.AddAuthentication("Identity.Application")
    .AddCookie(CookieAuthenticationDefaults.AuthenticationScheme, options =>
    {
        // % protected region % [Change AddCookie logic here] off begin
        options.LoginPath = "/api/authorization/login";
        options.LogoutPath = "/api/authorization/logout";
        options.SlidingExpiration = true;
        options.ExpireTimeSpan = TimeSpan.FromDays(7);
        options.Events.OnRedirectToLogin = redirectOptions =>
        {
            redirectOptions.Response.StatusCode = StatusCodes.Status401Unauthorized;
            return Task.CompletedTask;
        };
        // % protected region % [Change AddCookie logic here] end
    })

For authorisation in C#Bot, we use ASP.NET authorization. This is the tried and true approach used and we recommend this in your application. In the following code snippet, you can see one of the API endpoints from a controller. See on the first line where it says HttpPost, this annotation is for a POST requests so therefore is the create in CRUD.

[HttpPost]
[Route("")]
[Consumes("application/json")]
[Authorize(Roles = "Admin")]
// % protected region % [Override post attributes here] end
public async Task<MovieEntityDto> Post(
                    
    [BindRequired, FromBody] MovieEntityDto model,
    CancellationToken cancellation
)
{
    // % protected region % [Override Post here] off begin
    if (model.Id != Guid.Empty)
    {
        Response.StatusCode = (int)HttpStatusCode.BadRequest;
        return null;
    }
    return new MovieEntityDto(await _crudService.Create(model.ToModel(), cancellation: cancellation));
    // % protected region % [Override Post here] end
}

For accounting (or auditing) in C#Bot, we use Audit.NET. It is open-source and provides some pretty nifty features. To get it going we use a dynamic data providers and shown in the following code snippet. From there, we store the audit logs via the Entity Framework. Have a look at the documentation about an entity framework data provider.

Audit.Core.Configuration.Setup()
    .UseDynamicProvider(configurator =>
    {
        configurator.OnInsert(audit => AuditUtilities.LogAuditEvent(audit, logger));
        configurator.OnReplace((obj, audit) => AuditUtilities.LogAuditEvent(audit, logger));
    });

How does CRUD relate to AAA?

The best way to visualise the relationship between CRUD and AAA is using a security matrix. The following screenshot shows a security matrix from the Codebots platform and is a good reference for this discussion.

Codebots movie application security diagram

At the top of the screenshot, you can see there are two groups of columns; Admin and Visitors. Visitors are anonymous users that have not been authenticated. For each group (or role if you like), there are four columns, each column represents one of the four CRUD operations. In the example above, the Admin has access to everything.

The rows in the screenshot represent the data that the CRUD operations are available on. For example, the first row in the matrix is the Actor entity, the Admin has CRUD access to this entity and the Visitor has read (R) access only. Using a security matrix like this is an excellent way for you to lock down and have fine control over your data.

Summary

CRUD represents the four operation types that you can perform on any data. AAA is used to broadly describe a security framework for controlling access to a software application’s data. The best way for you to visualise the relationship is to use a security matrix! A security matrix makes it straight forward to lock down your data to authenticated people that are authorised to access it.

Beyond the relationship between CRUD and AAA, we have also looked at some code written by C#Bot for AAA. This bot uses the standard ASP.NET security framework to ensure the application’s data is secure. If you are interested in reviewing the code, no problem, it’s yours! Sign-up for the Platform and you can pull the source code from a git repository. One of the cool things about the bots’ code is that it looks the same as a developers, so you can do a code review without any vendor lock-in.

There are lots of other security related considerations beyond making sure that your CRUD operations are covered by AAA. For example, making sure you use SSL and that your sites are encrypted, i.e. use https and not plain http. There are also many considerations that have been produced through OWASP, the Open Web Application Security Project. On top of the application security you also have to consider your DevOps pipeline and make sure it is secure. And after that is even more! But before it gets too overwhelming … keep calm and CRUD-on!

Keep Calm and CRUD on with Codebots meme

Eban Escott

Written by Eban Escott

Founder of Codebots

Dr Eban Escott received his Doctorate from UQ (2013) in Model-Driven Engineering and his Masters from QUT (2004) in Artificial Intelligence. He is an advocate of using models as first class artefacts in software engineering and creating not just technologies, but methodologies that enhance the quality of life for software engineers.