How to secure data using stored procedures in SQL

A stored procedure is a set of SQL statements with an assigned name, which can be stored for later use so it can be reused and shared by multiple programs. Some of the benefits offered by stored procedures are:

• Security.
• Performance.
• Ease of use.
• Maintainability.
• Scalability.

In this article, I will show you how to secure data using a stored procedure. But before I do that, let us go through how stored procedures can be a secure and safe way to give access to your database.

Why are stored procedures useful?

Stored procedures can be used to allow access to some parts of a table in a database while denying direct select, insert, update and delete operations against the table. That means that someone cannot directly write SQL queries that do what they want to the database table.

Using stored procedures to limit access to a database makes the process easier to design and manage. This can be helpful in protecting sensitive data in a database.

Let’s say, for example, that your database has tables with financial data or any sensitive data like social security numbers. Giving direct access to these tables to anyone is not a good idea because someone can decide to commit fraud or steal personal information to commit identity theft. Disgruntled employees with access to the database can also decide to trash or delete some of your business-critical data.

These risks can be avoided by using stored procedures to define things that you want those who have access to the database to do. They give better control of permissions which means users can be limited to specific columns and rows in a table. Stored procedures can also provide an additional layer of protection against SQL Injection.

Creating and Calling a Stored Procedure

The general syntax for creating and calling a stored procedure is as shown below:

CREATE PROCEDURE procedure_name AS sql_statement GO;

And the resulting output is:

EXEC procedure_name;

The above process of creating and calling a stored procedure is just a general process. That means different databases have different ways of creating and calling a Stored Procedure.

In this article, I will be using PostgreSQL to create and call a stored procedure. But before creating and calling a stored procedure, let us create a table of employees that contains six columns.

The first column will be employee id, the second will be employee name, the third will be employee department, the fourth will be employee social security number, the fifth will be employee account number, and the last column will be employee salary. Below is the SQL query for creating the Employees table.

CREATE TABLE Employees (
   EmpNo integer,
   EmpName varchar(50),
   Dept varchar(50),
   SS_No integer,
   ACC_No integer,
   EmpSal integer
);

Now let's create a stored procedure that inserts values into the Employees table:

CREATE PROCEDURE Insert_Emp_Table (
   empno integer,
   empname varchar(50),
   dept varchar(50),
   ss_no integer,
   acc_no integer,
   empsal integer
   )
LANGUAGE 'plpgsql'
AS $$
BEGIN
INSERT INTO employees VALUES (empno, empname, dept, ss_no, acc_no, empsal);
COMMIT;
END;
$$;

The Employees table we created is empty but we can add data to the table by calling the stored procedure that we just created.

CALL Insert_Emp_Table(1, 'Bonface', 'WebDev', 23214, 42357, 10000);

Once you check the Employees table, you will find out that the values that you included when calling the stored procedure have been populated into the table.

Securing data using a Stored Procedure

As a database owner or database admin, how can you allow someone to only insert values to the Employees table without being able to do anything else?

You can do that by granting that user the permission to call the stored procedure we created above. Let us create a role or user called John with only login privileges. Then we will grant him permission to call the stored procedure that inserts values into the Employees table.

We can do that by running the SQL query below.

CREATE ROLE john WITH LOGIN PASSWORD 'john';

You can grant user john permission to call the stored procedure by running the following SQL query.

GRANT ALL ON PROCEDURE Insert_Emp_Table TO john;

When you log in as the user John and try to run any query that does anything to the Employees table, you will get an error that says permission denied for table employees.

That means that the user John cannot access any table in our database or do anything that we don’t want him to do as the database owner. The only way user John can access any table in our database is by calling stored procedures that we have granted them permission to call.

For example, as the user John, you can still insert values to the Employees table by running the SQL query below.

CALL Insert_Emp_Table(3, 'Brian', 'Writer', 36514, 97352, 9000);

And, as you can see from the screenshot below, the stored procedure call has succeeded because we gave him permission to call that stored procedure.

Now let us login back as the database owner and run the following query to view data in the Employees table.

SELECT * FROM employees;

You will find out that data was inserted into the employees table through the stored procedure call made by user John as shown by the screenshot below.

Conclusion

As seen from the process above, stored procedures are a secure and safe way to give access to your database. That means someone can only be able to do what is defined in stored procedures that you have given him permission to call. And that makes stored procedures great for securing data in a database.