GRANT and REVOKE
Decide who may read or change each table, and take access away when it's no longer needed.
Lesson 31 of 33 · about 14 minutes
The lesson
A real bookshop database is used by many people and programs: the website, warehouse staff, a data analyst, and an administrator. Not all of them should be able to do everything. The analyst needs to read sales figures, but has no reason to delete customers.
DCL (Data Control Language) decides who can do what. Think of it as handing out keys in an office building: everyone gets keys to the rooms they need, and no more.
GRANT gives a permission (called a privilege) on a table:
GRANT SELECT ON books TO analyst;
GRANT SELECT, UPDATE ON books TO warehouse;
REVOKE takes it away:
REVOKE UPDATE ON books FROM warehouse;
The privileges match the commands you already know: SELECT, INSERT, UPDATE and DELETE.
Roles save work. Instead of granting privileges to 40 warehouse staff one by one, you create a warehouse role, grant the privileges to the role, and add each person to it. When someone changes job, you move them to a different role.
Give the least access that does the job. This rule is called least privilege. If the website's login is ever stolen, an attacker can only do what the website could do, which is far less harmful than full control.
About your practice database. It runs inside your browser and has no user accounts, so GRANT won't run here. You'd use it on a shared database server such as PostgreSQL, MySQL or SQL Server. Those databases keep a list of every grant, and your practice database has a copy of that kind of list, in a table called grants with three columns: role, table_name and privilege. Query it with what you've learned to find out who can do what.
- GRANT privilege ON table TO role gives access; REVOKE privilege ON table FROM role removes it.
- Privileges match the commands: SELECT, INSERT, UPDATE and DELETE.
- Grant to roles rather than individuals, and give the least access that does the job.