sql / intermediate
Snippet
Role-Based Table Access Control with GRANT
Security management in standard SQL relies on roles and explicit privilege assignments. Granting read-only privileges while revoking destructive permissions enforces the principle of least privilege across user groups.
snippet.sql
sql
1
2
3
CREATE ROLE analyst_role;GRANT SELECT ON sales_reports TO analyst_role;REVOKE UPDATE, DELETE ON sales_reports FROM analyst_role;
Breakdown
1
CREATE ROLE analyst_role;
Creates a logical database role used to group specific access permissions.
2
GRANT SELECT ON sales_reports TO analyst_role;
Grants read access on the sales_reports table to members of analyst_role.
3
REVOKE UPDATE, DELETE ON sales_reports FROM analyst_role;
Explicitly strips update and deletion privileges on sales_reports from the role.