Scenario: ExampleHMO using row and column access control - Database users and roles

In this scenario, a number of different people create, secure, and use ExampleHMO data. These people have different user rights and database authorities.

ExampleHMO implemented their security strategy to classify the way data is accessed from the database. Internal and external access to data is based on the separation of duties to users who access the data and their data access privileges. ExampleHMO created the following database roles to separate these duties:
PCP
For primary care physicians.
DRUG_RESEARCH
For researchers.
ACCOUNTING
For accountants.
MEMBERSHIP
For members who add patients for opt-in and opt-out.
PATIENT
For patients.
The following people create, secure, and use ExampleHMO data:
Alex
ExampleHMO Chief Security Administrator. He holds the SECADM authority.
Peter
ExampleHMO Database Administrator. He holds the DBADM authority.
Paul
ExampleHMO Database Developer. He has the privileges to create triggers and user-defined functions.
Dr. Lee
ExampleHMO Physician. He belongs to the PCP role.
Jane
Drug researcher at Innovative Pharmaceutical Company, a ExampleHMO partner. She belongs to the DRUG_RESEARCH role.
John
ExampleHMO Accounting Department. He belongs to the ACCOUNTING role.
Tom
ExampleHMO Membership Officer. He belongs to the MEMBERSHIP role.
Bob
ExampleHMO Patient. He belongs to the PATIENT role.

If you want to try any of the example SQL statements and commands presented in this scenario, create these user IDs with their listed authorities.

The following example SQL statements assume that the users have been created on the system. The SQL statements create each role and grant SELECT and INSERT permissions to the various tables in the ExampleHMO database to the users:

--Creating roles and granting authority

CREATE ROLE PCP;

CREATE ROLE DRUG_RESEARCH;

CREATE ROLE ACCOUNTING;

CREATE ROLE MEMBERSHIP;

CREATE ROLE PATIENT;

GRANT ROLE PCP TO USER LEE;
GRANT ROLE DRUG_RESEARCH TO USER JANE;
GRANT ROLE ACCOUNTING  TO USER JOHN;
GRANT ROLE MEMBERSHIP TO USER TOM;
GRANT ROLE PATIENT TO USER BOB;