The where clause on a grant update command combines with the end user’s where clause in the select command to qualify the rows targeted for an update operation.
Use a column or column list with the grant ... update statement to filter rows that update only specific columns. This example allows all administrative and medical staff to update patients’ addresses, but restricts modifications to the medical status to only the patient’s physician:
grant update on patients (address, phone) to public grant update on patients (medical_status) where USER = primary_md to doctor_role
The update command raises a permissions error if any user other than one with doctor_role attempts to update a patient’s medical status. It also restricts the rows and columns that a doctor may update.
