sql / intermediate
Snippet
Granting Fine-Grained Table Privileges to Roles
Role-based access control enhances database security by aggregating database permissions into roles. Privileges like SELECT, INSERT, or DELETE can be selectively granted or revoked to enforce the principle of least privilege.
snippet.sql
sql
1
2
3
4
5
CREATE ROLE analyst_role;GRANT SELECT, INSERT ON employees TO analyst_role;REVOKE DELETE ON employees FROM analyst_role;
Breakdown
1
CREATE ROLE analyst_role;
Creates a new database role object for grouping security permissions.
2
GRANT SELECT, INSERT ON employees TO analyst_role;
Assigns read and append permissions on the employees table to the specified role.
3
REVOKE DELETE ON employees FROM analyst_role;
Explicitly ensures delete permissions are stripped from the analyst role for governance.