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.
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
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.
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:
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
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
To assign CRUD permissions for tables, and to the records contained inside of them, developers can use 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:
DELETE. Note that
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.
Why does the
GRANT statement not include
READ? Well, this is a bit of a trick question, because there is no
READ operation in MySQL. There is, however, a
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:
ALTER TABLE. Altering a table could, for example, mean adding or deleting a field inside a table. This is different from the
UPDATEstatement above, which only updates an existing record inside a table.
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 download, click here!