11. Malice and accidental damage

11.01 Introduction

If we make our structures and logic explicit, it’s far easier for human or system errors to be handled appropriately or avoided.

Some actions can still cause trouble to the system, such as:

• SQL Injection – adding malicious code into normal operations
• Privilege Escalation – a malicious agent gaining direct access to the database
• User Error – a user intends to do one thing but does something entirely different
• Non-confidential data sharing – confidential data being shared inappropriately

11.02 Security and user policies with SQL

Someone who is putting data into a database shouldn’t have access to change the structure of the database. There are multiple levels of privileges:

• Create, edit users
• Create, edit, use databases
• Create, edit, use tables
• Create, edit, use data

A user policy must be defined in advance if we want to avoid common pitfalls with regards to access control. A consideration should be given to whether a particular user needs separate roles.

The format for controlling access permissions is very simple:

GRANT privilege
ON resource
TO username;

For example, if we want user JonDoe to be able to SELECT data from tables Planets and Moons, we would write:

GRANT SELECT
ON Planets, Moons
TO JonDoe;

If JonDoe also requires permissions to INSERT data into these tables, the previous can be amended to:

GRANT SELECT, INSERT
ON Planets, Moons
TO JonDoe;

A DROP permission would allow JonDoe to delete those tables (you should grant this privilege with care):

GRANT SELECT, INSERT, DROP
ON Planets, Moons
TO JonDoe;

With the addition of WITH GRANT OPTION, we let JonDoe grant the same privileges to other users:

GRANT SELECT
ON Planets, Moons
TO JonDoe
WITH GRANT OPTION;

Privileges can be revoked with the REVOKE command:

REVOKE ALL
ON Planets, Moons
FROM JonDoe;

Roles can be created to streamline user access control. Instead of granting each permission to every relevant table, we can create a role and just assign users to that role. This
means that any user assigned to that role will have the exact same set of permissions.

For example, if we’re building a system for Astronomers, we may create a role Astronomer:

CREATE ROLE Astronomer;

And assign permissions to that role:

GRANT INSERT, SELECT
ON Planets, Moons
TO Astronomer;

Whenever a new Astronomer joins the team, we assign the username the Astronomer role:

GRANT Astronomer
TO JonDoe;

The goal of all of this is to grant users the minimum set of privileges required to carry out the intended use of the system, thus reducing impact of error or malice.

Tuesday 16 November 2021, 379 views

Next post: 12. SQL refresher
Previous post: 10. ACID: Guaranteeing a DBMS against errors