Exercise

Implementing access control for teams

In this chapter, you learned how to control access to your databases. This brings us to the final exercise in the course. You will create a new schema and table for holding data that will be provided to data scientists on your team. While your team members are likely responsible individuals, accidents can happen. You should only give these team members as much control over the database as required to do their job. These team members will have access to data on loans that have not been approved.

  • The schema analysis will be created.
  • The table unapproved_loan will be defined in this new schema.
  • User data_scientist will be created.
  • The user will be restricted to reading from the new table.

This exercise will reinforce the basics of access control for a database.

Instructions

100 XP
  • Create a new schema named analysis which will hold our new table.
  • Create a new table unapproved_loan within the analysis schema with a PRIMARY KEY (id), a FOREIGN KEY (loan_id) referencing the id key of the loan table, and a description TEXT field representing why the loan was rejected.
  • Add a user named data_scientist with password changeme.
  • Restrict the user to have read-only access to the analysis.unapproved_loan table.