sql / intermediate
Snippet
Managing Access Permissions with GRANT and REVOKE
Database security relies on the principle of least privilege. In standard SQL, GRANT bestows specific permissions on database objects to roles or users, while REVOKE removes existing permissions, ensuring tight control over data access and modification.
snippet.sql
sql
1
2
3
4
5
6
7
GRANT SELECT, UPDATE (email, phone_number)ON TABLE user_profilesTO analyst_role;REVOKE DELETEON TABLE user_profilesFROM analyst_role;
Breakdown
1
GRANT SELECT, UPDATE (email, phone_number)
Grants read privileges and restricts column updates to specific fields.
2
ON TABLE user_profiles
Specifies the target table for the privilege grant.
3
TO analyst_role;
Assigns permissions to the designated database role.
4
REVOKE DELETE
Specifies the permission to be explicitly withdrawn.
5
ON TABLE user_profiles
Specifies the target table for the privilege revocation.
6
FROM analyst_role;
Identifies the target role losing the specified permission.