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.
This exercise is part of the course
Creating PostgreSQL Databases
Exercise instructions
- Create a new schema named
analysis
which will hold our new table. - Create a new table
unapproved_loan
within theanalysis
schema with aPRIMARY KEY
(id
), aFOREIGN KEY
(loan_id
) referencing theid
key of the loan table, and adescription
TEXT
field representing why the loan was rejected. - Add a user named
data_scientist
with passwordchangeme
. - Restrict the user to have read-only access to the
analysis.unapproved_loan
table.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Create the new analysis schema
CREATE SCHEMA ___;
-- Create a table unapproved loan under the analysis schema
CREATE TABLE ___ (
id serial PRIMARY KEY,
loan_id INTEGER REFERENCES loan(id),
description ___ NOT NULL
);
-- Create 'data_scientist' user with password 'changeme'
___ USER ___ WITH ___ '___';
-- Give 'data_scientist' ability to use 'analysis' schema
GRANT ___ ON ___ ___ TO ___;
-- Grant read-only access to table for 'data_scientist' user
___ SELECT ON ___ TO ___;