CRUD Operations in SQL

Performing CRUD Operations in SQL: Create, Read, Update, and Delete

CRUD is a commonly used acronym in software and application development. The acronym describes the four basic operations that can be performed on a database: Create, Read, Update, and Delete.

As developers or database administrators, we are often tasked with creating user roles that carry different CRUD permissions.

Let’s explore how to perform CRUD operations in MySQL. We will first speak about how to perform CRUD operations on the table level, before also looking at operations on the database level.

For the purposes of this blog post, we are assuming that you are using MySQL Workbench as your MySQL GUI.

If you’re not using MySQL Workbench, don’t worry: the same principles apply to other database administration tools.

Implement CRUD Operations in a Web Application

Follow our FREE code-along tutorial and learn how to implement CRUD operations by building a web application on a MySQL database.

Build a CRUD App on a SQL Database
Get Started with Five's Online SQL CRUD App Builder For Free

What Are CRUD Operations in SQL?

CRUD refers to Create, Read, Update, and Delete. Assigning and managing CRUD permissions is a very common task in programming.

CRUD describes the four basic operations that a user can perform in a database. Users can

1. Create or insert new records into a table,
2. Read or select existing records from a table,
3. Update or modify existing records stored inside a table, or
4. Delete or remove existing records from a table.

Permissions in a CRUD App

CRUD permissions refer to the permissions granted to anyone having access to a database to perform all or part of the four basic operations on data: Create, Read, Update, and Delete. By assigning CRUD permissions, developers control and govern access to data stored in a database.

In SQL, CRUD permissions are often managed by granting or revoking permissions to specific users or roles. These permissions are typically set at the database level or on individual tables within the database.

It’s essential to carefully manage CRUD permissions in a web app to protect data from unauthorized access, modification, or deletion.

By granting the appropriate permissions to trusted users or roles and denying them to others, application developers ensure that only authorized users can interact with the data in the database. This approach helps to maintain data integrity, security, and privacy.

How to Perform CRUD Operations in MySQL Workbench

1. Creating Roles

Before we assign permissions or manage access, we must first define Roles. Roles are a collection of privileges. They save you from the tedious work of assigning CRUD permissions to individual users. Instead, individual users are assigned to Roles, and by default, they will be given the CRUD permissions that are associated with the role.

Typical roles include, for example:

• database administrators with full CRUD permissions,
• data analysts with partial access, typically READ only, or
• database developers with CREATE or ALTER access.

To create Roles, use this command:

CREATE ROLE 'database_administrator', 'data_analyst', 'database_developer';

In case you would like to delete a role instead, use DROP ROLE.

2. Assigning CRUD Permissions to Roles and Database Tables

In MySQL Workbench, CRUD operations can be performed on tables, or sets of tables. To manage CRUD permissions, MySQL offers granular access control features through its standard syntax. We’ve already looked at CREATE ROLE. So now, let’s move on and grant our roles permissions by using GRANT.

To assign CRUD permissions for tables, and to the records contained inside of them, developers can use the GRANT statement.

The GRANT statement allows administrators to grant specific privileges to users or to roles on specific database objects. Permissions can be assigned in point-and-click by using Workbench’s Role Editor.

To assign CRUD permissions in MySQL Workbench, follow these steps:

1. Connect to your MySQL database using MySQL Workbench.
2. Navigate to the “Server Administration” tab and select the “Users and Privileges” option.
3. Select the user or role to whom you want to assign CRUD permissions.
4. Click on the “Schema Privileges” tab and select the database schema where the table resides.
5. Find the table for which you want to assign CRUD permissions, and select the checkboxes for the appropriate privileges: INSERT, SELECT, UPDATE, and DELETE. Note that
6. Click “Apply” to save the changes and assign the CRUD permissions to the user or role.
7. Once equipped with the right set of permissions, different roles can perform CRUD operations in SQL.

Alternatively, you can also assign CRUD permissions using SQL commands. For example, to grant a user permission to perform all CRUD operations on a specific table in a MySQL database, you can use the following command:

GRANT SELECT, INSERT, UPDATE, DELETE ON database.table TO 'database_administrator'@'localhost';

This command grants the user “database_administrator” permission to perform all four CRUD operations on the table named ‘table’ in the database named ‘database’.

The ‘@’ symbol followed by ‘localhost’ indicates that the permission applies only to connections made from the same computer as the MySQL server.

Note how we have used the SELECT, INSERT, UPDATE, and DELETE operations in SQL. Why doesn’t MySQL use CREATE, READ, UPDATE, and DELETE to describe these operations?

That’s because CREATE is already reserved for another purpose. Let’s look at this next.

3. CRUD Operations for Databases and Tables

Why does the GRANT statement not include CREATE and READ? Well, this is a bit of a trick question, because there is no READ operation in MySQL. There is, however, a CREATE statement.

The correct syntax of the CREATE statement is this: CREATE DATABASE. It is used to create a new SQL database, and not to create database records.

So, on a database level, the syntax is as follows:

• CREATE: used to create a database like so: CREATE DATABASE
• READ: This only applies to the table level.
• UPDATE: ALTER TABLE. Altering a table could, for example, mean adding or deleting a field inside a table. This is different from the UPDATE statement above, which only updates an existing record inside a table.
• DELETE: DROP DATABASE or DROP TABLE

Conclusion

Let’s say you are a restaurant owner and you’d like to make sure your staff can enter the kitchen, but your guests can only stay in the dining room. How would you solve this problem? Easy! You have two roles: staff and guest. Each role has its own permission to enter different parts of the restaurant.

This is what CRUD permissions do for databases: they are used to govern access. By using CRUD operations in SQL, developers can make sure that data stays safe and is only accessed as intended. CRUD permissions are a useful tool in a developer’s access control toolbox!

To build CRUD web applications on a MySQL database, check out Five. Five is an easy way to manage a MySQL database, as well as its front end all from one platform. To sign up for a free trial, click here!