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:
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:
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.