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;