Authorisation
- SQL course
- Expert
- Lesson 38/45
- 5 min
Manage access with GRANT and REVOKE.
Authorization
Authorization sets which user can run which action on which table. GRANT grants a privilege, REVOKE takes it back. Roles group privileges together.
GRANT SELECT, INSERT ON orders
TO reader;
REVOKE INSERT ON orders FROM reader;GRANT grants, REVOKE takes back.
The expression , INSERT ON orders TO reader is evaluated and returned.
Authorization — details
The least privilege principle: give a user only the privilege their job needs. SELECT for readers, INSERT/UPDATE for writers, ALL for admins. This shrinks the attack surface.
Practice Authorization
Give the reader role read access on orders: GRANT SELECT ON orders TO reader.
Starter code
-- authorization
GRANTSample solution
GRANT SELECT ON orders TO reader;Expected output
GRANT
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Authorisation: match the terms
| Term | Meaning |
|---|---|
GRANT |
grant |
REVOKE |
revoke |
role |
role |
SELECT |
read |
2 Grant permission
GRANT SELECT ON orders TO ____GRANT SELECT ON orders TO readerExplanation: GRANT SELECT ON orders TO reader gives the reader role read-only access.
3 What does GRANT SELECT give?
- All permissions
- Delete permission
- Write permission
- Read permission Correct answer
Explanation: GRANT SELECT gives read permission.