Granting and revoking access
Access control is a key aspect of database management. Not all database users have the same needs and goals, from analysts, clerks, data scientists, to data engineers. As a general rule of thumb, write access should never be the default and only be given when necessary.
In the case of our Pitchfork reviews, we don't want all database users to be able to write into the long_reviews
view. Instead, the editor should be the only user able to edit this view.
This exercise is part of the course
Database Design
Exercise instructions
- Revoke all database users' update and insert privileges on the
long_reviews
view. - Grant the
editor
user update and insert privileges on thelong_reviews
view.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Revoke everyone's update and insert privileges
REVOKE ___, ___ ___ ___ FROM ___;
-- Grant the editor update and insert privileges
GRANT ___, ___ ___ ___ TO ___;