Scenario: ExampleHMO using row and column access control - Data updates
While in the hospital, Bob gets his treatment changed. As a result his records in the ExampleHMO database need updating.
Dr. Lee, who is Bob's physician, advises a treatment change and changes Bob's medicine. Bob's record in the ExampleHMO systems must be updated. The row permission rules set in the ExampleHMO database specify that anyone who cannot view the data in a row cannot update the data in that row. Since Bob's PCPID contains Dr. Lee's ID, and the row permission is set, Dr. Lee can both view, and update Bob's record using the following example SQL statement:
UPDATE PATIENT SET PHARMACY = 'codeine' WHERE NAME = 'Bob';
Dr. Lee checks the update:
Select * FROM PATIENT WHERE NAME = 'Bob';
SSN USERID NAME ADDRESS PHARMACY ACCT_BALANCE PCP_ID
----------- --------- ------- ------------ ----------- -------------- ------
123-45-6789 BOB Bob 123 Some St. codeine 0.00 LEE
Dug is a patient who is under the care of Dr.
James, one of Dr. Lee's colleagues. Dr. Lee attempts the same
update on the record for Dug:
UPDATE PATIENT SET PHARMACY = 'codeine' WHERE NAME = 'Dug';
SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query
is an empty table. SQLSTATE=02000
Since Dug's PCPID does
not contain Dr. Lee's ID, and the row permission is set, Dr.
Lee cannot view, or update Dug's record.