Scenario: ExampleBANK using row and column access control - Database users and roles
In this scenario, a number of different people use ExampleBANK data. These people have different user rights.
ExampleBANK implemented their security strategy to classify the
way data is accessed from the database. Internal access to data is
based on the separation of duties to users who access the data and
their data access privileges. ExampleBANK created the following database
roles to separate these duties:
- TELLER
- For tellers of branch locations.
- TELEMARKERTER
- For telephone marketing and sales people.
- CSR
- For customer service representatives.
The following people use ExampleBANK data:
- ZURBIE
- A customer service representative at ExampleBANK. She belongs to the CSR role.
- NEWTON
- A teller at an ExampleBANK branch. He belongs to the TELLER role.
- PLATO
- A telephone marketing and sales person at ExampleBANK. He belongs to the TELEMARKETER 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 permission to the various tables in the ExampleBANK database to the users:
--Creating roles and granting authority
CREATE ROLE TELLER;
CREATE ROLE CSR;
CREATE ROLE TELEMARKERTER;
GRANT ROLE TELLER TO USER NEWTON;
GRANT ROLE CSR TO USER ZURBIE;
GRANT ROLE TELEMARKERTER TO USER PLATO;