Scenario: ExampleHMO using row and column access control - Data queries

Peter, Bob, Dr. Lee, Tom, Jane, and John each connect to the database and try the following SQL query:

SELECT SSN, USERID, NAME, ADDRESS, PHARMACY, ACCT_BALANCE, PCP_ID FROM PATIENT;

Results of the query vary according to who runs the query. The row and column access control rules created by Alex are applied on these queries.

Here is the result set Peter sees:

SSN         USERID    NAME      ADDRESS     PHARMACY    ACC_BALANCE PCP_ID

----------- --------- --------- ----------- ----------- ----------- -----------

  0 record(s) selected.

Even though there is data in the table and Peter is the database administrator, he lacks the authority to see all data.

Here is the result set Bob sees:

SSN         USERID    NAME     ADDRESS     PHARMACY    ACC_BALANCE PCP_ID

----------- --------- --------- ----------- ----------- ----------- ------
123-45-6789 BOB       Bob      123 Some St.XXXXXXXXXXX 0.00        LEE

1 record(s) selected.

Bob, being a patient, can only see his own data. Bob belongs to the PATIENT role. The PHARMACY and ACC_BALANCE column data have been hidden from him.

Here is the result set Dr. Lee sees:

SSN         USERID   NAME     ADDRESS     PHARMACY            ACC_BALANCE PCP_ID

----------- -------- -------- ----------- ------------------- ----------- ------
123-55-1234 MAX      Max      First Strt  hypertension        0.00        LEE
123-11-9856 SAM      Sam      Big Strt    High blood pressure 0.00        LEE
123-45-6789 BOB      Bob      123 Some St.codeine             0.00        LEE

3 record(s) selected.

Dr. Lee can see only the data for patients under his care. Dr. Lee belongs to the PCP role. The ACC_BALANCE column data is hidden from him.

Here is the result set Tom sees:

SSN         USERID   NAME     ADDRESS     PHARMACY    ACC_BALANCE PCP_ID

----------- -------- -------- ----------- ----------- ----------- -----------
123-55-1234 MAX      Max      First Strt  XXXXXXXXXXX 0.00        LEE
123-58-9812 MIKE     Mike     Long Strt   XXXXXXXXXXX 0.00        JAMES
123-11-9856 SAM      Sam      Big Strt    XXXXXXXXXXX 0.00        LEE
123-19-1454 DUG      Dug      Good Strt   XXXXXXXXXXX 0.00        JAMES
123-45-6789 BOB      Bob      123 Some St.XXXXXXXXXXX 0.00        LEE

5 record(s) selected.

Tom can see all members. Tom belongs to the membership role. He is not privileged to see any data in the PHARMACY and ACC_BALANCE columns.

Here is the result set Jane sees:

SSN         USERID   NAME     ADDRESS     PHARMACY            ACC_BALANCE PCP_ID

----------- -------- -------- ----------- ------------------- ----------- -------
XXX-XX-1234 MAX      Max      First Strt  XXXXXXXXXXX         0.00        LEE
XXX-XX-9812 MIKE     Mike     Long Strt   XXXXXXXXXXX         0.00        JAMES
XXX-XX-9856 SAM      Sam      Big Strt    High blood pressure 0.00        LEE
XXX-XX-1454 DUG      Dug      Good Strt   Influenza           0.00        JAMES
XXX-XX-6789 BOB      Bob      123 Some St.codeine             0.00        LEE

5 record(s) selected.

Jane can see all members. She belongs to the DRUG_RESEARCH role. The SSN and ACC_BALANCE column data are hidden from her. The PHARMACY data is only available if the patients have opted-in to share their data with drug research companies.

Here is the result set John sees:

SSN         USERID   NAME     ADDRESS     PHARMACY    ACC_BALANCE PCP_ID

----------- -------- -------- ----------- ----------- ----------- --------
123-55-1234 MAX      Max      First Strt  XXXXXXXXXXX 89.70       LEE
123-58-9812 MIKE     Mike     Long Strt   XXXXXXXXXXX 8.30        JAMES
123-11-9856 SAM      Sam      Big Strt    XXXXXXXXXXX 0.00        LEE
123-19-1454 DUG      Dug      Good Strt   XXXXXXXXXXX 0.00        JAMES
123-45-6789 BOB      Bob      123 Some St.XXXXXXXXXXX 9.00        LEE

5 record(s) selected.

John can see all members. He belongs to the ACCOUNTING role. The PHARMACY column data is hidden from him.