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';

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

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